percona-discussion team mailing list archive

[Yasufumi Kinoshita <yasufumi.kinoshita@xxxxxxxxxxx>]

** Changed in: percona-xtradb
       Status: Confirmed => Fix Committed

-- 
Performance drops when concurrent select by secondary key with 16 sessions on 16 CPU
https://bugs.launchpad.net/bugs/319487
You received this bug notification because you are a member of Percona
developers, which is the registrant for Percona-XtraDB.

Status in Percona XtraDB Storage Engine for MySQL: Fix Committed

Bug description:
The chart: https://spreadsheets.google.com/ccc?key=pOIo5aX59b6Z2fJDE0TZjFg

SELECT name  FROM sbtest WHERE country_id = ? and state_id between ? and ?		
		
CREATE TABLE IF NOT EXISTS sbtest (
                  id int(10) unsigned NOT NULL auto_increment,
                  name varchar(64) NOT NULL default '',
                  email varchar(64) NOT NULL default '',
                  password varchar(64) NOT NULL default '',
                  dob date default NULL,
                  address varchar(128) NOT NULL default '',
                  city varchar(64) NOT NULL default '',
                  state_id tinyint(3) unsigned NOT NULL default '0',
                  zip varchar(8) NOT NULL default '',
                  country_id smallint(5) unsigned NOT NULL default '0',
                  PRIMARY KEY  (id)
                 ) 		
   db_query("CREATE INDEX email_idx on sbtest(email)")                                                                          		
   db_query("CREATE INDEX country_id_idx on sbtest(country_id,state_id,city)") 		

Mutex:
<Vadim> --Thread 1094527296 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> wait has ended
<Vadim> --Thread 1195309376 has waited at btr/btr0sea.c line 818 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> --Thread 1192700224 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> --Thread 1188886848 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> --Thread 1200126272 has waited at btr/btr0sea.c line 818 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> --Thread 1196714304 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 0
<Vadim> waiters flag 0
<Vadim> --Thread 1197717824 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1
<Vadim> waiters flag 1
<Vadim> --Thread 1191295296 has waited at btr/btr0sea.c line 818 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1
<Vadim> waiters flag 1
<Vadim> --Thread 1188284736 has waited at btr/btr0sea.c line 818 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1
<Vadim> waiters flag 1
<Vadim> --Thread 1192900928 has waited at btr/btr0sea.c line 818 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1
<Vadim> waiters flag 1
<Vadim> --Thread 1082542400 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1
<Vadim> waiters flag 1
<Vadim> --Thread 1084750144 has waited at buf/buf0buf.c line 1911 for 0.00 seconds the semaphore:
<Vadim> Mutex at 0xc346a0 created file buf/buf0buf.c line 940, lock var 1


Path:
<Vadim> #0  0x00000037a42099d5 in __pthread_mutex_unlock_usercnt () from /lib64/libpthread.so.0
<Vadim> #1  0x00000000007bb156 in mutex_spin_wait ()
<Vadim> #2  0x0000000000726222 in buf_page_get_gen ()
<Vadim> #3  0x000000000070fe3f in btr_cur_search_to_nth_level ()
<Vadim> #4  0x00000000007a9e79 in row_search_for_mysql ()
<Vadim> #5  0x0000000000701329 in ha_innobase::general_fetch ()
<Vadim> #6  0x0000000000674ba1 in handler::read_range_next ()
<Vadim> #7  0x0000000000671979 in handler::read_multi_range_next ()
<Vadim> #8  0x000000000065afd2 in QUICK_RANGE_SELECT::get_next ()
<Vadim> #9  0x000000000066e60d in rr_quick ()
<Vadim> #10 0x00000000005f6146 in sub_select ()
<Vadim> #11 0x00000000005f6bdd in do_select ()
<Vadim> #12 0x0000000000607058 in JOIN::exec ()
<Vadim> #13 0x0000000000608c48 in mysql_select ()
<Vadim> #14 0x0000000000609579 in handle_select ()
<Vadim> #15 0x00000000005927bc in execute_sqlcom_select ()
<Vadim> #16 0x0000000000597ad7 in mysql_execute_command ()
<Vadim> #17 0x0000000000614188 in Prepared_statement::execute ()
<Vadim> #18 0x00000000006166b0 in Prepared_statement::execute_loop ()
<Vadim> #19 0x000000000061738d in mysql_stmt_execute ()
<Vadim> #20 0x000000000059ed51 in dispatch_command ()
<Vadim> #21 0x000000000059f334 in do_command ()
<Vadim> #22 0x000000000058ffd7 in handle_one_connection ()
<Vadim> #23 0x00000037a42062f7 in start_thread () from /lib64/libpthread.so.0
<Vadim> #24 0x00000037a3ad1e3d in clone () from /lib64/libc.so.6

<Vadim> Yasufumi: and Thread 9 (Thread 1187080512 (LWP 633)):
<Vadim> #0  0x00000037a420c888 in __lll_mutex_lock_wait () from /lib64/libpthread.so.0
<Vadim> #1  0x00000037a4208911 in _L_mutex_lock_1133 () from /lib64/libpthread.so.0
<Vadim> #2  0x00000037a420886a in pthread_mutex_lock () from /lib64/libpthread.so.0
<Vadim> #3  0x000000000077988d in os_mutex_enter ()
<Vadim> #4  0x00000000007b9548 in sync_array_reserve_cell ()
<Vadim> ---Type <return> to continue, or q <return> to quit---
<Vadim> #5  0x00000000007bb0e0 in mutex_spin_wait ()
<Vadim> #6  0x0000000000726222 in buf_page_get_gen ()
<Vadim> #7  0x000000000070fe3f in btr_cur_search_to_nth_level ()
<Vadim> #8  0x000000000071091a in btr_estimate_n_rows_in_range ()
<Vadim> #9  0x00000000007045d7 in ha_innobase::records_in_range ()
<Vadim> #10 0x000000000065f513 in check_quick_keys ()
<Vadim> #11 0x000000000065fc45 in check_quick_keys ()
<Vadim> #12 0x000000000065fe20 in check_quick_select ()
<Vadim> #13 0x00000000006600be in get_key_scans_params ()
<Vadim> #14 0x000000000066951b in SQL_SELECT::test_quick_select ()
<Vadim> #15 0x00000000005fed76 in make_join_statistics ()
<Vadim> #16 0x00000000005ff8e6 in JOIN::optimize ()
<Vadim> #17 0x0000000000608b5b in mysql_select ()
<Vadim> #18 0x0000000000609579 in handle_select ()
<Vadim> #19 0x00000000005927bc in execute_sqlcom_select ()
<Vadim> #20 0x0000000000597ad7 in mysql_execute_command ()
<Vadim> #21 0x0000000000614188 in Prepared_statement::execute ()
<Vadim> #22 0x00000000006166b0 in Prepared_statement::execute_loop ()
<Vadim> #23 0x000000000061738d in mysql_stmt_execute ()
<Vadim> #24 0x000000000059ed51 in dispatch_command ()
<Vadim> #25 0x000000000059f334 in do_command ()
<Vadim> #26 0x000000000058ffd7 in handle_one_connection ()
<Vadim> #27 0x00000037a42062f7 in start_thread () from /lib64/libpthread.so.0
<Vadim> #28 0x00000037a3ad1e3d in clone () from /lib64/libc.so.6