0

We have a Magento website on a dedicated server (Xeon E5-1620, 64Gb RAM, SSD), we don't generate high traffic, it is a rather small site, around 30000 page views/month, an average of 100 orders/day. Our store has been running for years, we never had any problem. Some weeks ago I started getting complaints from the order preparation department in the warehouse about how slow Magento was, I noticed a high CPU load from mysqld.

I checked the MySQL configuration, and it seems it was still more or less the default configuration. I am not a DBA, not an expert in MySQL configuration/optimization, so I tried getting info here and there and made some adjustments, starting by setting innodb_buffer_pool_size and key_buffer_size in my.cnf along with few other settings as seen below:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address=127.0.0.1

# What I added below
key_buffer_size=512M
#sort_buffer_size=4M <-- I commented this, it seems better now
#read_buffer_size=4M <-- I commented this, it seems better now
innodb_thread_concurrency=0
innodb_buffer_pool_size=9G
tmp_table_size=128M
max_heap_table_size=128M
skip-name-resolve
query_cache_size=64M

It looked more or less ok on friday, but arriving at work this morning, the load was above 8 (8 cores), Magento almost not responding. I commented sort_buffer_size=4M and read_buffer_size=4M as seen above as I took these numbers a bit randomly, and now 30 minutes after restart, it looks way better, the load (1, 5 and 15 minutes) being under 2.5.

About the value of innodb_buffer_pool_size, I set it calculating the Recommended InnoDB Buffer Pool Size (as explained here) using this query:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     9 |
+-------+

This already helped.

Can someone advise me on optimizing my.cnf better? What is the most important settings? Thank you for your help. I know that kind of question has been asked many times, but after few days tried to fix this, I feel a bit hopeless

UPDATE - SOLVED: The problem is solved and had almost nothing to do with MySQL optimization (I did optimized it at the end anyway as it was still with its default configuration). For those interested, we are using Solrbridge module for the search engine, and there was a bug in the last version that was filling a table constantly. I noticed that new table last week, the biggest of the database 4.9Gb, then on monday it grew to 7.9Gb. I asked Solrbridge developer about it and he told me I can truncate this table and turn off a certain setting in Solrbridge. Since then everything went back to normal, CPU load average: 0.09 0.12 0.13 at this moment.

  • 1
    You should look into possible options for caching in Magento, so that it wouldn't need to execute that many database queries. – Tero Kilkanen Jul 24 '17 at 7:50
  • 1
    I see that you say your store has been running for years. What is and how old is your OS? If it's outdated you may be much more vulnerable to malicous attacks. You should run a good virus scan. – joedirt Jul 24 '17 at 9:08
  • @joedirt no problem with that, the server is running CentOS 6.7 and is ok – feub Jul 24 '17 at 13:56
2

Before changing MySQL server settings, especially since you are not a DBA, I suggest confirming that server does not have a failing drive or a degraded RAID. It does not apply if your storage is virtualized, but verify that dmesg output does not have suspicious messages and smartmontools do not report problems with HDDs.

If there is no disk problem, let MySQL run for at least few hours after the last restart and use a tool like MySQLTuner-perl for a quick analyses and tailored suggestions. I can point you to other tools, but without hands on experience, I suggest not to change more than one parameter at a time.

0

I personally prefer Percona

This site offers a free service, based on your server configuration it gives you optimal configuration parameters and I have experienced very good improvements. Give it a try

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.