8

I have a project with PHP/Laravel/MariaDB, the DB is getting bigger every day now some tables has 4kk+ records already

Server is 4GB RAM + Ubuntu 14.04 + Apache (DigitalOcean)

when I only start the server (or reboot it), everything works smooth, but few hours later, any request to mysql is extremely slow

at all the times I have 1.5-2Gb of free RAM, and I can't find any indications, what's wrong there. If I restart mysql process, it all get back to normal state, but it happens again few hours later.

I believe DB structure is good, indexes and everything. Because it is working pretty good first few hours.

I changed in my.cnf:

innodb_log_file_size    = 500M
innodb_buffer_pool_size = 2G

I tried to set up innodb_buffer_pool_size to 3G, but mysql won't start after that

I tried to google any possible combination of the words, and try to implement different solutions, but it's not helping. I really can use an advice

Improve this question
2

3 Answers 3

Reset to default
1

Have you tried to see what queries are slow? Do you have the slow_query_log turned on? What may seem like "good" indexes originally may simply be good because the queries.

https://stackoverflow.com/questions/2403793/how-can-i-enable-mysqls-slow-query-log-without-restarting-mysql

Improve this answer
1
  • yes, I tried that. There is nothing in the log. it's exactly "swapping", as @RickJames described. Sadly, but I couldn't defeat that, but partially solved it by moving some parts to Redis
    – user89540
    Commented Sep 4, 2016 at 16:47
1

Your system probably was "swapping". The is extremely detrimental to MySQL performance. And usually the quick fix is to shrink the buffer_pool.

innodb_buffer_pool_size = 1500M is about all that can be safely done in a "small" 4GB machines.

Keep in mind that the buffer_pool is a "cache". Changing the size by a small amount has only a minor impact on MySQL's performance -- unless it is too small. You seem to have it too big, thereby leading to OS issues, out size of MySQL.

Did you change anything else in my.cnf?

Also decrease max_connections. 100 is usually good, but maybe 50 would be better in your case. And decrease the number of Apache children it will create. 30 might be good.

Improve this answer
6
  • no, only what I described in the original post. Most "advices" recommends to have buffer_pool to 50-70% of RAM, that's what I did. I changed it back now, need some time to confirm if it will help
    – lasoweq
    Commented Mar 3, 2016 at 23:14
  • Those recommendations assume you have more than 4GB -- you can't buy a machine that tiny any more. (OK VMs can get even smaller.) Furthermore you implied that you have Apache, PHP, laravel, etc also running. So, 50% of what is left after accounting for them may be ok.
    – Rick James
    Commented Mar 3, 2016 at 23:43
  • it helped, a little, but the problem is still exists. Now it is still "swapping" every day, not every few hours
    – user89540
    Commented Mar 15, 2016 at 1:54
  • @lasoweq, "it is still swapping...", what process is swapping, MySQL or something else?
    – Lennart - Slava Ukraini
    Commented Dec 29, 2019 at 21:06
  • @lasoweq - I added a paragraph.
    – Rick James
    Commented Jan 3, 2020 at 17:25
0

Give innodb_buffer_pool_size at least 70 to 80 % of installed physical memory. Check the innodb_log_file_size and innodb_sort_buffer_size. This can help you and also clear the slow query log if it exceeds.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.