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
Best Answer
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