Hi,
Sometimes threads spend very long time (10+ seconds) in Opening tables state of SHOW COLUMNS FROM query.
pt-stalk shows that the most used call is flush_key_blocks_int():
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
samples % image name symbol name
437152 26.5366 mysqld flush_key_blocks_int
50326 3.0550 mysqld row_search_for_mysql
39687 2.4091 mysqld my_strtod
34657 2.1038 mysqld srv_master_thread
34111 2.0706 mysqld my_uca_scanner_next_any
33868 2.0559 mysqld my_scan_8bit
31970 1.9407 mysqld row_sel_store_mysql_rec
What I have to tune to avoid these lags?
Percona-Server-server-55-5.5.16
CentOS 6
kernel 2.6.32-431.el6.x86_64
/etc/my.cnf:
skip-external-locking
skip-name-resolve
ft_min_word_len = 2
back_log = 100
max_connections = 200
max_user_connections = 200
interactive_timeout = 1800
wait_timeout = 120
connect_timeout = 5
key_buffer_size = 6G
myisam_sort_buffer_size = 4095M
join_buffer_size = 1M
sort_buffer_size = 2M
table_open_cache = 2048
thread_cache_size = 286
thread_stack = 196K
max_allowed_packet = 16M
max_connect_errors = 10
max_heap_table_size = 1G
tmp_table_size = 64M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size = 40G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 1900M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = ALL_O_DIRECT
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 32
transaction_isolation = REPEATABLE-READ
innodb_stats_on_metadata = 0
server-id = 104
skip-slave-start
read_only = 1
binlog-format = MIXED
log-bin = mysql-bin
log-bin-index = mysql-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
expire_logs_days = 10
sync_binlog = 1
binlog_cache_size = 1M