High CPU usage on mysql
Posted by: wayne chen
Date: March 24, 2014 10:23AM
Date: March 24, 2014 10:23AM
We have a server running LAMP stack on CentOS release 5.6 (Final)
MySQL is having high CPU usage constantly which causes server to lag as shown
<code>
top - 13:50:47 up 80 days, 13:53, 1 user, load average: 4.02, 3.44, 3.19
Tasks: 129 total, 2 running, 127 sleeping, 0 stopped, 0 zombie
Cpu0 : 24.7%us, 19.8%sy, 0.2%ni, 54.5%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu1 : 23.0%us, 9.4%sy, 0.1%ni, 67.0%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4034120k total, 3317400k used, 716720k free, 124324k buffers
Swap: 8385920k total, 9788k used, 8376132k free, 2004692k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26266 mysql 15 0 2472m 464m 4448 S 158.6 11.8 109:00.59 mysqld
30276 apache 16 0 310m 16m 3724 S 12.1 0.4 0:00.85 httpd
30205 apache 16 0 309m 17m 3860 S 8.6 0.4 0:01.81 httpd
</code>
<code>
MySQL version:
+----------------+
| 5.1.57-ius-log |
+----------------+
</code>
The server has 4GB RAM, tables are stored in MyISAM.
Our database is about 6.6GB. It has a replicated database (readonly) on another server
All our tables are in MyISAM, highest rows: 6930622
Info for a few large tables:
+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------------------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| MyISAM | 10 | Dynamic | 6930622 | 72 | 503120776 | 281474976710655 | 497158144 | 0 | 21578189 | 2013-04-17 02:06:20 | 2014-03-24 02:24:04 | 2013-04-17 02:08:47 | latin1_swedish_ci | NULL | | |
| MyISAM | 10 | Dynamic | 2197631 | 1315 | 2891479112 | 281474976710655 | 133948416 | 0 | 2197632 | 2012-02-07 23:02:45 | 2014-03-24 02:28:20 | 2013-04-17 00:53:55 | latin1_swedish_ci | NULL | | |
| MyISAM | 10 | Dynamic | 3362364 | 29 | 98158700 | 281474976710655 | 145138688 | 0 | 3363221 | 2011-06-17 01:36:48 | 2014-03-24 02:28:18 | 2012-11-12 11:00:23 | latin1_swedish_ci | NULL | | |
Here's our my.cnf (IP information Xed out).
<code>
[mysqld]
# General
datadir = /home/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking = 1
skip-name-resolve
open-files-limit = 20000
# Cache
thread-cache-size = 16
table-open-cache = 2048
table-definition-cache = 512
query-cache-size = 32M
query-cache-limit = 1M
# Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 8M
join-buffer-size = 1M
# Temp Tables
tmp-table-size = 64M
max-heap-table-size = 64M
# Networking
back-log = 100
max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 600
wait-timeout = 600
#Storage Engines
innodb = FORCE
# MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
# InnoDB
innodb-buffer-pool-size = 16M
innodb_log_files_in_group = 2
innodb-log-buffer-size = 4M
# Replication
server-id = 1
bind-address = XX.XX.XXX.XXX
log-bin = /var/lib/mysqllogs/bin-log
binlog-do-db = rewards1
binlog-ignore-db = mysql
relay-log-index=master-relay-bin.index
relay-log = /var/lib/mysqllogs/relay-log
#Logging
slow-query-log = 1
slow-query-log-file = /var/log/slow-log
#MysqlTuner
skip-innodb
max_connections = 200
wait_timeout = 500
interactive_timeout = 500
query_cache_size = 64M
join_buffer_size = 5M
tmp_table_size = 256M
max_heap_table_size = 256M
table_cache = 4096
key_buffer_size = 2048M
[mysqld_safe]
log-error = /var/log/mysqld.log
</code>
We've seen similar problem as described in
http://dba.stackexchange.com/questions/41601/mysql-high-cpu-usage-myisam-table-indexes
which I've adjust the key_buffer_size to 2048M so it's greater than MyISAM index
<code>
mysql> sELECT SUM(index_length) ndxsize FROM information_schema.tables WHERE engine='MyISAM';
+------------+
| ndxsize |
+------------+
| 1876895744 |
+------------+
</code>
MySQL continues to have high %CPU, our system admin suggested that we add more RAM to the box, will this solve the problem?
Any suggestions are appreciated. Thanks!!
Below is some more detailed information that I pulled out:
<code>
mysql> show status;
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 7 |
| Aborted_connects | 101 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 115 |
| Bytes_sent | 186 |
| 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_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_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 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_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 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 | 968489 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 11032 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| 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 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1437009 |
| Key_blocks_used | 277727 |
| Key_read_requests | 2674695082 |
| Key_reads | 275649 |
| Key_write_requests | 5678071 |
| Key_writes | 306760 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 54 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1567 |
| Open_streams | 0 |
| Open_table_definitions | 531 |
| Open_tables | 1041 |
| Opened_files | 1526891 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 10730 |
| Qcache_free_memory | 35561560 |
| Qcache_hits | 4540664 |
| Qcache_inserts | 4497693 |
| Qcache_lowmem_prunes | 438462 |
| Qcache_not_cached | 698900 |
| Qcache_queries_in_cache | 17658 |
| Qcache_total_blocks | 46546 |
| Queries | 13187921 |
| 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 | 3 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 11982888 |
| Table_locks_waited | 158688 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 13 |
| Threads_connected | 4 |
| Threads_created | 3811 |
| Threads_running | 4 |
| Uptime | 169005 |
| Uptime_since_flush_status | 169005 |
+--------------------------------+------------+
249 rows in set (0.00 sec)
</code>
A few samples of mysql: SHOW PROCESSLIST, there doesn't seem to be long query locking the table.
<code>
mysql> show processlist;
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+-
| 275 | repl | 64.237.59.18:55594 | NULL | Binlog Dump | 224213 | Has sent all binlog to slave; waiting for binlog to be updated |
| 1278042 | root | localhost | rewards1 | Query | 0 | NULL |
| 1279440 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279441 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279444 | rewards_user | localhost | rewards1 | Query | 1 | Locked |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------mysql> show processlist;
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+-
| Id | User | Host | db | Command | Time | State |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
| 275 | repl | 64.237.59.18:55594 | NULL | Binlog Dump | 224383 | Has sent all binlog to slave; waiting for binlog to be updated |
| 1278042 | root | localhost | rewards1 | Query | 0 | NULL |
| 1279972 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279973 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279974 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279975 | rewards_user | localhost | rewards1 | Query | 0 | Sending data |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
</code>
Edited 1 time(s). Last edit at 03/24/2014 10:25AM by wayne chen.
MySQL is having high CPU usage constantly which causes server to lag as shown
<code>
top - 13:50:47 up 80 days, 13:53, 1 user, load average: 4.02, 3.44, 3.19
Tasks: 129 total, 2 running, 127 sleeping, 0 stopped, 0 zombie
Cpu0 : 24.7%us, 19.8%sy, 0.2%ni, 54.5%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Cpu1 : 23.0%us, 9.4%sy, 0.1%ni, 67.0%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4034120k total, 3317400k used, 716720k free, 124324k buffers
Swap: 8385920k total, 9788k used, 8376132k free, 2004692k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26266 mysql 15 0 2472m 464m 4448 S 158.6 11.8 109:00.59 mysqld
30276 apache 16 0 310m 16m 3724 S 12.1 0.4 0:00.85 httpd
30205 apache 16 0 309m 17m 3860 S 8.6 0.4 0:01.81 httpd
</code>
<code>
MySQL version:
+----------------+
| 5.1.57-ius-log |
+----------------+
</code>
The server has 4GB RAM, tables are stored in MyISAM.
Our database is about 6.6GB. It has a replicated database (readonly) on another server
All our tables are in MyISAM, highest rows: 6930622
Info for a few large tables:
+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------------------------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| MyISAM | 10 | Dynamic | 6930622 | 72 | 503120776 | 281474976710655 | 497158144 | 0 | 21578189 | 2013-04-17 02:06:20 | 2014-03-24 02:24:04 | 2013-04-17 02:08:47 | latin1_swedish_ci | NULL | | |
| MyISAM | 10 | Dynamic | 2197631 | 1315 | 2891479112 | 281474976710655 | 133948416 | 0 | 2197632 | 2012-02-07 23:02:45 | 2014-03-24 02:28:20 | 2013-04-17 00:53:55 | latin1_swedish_ci | NULL | | |
| MyISAM | 10 | Dynamic | 3362364 | 29 | 98158700 | 281474976710655 | 145138688 | 0 | 3363221 | 2011-06-17 01:36:48 | 2014-03-24 02:28:18 | 2012-11-12 11:00:23 | latin1_swedish_ci | NULL | | |
Here's our my.cnf (IP information Xed out).
<code>
[mysqld]
# General
datadir = /home/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking = 1
skip-name-resolve
open-files-limit = 20000
# Cache
thread-cache-size = 16
table-open-cache = 2048
table-definition-cache = 512
query-cache-size = 32M
query-cache-limit = 1M
# Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 8M
join-buffer-size = 1M
# Temp Tables
tmp-table-size = 64M
max-heap-table-size = 64M
# Networking
back-log = 100
max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 600
wait-timeout = 600
#Storage Engines
innodb = FORCE
# MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
# InnoDB
innodb-buffer-pool-size = 16M
innodb_log_files_in_group = 2
innodb-log-buffer-size = 4M
# Replication
server-id = 1
bind-address = XX.XX.XXX.XXX
log-bin = /var/lib/mysqllogs/bin-log
binlog-do-db = rewards1
binlog-ignore-db = mysql
relay-log-index=master-relay-bin.index
relay-log = /var/lib/mysqllogs/relay-log
#Logging
slow-query-log = 1
slow-query-log-file = /var/log/slow-log
#MysqlTuner
skip-innodb
max_connections = 200
wait_timeout = 500
interactive_timeout = 500
query_cache_size = 64M
join_buffer_size = 5M
tmp_table_size = 256M
max_heap_table_size = 256M
table_cache = 4096
key_buffer_size = 2048M
[mysqld_safe]
log-error = /var/log/mysqld.log
</code>
We've seen similar problem as described in
http://dba.stackexchange.com/questions/41601/mysql-high-cpu-usage-myisam-table-indexes
which I've adjust the key_buffer_size to 2048M so it's greater than MyISAM index
<code>
mysql> sELECT SUM(index_length) ndxsize FROM information_schema.tables WHERE engine='MyISAM';
+------------+
| ndxsize |
+------------+
| 1876895744 |
+------------+
</code>
MySQL continues to have high %CPU, our system admin suggested that we add more RAM to the box, will this solve the problem?
Any suggestions are appreciated. Thanks!!
Below is some more detailed information that I pulled out:
<code>
mysql> show status;
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 7 |
| Aborted_connects | 101 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 115 |
| Bytes_sent | 186 |
| 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_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_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 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_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 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 | 968489 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 11032 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| 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 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1437009 |
| Key_blocks_used | 277727 |
| Key_read_requests | 2674695082 |
| Key_reads | 275649 |
| Key_write_requests | 5678071 |
| Key_writes | 306760 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 54 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1567 |
| Open_streams | 0 |
| Open_table_definitions | 531 |
| Open_tables | 1041 |
| Opened_files | 1526891 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 10730 |
| Qcache_free_memory | 35561560 |
| Qcache_hits | 4540664 |
| Qcache_inserts | 4497693 |
| Qcache_lowmem_prunes | 438462 |
| Qcache_not_cached | 698900 |
| Qcache_queries_in_cache | 17658 |
| Qcache_total_blocks | 46546 |
| Queries | 13187921 |
| 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 | 3 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 11982888 |
| Table_locks_waited | 158688 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 13 |
| Threads_connected | 4 |
| Threads_created | 3811 |
| Threads_running | 4 |
| Uptime | 169005 |
| Uptime_since_flush_status | 169005 |
+--------------------------------+------------+
249 rows in set (0.00 sec)
</code>
A few samples of mysql: SHOW PROCESSLIST, there doesn't seem to be long query locking the table.
<code>
mysql> show processlist;
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+-
| 275 | repl | 64.237.59.18:55594 | NULL | Binlog Dump | 224213 | Has sent all binlog to slave; waiting for binlog to be updated |
| 1278042 | root | localhost | rewards1 | Query | 0 | NULL |
| 1279440 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279441 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279444 | rewards_user | localhost | rewards1 | Query | 1 | Locked |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------mysql> show processlist;
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+-
| Id | User | Host | db | Command | Time | State |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
| 275 | repl | 64.237.59.18:55594 | NULL | Binlog Dump | 224383 | Has sent all binlog to slave; waiting for binlog to be updated |
| 1278042 | root | localhost | rewards1 | Query | 0 | NULL |
| 1279972 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279973 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279974 | rewards_user | localhost | rewards1 | Query | 1 | Sending data |
| 1279975 | rewards_user | localhost | rewards1 | Query | 0 | Sending data |
+---------+--------------+--------------------+----------+-------------+--------+----------------------------------------------------------------+
</code>
Edited 1 time(s). Last edit at 03/24/2014 10:25AM by wayne chen.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.