Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB Query Concurrency issue

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB Query Concurrency issue

    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 < 0 119032229 undo n < 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?

  • #2
    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!

    Comment


    • #3
      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!

      Comment


      • #4
        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))


        ?

        Comment

        Working...
        X