Question posted in
You can find the official documentation here.

AJAX – MySQL Query Flooding Server – Jquery ajax

I’ve been at this for a few days now trying different methods to reduce the 95-135% CPU load due to a recent AJAX script, calling this query:

SELECT COUNT(*) as total FROM users WHERE last_access >= DATE_SUB(NOW(), INTERVAL 1 MINUTE) ORDER BY last_access DESC LIMIT 16

And I have tried COUNT(id) to reduce table scan times, I added LIMIT and ORDER BY, I think it improved but I can’t tell. I am monitoring top -P on my BSD box, and the CPU has been spiking quite high almost killing apache too, while relying on that for my query testing.

We have a jquery bit that queries the the AJAX script and returns a table count according to last users online with a 15 second interval on jquery-side (1 minute on the query statement). It was fine for a day, then noticed the server was running overtime and fans going haywire.

We ended up removing MySQL 5.7 and installing MariaDB 12.4 – and a huge difference that made, however while its sitting at reduced load by ~20% CPU, its struggling too.. so the query is bad. I disabled the script and sure enough, CPU went down to 15-30% avg, however this is a big part of our website’s UX. This simply reports (455 online) for example, and updates the text every 15 seconds (dynamically).

My question is.. due to the 15 second interval hits to the SELECT(*) statement on a table of 9600 records, how can I optimize this so the SQL server doesn’t crash and suck up all available memory?

I didn’t include the script as it works wonderfully, the query is the issue but will provide if needed.

This is our only AJAX script on the site. No other AJAX calls are made.

Kind regards,

Tags:

2

Answers


  1. 0

    I don’t think the SQL-query itself is the problem here. Adding ORDER BY and LIMIT should not make any difference since there is only one row to sort and limit. What you could think about is to add an index for the last_access column.

    Depending on your website traffic, I think the problem is the way you’ve designed your system. You’re saying the client is requesting your server every 15s which is asking your database for numbers of users last minute. Imagine having 1 000 users online, in that case, you will have 66 queries per second.

    In this case, you should consider implementing a cache. You tagged your post with PHP, Memcached is fairly easy to implement in PHP. Cache your SQL-query for 15 seconds and your database server will only have 0.06 queries per second instead.

    For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
    https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count

    Login or Signup to reply.
  2. 0 
    SELECT  COUNT(*) as total
    FROM  users
    WHERE  last_access >= DATE_SUB(NOW(), INTERVAL 1 MINUTE)
    --  ORDER BY  last_access DESC  -- Toss; one output row, nothing to sort
    --  LIMIT  16  -- Toss, only output row

    Be sure to have this on the table

    INDEX(last_access)

    COUNT(id) is, if anything, slower. It is the same as COUNT(*), plus checking id for being NOT NULL.

    How many different threads are there?

    Let’s look at what that query takes —

    1. The AJAX request hits the web server.
    2. It sends the request to a ‘child’ or ‘thread’ which it probably already has waiting for action.
    3. If the child needs PHP, then it launches that process.
    4. That connects to MySQL.
    5. The query is performed. If you have the index, this step is the trivial step.
    6. Things shutdown.

    In other words, have you investigated which process is taking most of the CPU?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search