…MySQL uses a lot of different timeout variables at different stages. For example when connection is just being established connect_timeout is used. When server waits for another query to be sent to it wait_timeout (or interactive_timeout… increasing net_write_timeout could be the good idea. …
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…transactions could block and fail with a lock wait timeout. The problem is, it can be very …. You can guess, and sometimes that’s good enough. Just pull the oldest transaction that… port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…overly full. Too many deadlocks. Deadlocks (and lock wait timeouts, covered in another plugin) are frequent causes of…a system reboot. The pmp-check-mysql-pidfile plugin verifies that MySQL‘s PID file exists. Evidence …uses a threshold for which there is no good one-size-fits-all value. This plugin, …
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
… 4096 updatable-views-with-limit YES userstat FALSE verbose TRUE wait-timeout 28800 Which is however rather ugly and only works with…_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; +—————————+ | @@global.sort_buffer_size | +—————————+ | 2097152… might be good idea to extend SELECT syntax to ease querying of variable global value Right now I can select: mysql> select…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… 1205 (HY000): Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting…is a good source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… size 368, 1 row lock(s), undo log entries 1 MySQL thread id 1720, query id 1011283 10.10.10.10… lock the same rows it will have to wait… possibly until innodb_lock_wait_timeout elapses and when it completes with error. Second… rollback transactions when they are recycled it might be good idea to set wait_timeout to some reasonable value, say 60 seconds. In…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…-size=16M key-buffer-size=64M local-infile=on lock-wait-timeout=300 log-error=/var/log/mysqld-innodb.log max-allowed…-length=16K #we value throughput over response time, get a good plan optimizer-prune-level=0 partition=ON port=3306 read…=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql wait-timeout=86400 To be continued You can now set up a…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… worry about replication delay However, good engineers know there is always a … will wait for that lock before proceeding (this is where deadlocks and lock wait timeouts … 0 rows affected (0.02 sec) node2 mysql> show create table autoinc\G *************************** 1. row ***************************…
Post: Configuring MySQL For High Number of Connections per Second
January 6, 2012 By 23 Comments
…system size to allow MySQL to handle spikes. Although MySQL connections are quite fast…up to 500ms stall which is good enough for most cases, so …have a large number of connections waiting on connection instead of failing quickly… fixed by setting the client connect timeout setting (which not a lot of …
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…disable them. The MySQL Settings are probably what is the most interesting: MySQL 5.0 Tuning …flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 300 innodb_thread_concurrency = 0 innodb_sync_spin…: transaction-isolation = READ-COMMITTED – this is indeed good setting for many workloads so unless …
