… is some cleanup thread that can do this under certain conditions) # ls -l /var/lib/mysql/ib_logfile* && date && mysql -BNe’show engine… sequence && sleep 60 && date && mysql -BNe’show engine innodb status\G’ | grep sequence && ls -l /var/lib/mysql/ib_logfile* -rw-rw—- 1 mysql mysql 536870912…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
… purge: in MySQL 5.5 there is an option to have a single separate dedicated purge thread and in MySQL 5.6.2 one can have multiple dedicated purge threads. Percona Server 5.1 supports multiple purge threads too…. 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: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | Info | +—-+———-+———–+——+———+——+———————————+———————————-+ | 1 | msandbox | localhost | test | Sleep | 403 | | NULL | | 3 | msandbox | localhost …threads can get piled up waiting for table metadata lock bringing down the MySQL…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though… queries and introducing “sleeps” between them can be a good idea. It also often helps with monopolizing replication thread. For example if…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… and run it in a loop with a 30-second sleep in between. Notice that it self-purges its own data…_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if [ "${host}" ]; then echo “Host…/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`date +%s`-innodbstatus pid=$! sleep 30 kill ${pid} fi…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… referring to the amazing MySQL manual, especially the option and variable reference table. …_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread_concurrencyblogpercona.commanual innodb_thread_sleep_delayblogpercona.commanual Innodb_truncated_status_writesblogpercona…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… | grep mysqld >> ps.log sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot…) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0) Threads 425080 (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
Post: InnoDB thread concurrency
June 5, 2006 By 4 Comments
…_thread_sleep_delay and innodb_concurrency_tickets. I’ll try to explain how it works. MySQL has pluginable architecture which divides work between mysql… (entered_thread < innodb_thread_concurrency) { entered_threads++; thread->n_tickets_to_enter_innodb = innodb_concurrency_tickets; ENTER; } if (innodb_thread_sleep_delay > 0) { thread_sleep(innodb_thread_sleep_delay); } goto…
Post: Impact of the number of idle connections in MySQL
December 17, 2010 By 21 Comments
…. I was expecting an influence, because those idle threads are presents in internal MySQL list objects that need to be scanned and….php $numconn & cat dbt2.sql | mysql -h 10.2.2.129 -u root dbt2 sleep 120 ./run_mysql.sh -h 10.2.2…%. I also hacked a bit the “run_mysql.sh” script to allow more processing threads (100) instead of the max of 20…
Comment: Debugging sleeping connections with MySQL
March 9, 2009 By 30 Comments
Are you sure that sleeping threads are a problem? I always thought that a thread showing a Command value of “Sleep” just meant that… for those threads is how long it’s been sitting idle. The MySQL docs seem to say the same thing: “Sleep – The thread is waiting for the client to send a new statement to it.” From: http://dev.mysql.com/doc…
