MySQL + Memory Issues on Cent OS 7
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
-
What's in my.cnf file? 0 -
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=32M0 -
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.0 -
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=32M0 -
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 0 -
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. 0 -
Hello, You may also find the following thread helpful: optimize for WordPress Thank you. 0
Please sign in to leave a comment.
Comments
7 comments