… the output of SHOW PROCESSLIST that might be a sign that trouble is brewing. Or if you watch your MySQL server’s memory… the MySQL world, except maybe MyISAM files on top of a clustered filesystem with an external lock manager. You might say that MySQL…: Incorrect information in file: ‘./mysql/servers.frm’ ERROR: 1033 Incorrect information in file: ‘./mysql/servers.frm’ Running mysql_install_db under strace, I…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… the Meta Data Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5… | localhost | test | Query | 0 | NULL | show processlist | | 6 | msandbox | localhost | test | Query | 229 | Waiting for table metadata lock | ALTER TABLE test_2 DROP…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… its thread ID. Match this up with the ID in SHOW PROCESSLIST, and look in the Host column; there you’ll see… free)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information… the resulting tcpdump file (after inspecting the captured SHOW INNODB STATUS and SHOW PROCESSLIST to make sure you haven’t caught a false…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 30898, query… if LOCK TABLES was used. In addition to Innodb specific information, there is generic statement information which is visible in SHOW PROCESSLIST showed in SHOW…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…show_privilegesblogpercona.commanual Com_show_procedure_codeblogpercona.commanual Com_show_procedure_statusblogpercona.commanual Com_show_processlistblogpercona.commanual Com_show_profileblogpercona.commanual Com_show_profilesblogpercona.commanual Com_show…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…-mysql-pidfile plugin verifies that MySQL‘s PID file exists. Evidence of contention in the processlist. The pmp-check-mysql-processlist plugin checks SHOW FULL PROCESSLIST…connections all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped. Our plugins …
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
…mysql> show processlist; …lock | | 6 | root | localhost | test | Field List | 35 | Waiting for table | | | 7 | root | localhost | test | Query | 0 | NULL | show processlist…
Post: More Gotchas with MySQL 5.0
July 27, 2007 By 23 Comments
… lot of queries pile up waiting on locked innodb buffer pool mutex, so SHOW STATUS shows high number of running threads. This theory can be confirmed by the fact using mysqladmin processlist | grep -v Sleep shows much smaller value which is close in MySQL 5.0 and 4.1…
Post: Debugging sleeping connections with MySQL
February 8, 2007 By 30 Comments
Have you ever seen connection in the SHOW PROCESSLIST output which is in “Sleep” state for a long time … in the “Host” filed of SHOW PROCESSLIST output not only host but also port is specified, showing you something like “192.168.1… helpful to debug sleeping connections with MySQL but many other cases when you see web application locking up or starting to runs…
