Profiling Server Performance - High Performance MySQL - page 102

the -l 5 option to truncate the stack traces after five levels so that we don't get so many
traces with common tops but different bottoms, which would prevent them from ag-
gregating together and showing where things are really waiting:
$ pt-pmp -l 5 stacktraces.txt
507 pthread_cond_wait,one_thread_per_connection_end,handle_one_connection,
start_thread,clone
398 pthread_cond_wait,os_event_wait_low,srv_conc_enter_innodb,
innodb_srv_conc_enter_innodb,ha_innodb::index_read
83 pthread_cond_wait,os_event_wait_low,sync_array_wait_event,mutex_spin_wait,
mutex_enter_func
10 pthread_cond_wait,os_event_wait_low,os_aio_simulated_handle,fil_aio_wait,
io_handler_thread
7 pthread_cond_wait,os_event_wait_low,srv_conc_enter_innodb,
innodb_srv_conc_enter_innodb,ha_innodb::general_fetch
5 pthread_cond_wait,os_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,
rw_lock_s_lock_func
1 sigwait,signal_hand,start_thread,clone,??
1 select,os_thread_sleep,srv_lock_timeout_and_monitor_thread,start_thread,clone
1 select,os_thread_sleep,srv_error_monitor_thread,start_thread,clone
1 select,handle_connections_sockets,main
1 read,vio_read_buff,::??,my_net_read,cli_safe_read
1 pthread_cond_wait,os_event_wait_low,sync_array_wait_event,rw_lock_x_lock_low,
rw_lock_x_lock_func
1 pthread_cond_wait,MYSQL_BIN_LOG::wait_for_update,mysql_binlog_send,
dispatch_command,do_command
1 fsync,os_file_fsync,os_file_flush,fil_flush,log_write_up_to
The first line is the characteristic signature of an idle thread in MySQL, so you can
ignore that. The second line is the most interesting one: it shows that a lot of threads
are waiting to enter the InnoDB kernel but are blocked. The third line shows many
threads waiting on some mutex, but we can't see which one because we have truncated
the deeper levels of the stack trace. If it is important to know which mutex that is, we
would need to re run the tool with a larger value for the -l option. In general, the stack
traces show that lots of things are waiting for their turn inside InnoDB, but why? That
isn't clear at all. To find out, we probably need to look elsewhere.
As the preceding stack trace and oprofile reports show, these types of analysis are not
always useful to those who aren't experts with MySQL and InnoDB source code, and
you should ask for help from someone else if you get stuck.
Now let's move on to a server whose problems don't show up on either a profile or
wait analysis, and need to be diagnosed differently.
A Case Study in Diagnostics
In this section we'll step you through the process of diagnosing a real customer's in-
termittent performance problem. This case study is likely to get into unfamiliar territory
unless you're an expert with MySQL, InnoDB, and GNU/Linux. However, the specifics