5

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

migrated from stackoverflow.com Mar 13 '16 at 23:13

This question came from our site for professional and enthusiast programmers.

  • I found the similar question stackoverflow.com/q/9815155/846437, but there is no answer. I also have enough of free RAM (1595), swap (1023), and lots of php scripts who work with MySQL at once – lasoweq Mar 17 '16 at 1:49
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?

  • 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 Mar 3 '16 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 Mar 3 '16 at 23:43
  • it helped, a little, but the problem is still exists. Now it is still "swapping" every day, not every few hours – lasoweq Mar 15 '16 at 1:54
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

  • 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 – lasoweq Sep 4 '16 at 16:47
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.

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.