Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

concurrent_insert feature understanding

XuekunXuekun EntrantCurrent User Role Beginner
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

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    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 )).

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Xuekun wrote on Thu, 06 December 2007 13:09</td></tr><tr><td class="quote">
    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?

    </td></tr></table>
    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.
  • XuekunXuekun Entrant Current User Role Beginner
    Thanks for your suggestion. I will do more testing to understand them.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.