… | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost… | 2359297 | | 10290 | root | localhost | NULL | Query | 681 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost… can consider having scripts which would check for situation like above and either kill FLUSH TABLES WITH READ LOCK and fail backup…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
… into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case… be executed during EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use… are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see… to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… get different values, because some other transaction has modified the table‘s rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM… 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD…. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: How fast is FLUSH TABLES WITH READ LOCK?
February 24, 2012 By 28 Comments
FLUSH TABLES WITH READ LOCK; also tries to close open files that obviously takes time too. I did not double check it… really a way to take online backups of MyISAM tables without issuing FLUSH TABLES WITH READ LOCK command and/or setting flush… OS buffers and getting flushed to disk after the FLUSH TABLES WITH READ LOCK command returns? If it still exists in…
Comment: MySQL Query Cache
March 21, 2012 By 69 Comments
… still a single query that contains multiple joins with four tables uses “checking query cache” state even though the SQL SELECT statement… strange thing is that it only happens for these four tables. Would there be any reason for it.
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO_INCREMENT, a varchar… NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index… see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It…
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
…/Ubuntu is the /etc/mysql/debian-start and the ‘check_for_crashed_tables‘ function (found in /usr/share/mysql/debian-start.inc… select from information_schema. On an instance having thousands+ of tables it has to be disabled in order to not let…
