MySQl Tuning

[matthew_grr]

Hey all, I know this has been asked many times before but I'm still battling to find a solid solution, my server is struggling due to MySql memory usage, I'm not that clued up on SQL but I've run the tuner script and have the below results.

We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See MySQL :: MySQL Internals Manual :: 10.5 How MySQL Uses the Join Buffer Cache
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from mysql/mysql-sys for MySQL
Consider installing Sys schema from FromDual/mariadb-sys for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: MySQL :: MySQL 8.0 Reference Manual :: 15.6.5 Redo Log
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 70384 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 4.1G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Server spec is 26 core with 48GB Ram.

If anyone could please help as I would be super grateful.

 

[cPRex]

Hey there! Sorry to hear about the issues with the MySQL service on the machine. You mentioned the service was struggling - are you seeing the process being killed due to out of memory issues, or do the queries just seem slower than you expect?

I am a fan of the tuner script, but you do need to ensure that MySQL has been up and running for at least 24 hours in order for that to provide valid data. If the process is frequently being killed, and hasn't been running for very long by the time you execute the tuner script, you may not get accurate results.

It could also be the server just really is that busy and needs that amount of resources for MySQL. You can check the processes that are running in real-time with the following command:

mysqladmin proc status

This will show you the queries that are running, what state they are in, and also provide an average number of queries per second. I've found this is a great way to evaluate the MySQL performance on the machine.

If you see slow queries mentioned in that output, you may want to enable the slow query log to track those down.

I don't believe there is ever an easy answer to the question "how should I optimize my system," but with the tools mentioned above you should be able to find a solution that works well for your users and hardware.