MySQL5.5 InnoDB INSER/更新非常慢

Question

我使用MySQL5.5和centos 7。我的数据库中有些表是InnoDB，我通过在互联网上阅读文章来优化my.cnf文件。我使用事务和提交。但是插入和更新非常缓慢，您可以看到。我不能使用MyISAM，因为这些表总是得到太多的插入、更新和读取。

查询照片

My.cnf文件

# The following options will be passed to all MySQL clients
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
#innodb_force_recovery=6
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
datadir=/var/lib/mysql/
log-error=/var/lib/mysql/server.mysql.err
symbolic-links=0
tmpdir=/var/tmp
skip-external-locking
table_cache = 2000
key_buffer_size=20G
join_buffer_size = 4M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size=2M
thread_cache_size = 512
query_cache_limit = 1G
query_cache_size = 40M
query_cache_type=1
thread_stack = 256K
tmp_table_size = 128M
max_heap_table_size = 128M
open_files_limit=65535
#thread_concurrency = 10
max_connect_errors=1
connect_timeout=60
interactive_timeout = 60
lock_wait_timeout=60
wait_timeout = 30
max_connections = 1000
slow_query_log=1
long_query_time=1
slow-query-log-file=/var/log/mysql-slow.log
#log-queries-not-using-indexes
innodb_buffer_pool_size=32G
innodb_additional_mem_pool_size=64M
innodb_data_file_path=ibdata1:100M:autoextend
innodb_log_buffer_size=128M
innodb-log-files-in-group = 2
innodb_change_buffering=all
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
#innodb_thread_concurrency=10
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_file_per_table=1
innodb_lock_wait_timeout = 60
innodb_table_locks=0
innodb_open_files=65535
innodb_io_capacity=2000
#innodb_doublewrite=0
#innodb_support_xa=0

[mysqldump]
max_allowed_packet=2G
quick

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 4M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
tmpdir=/tmp
key_buffer_size=128M
sort_buffer_size=4M
read_buffer=4M
write_buffer=4M

[mysqlhotcopy]
interactive-timeout

我的服务器属性是

CPU
Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz
Cores : 8
Cache : 10240KB
RAM
64 GB
Disks
3 x 160 GB SSD

Answer 1

query_cache_limit = 1G  -- should be less than the size.
query_cache_size = 40M  -- this is reasonable.
key_buffer_size=20G -- too big; change to 6G.  Note: only used for MyISAM indexes.
-- if you are not using MyISAM, then drop to 10M
slow_query_log=1 --

作为“最差”的3次查询，pt-query-摘要指出了什么？向我们展示它们，以及SHOW CREATE TABLE和(如果是SELECT)，EXPLAIN SELECT ...。

Answer 2

如果查询工作缓慢，则不必对优化的my.cnf文件进行调优。您可能必须通过适当的索引和联接来优化查询。