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:51Quote: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...

lex0r wrote on Wed, 05 January 2011 17:11Ah yes, a pretty common problem when InnoDB has poor performance.

How big is your database anyway?
32M still sounds like very small.
Set it to the size of your DB or about 75% of the available RAM on the machine whichever comes first.

Your case can be done without FOR UPDATE. Update the rows to mark them as belonging to a particular thread, but only if they are not already owned. THEN COMMIT THE TRANSACTION and continue to work on those rows. If you don't commit, you'll cause blocking just as surely as SELECT FOR UPDATE will.

I have rewritten dozens of these types of scenarios. They are always possible without FOR UPDATE. FOR UPDATE is the root of a lot of evil.

sterin71 wrote on Wed, 05 January 2011 18:49I'm a bit curious about gradual performance improvement . So I put it to 128M and will compare performance to previous one. In fact the main InnoDB table (other tables are not under high-load) has ~500 MB. May be I should use this value?

xaprbFirst, let me explain the procedure.
1. Start transaction. Select a range of unlocked rows (we do select because we need the information) - I use SELECT FOR UPDATE here. I don't know the range, I find it after selection, and I use it when performing further updates and deletes.
2. Mark them as "locked" to ensure no other thread will take them. No need to place a thread id here, I already know the range and it's locked for others.
Commit transaction.
3. Process the rows.
4. Delete the rows.

As I understand you propose the following:
1. Start transaction. Update a range of unlocked rows by marking as belonging to a thread and setting status to "locked", using where and limit clauses like in SELECT FOR UPDATE scenario. Commit transaction.
(btw, do we need to explicitly start transaction?)
2. Now, simply select the rows by thread marker.
3. Process the rows
4. Delete the rows.

By doing this way we will evade X lock caused by SELECT FOR UPDATE, but the UPDATE we perform requires an X lock anyway. So, what's the difference, why the performance is better in the second variant?

I wrote too quickly before -- you need to do it in auto-commit mode for best results. The difference is the network round-trip time and the application's "think" time.

xaprb wrote on Thu, 06 January 2011 17:00Hm, still don't understand how the second way may reduce this time...
We do the same number of requests.

lex0r wrote on Thu, 06 January 2011 17:06Yes, but the lock is held for a lot shorter time so that you don't affect the other client threads.

Your solution:

BEGINSELECT ... FOR UPDATE <-- Lock is acquired here(network time, roundtrip to your application, etc)UPDATE rows to be "locked"COMMIT; <-- Lock isn't released until here

That's a lot of time that these rows are locked.

In Barons proposal you just update the rows directly without the transaction:

UPDATE ... WHERE ... <-- Lock is acquired and _released_ here


And the time the rows are locked is actually even less than what it looks like in this example.
Because the actual lock on the table is just a part of the entire execution of the query in scenario 2.
Since a lot of the execution of the query is parsing, deciding on execution plan, etc, then a short lock and perform the changes on the table, and then returning to post process and send the result to the client.
When you involve the network time, application time, a second query, etc in the lock time it increases a lot.



Compare with a scenario where you want to insert a record only if it doesn't already exist, you've got two choices:

BEGINSELECT ... -- Check if it exists or not by locking the tableINSERT ... -- in case select returned that it didn't existCOMMIT


Other choice with a proper unique index in place and auto commit on:

INSERT ... -- just insert, no checking in advance,-- instead check the result of the query, did you get an error? How many rows was inserted? That status tells you everything that you need to know, if a record was inserted or not

Hmm, the example didn't really turn out as good as I thought.

But the point is that with an UPDATE you can essentially do all the same conditions that you can have in the SELECT and by just performing the query and then check the result you know if the query went well, and how many rows that was affected, etc.

No need to check first and execute later,
execute first and check later is faster (but not always in line with parents teaching i presume ) .

Sterin71, xaprb,
thank you very much for your assistance, this forum is definitely the best place to get MySQL help!

Hello, I'm back with some interesting real-life results.
I rewrote the code without FOR UPDATE (UPDATE LIMIT, then SELECT) and performance became much worse
I was able to run 20-24 parallel queries of 200, the others just died because of exceeding lock wait timeout.
When I switched back to previous version (SELECT FOR UPDATE) things improved, only 5-10 threads of 200 died because of lock waits...
What was wrong?

Btw, my current MySQL settings are:

innodb_buffer_pool_size = 512M
innodb_thread_concurrency = 16
innodb_thread_sleep_delay = 0
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit=2

Guys, any ideas?

Sorry for the delay, I've been a bit swamped the last week since I swapped out an old firewall yesterday replacing it with new of different model and brand. A bit of a hassle as usual to get all routing and firewall rules correct.

What does SHOW ENGINE INNODB STATUS look like? Attach the output as a .txt to this thread.

BTW what is your innodb_log_file_size set to? The default 5M is very small, set it to about 64M. It reduces the occurrence of flushing which happens when it switches log file. BTW the trick here is that you need to perform a clean shutdown, move the old innodb log files, change the setting and start MySQL again at which point the new log files are being created automatically. The procedure is documented at the bottom of this page in the manual.

innodb_log_file_size has the default value of 5M. I will make it larger a bit later.
And, I added the file you asked - with SHOW ENGINE INNODB STATUS, but it was taken during moderate load.