Need help with MySQL configuration settings

[beansbaxter]

I am running a single website on a dedicated server, and the server load averages are always too high.

I went from 2 CPU's to 4 CPU's and that helped some, but the server still maintains a high CPU load average and the memory usage stays very low.

I don't want to throw more CPU's at the server, and would prefer to configure MySQL for better performance, assuming this is possible.

The website has only 1 database, which is around 15 GB in size.

The server is running MySQL 5.7.23, along with Apache 2.4.34 and php 7.2.10.

Here you can see the server CPU and Memory usage over the last 24 hours - https://cl.ly/45d3c0e6c1ba

And when the system generates a server load warning, everything is being consumed by MySQL - https://cl.ly/02fe3658155f

This is what's been setup in the MySQL configuration settings:

[mysqld]
local-infile = 0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=24M
max_connections=1000
wait_timeout=28800
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=3000
innodb_flush_neighbors=0

What other changes can I make to the MySQL configuration settings to reduce the server load average?

If it matters, running the latest DirectAdmin 1.54.1.

Thanks in advance.

 

[wattie]

Run this query to see how much (closely) is the maximum memory limit of MySQL:

SELECT ( @@key_buffer_size + @@query_cache_size +
@@tmp_table_size + @@innodb_buffer_pool_size +
@@innodb_additional_mem_pool_size +
@@innodb_log_buffer_size +
@@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size +
@@sort_buffer_size + @@join_buffer_size +
@@binlog_cache_size + @@thread_stack
)
) / 1073741824 AS MAX_MEMORY_GB

Now depending if you use mainly InnoDB or MyISAM, you can tweak some settings.

However don't expect miracles. Raising the buffers may help a lot when MySQL is running on a single huge website. When you are running many different databases on many different sites, it won't be that much beneficial. The issue is that in such case the biggest problem is not the CPU or RAM, but the disk reads. And it's not only MySQL, but Apache too. Check your disk drive usage - if it is close to 100% usage, that's your problem. It is the same on my server too.