# InnoDB Query Concurrency issue **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 5, 2008, 4:41pm UTC](https://forums.percona.com/t/innodb-query-concurrency-issue/868 "2008-08-05T16:41:35Z")\ **Posts on this page:** 4\ **Page:** 1

**Author:** ![dsuehrin](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@dsuehrin](https://forums.percona.com/u/dsuehrin)\ **Post date:** [August 5, 2008, 4:41pm UTC](https://forums.percona.com/t/innodb-query-concurrency-issue/868/1 "2008-08-05T16:41:35Z")

We are running into issues doing load testing for an application on our MySQL database. When we start the load test, everything runs very quickly with no problems. After about a minute or so, we start seeing queries that had been taking less than a second, start taking 10+ seconds. As this happens the effect snowballs, and the queries keep taking longer and longer. The queries are all SELECTs and there does not seem to be any waits for table locks(based on the SHOW STATUS output). Here is our system information MySQL v5.0.51a OS: SLES 10 SP2 DELL PE R900 4X Quad-Core E7330 @ 2.40GHz 32 GB RAM 9X300GB SAS RAID 5 Here is our my.cnf: # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/data) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the “–help” option. # The following options will be passed to all MySQL clients [client] #password = your\_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock max\_connections = 200 skip-locking key\_buffer = 16M max\_allowed\_packet = 8M table\_cache = 1024 sort\_buffer\_size = 16M read\_buffer\_size = 16M read\_rnd\_buffer\_size = 8M myisam\_sort\_buffer\_size = 8M thread\_cache\_size = 16 query\_cache\_size = 128M query\_cache\_limit = 2M long\_query\_time = 1 tmpdir = /tmp datadir = /var/lib/mysql # Try number of CPU’s\*2 for thread\_concurrency thread\_concurrency = 32 # Don’t listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the “enable-named-pipe” option) will render mysqld useless! # #skip-networking # MySQL General Query Log #log=mysql.general.log # MySQL Binary Log #log-bin=mysql-bin #expire\_log\_days = 7 # MySQL Slow Query Log #log-slow-queries=/var/lib/mysql/mysql.slow.log #log-queries-not-using-indexes # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # InnoDB settings innodb\_file\_per\_table innodb\_data\_home\_dir = /var/lib/mysql/ innodb\_data\_file\_path = ibdata1:10M:autoextend innodb\_log\_group\_home\_dir = /var/lib/mysql/ innodb\_log\_arch\_dir = /var/lib/mysql/ innodb\_log\_files\_in\_group = 2 innodb\_buffer\_pool\_size = 24576M innodb\_additional\_mem\_pool\_size = 20M innodb\_log\_file\_size = 256M innodb\_log\_buffer\_size = 16M innodb\_flush\_log\_at\_trx\_commit = 1 innodb\_lock\_wait\_timeout = 50 innodb\_thread\_concurrency = 8 innodb\_flush\_method = O\_DIRECT transaction-isolation = READ-COMMITTED #innodb\_sync\_spin\_loops = 20 [mysqldump] quick max\_allowed\_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key\_buffer = 256M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M [myisamchk] key\_buffer = 256M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M [mysqlhotcopy] interactive-timeout # Here is a SHOW ENGINE INNODB STATUS from while the load test was running: # 080805 16:16:48 INNODB MONITOR OUTPUT ## Per second averages calculated from the last 48 seconds ## SEMAPHORES ## OS WAIT ARRAY INFO: reservation count 1452975, signal count 769947 –Thread 1148070208 has waited at buf0buf.c line 1125 for 0.00 seconds the semaphore: Mutex at 0x2aaab4064cb8 created file buf0buf.c line 545, lock var 1 waiters flag 1 wait has ended –Thread 1147005248 has waited at buf0buf.c line 1125 for 0.00 seconds the semaphore: Mutex at 0x2aaab4064cb8 created file buf0buf.c line 545, lock var 1 waiters flag 1 –Thread 1146739008 has waited at buf0buf.c line 1125 for 0.00 seconds the semaphore: Mutex at 0x2aaab4064cb8 created file buf0buf.c line 545, lock var 1 waiters flag 1 –Thread 1148602688 has waited at buf0buf.c line 1125 for 0.00 seconds the semaphore: Mutex at 0x2aaab4064cb8 created file buf0buf.c line 545, lock var 1 waiters flag 1 wait has ended Mutex spin waits 0, rounds 56032069, OS waits 1227355 RW-shared spins 79, OS waits 24; RW-excl spins 39, OS waits 1 ## TRANSACTIONS ## Trx id counter 0 119032409 Purge done for trx’s n:o \< 0 119032229 undo n:o \< 0 0 History list length 27 Total number of lock structs in row lock hash table 4 LIST OF TRANSACTIONS FOR EACH SESSION: —TRANSACTION 0 119032392, not started, process no 12606, OS thread id 1147803968 MySQL thread id 289, query id 8896 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 119032388, not started, process no 12606, OS thread id 1150998848 MySQL thread id 288, query id 8891 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 119032407, not started, process no 12606, OS thread id 1147537728 MySQL thread id 287, query id 8891 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 0, not started, process no 12606, OS thread id 1146472768 MySQL thread id 98, query id 3176 omdc-postfix 10.4.5.53 dbmail —TRANSACTION 0 119031828, not started, process no 12606, OS thread id 1144609088 MySQL thread id 22, query id 7027 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 119031823, not started, process no 12606, OS thread id 1144875328 MySQL thread id 20, query id 7021 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 119031829, not started, process no 12606, OS thread id 1145141568 MySQL thread id 21, query id 7028 omdc-dbmail 10.4.5.55 dbmail —TRANSACTION 0 0, not started, process no 12606, OS thread id 1144342848 MySQL thread id 17, query id 8897 localhost root show engine innodb status —TRANSACTION 0 0, not started, process no 12606, OS thread id 1141680448 MySQL thread id 15, query id 4240 localhost 127.0.0.1 root —TRANSACTION 0 119032408, ACTIVE 0 sec, process no 12606, OS thread id 1145674048 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 292, query id 8887 omdc-dbmail 10.4.5.55 dbmail Sending data SELECT user\_idnr FROM dbmail\_users WHERE lower(userid) = lower(‘loadtest’) Trx read view will not see trx with id \>= 0 119032409, sees \< 0 119032220 —TRANSACTION 0 119032403, ACTIVE 1 sec, process no 12606, OS thread id 1145407808 starting index read, thread declared inside InnoDB 252 mysql tables in use 3, locked 0 MySQL thread id 291, query id 8860 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE mbx.name LIKE ‘INBOX’ AND ((mbx.owner\_idnr = 2492) OR (acl.user\_id = 2492 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032404, sees \< 0 119032220 —TRANSACTION 0 119032400, ACTIVE 1 sec, process no 12606, OS thread id 1146739008 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 290, query id 8856 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE mbx.name LIKE ‘INBOX’ AND ((mbx.owner\_idnr = 4009) OR (acl.user\_id = 4009 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032401, sees \< 0 119032220 —TRANSACTION 0 119032393, ACTIVE 1 sec, process no 12606, OS thread id 1145940288 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 11 lock struct(s), heap size 1216, undo log entries 9 MySQL thread id 286, query id 8896 omdc-dbmail 10.4.5.55 dbmail update INSERT INTO dbmail\_headervalue (headername\_id, physmessage\_id, headervalue) VALUES (4,2048230,‘LoadRunner test for MyMail’) —TRANSACTION 0 119032364, ACTIVE 6 sec, process no 12606, OS thread id 1150732608 sleeping before joining InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 284, query id 8705 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4009) OR (acl.user\_id = 4009 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032365, sees \< 0 119032165 —TRANSACTION 0 119032353, ACTIVE 8 sec, process no 12606, OS thread id 1150200128 starting index read, thread declared inside InnoDB 290 mysql tables in use 3, locked 0 MySQL thread id 282, query id 8631 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4006) OR (acl.user\_id = 4006 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032354, sees \< 0 119032147 —TRANSACTION 0 119032348, ACTIVE 8 sec, process no 12606, OS thread id 1149667648 waiting in InnoDB queue mysql tables in use 2, locked 0 MySQL thread id 281, query id 8624 omdc-dbmail 10.4.5.55 dbmail Sending data SELECT seen\_flag, answered\_flag, deleted\_flag, flagged\_flag, draft\_flag, recent\_flag, DATE\_FORMAT(internal\_date, ‘%Y-%m-%d %T’), rfcsize, message\_idnr FROM dbmail\_messages msg, dbmail\_physmessage pm WHERE pm.id = msg.physmessage\_id AND message\_idnr BETWEEN 2667877 AND 2682944 AND mailbox\_idnr = 2489 AND status IN (0,1,2) ORDER BY message\_idnr ASC Trx read view will not see trx with id \>= 0 119032350, sees \< 0 119032147 —TRANSACTION 0 119032332, ACTIVE 10 sec, process no 12606, OS thread id 1149401408 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 280, query id 8566 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4006) OR (acl.user\_id = 4006 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032333, sees \< 0 119032143 —TRANSACTION 0 119032327, ACTIVE 10 sec, process no 12606, OS thread id 1149135168 starting index read, thread declared inside InnoDB 385 mysql tables in use 3, locked 0 MySQL thread id 278, query id 8539 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4011) OR (acl.user\_id = 4011 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032328, sees \< 0 119032143 —TRANSACTION 0 119032321, ACTIVE 10 sec, process no 12606, OS thread id 1150466368 starting index read, thread declared inside InnoDB 0 mysql tables in use 3, locked 0 MySQL thread id 277, query id 8521 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4010) OR (acl.user\_id = 4010 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032322, sees \< 0 119032141 —TRANSACTION 0 119032283, ACTIVE 12 sec, process no 12606, OS thread id 1149933888 starting index read, thread declared inside InnoDB 1 mysql tables in use 3, locked 0 MySQL thread id 275, query id 8433 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4008) OR (acl.user\_id = 4008 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032285, sees \< 0 119032141 —TRANSACTION 0 119032262, ACTIVE 13 sec, process no 12606, OS thread id 1148868928 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 271, query id 8357 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4007) OR (acl.user\_id = 4007 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032263, sees \< 0 119032141 —TRANSACTION 0 119032249, ACTIVE 14 sec, process no 12606, OS thread id 1148602688 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 270, query id 8303 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4012) OR (acl.user\_id = 4012 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032252, sees \< 0 119032141 —TRANSACTION 0 119032239, ACTIVE 15 sec, process no 12606, OS thread id 1147005248 waiting in InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 269, query id 8281 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4008) OR (acl.user\_id = 4008 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032240, sees \< 0 119032141 —TRANSACTION 0 119032223, ACTIVE 16 sec, process no 12606, OS thread id 1148336448 starting index read, thread declared inside InnoDB 374 mysql tables in use 3, locked 0 MySQL thread id 268, query id 8248 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4007) OR (acl.user\_id = 4007 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032224, sees \< 0 119032141 —TRANSACTION 0 119032221, ACTIVE 16 sec, process no 12606, OS thread id 1148070208 starting index read, thread declared inside InnoDB 476 mysql tables in use 3, locked 0 MySQL thread id 267, query id 8237 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4014) OR (acl.user\_id = 4014 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032222, sees \< 0 119032141 —TRANSACTION 0 119032220, ACTIVE 16 sec, process no 12606, OS thread id 1146206528 sleeping before joining InnoDB queue mysql tables in use 3, locked 0 MySQL thread id 266, query id 8219 omdc-dbmail 10.4.5.55 dbmail Copying to tmp table SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4013) OR (acl.user\_id = 4013 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) Trx read view will not see trx with id \>= 0 119032221, sees \< 0 119032141 ## FILE I/O ## I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1245 OS file reads, 885 OS file writes, 451 OS fsyncs 0.83 reads/s, 16384 avg bytes/read, 11.67 writes/s, 5.46 fsyncs/s ## INSERT BUFFER AND ADAPTIVE HASH INDEX ## Ibuf: size 1, free list len 44, seg size 46, 89 inserts, 89 merged recs, 60 merges Hash table size 50999537, used cells 69672, node heap has 104 buffer(s) 18459.82 hash searches/s, 98620.97 non-hash searches/s ## LOG ## Log sequence number 333 646792591 Log flushed up to 333 646791954 Last checkpoint at 333 646787952 0 pending log writes, 0 pending chkp writes 332 log i/o’s done, 3.96 log i/o’s/second ## BUFFER POOL AND MEMORY ## Total memory allocated 28515656576; in additional pool allocated 12736512 Buffer pool size 1572864 Free buffers 1571235 Database pages 1525 Modified db pages 40 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1521, created 4, written 596 0.83 reads/s, 0.06 creates/s, 8.27 writes/s Buffer pool hit rate 1000 / 1000 ## ROW OPERATIONS ## 8 queries inside InnoDB, 9 queries in queue 16 read views open inside InnoDB Main thread process no. 12606, id 1140881728, state: sleeping Number of rows inserted 335, updated 312, deleted 20, read 9725355 4.98 inserts/s, 3.92 updates/s, 0.29 deletes/s, 116080.21 reads/s # END OF INNODB MONITOR OUTPUT Here is a SHOW STATUS from after the load test: ±----------------------------------±----------+ | Variable\_name | Value | ±----------------------------------±----------+ | Aborted\_clients | 0 | | Aborted\_connects | 1 | | Binlog\_cache\_disk\_use | 0 | | Binlog\_cache\_use | 0 | | Bytes\_received | 115 | | Bytes\_sent | 178 | | Com\_admin\_commands | 0 | | Com\_alter\_db | 0 | | Com\_alter\_table | 0 | | Com\_analyze | 0 | | Com\_backup\_table | 0 | | Com\_begin | 0 | | Com\_call\_procedure | 0 | | Com\_change\_db | 0 | | Com\_change\_master | 0 | | Com\_check | 0 | | Com\_checksum | 0 | | Com\_commit | 0 | | Com\_create\_db | 0 | | Com\_create\_function | 0 | | Com\_create\_index | 0 | | Com\_create\_table | 0 | | Com\_create\_user | 0 | | Com\_dealloc\_sql | 0 | | Com\_delete | 0 | | Com\_delete\_multi | 0 | | Com\_do | 0 | | Com\_drop\_db | 0 | | Com\_drop\_function | 0 | | Com\_drop\_index | 0 | | Com\_drop\_table | 0 | | Com\_drop\_user | 0 | | Com\_execute\_sql | 0 | | Com\_flush | 0 | | Com\_grant | 0 | | Com\_ha\_close | 0 | | Com\_ha\_open | 0 | | Com\_ha\_read | 0 | | Com\_help | 0 | | Com\_insert | 0 | | Com\_insert\_select | 0 | | Com\_kill | 0 | | Com\_load | 0 | | Com\_load\_master\_data | 0 | | Com\_load\_master\_table | 0 | | Com\_lock\_tables | 0 | | Com\_optimize | 0 | | Com\_preload\_keys | 0 | | Com\_prepare\_sql | 0 | | Com\_purge | 0 | | Com\_purge\_before\_date | 0 | | Com\_rename\_table | 0 | | Com\_repair | 0 | | Com\_replace | 0 | | Com\_replace\_select | 0 | | Com\_reset | 0 | | Com\_restore\_table | 0 | | Com\_revoke | 0 | | Com\_revoke\_all | 0 | | Com\_rollback | 0 | | Com\_savepoint | 0 | | Com\_select | 1 | | Com\_set\_option | 0 | | Com\_show\_binlog\_events | 0 | | Com\_show\_binlogs | 0 | | Com\_show\_charsets | 0 | | Com\_show\_collations | 0 | | Com\_show\_column\_types | 0 | | Com\_show\_create\_db | 0 | | Com\_show\_create\_table | 0 | | Com\_show\_databases | 0 | | Com\_show\_errors | 0 | | Com\_show\_fields | 0 | | Com\_show\_grants | 0 | | Com\_show\_innodb\_status | 0 | | Com\_show\_keys | 0 | | Com\_show\_logs | 0 | | Com\_show\_master\_status | 0 | | Com\_show\_ndb\_status | 0 | | Com\_show\_new\_master | 0 | | Com\_show\_open\_tables | 0 | | Com\_show\_privileges | 0 | | Com\_show\_processlist | 0 | | Com\_show\_slave\_hosts | 0 | | Com\_show\_slave\_status | 0 | | Com\_show\_status | 1 | | Com\_show\_storage\_engines | 0 | | Com\_show\_tables | 0 | | Com\_show\_triggers | 0 | | Com\_show\_variables | 0 | | Com\_show\_warnings | 0 | | Com\_slave\_start | 0 | | Com\_slave\_stop | 0 | | Com\_stmt\_close | 0 | | Com\_stmt\_execute | 0 | | Com\_stmt\_fetch | 0 | | Com\_stmt\_prepare | 0 | | Com\_stmt\_reset | 0 | | Com\_stmt\_send\_long\_data | 0 | | Com\_truncate | 0 | | Com\_unlock\_tables | 0 | | Com\_update | 0 | | Com\_update\_multi | 0 | | Com\_xa\_commit | 0 | | Com\_xa\_end | 0 | | Com\_xa\_prepare | 0 | | Com\_xa\_recover | 0 | | Com\_xa\_rollback | 0 | | Com\_xa\_start | 0 | | Compression | OFF | | Connections | 462 | | Created\_tmp\_disk\_tables | 0 | | Created\_tmp\_files | 5 | | Created\_tmp\_tables | 1 | | Delayed\_errors | 0 | | Delayed\_insert\_threads | 0 | | Delayed\_writes | 0 | | Flush\_commands | 1 | | Handler\_commit | 0 | | Handler\_delete | 0 | | Handler\_discover | 0 | | Handler\_prepare | 0 | | Handler\_read\_first | 0 | | Handler\_read\_key | 0 | | Handler\_read\_next | 0 | | Handler\_read\_prev | 0 | | Handler\_read\_rnd | 0 | | Handler\_read\_rnd\_next | 0 | | Handler\_rollback | 0 | | Handler\_savepoint | 0 | | Handler\_savepoint\_rollback | 0 | | Handler\_update | 0 | | Handler\_write | 132 | | Innodb\_buffer\_pool\_pages\_data | 1562 | | Innodb\_buffer\_pool\_pages\_dirty | 0 | | Innodb\_buffer\_pool\_pages\_flushed | 1091 | | Innodb\_buffer\_pool\_pages\_free | 1571196 | | Innodb\_buffer\_pool\_pages\_latched | 0 | | Innodb\_buffer\_pool\_pages\_misc | 106 | | Innodb\_buffer\_pool\_pages\_total | 1572864 | | Innodb\_buffer\_pool\_read\_ahead\_rnd | 5 | | Innodb\_buffer\_pool\_read\_ahead\_seq | 2 | | Innodb\_buffer\_pool\_read\_requests | 36055362 | | Innodb\_buffer\_pool\_reads | 1139 | | Innodb\_buffer\_pool\_wait\_free | 0 | | Innodb\_buffer\_pool\_write\_requests | 7032 | | Innodb\_data\_fsyncs | 795 | | Innodb\_data\_pending\_fsyncs | 0 | | Innodb\_data\_pending\_reads | 0 | | Innodb\_data\_pending\_writes | 0 | | Innodb\_data\_read | 27627520 | | Innodb\_data\_reads | 1277 | | Innodb\_data\_writes | 1585 | | Innodb\_data\_written | 36307968 | | Innodb\_dblwr\_pages\_written | 1091 | | Innodb\_dblwr\_writes | 53 | | Innodb\_log\_waits | 0 | | Innodb\_log\_write\_requests | 593 | | Innodb\_log\_writes | 501 | | Innodb\_os\_log\_fsyncs | 538 | | Innodb\_os\_log\_pending\_fsyncs | 0 | | Innodb\_os\_log\_pending\_writes | 0 | | Innodb\_os\_log\_written | 539136 | | Innodb\_page\_size | 16384 | | Innodb\_pages\_created | 9 | | Innodb\_pages\_read | 1553 | | Innodb\_pages\_written | 1091 | | Innodb\_row\_lock\_current\_waits | 0 | | Innodb\_row\_lock\_time | 2 | | Innodb\_row\_lock\_time\_avg | 0 | | Innodb\_row\_lock\_time\_max | 1 | | Innodb\_row\_lock\_waits | 3 | | Innodb\_rows\_deleted | 29 | | Innodb\_rows\_inserted | 464 | | Innodb\_rows\_read | 16116402 | | Innodb\_rows\_updated | 491 | | Key\_blocks\_not\_flushed | 0 | | Key\_blocks\_unused | 13393 | | Key\_blocks\_used | 3 | | Key\_read\_requests | 6 | | Key\_reads | 3 | | Key\_write\_requests | 0 | | Key\_writes | 0 | | Last\_query\_cost | 0.000000 | | Max\_used\_connections | 54 | | Ndb\_cluster\_node\_id | 0 | | Ndb\_config\_from\_host | | | Ndb\_config\_from\_port | 0 | | Ndb\_number\_of\_data\_nodes | 0 | | Not\_flushed\_delayed\_rows | 0 | | Open\_files | 12 | | Open\_streams | 0 | | Open\_tables | 133 | | Opened\_tables | 0 | | Prepared\_stmt\_count | 0 | | Qcache\_free\_blocks | 31 | | Qcache\_free\_memory | 133929128 | | Qcache\_hits | 8601 | | Qcache\_inserts | 2270 | | Qcache\_lowmem\_prunes | 0 | | Qcache\_not\_cached | 1509 | | Qcache\_queries\_in\_cache | 227 | | Qcache\_total\_blocks | 496 | | Questions | 14984 | | Rpl\_status | NULL | | Select\_full\_join | 0 | | Select\_full\_range\_join | 0 | | Select\_range | 0 | | Select\_range\_check | 0 | | Select\_scan | 1 | | Slave\_open\_temp\_tables | 0 | | Slave\_retried\_transactions | 0 | | Slave\_running | OFF | | Slow\_launch\_threads | 0 | | Slow\_queries | 0 | | Sort\_merge\_passes | 0 | | Sort\_range | 0 | | Sort\_rows | 0 | | Sort\_scan | 0 | | Ssl\_accept\_renegotiates | 0 | | Ssl\_accepts | 0 | | Ssl\_callback\_cache\_hits | 0 | | Ssl\_cipher | | | Ssl\_cipher\_list | | | Ssl\_client\_connects | 0 | | Ssl\_connect\_renegotiates | 0 | | Ssl\_ctx\_verify\_depth | 0 | | Ssl\_ctx\_verify\_mode | 0 | | Ssl\_default\_timeout | 0 | | Ssl\_finished\_accepts | 0 | | Ssl\_finished\_connects | 0 | | Ssl\_session\_cache\_hits | 0 | | Ssl\_session\_cache\_misses | 0 | | Ssl\_session\_cache\_mode | NONE | | Ssl\_session\_cache\_overflows | 0 | | Ssl\_session\_cache\_size | 0 | | Ssl\_session\_cache\_timeouts | 0 | | Ssl\_sessions\_reused | 0 | | Ssl\_used\_session\_cache\_entries | 0 | | Ssl\_verify\_depth | 0 | | Ssl\_verify\_mode | 0 | | Ssl\_version | | | Table\_locks\_immediate | 6866 | | Table\_locks\_waited | 0 | | Tc\_log\_max\_pages\_used | 0 | | Tc\_log\_page\_size | 0 | | Tc\_log\_page\_waits | 0 | | Threads\_cached | 15 | | Threads\_connected | 15 | | Threads\_created | 54 | | Threads\_running | 1 | | Uptime | 2157 | | Uptime\_since\_flush\_status | 2157 | ±----------------------------------±----------+ Does anyone see anything we may have misconfigured, or have any recommendations on things to try or change to help improve our performance? ---

**Author:** ![dsuehrin](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@dsuehrin](https://forums.percona.com/u/dsuehrin)\ **Post date:** [August 13, 2008, 8:35am UTC](https://forums.percona.com/t/innodb-query-concurrency-issue/868/2 "2008-08-13T08:35:21Z")

A little more research led me to do a SHOW MUTEX STATUS, which returned around 3 million rows. Most of the rows had 0 (or close to it) for the number of OS\_waits, except for these few at the end: File Line OS\_waits ‘buf0buf.c’ 494 0 ‘buf0buf.c’ 497 0 ‘buf0buf.c’ 494 0 ‘buf0buf.c’ 545 40964722 ‘fil0fil.c’ 1293 877 ‘srv0start.c’ 1201 0 ‘srv0start.c’ 1194 0 ‘srv0start.c’ 1172 2098 ‘dict0mem.c’ 90 0 ‘dict0mem.c’ 90 0 ‘srv0srv.c’ 875 8707 ‘srv0srv.c’ 872 28512 ‘thr0loc.c’ 229 0 ‘mem0pool.c’ 205 43 ‘sync0sync.c’ 1319 0 I’m guessing that the 40 million OS\_waits on buf0buf.c line 545 probably is part of our issue, but I haven’t been able to find any other information on that line besides the fact that buf0buf.c deals with the buffer pool. Would anyone be able to provide any information on what OS\_waits for buf0buf.c line 545 would indicate, or point me in the direction of a good resource to learn more about it? Thanks! ---

**Author:** ![dsuehrin](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@dsuehrin](https://forums.percona.com/u/dsuehrin)\ **Post date:** [August 13, 2008, 9:02am UTC](https://forums.percona.com/t/innodb-query-concurrency-issue/868/3 "2008-08-13T09:02:27Z")

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [September 19, 2008, 11:12pm UTC](https://forums.percona.com/t/innodb-query-concurrency-issue/868/4 "2008-09-19T23:12:15Z")

What can you learn from the “iostat” command? Is your disk usage pinned? A lot of those queries are creating temporary tables. What does EXPLAIN tell you about this query: SELECT distinct(mbx.name), mbx.mailbox\_idnr, mbx.owner\_idnr FROM dbmail\_mailboxes mbx LEFT JOIN dbmail\_acl acl ON mbx.mailbox\_idnr = acl.mailbox\_id LEFT JOIN dbmail\_users usr ON acl.user\_id = usr.user\_idnr WHERE ((mbx.owner\_idnr = 4013) OR (acl.user\_id = 4013 AND acl.lookup\_flag = 1) OR (usr.userid = ‘anyone’ AND acl.lookup\_flag = 1)) ?