我服务器配置是:Linux 5.5 CPU:16核,内存:64GB    MySql 5.5.18
问题是:
MySQL稳定运行10天,内存在15G左右,CPU也很平稳,在5%左右。10天以后(也不一定,也就是达到一定时间以后),在其后的几天内,内存增长加速,一天内达到10%,而且CPU也会增长。这个问题一直没有查出来,网上内似的信息也很少。这还是跟内存的使用有关,但也找不到具体的原因。各位高手帮忙看看,是什么个问题。现在增长中的状态值:(大部分是0的我都去掉了,免得看起来很多。)
Aborted_clients 28936121
Aborted_connects 2332
Binlog_cache_use 4481333
Binlog_stmt_cache_use 1937129
Bytes_received 2564
Bytes_sent 182321
Com_select 12
Com_set_option 8
Compression OFF
Connections 43985567
Created_tmp_files 481
Created_tmp_tables 24
Handler_read_key 1431
Handler_read_rnd 171
Handler_read_rnd_next 2935
Handler_update 1260
Handler_write 2911
Innodb_buffer_pool_pages_data 569825
Innodb_buffer_pool_pages_dirty 344
Innodb_buffer_pool_pages_flushed 29073286
Innodb_buffer_pool_pages_free 1485891
Innodb_buffer_pool_pages_misc 41435
Innodb_buffer_pool_pages_total 2097151
Innodb_buffer_pool_read_ahead 73057
Innodb_buffer_pool_read_requests 134992208571
Innodb_buffer_pool_reads 424003
Innodb_buffer_pool_write_requests 62473513
Innodb_data_fsyncs 10666195
Innodb_data_pending_fsyncs 1
Innodb_data_read 8436584448
Innodb_data_reads 444508
Innodb_data_writes 21480882
Innodb_data_written 486137894912
Innodb_dblwr_pages_written 14536643
Innodb_dblwr_writes 220262
Innodb_have_atomic_builtins ON
Innodb_log_write_requests 14694711
Innodb_log_writes 9129785
Innodb_os_log_fsyncs 9215320
Innodb_os_log_pending_fsyncs 1
Innodb_os_log_written 9757420032
Innodb_page_size 16384
Innodb_pages_created 58204
Innodb_pages_read 514630
Innodb_pages_written 14536643
Innodb_row_lock_time 639073
Innodb_row_lock_time_avg 176
Innodb_row_lock_time_max 31962
Innodb_row_lock_waits 3611
Innodb_rows_deleted 77365
Innodb_rows_inserted 2151604
Innodb_rows_read 109709175982
Innodb_rows_updated 3938873
Key_blocks_not_flushed 2
Key_blocks_unused 26768
Key_blocks_used 984
Key_read_requests 703118813
Key_reads 49
Key_write_requests 30062010
Key_writes 516196
Last_query_cost 12.499000
Max_used_connections 501
Open_files 332
Open_table_definitions 113
Open_tables 887
Opened_files 59764277
Prepared_stmt_count 94
Qcache_free_blocks 4
Qcache_free_memory 67030952
Qcache_hits 28062
Qcache_inserts 13686
Qcache_not_cached 1079396116
Qcache_queries_in_cache 24
Qcache_total_blocks 64
Queries 7995684443
Questions 48
Select_scan 12
Sort_rows 171
Sort_scan 12
Table_locks_immediate 5233183853
Table_locks_waited 1314
Threads_cached 27
Threads_connected 173
Threads_created 1965
Threads_running 3
Uptime 828692
Uptime_since_flush_status 828692
my.cnf的配置如下:
[client]
back_log = 256
max_connections = 500
max_connect_errors = 10000
table_open_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 1M
read_buffer_size = 12M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 64
thread_concurrency = 32
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 5G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 32G
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 16
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
skip-name-resolve[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M[mysqlhotcopy]
interactive-timeout[mysqld_safe]
open-files-limit = 8192
内存:
$ free -m
             total       used       free     shared    buffers     cached
Mem:         64449      41183      23266          0        164       4810
-/+ buffers/cache:      36208      28241
Swap:         4094          0       4094

解决方案 »

  1.   

    innodb_buffer_pool_size = 32G
    这个值设置太大小点,10G
      

  2.   

    你好,因为服务器内存很大,而且我们主以innodb引擎为主,都没有用myisam的表,所以设的有点大,这个会有影响吗,我设成32G,那LINUX内MYSQL占用的内存应该不会大于这个值啊??
      

  3.   

    看下慢查询  定位到具体是哪些sql导致的