- 论坛徽章:
- 0
|
本帖最后由 a.a 于 2013-12-24 16:44 编辑
最近将数据库迁移到了新的版本5.6.15 ,加了内存、cpu 也升级,居然查询还慢了有些难以理解。
影响比较大的那个表里面有将近2000w的数据,做一个count居然需要5s,做其的它查询就更加惨不忍睹了。
64bit CentOS 6.3 ,
32G innodb_buffer_pool_size
调整了多次始终还是没能进步,优化方面的高手能给指点下?下面是主要的配置参数。
- [msqld]
- expire_logs_days = 7
- max_allowed_packet = 16M
- sort_buffer_size = 2G
- net_buffer_length = 8K
- read_buffer_size = 512M
- read_rnd_buffer_size = 512M
- myisam_sort_buffer_size = 1G
- binlog_format=mixed
- max_binlog_size=512M
- max_binlog_cache_size=1G
- max_binlog_stmt_cache_size=1G
- log-bin=mysql-bin
- skip-host-cache
- skip-name-resolve
- innodb_buffer_pool_size = 32G
- innodb_log_buffer_size = 64M
- innodb_thread_concurrency = 16
- innodb_flush_method=O_DIRECT
- innodb_file_per_table = true
- open-files-limit= 65535
- symbolic-links=0
- max-connections = 2000
- # increase query cache
- query_cache_limit = 512M
- query_cache_size = 512M
- query_cache_type = 1
- join_buffer_size = 64M
- innodb_io_capacity = 1000
- # replication
- server-id = 1
- plugin-load=rpl_semi_sync_master=semisync_master.so
- rpl_semi_sync_master_enabled=1
- rpl_semi_sync_master_timeout=1000
- performance_schema
-
复制代码 |
|