Dear colleagues,
I’ve created an application that uses multi-threading for parallel processing
of data located in an InnoDB table. Each thread makes SELECT FOR UPDATE, UPDATE and DELETE queries. Sometimes I get deadlocks, but more often I receive errors about exceeding lock wait timeout.
For example, 150 threads may run without any issues, but may get blocked because of waiting too much in query queue, or exceeding the lock wait timeout
(50 seconds by default for InnoDB).
I need to understand what exactly causes these issues.
First idea was parallel INSERT queries, that are performed as well on the table.
But after enabling global query log and analyzing it I came to conclusion that inserts are not guilty. In fact during these locks no heavy queries are running on the table, so, what else can it be?
What setting do you have on innodb_flush_log_at_trx_commit?
If it’s 1 (default) and you are running on a machine without RAID card with write cache then the rotation speed of the disk limits you to about 100-150 inserts/updates/deletes.
Change it to:
innodb_flush_log_at_trx_commit = 2
to get better performance (note that it will not be as robust in case of a power failure, but most people can live with that).
What is your server doing during this time? High CPU? High IO?
And so that we can know better what your InnoDB does you should attach the output from:
show engine innodb status \G
executed when you are running all these connections.
Rewrite without SELECT FOR UPDATE.
Sterin, thanks for your reply, I have innodb_flush_log_at_trx_commit = 2.
In fact I was able to solve the problem by settings
innodb_buffer_pool_size=32M
since it was 8 by default that was in practice not enough to run without lock waits and deadlocks…
Besides, I used the following important settings
innodb_thread_concurrency = 16
innodb_thread_sleep_delay = 0
sterin wrote on Wed, 05 January 2011 14:51
Quote:
It is not possible in my case - I use many threads that run selects without strict range (only limit clause) so that each threads has to compete with others to fetch necessary rows…