服务器版本 5.0.67-community-log
内存32G,8核cpu
数据库进程数保持在200-300之间
io负载并不大,保持在15%左右,内存吃了85%, innodb buffer pool使用率100%(innodb_buffer_pool_size = 24G), free 0
现在遇到的问题是在高并发下查询和写入性能不高,表使用的都是InnoDB
数据库构架是master带2个slave,慢查询基本集中在master上
慢查询基本上都是insert、update、select(因为有些业务要求实时性,所以从master上select),都是单表操作(已经做了32个分表,每个分表数据量级在1千万到4千万之间),查询时间集中在1-2秒之间
各位看一下还有哪些优化的思路
1. 改进InnoDB的配置
2. 继续在每个数据库实例上再进行分表
3. master改为MyISAM,slave用InnoDB,这样数据、索引损坏的概率大吗?索引损坏在master上修复貌似对服务有很大影响
下面是my.cnf的配置
我只列出了我觉得可能影响并发性能的一些参数
key_buffer = 1024M
max_allowed_packet = 1M
table_cache = 2048
join_buffer_size = 8M
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 24M
myisam_sort_buffer_size = 128M
query_cache_size = 128M
query_cache_limit = 2M
max_tmp_tables = 64
tmp_table_size = 192M
max_heap_table_size = 64M
thread_cache = 16
thread_concurrency = 4
max_connections = 1536
max_user_connections = 1024
back_log = 300
delayed_insert_limit = 256
max_delayed_threads = 256
wait_timeout = 30
interactive_timeout = 30
long_query_time = 1
delay_key_write = ALL
low_priority_updates
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 24G
innodb_log_buffer_size = 12M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_lock_wait_timeout = 20
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
transaction-isolation=READ-COMMITTED
innodb_file_io_threads = 4
innodb_thread_concurrency = 18
innodb_additional_mem_pool_size = 20M
innodb_max_dirty_pages_pct = 50