… TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table…mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… 10 years, with very little changes done to the core of original implementation which appeared in MySQL 4.0 Query Cache was… queries cache 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… generated, wait for completion and when simply use it. More Granular Invalidation Can we get something less coarse than per table invalidation ? It…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…change_dbblogpercona.commanual Com_change…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…ON | OFF | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE | 10 | 5 | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000 | …2M. This change should help many small sorts for which allocation of 2M for sort buffer was…MySQL 5.6 innodb_file_per_table is now ON by default. This is very big change…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for…mysql# And restart MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…for MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL…table change. Peter’s advice in the miss storm blog post suggests that for best performance one should pre-compute the data for… to do the computation while others wait for the result. There are also …
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE TABLE…Changed: 1 Warnings: 0 node1 mysql> SHOW ENGINE …waiting for our first transaction, but it does not. Let’s go back to node1 and see what happened: node1 mysql…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…MySQL will not write into slow log any queries executed by the slave’s SQL thread. This patch allows you to change…wait is the time (in seconds) query spent either waiting to enter the InnoDB queue or inside that queue waiting for…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
…) for table2 table. It also applies to similar tables with where clause and joins. It is important for tables…for_binlog=1 as well. This is done so enabling binary log does not cause unexpected changes…mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1 MySQL…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
…Waiting for query cache lock | 0.000004 | | Waiting on query cache mutex | 0.000002 | | Sending data | 0.003407 | | Waiting for query cache lock | 0.000003 | | Waiting…for the lock. Of course, the question was: why did MySQL…for a query, you should suspect either some change…
