… performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some… queries and introducing “sleeps” between them can be a good idea. It also often helps with monopolizing replication thread. For example if…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL…see “VSZ” going down. What you might see instead…causes excessive memory usage you might look at how it uses MySQL to identify potential causes…128560) Recovery system 0 (0 + 0) Threads 425080 (406936 + 18144) Dictionary…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
…caused by the number of dedicated purge threads…MySQL 5.6.2 multiple purge threads have major issues and just removing the purge sleeps is not enough to solve them. However, MySQL…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…causing the trouble. If you’re in pre-5.1 or non-plugin InnoDB versions, you can’t reliably figure out what… it in a loop with a 30-second sleep in between. Notice that it self-purges its …INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if [ "${host}" ]; then echo…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…deadlock Innodb shows transactions which caused deadlocks, their state during deadlock, what locks they were holding and what they were waiting for, …time thread waits before it enters the queue. This is done by adjusting innodb_thread_sleep_delay variable. Value is specified in microseconds. mysql…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…much information. This can cause the disk to fill… on flights, and what airline offered those … 2010′; mysql> use ontime1; Database changed mysql> show table status…thread-concurrency=0 innodb-thread-concurrency-timer-based innodb-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads…
Post: Living with backups
November 6, 2008 By 28 Comments
…, just as the archiving process does when it runs, it causes a huge number of requests being pushed down to the… operation from MySQL data directory will result in a total disaster after MySQL stops responding to the incoming queries quick enough. What can…) { last; } sleep(1); } What this script does is to check MySQL status every second and in case the number of connected threads goes above…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…with the very large transaction caused by the single statement…. transactions’ changes too; the purge thread cannot purge them, so …-e /tmp/my-fifo ]; do time mysql -e “set foreign_key_… terminated by ‘\n’ (col1, col2);” sleep 1; done Note that…-bound server and see what the performance impact is,…
