I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having.
Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely.
As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well.
In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to:
- Slow queries
- Too many connections
- Too many queries per second
- Other potential issues I'm not aware of
I know about the slow query log, but none of my queries are very slow.
What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?