Bug #43435 LOCK_open does not use MY_MUTEX_INIT_FAST
Submitted: 5 Mar 2009 21:07 Modified: 10 Aug 2009 22:28
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.1 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: LOCK_open, MY_MUTEX_INIT_FAST, pthread

[5 Mar 2009 21:07] Mark Callaghan
Description:
From mysqld.cc, LOCK_open does not use MY_MUTEX_INIT_FAST. On my platform, that means that it does not use:
   pthread_mutexattr_settype(&my_fast_mutexattr,PTHREAD_MUTEX_ADAPTIVE_NP);

This is a change from MySQL 5.0. Can you exlpain why it was done?

------
 
static int init_thread_environment()
{
  (void) pthread_mutex_init(&LOCK_mysql_create_db,MY_MUTEX_INIT_SLOW);
  (void) pthread_mutex_init(&LOCK_lock_db,MY_MUTEX_INIT_SLOW);
  (void) pthread_mutex_init(&LOCK_Acl,MY_MUTEX_INIT_SLOW);
  (void) pthread_mutex_init(&LOCK_open, NULL);

How to repeat:
na

Suggested fix:
na
[5 Mar 2009 21:20] Davi Arnaut
Removed in revision sp1r-monty@mysql.com-20051123204502-44248 with timestamp Wed 2005-11-23 22:45:02 +0200 by Monty without explanations on the changeset comments (can be visualized with bzr gannotate). Seems to have been related to the table definition cache work.

We can revert this bit if there is a compelling reason. Do you have benchmarks or a workload in mind?
[8 Mar 2009 16:13] Mark Callaghan
My results are interesting, but not compelling. It would be nice to get results from other platforms. On the other hand, mutex contention on LOCK_open appears to be much reduced from 5.0 -> 5.1.31. So this might not matter anymore. And whoever fixed that in 5.1.31 deserves a lot of credit.

But it would still be nice to know why it was changed.

Results from:
* 16 core server
* sysbench readonly with --oltp-secondary-index patch from me
* 1, 2, 4, 8, 16, 32, 64 concurrent sessions
* no stored procedures
* 2M rows in table, all in innodb cache
* mysql 5.0.75

 220  485  856 1364  703  646  468 LOCK_open with NULL
 219  485  916 1425  894  793  583 LOCK_open with MY_MUTEX_INIT_FAST
[26 Apr 2009 13:47] Mark Callaghan
A good stress test for locks above the storage engine level is:
* sysbench readonly
* blackhole storage engine
* clients run on same host as server
[3 Jul 2009 5:36] Sveta Smirnova
Thank you for the report.

Set to "Verified" as there are some results.

My tests as described in comment "[26 Apr 15:47] Mark Callaghan" showed following results when I used release server and my-small.cnf:

sysbench --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql_ssmirnova.sock --mysql-user=root --max-requests=100000 --oltp-read-only --mysql-engine-trx=no  --num-threads=N run

Threads             1            2            4             8             16            32
Release with NULL
execution time   99.0724 60.7872 91.9503 185.9037 325.0197 580.4906
Release with MY_MUTEX_INIT_FAST
execution time  94.9017 60.9907 83.5950 130.0092 325.8749 580.1999

Server compiled as ./configure --with-plugins=max-no-ndb, box with 2 CPU and 4 cores
[10 Jul 2009 3:03] Mark Callaghan
Can you ask Mikael Ronstrom to look at this?
[30 Jul 2009 20:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79720

3051 Davi Arnaut	2009-07-30
      Bug#43435: LOCK_open does not use MY_MUTEX_INIT_FAST
      
      Initialize LOCK_open as a adapative mutex on platforms where the
      PTHREAD_ADAPTIVE_MUTEX_INITIALIZER_NP macro is available. The flag
      indicates that a thread should spin (busy wait) for some time on a
      locked adaptive mutex before blocking (sleeping). It's intended to
      to alleviate performance problems due to LOCK_open being a highly
      contended mutex.
     @ sql/mysqld.cc
        Initialize LOCK_open as a adapative mutex.
[31 Jul 2009 12:43] Davi Arnaut
Queued to 5.1-bugteam
[4 Aug 2009 19:52] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:davi.arnaut@sun.com-20090731124450-4dq2edbwnev11mzs) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[10 Aug 2009 22:28] Paul Dubois
Noted in 5.1.38, 5.4.4 changelogs.

The table cache lock (LOCK_open) is now an adaptive mutex, which
should improve performance in workloads where this lock is heavily
contested.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[8 Oct 2009 2:49] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.