0

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!

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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.