…INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…Created_tmp_tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table…index optimization Analyzing and optimizing a server’s table and index…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and not too… is create another table with desired table structure and switch .frm table definition files. For safety, I’d recommend to flush tables with read lock while…) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table_new…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
…this as a result of how pt-table-checksum/pt-table-sync calculate their checksums. Lets look …h=192.168.1.46,p=…,u=percona lock:1 transaction:1 changing_src:percona.checksums … `date_created` + 0, CONCAT(ISNULL(`data`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `foo`.`bar` FORCE INDEX (`PRIMARY`) …
Post: Another scalability fix in XtraDB
January 23, 2009 By 13 Comments
… SELECT name FROM sbtest WHERE country_id = ? LIMIT 5 against table CREATE TABLE IF NOT EXISTS sbtest ( id int(10) unsigned NOT NULL… KEY (id) ) CREATE INDEX email_idx on sbtest(email); CREATE INDEX country_id_idx on sbtest(country_id,state_id,city); with table size 1mil… is used as mutex even in places where shared read lock is enough – and we replaced page_hash mutex to page…
Post: Looking for InnoDB/XtraDB hacker
October 25, 2011 By 3 Comments
…-flushing/ Fix InnoDB index locking Figure out InnoDB adaptive locking issue, e.g: http://www.mysqlperformanceblog.com/2010/02/25/index-lock-and-adaptive-search-next-two-biggest-innodb-problems/ Make some InnoDB tables non-transactional 
Make InnoDB page size setting per tables… InnoDB tables (e.g: disk or SSD) Make InnoDB just in general faster on SSD, so we do need to create several…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
…lock or shutdown server. To export table you need XtraBackup, and you can just specify table or table…create empty table with the same table definition as on old servers: CREATE DATABASE “testimport”; USE “testimport” CREATE TABLE… started. InnoDB: import: 2 indexes are detected. InnoDB: Progress in …
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… neither system table space nor log files, so it will create empty ones… stop application or lock tables: mysql> flush tables with read lock; Query OK, 0 rows…): ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free list …
Comment: Pitfalls of converting to InnoDB
March 28, 2007 By 16 Comments
… clients (A, B) and the following sequence of statements. A – create table ed (id int unsigned, name varchar(10)) engine=innodb; A… implicit transaction of an insert/update as well as the index locks (especially foreign keys) acquired. Also, don’t forget that summary… like “update parent set column = column + 1 where primary_key = ##” lock parent’s row until the original statement is completed. Moral…
Comment: How much memory can MySQL use in the worst case?
February 19, 2009 By 17 Comments
… and/or rent. We are currently using MySQL and MyISAM tables and we keep running out of memory so the server… server runs) Created_tmp_disk_tables 9,208 Select_full_join 1,074(the application is using indexes to join the tables so this puzzles me) Sort_merge_passes 2,602 Opened_tables 1,500 Table_locks_waited 74…
