Fine tuning MYSQL for best performance

user_7242096 0
Viewed 260

I have a database server that runs on 128GB of RAM, 500GB HDD and 10core processor. I have installed mysqltuner and adjusted all variables as suggested but the MySQL performance improves for only a few hours and then goes back to sluggish performance. According to mytop, i have about 8 slow queries which I doubt can be the cause of this. Note that this server has only MySQL running.

How best can i further improve/optimize MySQL perfomance. Below is a snippet of mysql.conf file.

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer_size     = 128M
max_allowed_packet  = 400M
thread_stack        = 284K
thread_cache_size       = 64

innodb_buffer_pool_size = 4G
innodb_log_file_size = 576M
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_checksums = 0

join_buffer_size=500M
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=4M
table_definition_cache=4096
table_open_cache=4096
tmp_table_size=536870912
optimizer_search_depth=12
innodb_adaptive_hash_index=0
max_heap_table_size=1610612736
expire_logs_days    = 10
max_binlog_size   = 100M
mysql
3 Answers

You can't tune your way out of most performance problems. Identify the "slow" queries, present them here, together with SHOW CREATE TABLE and EXPLAIN.

The slowlog is a good way to discover which are 'worst'. http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Meanwhile, what version are you using? What ENGINE are you using for the tables? (Hopefully InnoDB for all of your tables.)

Since you have 128GB of RAM, 4G for the buffer pool is much too small; change:

innodb_buffer_pool_size = 90G
innodb_log_file_size = 2G
innodb_buffer_pool_instances = 16

Are you using ENGINE=MEMORY with some huge tables? If not, change to:

max_heap_table_size = 500M
user_17668310

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section to improve performance

tmp_table_size=1610612736  # from 536870912 to match max_heap_table_size and reduce created_tmp_table RPHr of 218
read_rnd_buffer_size=96K  # from 4M to reduce handler_read_rnd_next RPS of 1,005,923
read_buffer_size=512K  # from 4M to reduce handler_read_next RPS of 2,584,343
net_buffer_length=96K  # from 16K to reduce packet send/receive frequency

Observation, opened_tables count in your status measured at 1,261 per hour. Providing ulimit -a report from your OS may identify Open Files with too low a limit for your workload.

Please view profile for contact info and FREE tips and Utility Scripts to assist with performance tuning.

user_44206490

To suggest you changes in your configuration we should also know current MySQL Status and size of databases.

Try to look at Releem - MySQL Performance Tuning tool. It'll collect metrics from your server and score your current configuration.

Also it can suggest you the performance optimized MySQL configuration.

user_155755190
Related