I use mysql 5.5 and centos 7. Some tables are InnoDB in my database and i optimized my.cnf file with i read articles in the internet. I use TRANSACTION and COMMIT. But insert and update very slow you can see. I can't use MyISAM because these tables always get insert, update and read same time too much.
My.cnf file
# 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
And my server properties are
CPU
Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz
Cores : 8
Cache : 10240KB
RAM
64 GB
Disks
3 x 160 GB SSD
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 --
What does pt-query-digest point out as the 'worst' 3 queries? Show us them, together with SHOW CREATE TABLE and (if SELECT), EXPLAIN SELECT ....