Announcement

Announcement Module
Collapse
No announcement yet.

Long Opening tables and flush_key_blocks_int()

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

  • Long Opening tables and flush_key_blocks_int()

    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:
    [mysqld]
    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

Working...
X