Optimise Percona 5.7 my.cnf for a 32GB mixed nginx/php/mysql server

Question

I have a dedicated server which I run my website from. It's got 32GB of ram, Xeon E5-1650 3.2Ghz (6 core, 12 with HT) and two 3TB drives in RAID 1.

I'm running Percona 5.7.18

99% of my tables are InnoDB

My ibdata1 file is 500MB.

It's mainly SELECT queries, but some pretty complex joins/queries. With a proportion of INSERT/UPDATE too though.

The server spends some of its time resizing JPEGs that users have uploaded, so I need to be mindful of that when allocating memory etc.

Is there anything else I could tell you that would help?

I do plan to implement some decent webapp-level caching at some point, but would like to get the mysql config improved in the meantime.

The site runs pretty well on normal traffic days, but can struggle a bit on busy days (like today...)

I'm a sole dev, so full-stack and spread thin, and will happily admit that my DBA skills are fairly poor. I can write a decent query, but I know nothing about server set up. What's in my my.cnf has been cobbled together from various different sources over the years, and I'm guessing it's far from optimal!

[client]
default-character-set           = utf8mb4


[mysql]
default-character-set           = utf8mb4


[mysqld]
user                            = mysql
default-storage-engine          = InnoDB
socket                          = /var/lib/mysql/mysql.sock
pid-file                        = /var/run/mysqld/mysqld.pid

datadir                         = /var/lib/mysql

skip-name-resolve

max-allowed-packet              = 16M
max-connect-errors              = 1000000

# is this a silly thing to do..?
tmpdir                          = /dev/shm

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                  = 0

character-set-client-handshake  = FALSE
character_set_server            = utf8mb4
collation_server                = utf8mb4_unicode_ci

ft_min_word_len                 = 2
group_concat_max_len            = 4096


max_connections                 = 500

tmp-table-size                 = 32M
max-heap-table-size            = 32M
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

join_buffer_size                = 2M
sort_buffer_size                = 2M
read_rnd_buffer_size            = 2M

slow-query-log                  = 1
long-query-time                 = 1
slow-query-log-file             = /var/lib/mysql/slow_log

log-error                       = /var/lib/mysql/mysite.err

max_allowed_packet              = 256M

query_cache_size                = 268435456
query_cache_limit               = 1048576
query_cache_type                = 1

innodb_buffer_pool_size         = 12G
innodb_buffer_pool_instances    = 12
innodb_read_io_threads          = 12
innodb_write_io_threads         = 12
thread_pool_size                = 24

sql_mode                        = ""




[mysqld_safe]
log-error=/var/lib/mysql/mysite.err
pid-file=/var/run/mysqld/mysqld.pid

Any help/guidance would be hugely appreciated, I love to learn and more importantly I need to learn!

Thank you!

Answer 1

32GB RAM, 12G buffer_pool --> lots of room left over for resizing images.
12G buffer_pool, 0.5GB ibdata1 and 5.7 --> innodb_file_per_table is probably ON. Is it? If so, you have not given us a clue of how much data you have. Look at the .ibd files.

query_cache_size = 256M is probably bad. Any write to a table causes the purging of all QC entries for that table. The purging is proportional to the size of the QC. Keep it under 50M. (Or turn it off.)

A Rule of Thumb: "Don't put a cache in front of a cache." MySQL does a lot of caching internally; adding a webapp-level cache may not help, and may rob RAM from the buffer_pool, where it is important.

Do you know if you are I/O-bound? CPU-bound?

I see that you have the slowlog on. Use pt-query-digest to summarize it. Then, let's discuss the worst couple of queries. Fixing slow queries is more likely to give your performance a boost than any tuning.

But, if you would like more tuning advice, let's see the GLOBAL STATUS. In particular, follow the directions here .