concurrent_insert feature understanding

Hi, peter

I happened to find your “Optimizing MySQL on source code level” slides for mysql conference 2006 ( http://www.mysqlperformanceblog.com/files/presentations/UC20 06-Optimizing-MySQL-on-Source-Code-Level.pdf ). In your slides, you talked about key_root_lock rwlock that cause the bad scaling of MyISAM. And by disabling concurrent_insert feature, the scaling is improved.

I recently used standard sysbench (read only mode, 14 reads) to test MySQL5.1.22. I found that the thread scaling is ~1.52x from 2 threads to 4 threads (maybe acceptable for some guys) with concurrent_insert enabled. While with concurrent_insert disabled, the thread scaling improved to ~1.9x from 2 threads to 4 threads. Wow, this scaling 1.9x is what I want. The test environment is 2 x Intel XEON Dual-Core processors and SuSE10 (2.6.16.46-0.12-smp).

From my understanding, disabling concurrent_insert feature is only beneficial to read-intensive application. However for real read/write mix applications, I think still need to enable concurrent_insert feature. Do you know how much benefit concurrent_insert could give?

Thx, Xuekun

Even though I’m not Peter I will try to answer your question to the best of my abilities (sorry Peter if I take your job )).

[B]Xuekun wrote on Thu, 06 December 2007 13:09[/B]
From my understanding, disabling concurrent_insert feature is only beneficial to read-intensive application. However for real read/write mix applications, I think still need to enable concurrent_insert feature. Do you know how much benefit concurrent_insert could give?
The answer is as always that it depends.

In this case on how much concurrency you actually have in your application, how long the selects take to run and if it is OK for the inserting thread to wait until the table is available or if you possibly can use INSERT DELAYED instead.

But notice that this optimization is only working for INSERT’s, so if you also have a lot of UPDATE’s also then it’s an entirely other ballgame.

So I usually recommend switching to InnoDB and row level locks when I see someone having problem with read/write concurrency and MyISAM tables.

Thanks for your suggestion. I will do more testing to understand them.