Home / server / Questions / 247382
Accepted
webnoob
Asked: 2011-03-15 14:32:35 +0800 CST

PHP scripts randomly becoming really slow to respond - Database lockup?

  • 772

I wasn't sure whether to post this here or on stackoverflow, so apologies if its in the wrong place.

I have about 7 php scripts running on a centOS VPS. Each of these scripts contacts a game server and processes the logs, with the logs it either does some database queries or sends info back to the game server.

I am having an issue where some of the scripts will randomly become REALLY slow to respond and I don't know where to start with my debugging. Each script connects to its own database schema but on the same MySQL server. Each script will do about 4 inserts per second and twice as many select statements on their respective databases. I thought a database lockup may cause the issue but some console messages that are read from the database are sent to the game servers console without issue every 30 seconds, even when the script is slow to responding to other commands.

Non of the scripts are using a lot of memory or CPU power. About 0.1% each.

I know this information is really vague but I don't know linux very well at all (in fact, top is about my limit) and I really don't know where to start debugging this.

Thanks.

linux mysql php

1 Answers

  1. Best Answer

    A few things you can try:

    • Check the system and application logs for any relevant messages. Sometimes there are issues/timeouts that will get logged there. This includes the MySQL slow query log that DerfK mentioned.
    • Some sort of monitoring system on the server would be nice to have. This would tell you for sure if there are any obvious bottlenecks (CPU, Memory, IO, etc...). Alternatively, if the issues happen at specific times you can use the various command line tools top, vmstat, iostat, etc... to check the system status. Make sure you eliminate the obvious/common causes first (high CPU, low RAM, swap usage, IO locks/issues/high load).
    • If you can benchmark/stress test your scripts it might help determine the source of the issue. Being able to reliably duplicate an issue is a big step in solving it.
    • Consider adding some basic logging in your scripts. I would start by doing a basic check of the script running time and output something only if it took more than X seconds to run.
    • You mention the scripts parse logs. If possible try running the scripts over the same set of logs multiple times and see if the issue happens at the same place or randomly at different places. You can also trying disabling writes to see if the issues are caused by the reads/parses or the writes (or both).
    • If you find particular queries that are running slow try running/testing them manually on the database via the command line client. This will tell you if the queries themselves are slow or just the execution of them remotely from PHP for some reason.
    • 3