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!