…table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql… localhost | dumptest | Field List | 15 | Waiting for table flush | | 0 | 0 | 4 | | 10305 | root | localhost | dumptest | Query … check for situation like above and either kill FLUSH TABLES …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… during EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was … less than 50ms. Low values, such as 3,4 provided a bit better performance but I decided …conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test… id 19 page no 4 n bits 80 index `age` of table `test`.`t` trx id …gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Upgrading MySQL
January 5, 2010 By 23 Comments
…over years is change of TIMESTAMP output format in MySQL 4.1 or changing JOIN evaluation in 5.0 (which…run SELECT queries on both new and old MySQL installations and check result set, explain plan and execution time, …made it to caught up you can use mk-table-checksum to ensure data is the same. It …
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… done to the core of original implementation which appeared in MySQL 4.0 Query Cache was designed to work with single core… for given table. Instead we could track versions for tables (increment each time table is updated) and check whenever version matches for all tables on… and operation can be presented as some INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache causes not uniform performance as you get…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…already) 7, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353 InnoDB: …corrupted table I am getting: mysql> check table test; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> check table test; +———–+——-+———-+———-+ | Table | Op |…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… we’re speaking about 4 years worth of changes in versions. We also ran mysql_fix_privilege_tables to ensure all new privileges… could use. First we ran the check using default CHECKSUM TABLE algorithm. We got number of tables reporting wrong checksums while running SELECT… version – upgrade from MySQL 4.1 to 5.0 had a lot of different issues than upgrade from MySQL 5.0 to 5…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time it… use tiny script which moves out all MyISAM tables out of MySQL database directory, checks them with MyISAMchk and moves them back to… unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was added…
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… NULL, `ol_delivery_d` datetime default NULL, `ol_quantity` tinyint(4) default NULL, `ol_amount` decimal(6,2) default NULL, `ol… finished quick check mysql> select count(*) from order_line; +———-+ | count(*) | +———-+ | 32093604 | +———-+ 1 row in set (3 min 29.32 sec) New table has…
