MySQL 5.5 performance settings

Question

We are using server this theses parameters: 8 cores CPU 18GB RAM MySQL version 5.5

We would like to get the best performance. The DB is InnoDB type. I would like to know your experience and maybe you help me for performance.

[client]
#password   = [your_password]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld]

port        = 3306
socket      = /var/run/mysqld/mysqld.sock


back_log = 100
max_connections = 151
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M    
binlog_cache_size = 1M   
max_heap_table_size = 128M    
read_buffer_size = 2M   
read_rnd_buffer_size = 16M    
sort_buffer_size = 16M    
join_buffer_size = 16M    
thread_cache_size = 8    
thread_concurrency = 8   
query_cache_size = 256M    
query_cache_limit = 32M   
ft_min_word_len = 4    
memlock   
innodb = ON
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M

log-bin=/var/lib/mysql/log/mysql-bin.log

binlog-do-db=db_X1
binlog-do-db=db_X2
binlog-do-db=db_X3
binlog-do-db=db_X4
binlog-do-db=db_X5
binlog-do-db=db_X6

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 10

#*** MyISAM Specific options
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 512M
innodb_buffer_pool_size = 12G
innodb_data_file_path = ibdata1:20G:autoextend
innodb_write_io_threads = 32
innodb_read_io_threads = 32
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[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

Is it correct settings for best performance?

There is no universal *best* ; you should tune for the best performance *for your load.* Start for instance with https://dev.mysql.com/doc/refman/5.7/en/optimization.html — HBruijn, Nov 11 '15 at 05:31
at ths moment we are using 5.5 and can't use higher version. Example for query_cache_size or read_buffer_size? — Myk Syk, Nov 11 '15 at 06:04
Simply change the link above to 5.5 for your version of the docs. Correct settings depend on your load and your server specs, which we don't know. Also check the linked Q&A's in the right column such as http://serverfault.com/questions/414031/optimal-mysql-my-cnf-setting-for-large-magento-site-on-a-dedicated-server — HBruijn, Nov 11 '15 at 07:09
I have only max select ~21qps and max insert 51qps. I think it slovly. thread cached 8, thread connected ~46. Sort rows about avg 3,5K. — Myk Syk, Nov 11 '15 at 07:23

MySQL 5.5 performance settings

0 Answers0