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.
