I have a MySQL server running on CentOS.
Recently I am having an issue that happens about every 2 days. The server is running fast and OK, but then suddenly it's getting very slow, until I restart MySQL, then it returns to a regular state.
This happened to me a couple of times, so this time I took 2 screenshots before I run service mysqld restart
.
Most of my tables are InnoDB, a few are MyISAM. (4 MyISAM tables, 38 InnoDB tables)
my.cnf:
[mysqld]
bulk_insert_buffer_size = 8M
concurrent_insert = 2
connect_timeout = 30
default-storage-engine = MyISAM
innodb_buffer_pool_size=1300M
innodb_file_per_table=1
interactive_timeout = 1000
join_buffer_size=128M
key_buffer_size = 1200M
local-infile=0
slow_query_log=1
long_query_time=0.5
#skip-grant-tables
max_allowed_packet = 900M
max_connections = 40000
max_heap_table_size = 256M
max_user_connections = 10000
max_write_lock_count = 8
myisam_max_sort_file_size = 256M
myisam_sort_buffer_size = 64M
open_files_limit = 10192
query_alloc_block_size = 65536
query_cache_limit = 256M
query_cache_size = 384M
query_cache_type = 1
query_prealloc_size = 262144
range_alloc_block_size = 4096
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 4M
table_cache = 8048
table_open_cache = 8000
thread_cache_size = 50
tmp_table_size = 256M
transaction_alloc_block_size = 4096
transaction_prealloc_size = 4096
#innodb_force_recovery=5
wait_timeout = 1000
max_connect_errors = 5000
open-files = 50000
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
SHOW GLOBAL STATUS LIKE '%connect%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| Aborted_connects | 0 |
| Connections | 859148 |
| Max_used_connections | 103 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+--------------------------+--------+
SHOW GLOBAL VARIABLES LIKE 'thread_%';
+---------------------------+---------------------------+
| Variable_name | Value |
+---------------------------+---------------------------+
| thread_cache_size | 50 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 500 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 8 |
| thread_pool_stall_limit | 500 |
| thread_stack | 294912 |
+---------------------------+---------------------------+
SHOW GLOBAL STATUS LIKE 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 49 |
| Threads_connected | 1 |
| Threads_created | 372 |
| Threads_running | 1 |
+-------------------+-------+
SHOW GLOBAL STATUS LIKE 'key_%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 1003901 |
| Key_blocks_used | 3365 |
| Key_blocks_warm | 0 |
| Key_read_requests | 99176 |
| Key_reads | 3052 |
| Key_write_requests | 29353 |
| Key_writes | 29347 |
+------------------------+---------+
SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 961 |
| Qcache_free_memory | 400828904 |
| Qcache_hits | 1634009 |
| Qcache_inserts | 1201887 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 59970 |
| Qcache_queries_in_cache | 1467 |
| Qcache_total_blocks | 3926 |
| Queries | 5316596 |
| Questions | 5187929 |
+-------------------------+-----------+
SHOW GLOBAL VARIABLES LIKE '_size';
Empty set
top
output, this server is way overprovisioned. You're definitely not out of RAM or CPU capacity. However, this data doesn't point to any kind of problem. Try looking elsewhere, such as disk I/O, CPU I/O wait, disk and filesystem health, or network utilization.