MySQL + Memory Issues on Cent OS 7

[Wil Huddleston]

We have been hosting 30+ WordPress sites on an old VPS with Cent OS 5 and it has been running smooth for years. We recently needed to upgrade a few of the websites to Cent OS 7 due to some requirements for PayPal & WooCommerce running on those sites. We have migrated about 5 or 6 of the WordPress sites over to the new server and are now suddenly having memory issues where the MySQL is being shut down often due to excessive memory use.

We have had the hosting company check the server multiple times and they have made numerous adjustments, none of which have resolved the issue. We also added some RAM because we were told that the system itself needed more memory to run everything. However, the issue still persists. We currently have CentOS 7 running ( CENTOS 7.3 x86_64 virtuozzo – WHM 62.0 (build 21) ), 3 GB of RAM installed, 8+ shared dual Xeon processors. The websites are all running the latest WordPress and a couple have the latest WooCommerce installed. The sites on average get about 20 users/day and the most active of the bunch gets 50-100 users per day.

The hosting company is currently installing MySQLTuner in order to see what setting adjustments might be recommended. I will post those details here as soon as I receive them. In the interim, is there anything else you would recommend that I check?

Thanks,
Wil

 

[vacancy]

What's in my.cnf file?

 

[Wil Huddleston]

Here's the current my.cnf file...

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=128M
innodb_use_sys_malloc=0
datadir=/var/lib/mysql
performance_schema=ON
slow_query_log=0
slow_query_log_file=/var/lib/mysql/slow_query.log
long_query_time=2
query_cache_limit=512K
query_cache_size=128M
query_cache_type=0
max_user_connections=80
max_connections=30
thread_cache_size=128
table_open_cache=2512
table_definition_cache=2512
key_buffer_size=128M
max_connect_errors=20
max_allowed_packet=2M
innodb_log_file_size=16M
innodb_log_files_in_group=4

#WT commenting buffer size values

#join_buffer_size=2M
#sort_buffer_size=2M
#read_buffer_size=2M
#read_rnd_buffer_size=2M
thread_concurrency=4
myisam_sort_buffer_size=16M
open_files_limit=8192
concurrent_insert=1
local-infile=0
max_heap_table_size=96M
tmp_table_size=96M
tmpdir=/var/lib/mysql/tmpdir/tmp
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 1
innodb_thread_concurrency = 8


default-storage-engine=MyISAM
[myisamchk]
key_buffer_size=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

 

[Wil Huddleston]

Here were the recommendations reported by the tuner...

General recommendations:
Control warning line(s) into /var/lib/mysql/hostname.com.err file
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 512K, or use smaller result sets)
tmp_table_size (> 96M)
max_heap_table_size (> 96M)
innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=128M) if possible.

 

[vacancy]

You can edit it in this way and follow the situation.

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=128M
innodb_use_sys_malloc=0
datadir=/var/lib/mysql
performance_schema=off
slow_query_log=0
slow_query_log_file=/var/lib/mysql/slow_query.log
long_query_time=2
query_cache_limit=1M
query_cache_size=96M
query_cache_type=1
max_user_connections=200
max_connections=200
thread_cache_size=512
table_open_cache=2048
table_definition_cache=2048
key_buffer_size=128M
max_connect_errors=20
max_allowed_packet=2M
innodb_log_file_size=16M
innodb_log_files_in_group=4

#WT commenting buffer size values

#join_buffer_size=2M
#sort_buffer_size=2M
#read_buffer_size=2M
#read_rnd_buffer_size=2M
thread_concurrency=4
myisam_sort_buffer_size=64M
open_files_limit=8192
concurrent_insert=1
local-infile=0
max_heap_table_size=96M
tmp_table_size=64M
tmpdir=/var/lib/mysql/tmpdir/tmp
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 1
innodb_thread_concurrency = 8


default-storage-engine=MyISAM
[myisamchk]
key_buffer_size=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

 

[Wil Huddleston]

Thank you for the quick replies. Before I go changing things, can you give a little detail about why the items were changed that you changed? Also, I have read some guides about optimizing MySQL memory performance and a couple of sources seem to recommend keeping the values for tmp_table_size and max_heap_table_size equal. I noticed yours were different, so is keeping them equal not really an issue?

Thanks,
Wil

 

[vacancy]

It is not possible to make a definite arrangement without knowing the databases, the instant queries, the hardware in the system.

The mysql_tunner software will never give you the exact values to use. Since you said that the memory usage is high, I made an interpretation accordingly.

Depending on the status of your system, you can find the appropriate settings by making minor adjustments.

 

[cPanelMichael]

Hello,

You may also find the following thread helpful:

optimize for WordPress

Thank you.