… 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache,… being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… look at what the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as… no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist… 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
… be executed during EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use… somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as… backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select …/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET …
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: InnoDB's gap locks
March 27, 2012 By 14 Comments
…), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx…. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index… MySQL to use an index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if…
Comment: How fast is FLUSH TABLES WITH READ LOCK?
February 24, 2012 By 28 Comments
… not double check it using lsof etc but the post http://www.geeksww.com/tutorials/database_management_systems/mysql/installation/mysql_tablecache_informationschema… really a way to take online backups of MyISAM tables without issuing FLUSH TABLES WITH READ LOCK command and/or setting flush…
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
… for MySQL? The most annoying for me part in Debian/Ubuntu is the /etc/mysql/debian-start and the ‘check_for_crashed_tables‘ function (found in /usr/share/mysql/debian-start.inc.sh) which, among other things, does nasty select from information_schema. On an instance having thousands+ of tables…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… have my website develop in PHP with MYSQL, these are MYSQL values: datadir=/data/mysql socket=/data/mysql/mysql.sock max_connections = 600 wait_timeout…_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql/drbd expire… and I have to re-restart the server. I’ve checked the security aspect and not an attack, will have any…
