GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Percona server performance degradation

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

  • Percona server performance degradation

    We have Percona server 5.5.29-30.0 up and running with the following configuration

    Code:
    innodb_additional_mem_pool_size = 128M
    innodb_buffer_pool_size = 310G
    innodb_buffer_pool_instances = 64
    innodb_thread_concurrency = 48
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 128M
    innodb_log_file_size = 1G
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_file_per_table = 1
    innodb_file_format = barracuda
    innodb_sync_spin_loops = 80
    innodb_buffer_pool_restore_at_startup = 300
    innodb_adaptive_hash_index_partitions = 20
    innodb_flush_method = O_DIRECT
    innodb_flush_neighbor_pages = cont
    innodb_log_block_size = 4096
    and every 5-7 days all of the queries are getting slower, restart of client application doesn't help, but after server restart everything looks fine.
    Below you can see graph from zabbix with query response time, at 05:00 the server was restarted.



    Has anyone ideas ? May be MySQL can work good with such huge buffer pool?
    Last edited by DmnBY; 04-26-2013, 03:49 AM.

  • #2
    Do you have more graphs? Preferably on:
    - disk i/o
    - flushed pages
    - buffer pool size and content (esp. dirty pages)
    - binary logging

    Comment


    • #3
      buffer pool

      Checkpoint age

      Commands

      Innodb pages

      Comment


      • #4
        Innodb rows

        Disk data (2 images because of buffer pool restore on startup and zabbix terrible scaling)



        Last edited by DmnBY; 04-29-2013, 04:13 PM.

        Comment


        • #5
          Disk IO


          Innodb pendings


          Last edited by DmnBY; 04-29-2013, 04:16 PM.

          Comment


          • #6
            Threads

            Comment


            • #7
              I don't see anything odd here. You say queries are getting slower. How much slower? Is this visible in a 10-day graph?

              I still miss direct disk stats. What is the swapiness? Have you checked vmstat swapping statistics?

              Could you check buffer pool mutex contention when the server is slow? See http://www.percona.com/doc/percona-s...ool_mutex.html

              There may be other troubleshooting hints in http://www.percona.com/resources/tec...uickly-percona

              Comment


              • #8
                On the first graph the number of queries with response time 0.01 (yellow line) after restart gets much lower. But the load on server is completely the same.
                swapiness is 0.
                Server is not very slow, its getting slower and this is enough for us to see the problems in our software with response time.

                Comment


                • #9
                  I have same problem in 5.1.54 (FreeBSD 9.0 amd64). After restart percona server works fast, but after some hours (about 24) it works very slow. There is some statistics:

                  query_response_time_stats when performance are degraded (resets each 2 min):

                  Code:
                  time count total
                  0.000001 0 0.000000
                  0.000010 165 0.000646
                  0.000100 9172 0.464741
                  0.001000 7510 2.384370
                  0.010000 2555 9.394653
                  0.100000 1447 67.788210
                  1.000000 2634 1040.339679
                  10.000000 210 336.660582
                  100.000000 0 0.000000
                  1000.000000 0 0.000000
                  10000.000000 0 0.000000
                  100000.000000 0 0.000000
                  1000000.00000 0 0.000000
                  After restart:
                  Code:
                  time count total
                  0.000001 1 0.000000
                  0.000010 354 0.001318
                  0.000100 22695 1.113743
                  0.001000 12765 3.382774
                  0.010000 4640 15.994595
                  0.100000 1821 97.915290
                  1.000000 901 178.562421
                  10.000000 1 1.671335
                  100.000000 0 0.000000
                  1000.000000 0 0.000000
                  10000.000000 0 0.000000
                  100000.000000 0 0.000000
                  1000000.00000 0 0.000000
                  Status when performance are degraded

                  Code:
                  
                  	Variable_name   Value
                  
                  
                  	Aborted_clients 56
                  
                  
                  	Aborted_connects        154
                  
                  
                  	Binlog_cache_disk_use   6130
                  
                  
                  	Binlog_cache_use        2110517
                  
                  
                  	Bytes_received  115
                  
                  
                  	Bytes_sent      188
                  
                  
                  	Com_admin_commands      0
                  
                  
                  	Com_assign_to_keycache  0
                  
                  
                  	Com_alter_db    0
                  
                  
                  	Com_alter_db_upgrade    0
                  
                  
                  	Com_alter_event 0
                  
                  
                  	Com_alter_function      0
                  
                  
                  	Com_alter_procedure     0
                  
                  
                  	Com_alter_server        0
                  
                  
                  	Com_alter_table 0
                  
                  
                  	Com_alter_tablespace    0
                  
                  
                  	Com_analyze     0
                  
                  
                  	Com_backup_table        0
                  
                  
                  	Com_begin       0
                  
                  
                  	Com_binlog      0
                  
                  
                  	Com_call_procedure      0
                  
                  
                  	Com_change_db   0
                  
                  
                  	Com_change_master       0
                  
                  
                  	Com_check       0
                  
                  
                  	Com_checksum    0
                  
                  
                  	Com_commit      0
                  
                  
                  	Com_create_db   0
                  
                  
                  	Com_create_event        0
                  
                  
                  	Com_create_function     0
                  
                  
                  	Com_create_index        0
                  
                  
                  	Com_create_procedure    0
                  
                  
                  	Com_create_server       0
                  
                  
                  	Com_create_table        0
                  
                  
                  	Com_create_trigger      0
                  
                  
                  	Com_create_udf  0
                  
                  
                  	Com_create_user 0
                  
                  
                  	Com_create_view 0
                  
                  
                  	Com_dealloc_sql 0
                  
                  
                  	Com_delete      0
                  
                  
                  	Com_delete_multi        0
                  
                  
                  	Com_do  0
                  
                  
                  	Com_drop_db     0
                  
                  
                  	Com_drop_event  0
                  
                  
                  	Com_drop_function       0
                  
                  
                  	Com_drop_index  0
                  
                  
                  	Com_drop_procedure      0
                  
                  
                  	Com_drop_server 0
                  
                  
                  	Com_drop_table  0
                  
                  
                  	Com_drop_trigger        0
                  
                  
                  	Com_drop_user   0
                  
                  
                  	Com_drop_view   0
                  
                  
                  	Com_empty_query 0
                  
                  
                  	Com_execute_sql 0
                  
                  
                  	Com_flush       0
                  
                  
                  	Com_grant       0
                  
                  
                  	Com_ha_close    0
                  
                  
                  	Com_ha_open     0
                  
                  
                  	Com_ha_read     0
                  
                  
                  	Com_help        0
                  
                  
                  	Com_insert      0
                  
                  
                  	Com_insert_select       0
                  
                  
                  	Com_install_plugin      0
                  
                  
                  	Com_kill        0
                  
                  
                  	Com_load        0
                  
                  
                  	Com_load_master_data    0
                  
                  
                  	Com_load_master_table   0
                  
                  
                  	Com_lock_tables 0
                  
                  
                  	Com_optimize    0
                  
                  
                  	Com_preload_keys        0
                  
                  
                  	Com_prepare_sql 0
                  
                  
                  	Com_purge       0
                  
                  
                  	Com_purge_before_date   0
                  
                  
                  	Com_release_savepoint   0
                  
                  
                  	Com_rename_table        0
                  
                  
                  	Com_rename_user 0
                  
                  
                  	Com_repair      0
                  
                  
                  	Com_replace     0
                  
                  
                  	Com_replace_select      0
                  
                  
                  	Com_reset       0
                  
                  
                  	Com_restore_table       0
                  
                  
                  	Com_revoke      0
                  
                  
                  	Com_revoke_all  0
                  
                  
                  	Com_rollback    0
                  
                  
                  	Com_rollback_to_savepoint       0
                  
                  
                  	Com_savepoint   0
                  
                  
                  	Com_select      1
                  
                  
                  	Com_set_option  0
                  
                  
                  	Com_show_authors        0
                  
                  
                  	Com_show_binlog_events  0
                  
                  
                  	Com_show_binlogs        0
                  
                  
                  	Com_show_charsets       0
                  
                  
                  	Com_show_client_statistics      0
                  
                  
                  	Com_show_collations     0
                  
                  
                  	Com_show_column_types   0
                  
                  
                  	Com_show_contributors   0
                  
                  
                  	Com_show_create_db      0
                  
                  
                  	Com_show_create_event   0
                  
                  
                  	Com_show_create_func    0
                  
                  
                  	Com_show_create_proc    0
                  
                  
                  	Com_show_create_table   0
                  
                  
                  	Com_show_create_trigger 0
                  
                  
                  	Com_show_databases      0
                  
                  
                  	Com_show_engine_logs    0
                  
                  
                  	Com_show_engine_mutex   0
                  
                  
                  	Com_show_engine_status  0
                  
                  
                  	Com_show_events 0
                  
                  
                  	Com_show_errors 0
                  
                  
                  	Com_show_fields 0
                  
                  
                  	Com_show_function_status        0
                  
                  
                  	Com_show_grants 0
                  
                  
                  	Com_show_index_statistics       0
                  
                  
                  	Com_show_keys   0
                  
                  
                  	Com_show_master_status  0
                  
                  
                  	Com_show_new_master     0
                  
                  
                  	Com_show_open_tables    0
                  
                  
                  	Com_show_patches        0
                  
                  
                  	Com_show_plugins        0
                  
                  
                  	Com_show_privileges     0
                  
                  
                  	Com_show_procedure_status       0
                  
                  
                  	Com_show_processlist    0
                  
                  
                  	Com_show_profile        0
                  
                  
                  	Com_show_profiles       0
                  
                  
                  	Com_show_slave_hosts    0
                  
                  
                  	Com_show_slave_status   0
                  
                  
                  	Com_show_slave_status_nolock    0
                  
                  
                  	Com_show_status 1
                  
                  
                  	Com_show_storage_engines        0
                  
                  
                  	Com_show_table_statistics       0
                  
                  
                  	Com_show_table_status   0
                  
                  
                  	Com_show_tables 0
                  
                  
                  	Com_show_thread_statistics      0
                  
                  
                  	Com_show_temporary_tables       0
                  
                  
                  	Com_show_triggers       0
                  
                  
                  	Com_show_user_statistics        0
                  
                  
                  	Com_show_variables      0
                  
                  
                  	Com_show_warnings       0
                  
                  
                  	Com_slave_start 0
                  
                  
                  	Com_slave_stop  0
                  
                  
                  	Com_stmt_close  0
                  
                  
                  	Com_stmt_execute        0
                  
                  
                  	Com_stmt_fetch  0
                  
                  
                  	Com_stmt_prepare        0
                  
                  
                  	Com_stmt_reprepare      0
                  
                  
                  	Com_stmt_reset  0
                  
                  
                  	Com_stmt_send_long_data 0
                  
                  
                  	Com_truncate    0
                  
                  
                  	Com_uninstall_plugin    0
                  
                  
                  	Com_unlock_tables       0
                  
                  
                  	Com_update      0
                  
                  
                  	Com_update_multi        0
                  
                  
                  	Com_xa_commit   0
                  
                  
                  	Com_xa_end      0
                  
                  
                  	Com_xa_prepare  0
                  
                  
                  	Com_xa_recover  0
                  
                  
                  	Com_xa_rollback 0
                  
                  
                  	Com_xa_start    0
                  
                  
                  	Compression     OFF
                  
                  
                  	Connections     259251
                  
                  
                  	Created_tmp_disk_tables 0
                  
                  
                  	Created_tmp_files       8883
                  
                  
                  	Created_tmp_tables      0
                  
                  
                  	Delayed_errors  0
                  
                  
                  	Delayed_insert_threads  2
                  
                  
                  	Delayed_writes  133198
                  
                  
                  	Flashcache_enabled      OFF
                  
                  
                  	Flush_commands  1
                  
                  
                  	Handler_commit  0
                  
                  
                  	Handler_delete  0
                  
                  
                  	Handler_discover        0
                  
                  
                  	Handler_prepare 0
                  
                  
                  	Handler_read_first      0
                  
                  
                  	Handler_read_key        0
                  
                  
                  	Handler_read_next       0
                  
                  
                  	Handler_read_prev       0
                  
                  
                  	Handler_read_rnd        0
                  
                  
                  	Handler_read_rnd_next   0
                  
                  
                  	Handler_rollback        0
                  
                  
                  	Handler_savepoint       0
                  
                  
                  	Handler_savepoint_rollback      0
                  
                  
                  	Handler_update  0
                  
                  
                  	Handler_write   0
                  
                  
                  	Innodb_buffer_pool_pages_data   852611
                  
                  
                  	Innodb_buffer_pool_pages_dirty  312
                  
                  
                  	Innodb_buffer_pool_pages_flushed        6580420
                  
                  
                  	Innodb_buffer_pool_pages_free   919228
                  
                  
                  	Innodb_buffer_pool_pages_misc   63169
                  
                  
                  	Innodb_buffer_pool_pages_total  1835008
                  
                  
                  	Innodb_buffer_pool_read_ahead   115089
                  
                  
                  	Innodb_buffer_pool_read_ahead_evicted   0
                  
                  
                  	Innodb_buffer_pool_read_requests        52557150083
                  
                  
                  	Innodb_buffer_pool_reads        507097
                  
                  
                  	Innodb_buffer_pool_wait_free    0
                  
                  
                  	Innodb_buffer_pool_write_requests       493538656
                  
                  
                  	Innodb_data_fsyncs      6401331
                  
                  
                  	Innodb_data_pending_fsyncs      0
                  
                  
                  	Innodb_data_pending_reads       0
                  
                  
                  	Innodb_data_pending_writes      0
                  
                  
                  	Innodb_data_read        10896773120
                  
                  
                  	Innodb_data_reads       555665
                  
                  
                  	Innodb_data_writes      9272785
                  
                  
                  	Innodb_data_written     131561846784
                  
                  
                  	Innodb_dblwr_pages_written      0
                  
                  
                  	Innodb_deadlocks        0
                  
                  
                  	Innodb_dblwr_writes     0
                  
                  
                  	Innodb_dict_tables      382
                  
                  
                  	Innodb_have_atomic_builtins     ON
                  
                  
                  	Innodb_log_waits        0
                  
                  
                  	Innodb_log_write_requests       50662374
                  
                  
                  	Innodb_log_writes       1933044
                  
                  
                  	Innodb_os_log_fsyncs    1955611
                  
                  
                  	Innodb_os_log_pending_fsyncs    0
                  
                  
                  	Innodb_os_log_pending_writes    0
                  
                  
                  	Innodb_os_log_written   23735574016
                  
                  
                  	Innodb_page_size        16384
                  
                  
                  	Innodb_pages_created    5485198
                  
                  
                  	Innodb_pages_read       664953
                  
                  
                  	Innodb_pages_written    6580420
                  
                  
                  	Innodb_row_lock_current_waits   0
                  
                  
                  	Innodb_row_lock_time    1024994
                  
                  
                  	Innodb_row_lock_time_avg        10459
                  
                  
                  	Innodb_row_lock_time_max        92585
                  
                  
                  	Innodb_row_lock_waits   98
                  
                  
                  	Innodb_rows_deleted     8180322
                  
                  
                  	Innodb_rows_inserted    23039851
                  
                  
                  	Innodb_rows_read        67296077907
                  
                  
                  	Innodb_rows_updated     3001715
                  
                  
                  	Key_blocks_not_flushed  0
                  
                  
                  	Key_blocks_unused       800000
                  
                  
                  	Key_blocks_used 57376
                  
                  
                  	Key_read_requests       147480037
                  
                  
                  	Key_reads       56872
                  
                  
                  	Key_write_requests      5282504
                  
                  
                  	Key_writes      742619
                  
                  
                  	Last_query_cost 0.000000
                  
                  
                  	Max_used_connections    39
                  
                  
                  	Not_flushed_delayed_rows        0
                  
                  
                  	Open_files      460
                  
                  
                  	Open_streams    0
                  
                  
                  	Open_table_definitions  431
                  
                  
                  	Open_tables     1578
                  
                  
                  	Opened_files    17114921
                  
                  
                  	Opened_table_definitions        0
                  
                  
                  	Opened_tables   0
                  
                  
                  	Prepared_stmt_count     0
                  
                  
                  	Qcache_free_blocks      28166
                  
                  
                  	Qcache_free_memory      95901016
                  
                  
                  	Qcache_hits     241166
                  
                  
                  	Qcache_inserts  17846299
                  
                  
                  	Qcache_lowmem_prunes    6041433
                  
                  
                  	Qcache_not_cached       5724637
                  
                  
                  	Qcache_queries_in_cache 81740
                  
                  
                  	Qcache_total_blocks     196354
                  
                  
                  	Queries 42349353
                  
                  
                  	Questions       2
                  
                  
                  	Rpl_status      NULL
                  
                  
                  	Select_full_join        0
                  
                  
                  	Select_full_range_join  0
                  
                  
                  	Select_range    0
                  
                  
                  	Select_range_check      0
                  
                  
                  	Select_scan     0
                  
                  
                  	Slave_open_temp_tables  0
                  
                  
                  	Slave_retried_transactions      0
                  
                  
                  	Slave_running   OFF
                  
                  
                  	Slow_launch_threads     0
                  
                  
                  	Slow_queries    0
                  
                  
                  	Sort_merge_passes       0
                  
                  
                  	Sort_range      0
                  
                  
                  	Sort_rows       0
                  
                  
                  	Sort_scan       0
                  
                  
                  	Table_locks_immediate   65739338
                  
                  
                  	Table_locks_waited      67201
                  
                  
                  	Tc_log_max_pages_used   0
                  
                  
                  	Tc_log_page_size        0
                  
                  
                  	Tc_log_page_waits       1
                  
                  
                  	Threads_cached  33
                  
                  
                  	Threads_connected       8
                  
                  
                  	Threads_created 39
                  
                  
                  	Threads_running 5
                  
                  
                  	Uptime  255146
                  
                  
                  	Uptime_since_flush_status       255146




                  Status after restart (10 min ago)


                  Code:
                  
                  	Variable_name   Value
                  
                  
                  	Aborted_clients 0
                  
                  
                  	Aborted_connects        0
                  
                  
                  	Binlog_cache_disk_use   5
                  
                  
                  	Binlog_cache_use        8139
                  
                  
                  	Bytes_received  115
                  
                  
                  	Bytes_sent      188
                  
                  
                  	Com_admin_commands      0
                  
                  
                  	Com_assign_to_keycache  0
                  
                  
                  	Com_alter_db    0
                  
                  
                  	Com_alter_db_upgrade    0
                  
                  
                  	Com_alter_event 0
                  
                  
                  	Com_alter_function      0
                  
                  
                  	Com_alter_procedure     0
                  
                  
                  	Com_alter_server        0
                  
                  
                  	Com_alter_table 0
                  
                  
                  	Com_alter_tablespace    0
                  
                  
                  	Com_analyze     0
                  
                  
                  	Com_backup_table        0
                  
                  
                  	Com_begin       0
                  
                  
                  	Com_binlog      0
                  
                  
                  	Com_call_procedure      0
                  
                  
                  	Com_change_db   0
                  
                  
                  	Com_change_master       0
                  
                  
                  	Com_check       0
                  
                  
                  	Com_checksum    0
                  
                  
                  	Com_commit      0
                  
                  
                  	Com_create_db   0
                  
                  
                  	Com_create_event        0
                  
                  
                  	Com_create_function     0
                  
                  
                  	Com_create_index        0
                  
                  
                  	Com_create_procedure    0
                  
                  
                  	Com_create_server       0
                  
                  
                  	Com_create_table        0
                  
                  
                  	Com_create_trigger      0
                  
                  
                  	Com_create_udf  0
                  
                  
                  	Com_create_user 0
                  
                  
                  	Com_create_view 0
                  
                  
                  	Com_dealloc_sql 0
                  
                  
                  	Com_delete      0
                  
                  
                  	Com_delete_multi        0
                  
                  
                  	Com_do  0
                  
                  
                  	Com_drop_db     0
                  
                  
                  	Com_drop_event  0
                  
                  
                  	Com_drop_function       0
                  
                  
                  	Com_drop_index  0
                  
                  
                  	Com_drop_procedure      0
                  
                  
                  	Com_drop_server 0
                  
                  
                  	Com_drop_table  0
                  
                  
                  	Com_drop_trigger        0
                  
                  
                  	Com_drop_user   0
                  
                  
                  	Com_drop_view   0
                  
                  
                  	Com_empty_query 0
                  
                  
                  	Com_execute_sql 0
                  
                  
                  	Com_flush       0
                  
                  
                  	Com_grant       0
                  
                  
                  	Com_ha_close    0
                  
                  
                  	Com_ha_open     0
                  
                  
                  	Com_ha_read     0
                  
                  
                  	Com_help        0
                  
                  
                  	Com_insert      0
                  
                  
                  	Com_insert_select       0
                  
                  
                  	Com_install_plugin      0
                  
                  
                  	Com_kill        0
                  
                  
                  	Com_load        0
                  
                  
                  	Com_load_master_data    0
                  
                  
                  	Com_load_master_table   0
                  
                  
                  	Com_lock_tables 0
                  
                  
                  	Com_optimize    0
                  
                  
                  	Com_preload_keys        0
                  
                  
                  	Com_prepare_sql 0
                  
                  
                  	Com_purge       0
                  
                  
                  	Com_purge_before_date   0
                  
                  
                  	Com_release_savepoint   0
                  
                  
                  	Com_rename_table        0
                  
                  
                  	Com_rename_user 0
                  
                  
                  	Com_repair      0
                  
                  
                  	Com_replace     0
                  
                  
                  	Com_replace_select      0
                  
                  
                  	Com_reset       0
                  
                  
                  	Com_restore_table       0
                  
                  
                  	Com_revoke      0
                  
                  
                  	Com_revoke_all  0
                  
                  
                  	Com_rollback    0
                  
                  
                  	Com_rollback_to_savepoint       0
                  
                  
                  	Com_savepoint   0
                  
                  
                  	Com_select      1
                  
                  
                  	Com_set_option  0
                  
                  
                  	Com_show_authors        0
                  
                  
                  	Com_show_binlog_events  0
                  
                  
                  	Com_show_binlogs        0
                  
                  
                  	Com_show_charsets       0
                  
                  
                  	Com_show_client_statistics      0
                  
                  
                  	Com_show_collations     0
                  
                  
                  	Com_show_column_types   0
                  
                  
                  	Com_show_contributors   0
                  
                  
                  	Com_show_create_db      0
                  
                  
                  	Com_show_create_event   0
                  
                  
                  	Com_show_create_func    0
                  
                  
                  	Com_show_create_proc    0
                  
                  
                  	Com_show_create_table   0
                  
                  
                  	Com_show_create_trigger 0
                  
                  
                  	Com_show_databases      0
                  
                  
                  	Com_show_engine_logs    0
                  
                  
                  	Com_show_engine_mutex   0
                  
                  
                  	Com_show_engine_status  0
                  
                  
                  	Com_show_events 0
                  
                  
                  	Com_show_errors 0
                  
                  
                  	Com_show_fields 0
                  
                  
                  	Com_show_function_status        0
                  
                  
                  	Com_show_grants 0
                  
                  
                  	Com_show_index_statistics       0
                  
                  
                  	Com_show_keys   0
                  
                  
                  	Com_show_master_status  0
                  
                  
                  	Com_show_new_master     0
                  
                  
                  	Com_show_open_tables    0
                  
                  
                  	Com_show_patches        0
                  
                  
                  	Com_show_plugins        0
                  
                  
                  	Com_show_privileges     0
                  
                  
                  	Com_show_procedure_status       0
                  
                  
                  	Com_show_processlist    0
                  
                  
                  	Com_show_profile        0
                  
                  
                  	Com_show_profiles       0
                  
                  
                  	Com_show_slave_hosts    0
                  
                  
                  	Com_show_slave_status   0
                  
                  
                  	Com_show_slave_status_nolock    0
                  
                  
                  	Com_show_status 1
                  
                  
                  	Com_show_storage_engines        0
                  
                  
                  	Com_show_table_statistics       0
                  
                  
                  	Com_show_table_status   0
                  
                  
                  	Com_show_tables 0
                  
                  
                  	Com_show_thread_statistics      0
                  
                  
                  	Com_show_temporary_tables       0
                  
                  
                  	Com_show_triggers       0
                  
                  
                  	Com_show_user_statistics        0
                  
                  
                  	Com_show_variables      0
                  
                  
                  	Com_show_warnings       0
                  
                  
                  	Com_slave_start 0
                  
                  
                  	Com_slave_stop  0
                  
                  
                  	Com_stmt_close  0
                  
                  
                  	Com_stmt_execute        0
                  
                  
                  	Com_stmt_fetch  0
                  
                  
                  	Com_stmt_prepare        0
                  
                  
                  	Com_stmt_reprepare      0
                  
                  
                  	Com_stmt_reset  0
                  
                  
                  	Com_stmt_send_long_data 0
                  
                  
                  	Com_truncate    0
                  
                  
                  	Com_uninstall_plugin    0
                  
                  
                  	Com_unlock_tables       0
                  
                  
                  	Com_update      0
                  
                  
                  	Com_update_multi        0
                  
                  
                  	Com_xa_commit   0
                  
                  
                  	Com_xa_end      0
                  
                  
                  	Com_xa_prepare  0
                  
                  
                  	Com_xa_recover  0
                  
                  
                  	Com_xa_rollback 0
                  
                  
                  	Com_xa_start    0
                  
                  
                  	Compression     OFF
                  
                  
                  	Connections     964
                  
                  
                  	Created_tmp_disk_tables 0
                  
                  
                  	Created_tmp_files       14
                  
                  
                  	Created_tmp_tables      0
                  
                  
                  	Delayed_errors  0
                  
                  
                  	Delayed_insert_threads  2
                  
                  
                  	Delayed_writes  704
                  
                  
                  	Flashcache_enabled      OFF
                  
                  
                  	Flush_commands  1
                  
                  
                  	Handler_commit  0
                  
                  
                  	Handler_delete  0
                  
                  
                  	Handler_discover        0
                  
                  
                  	Handler_prepare 0
                  
                  
                  	Handler_read_first      0
                  
                  
                  	Handler_read_key        0
                  
                  
                  	Handler_read_next       0
                  
                  
                  	Handler_read_prev       0
                  
                  
                  	Handler_read_rnd        0
                  
                  
                  	Handler_read_rnd_next   0
                  
                  
                  	Handler_rollback        0
                  
                  
                  	Handler_savepoint       0
                  
                  
                  	Handler_savepoint_rollback      0
                  
                  
                  	Handler_update  0
                  
                  
                  	Handler_write   0
                  
                  
                  	Innodb_buffer_pool_pages_data   50879
                  
                  
                  	Innodb_buffer_pool_pages_dirty  738
                  
                  
                  	Innodb_buffer_pool_pages_flushed        10701
                  
                  
                  	Innodb_buffer_pool_pages_free   1781756
                  
                  
                  	Innodb_buffer_pool_pages_misc   2373
                  
                  
                  	Innodb_buffer_pool_pages_total  1835008
                  
                  
                  	Innodb_buffer_pool_read_ahead   2096
                  
                  
                  	Innodb_buffer_pool_read_ahead_evicted   0
                  
                  
                  	Innodb_buffer_pool_read_requests        197925577
                  
                  
                  	Innodb_buffer_pool_reads        48141
                  
                  
                  	Innodb_buffer_pool_wait_free    0
                  
                  
                  	Innodb_buffer_pool_write_requests       1358955
                  
                  
                  	Innodb_data_fsyncs      30687
                  
                  
                  	Innodb_data_pending_fsyncs      0
                  
                  
                  	Innodb_data_pending_reads       0
                  
                  
                  	Innodb_data_pending_writes      0
                  
                  
                  	Innodb_data_read        828493824
                  
                  
                  	Innodb_data_reads       48501
                  
                  
                  	Innodb_data_writes      34924
                  
                  
                  	Innodb_data_written     253442048
                  
                  
                  	Innodb_dblwr_pages_written      0
                  
                  
                  	Innodb_deadlocks        0
                  
                  
                  	Innodb_dblwr_writes     0
                  
                  
                  	Innodb_dict_tables      124
                  
                  
                  	Innodb_have_atomic_builtins     ON
                  
                  
                  	Innodb_log_waits        0
                  
                  
                  	Innodb_log_write_requests       161961
                  
                  
                  	Innodb_log_writes       9379
                  
                  
                  	Innodb_os_log_fsyncs    9417
                  
                  
                  	Innodb_os_log_pending_fsyncs    0
                  
                  
                  	Innodb_os_log_pending_writes    0
                  
                  
                  	Innodb_os_log_written   78097408
                  
                  
                  	Innodb_page_size        16384
                  
                  
                  	Innodb_pages_created    26039
                  
                  
                  	Innodb_pages_read       50433
                  
                  
                  	Innodb_pages_written    10701
                  
                  
                  	Innodb_row_lock_current_waits   0
                  
                  
                  	Innodb_row_lock_time    0
                  
                  
                  	Innodb_row_lock_time_avg        0
                  
                  
                  	Innodb_row_lock_time_max        0
                  
                  
                  	Innodb_row_lock_waits   0
                  
                  
                  	Innodb_rows_deleted     2251
                  
                  
                  	Innodb_rows_inserted    47140
                  
                  
                  	Innodb_rows_read        258897798
                  
                  
                  	Innodb_rows_updated     6016
                  
                  
                  	Key_blocks_not_flushed  1
                  
                  
                  	Key_blocks_unused       855901
                  
                  
                  	Key_blocks_used 1467
                  
                  
                  	Key_read_requests       643025
                  
                  
                  	Key_reads       1466
                  
                  
                  	Key_write_requests      19259
                  
                  
                  	Key_writes      2930
                  
                  
                  	Last_query_cost 0.000000
                  
                  
                  	Max_used_connections    12
                  
                  
                  	Not_flushed_delayed_rows        1
                  
                  
                  	Open_files      161
                  
                  
                  	Open_streams    0
                  
                  
                  	Open_table_definitions  130
                  
                  
                  	Open_tables     424
                  
                  
                  	Opened_files    82941
                  
                  
                  	Opened_table_definitions        0
                  
                  
                  	Opened_tables   0
                  
                  
                  	Prepared_stmt_count     0
                  
                  
                  	Qcache_free_blocks      1
                  
                  
                  	Qcache_free_memory      178309504
                  
                  
                  	Qcache_hits     73
                  
                  
                  	Qcache_inserts  70702
                  
                  
                  	Qcache_lowmem_prunes    0
                  
                  
                  	Qcache_not_cached       23411
                  
                  
                  	Qcache_queries_in_cache 39405
                  
                  
                  	Qcache_total_blocks     78891
                  
                  
                  	Queries 166623
                  
                  
                  	Questions       2
                  
                  
                  	Rpl_status      NULL
                  
                  
                  	Select_full_join        0
                  
                  
                  	Select_full_range_join  0
                  
                  
                  	Select_range    0
                  
                  
                  	Select_range_check      0
                  
                  
                  	Select_scan     0
                  
                  
                  	Slave_open_temp_tables  0
                  
                  
                  	Slave_retried_transactions      0
                  
                  
                  	Slave_running   OFF
                  
                  
                  	Slow_launch_threads     0
                  
                  
                  	Slow_queries    0
                  
                  
                  	Sort_merge_passes       0
                  
                  
                  	Sort_range      0
                  
                  
                  	Sort_rows       0
                  
                  
                  	Sort_scan       0
                  
                  
                  	Table_locks_immediate   281388
                  
                  
                  	Table_locks_waited      521
                  
                  
                  	Tc_log_max_pages_used   0
                  
                  
                  	Tc_log_page_size        0
                  
                  
                  	Tc_log_page_waits       0
                  
                  
                  	Threads_cached  4
                  
                  
                  	Threads_connected       10
                  
                  
                  	Threads_created 12
                  
                  
                  	Threads_running 8
                  
                  
                  	Uptime  376
                  
                  
                  	Uptime_since_flush_status       376

                  There is no SWAP (48 GB RAM). Disks are not busy. CPU load about 2 (4 cores+hypertrading). What information may helps?

                  Last edited by Vladimir; 05-08-2013, 12:48 AM. Reason: Add more info.

                  Comment


                  • #10
                    Dear DmnBY,



                    Could you share your zabbiz monitor templates for MySQL(Percona)?

                    Comment

                    Working...
                    X