OS: [admin@xxx ~]$ lsb_release -a LSB Version: :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch Distributor ID: RedHatEnterpriseServer Description: Red Hat Enterprise Linux Server release 5.3 (Tikanga) Release: 5.3 Codename: Tikanga MySQL Server: root@localhost : (none) 04:51:29> show variables like 'version'; +---------------+---------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------+ | version | 5.1.34sp1-enterprise-gpl-advanced-log | +---------------+---------------------------------------+ InnoDB Config: root@localhost : (none) 04:51:39> show variables like 'innodb%'; +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 16777216 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 17179869184 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2048M:autoextend | | innodb_data_home_dir | /data/mysqldata/innodb_ts | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 0 | | innodb_flush_method | O_DIRECT | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 120 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 1073741824 | | innodb_log_files_in_group | 3 | | innodb_log_group_home_dir | /data/mysqldata/innodb_log | | innodb_max_dirty_pages_pct | 5 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | +---------------------------------+----------------------------+ root@localhost : (none) 04:52:18> show variables like 'sort%'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | sort_buffer_size | 2097152 | +------------------+---------+ root@localhost : (none) 04:52:26> show variables like 'max_length%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 16384 | +--------------------------+-------+ root@localhost : (none) 04:53:49> show variables like '%page%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | innodb_max_dirty_pages_pct | 5 | | large_page_size | 2097152 | | large_pages | ON | +----------------------------+---------+ Schema info: root@localhost : (none) 05:01:36> show create table my_table_name\G *************************** 1. row *************************** Table: my_table_name Create Table: CREATE TABLE `my_table_name` ( `SITE` varchar(64) COLLATE utf8_bin DEFAULT NULL, `id` bigint(20) NOT NULL, `gmt_create` datetime NOT NULL DEFAULT '1999-09-09 00:00:00', `gmt_modified` datetime NOT NULL DEFAULT '1999-09-09 00:00:00', `gmt_expire` datetime DEFAULT NULL, `gmt_post` datetime DEFAULT NULL, `ACTION` varchar(32) COLLATE utf8_bin DEFAULT NULL, `STATUS` varchar(32) COLLATE utf8_bin DEFAULT NULL, `SUBJECT` varchar(512) COLLATE utf8_bin DEFAULT NULL, `PRICE_TERMS` varchar(512) COLLATE utf8_bin DEFAULT NULL, `PACKAGING` varchar(512) COLLATE utf8_bin DEFAULT NULL, `TYPE` varchar(32) COLLATE utf8_bin DEFAULT NULL, `member_id` varchar(32) COLLATE utf8_bin NOT NULL, `WSAD_OFFER` char(1) COLLATE utf8_bin DEFAULT NULL, `REMMIT_STATUS` char(1) COLLATE utf8_bin DEFAULT NULL, `group_id` bigint(20) DEFAULT NULL, <........................> PRIMARY KEY (`id`), KEY `idx_my_table_name_mid_st_ge_gid_tp` (`member_id`,`STATUS`,`gmt_expire`,`group_id`,`id`,`TYPE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin root@localhost : my_table_name1 05:05:42> show table status like 'my_table_name'\G *************************** 1. row *************************** Name: my_table_name Engine: InnoDB Version: 10 Row_format: Compact Rows: 1614637 Avg_row_length: 1527 Data_length: 2466250752 Max_data_length: 0 Index_length: 146800640 Data_free: 7340032 Auto_increment: NULL Create_time: 2010-02-26 11:42:54 Update_time: NULL Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: ******************************************************************************************************************* When query(with order by,wort by index,no filesort) same data with several threads, most of all threads wait at sorting result,innodb status like this: *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 100226 14:17:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 56 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 76696, signal count 51519 --Thread 1184885056 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10cbb10 created in file buf/buf0buf.c line 547 a writer (thread id 1170241856) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 --Thread 1166514496 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10cbb10 created in file buf/buf0buf.c line 547 a writer (thread id 1170241856) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 --Thread 1185417536 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10cbb10 created in file buf/buf0buf.c line 547 a writer (thread id 1170241856) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 --Thread 1180891456 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10cbb10 created in file buf/buf0buf.c line 547 a writer (thread id 1170241856) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 --Thread 1184618816 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10cbb10 created in file buf/buf0buf.c line 547 a writer (thread id 1170241856) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 --Thread 1179560256 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore: S-lock on RW-latch at 0x2aaec10c93b0 created in file buf/buf0buf.c line 547 a writer (thread id 1185683776) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file not yet reserved line 0 Last time write locked in file buf/buf0buf.c line 1797 Mutex spin waits 0, rounds 1646767, OS waits 3389 RW-shared spins 150566, OS waits 70115; RW-excl spins 38241, OS waits 1329 ------------ TRANSACTIONS ------------ Trx id counter 0 416217421 Purge done for trx's n:o < 0 414474793 undo n:o < 0 0 History list length 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 404, OS thread id 1185950016 MySQL thread id 80, query id 3721 localhost root show innodb status ---TRANSACTION 0 416217195, not started, process no 404, OS thread id 1180358976 estimating records in index range mysql tables in use 1, locked 0 MySQL thread id 55, query id 3724 172.22.32.45 my_table_name statistics select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='tzyyme' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 <..............................> ---TRANSACTION 0 416217419, ACTIVE 0 sec, process no 404, OS thread id 1183820096 fetching rows mysql tables in use 1, locked 0 MySQL thread id 70, query id 3722 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida3' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217420, sees < 0 416214381 ---TRANSACTION 0 416217418, ACTIVE 0 sec, process no 404, OS thread id 1179027776 fetching rows mysql tables in use 1, locked 0 MySQL thread id 50, query id 3719 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='yuelianqd' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217419, sees < 0 416214381 ---TRANSACTION 0 416217416, ACTIVE 0 sec, process no 404, OS thread id 1180092736 fetching rows mysql tables in use 1, locked 0 MySQL thread id 54, query id 3716 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='jxkeyi' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217417, sees < 0 416214381 ---TRANSACTION 0 416217413, ACTIVE 0 sec, process no 404, OS thread id 1177164096 fetching rows mysql tables in use 1, locked 0 MySQL thread id 43, query id 3713 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='cnhcxb' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217414, sees < 0 416214381 ---TRANSACTION 0 416217411, ACTIVE 0 sec, process no 404, OS thread id 1167579456 fetching rows mysql tables in use 1, locked 0 MySQL thread id 6, query id 3714 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='xhjngs' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217412, sees < 0 416214381 ---TRANSACTION 0 416217408, ACTIVE 0 sec, process no 404, OS thread id 1178495296 fetching rows mysql tables in use 1, locked 0 MySQL thread id 48, query id 3708 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='alina6666' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217409, sees < 0 416214381 ---TRANSACTION 0 416217407, ACTIVE 0 sec, process no 404, OS thread id 1172904256 fetching rows mysql tables in use 1, locked 0 MySQL thread id 27, query id 3710 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='fcruntai' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217408, sees < 0 416214381 ---TRANSACTION 0 416217406, ACTIVE 0 sec, process no 404, OS thread id 1172638016 fetching rows mysql tables in use 1, locked 0 MySQL thread id 26, query id 3709 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='nhyibo' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217407, sees < 0 416214381 ---TRANSACTION 0 416217392, ACTIVE 1 sec, process no 404, OS thread id 1175566656 fetching rows mysql tables in use 1, locked 0 MySQL thread id 37, query id 3694 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kyfeed' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217393, sees < 0 416214381 ---TRANSACTION 0 416217386, ACTIVE 1 sec, process no 404, OS thread id 1179560256 fetching rows mysql tables in use 1, locked 0 MySQL thread id 52, query id 3688 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='yanghuaminglan' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217387, sees < 0 416214381 ---TRANSACTION 0 416217384, ACTIVE 1 sec, process no 404, OS thread id 1172105536 fetching rows mysql tables in use 1, locked 0 MySQL thread id 24, query id 3683 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='zshy8368' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217385, sees < 0 416214381 ---TRANSACTION 0 416217382, ACTIVE 1 sec, process no 404, OS thread id 1168378176 fetching rows mysql tables in use 1, locked 0 MySQL thread id 9, query id 3682 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='zcw522' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217383, sees < 0 416214381 ---TRANSACTION 0 416217376, ACTIVE 1 sec, process no 404, OS thread id 1175034176 fetching rows mysql tables in use 1, locked 0 MySQL thread id 35, query id 3677 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='goldenkelen' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217377, sees < 0 416214381 ---TRANSACTION 0 416217375, ACTIVE 1 sec, process no 404, OS thread id 1181956416 fetching rows mysql tables in use 1, locked 0 MySQL thread id 63, query id 3679 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='hlyln' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217376, sees < 0 416214381 ---TRANSACTION 0 416217367, ACTIVE 1 sec, process no 404, OS thread id 1166780736 fetching rows mysql tables in use 1, locked 0 MySQL thread id 3, query id 3666 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='dnsjj' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217368, sees < 0 416214381 ---TRANSACTION 0 416217366, ACTIVE 1 sec, process no 404, OS thread id 1186216256 fetching rows mysql tables in use 1, locked 0 MySQL thread id 81, query id 3669 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='szjyfwj' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217367, sees < 0 416214381 ---TRANSACTION 0 416217357, ACTIVE 1 sec, process no 404, OS thread id 1167845696 fetching rows mysql tables in use 1, locked 0 MySQL thread id 7, query id 3659 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='dongnanshishang' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217358, sees < 0 416214381 ---TRANSACTION 0 416217355, ACTIVE 1 sec, process no 404, OS thread id 1167313216 fetching rows mysql tables in use 1, locked 0 MySQL thread id 5, query id 3655 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='zuoy1478' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217356, sees < 0 416214381 ---TRANSACTION 0 416217326, ACTIVE 2 sec, process no 404, OS thread id 1175832896 fetching rows mysql tables in use 1, locked 0 MySQL thread id 38, query id 3625 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='shpchdzkj' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217327, sees < 0 416214381 ---TRANSACTION 0 416217289, ACTIVE 2 sec, process no 404, OS thread id 1169709376 fetching rows mysql tables in use 1, locked 0 MySQL thread id 15, query id 3591 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='shuanglian02' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217290, sees < 0 416214381 ---TRANSACTION 0 416217267, ACTIVE 3 sec, process no 404, OS thread id 1176897856 fetching rows mysql tables in use 1, locked 0 MySQL thread id 42, query id 3566 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='hanskorea' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217268, sees < 0 416214381 ---TRANSACTION 0 416217218, ACTIVE 4 sec, process no 404, OS thread id 1177430336 fetching rows mysql tables in use 1, locked 0 MySQL thread id 44, query id 3517 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='tjomr' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217219, sees < 0 416214381 ---TRANSACTION 0 416217183, ACTIVE 4 sec, process no 404, OS thread id 1185151296 fetching rows mysql tables in use 1, locked 0 MySQL thread id 77, query id 3485 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='zhongzhengbing998' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217184, sees < 0 416214381 ---TRANSACTION 0 416217154, ACTIVE 4 sec, process no 404, OS thread id 1184885056 fetching rows mysql tables in use 1, locked 0 MySQL thread id 75, query id 3456 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217155, sees < 0 416214381 ---TRANSACTION 0 416217082, ACTIVE 6 sec, process no 404, OS thread id 1185417536 fetching rows mysql tables in use 1, locked 0 MySQL thread id 78, query id 3384 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416217083, sees < 0 416214381 ---TRANSACTION 0 416216986, ACTIVE 7 sec, process no 404, OS thread id 1176365376 fetching rows mysql tables in use 1, locked 0 MySQL thread id 40, query id 3274 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='guoqinglong2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416216987, sees < 0 416214381 ---TRANSACTION 0 416216937, ACTIVE 8 sec, process no 404, OS thread id 1185683776 fetching rows mysql tables in use 1, locked 0 MySQL thread id 79, query id 3228 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='yanghuaminglan' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416216938, sees < 0 416214381 ---TRANSACTION 0 416216769, ACTIVE 16 sec, process no 404, OS thread id 1166514496 fetching rows mysql tables in use 1, locked 0 MySQL thread id 2, query id 3059 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416216770, sees < 0 416214381 ---TRANSACTION 0 416216044, ACTIVE 65 sec, process no 404, OS thread id 1184618816 fetching rows mysql tables in use 1, locked 0 MySQL thread id 73, query id 2295 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416216045, sees < 0 416214381 ---TRANSACTION 0 416214611, ACTIVE 209 sec, process no 404, OS thread id 1180891456 fetching rows mysql tables in use 1, locked 0 MySQL thread id 57, query id 737 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416214612, sees < 0 416214314 ---TRANSACTION 0 416214381, ACTIVE 251 sec, process no 404, OS thread id 1170241856 fetching rows mysql tables in use 1, locked 0 MySQL thread id 17, query id 419 172.22.32.45 my_table_name Sorting result select ID,GMT_MODIFIED,GMT_POST,GMT_EXPIRE,STATUS,ACTION from my_table_name where MEMBER_ID='kangshida2' and (STATUS<>'member deleted') order by MEMBER_ID,STATUS,GMT_EXPIRE limit 0,20 Trx read view will not see trx with id >= 0 416214382, sees < 0 416214280 -------- 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 343626 OS file reads, 51 OS file writes, 48 OS fsyncs 25 pending preads, 0 pending pwrites 1120.00 reads/s, 24356 avg bytes/read, 0.34 writes/s, 0.29 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 260, seg size 262, 0 inserts, 0 merged recs, 0 merges Hash table size 35401603, node heap has 5970 buffer(s) 44958.29 hash searches/s, 11942.09 non-hash searches/s --- LOG --- Log sequence number 141 852477603 Log flushed up to 141 852477603 Last checkpoint at 141 852477583 0 pending log writes, 0 pending chkp writes 32 log i/o's done, 0.16 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1693865495; in additional pool allocated 16777216 Dictionary memory allocated 151008 Buffer pool size 1048576 Free buffers 585403 Database pages 457203 Modified db pages 0 Pending reads 25 Pending writes: LRU 0, flush list 0, single page 0 Pages read 457178, created 0, written 12 1665.08 reads/s, 0.00 creates/s, 0.09 writes/s Buffer pool hit rate 992 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 32 read views open inside InnoDB Main thread process no. 404, id 1165715776, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 5281531 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 56746.56 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set, 1 warning (0.01 sec)