SQL Query overloading server

Question

I have one php file which is getting called very often for a session check of an external application. I don't know how many calls there are every second, but I think it may be like 20-100 calls every second.

However this .php file includes one SQL query which updates a single row. Once I don't outcomment these lines the whole database slows down a lot and in the end my forum is not reachable anymore. I think it is something like a server issue (for example to many IOPS?). Unfortunately I don't know how I can locate the exact reason for the slowdown, nor I don't know how I can determine how many calls I get every second for the php file.

This is the very simple SQL query (the where clause contains only the primary key):

UPDATE bot_sessions SET ip_address = :ip_address, renewal_count = renewal_count + 1
       WHERE username = :username AND session_token = :session_token

So two questions:

1. How can I find out how often my specifc php-file is being called every second?
2. How can I locate the reason that my whole database slows down once I use this Update Query?

Answer 1

You should be able to find out how often a specific PHP file is being called by looking through the web server logs. There are a couple of other hacks - for example Using Google Analytics (which will be less accurate), or updating a file / counter (more resource intensive).

As @ma.tome said, monitoring your utilisation gives you a good place to start, and is generally useful. A Mysql process running at 135% means its using more then 1 CPU. (If you have 4 CPUs the total is 400%). Running vmstat will give you IO information.

Another thing to do might be to start logging query times in your database. Also, check your indexes - depending on your application, if its not already indexed you could get huge performance increases by creating an index for username in bot_sessions. (Not sure if username or session_token is the primary key you are specifying, may want to look at creating an index on the one which is not a primary key) - either way, the where clause contains more then just the primary key.

Also, your system may simply be running out of RAM. look at how much swap you are using - and, if required, throw more RAM at the problem or decrease vm.swappiness.