0

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,

Improve this question
4
  • Did you try doing SELECT COUNT(TBLPRIMARYKEY) AS TOTAL FROM USERS WHERE last_access >= DATE_SUB(NOW(), INTERVAL 1 MINUTE) ORDER BY last_access DESC LIMIT 16 ? Commented Apr 26, 2020 at 22:16
  • I mean you just need how many records in users table with such condition, no need to include all the columns, just the table primary key will do. Commented Apr 26, 2020 at 22:17
  • Primary key is ID, I tried SELECT(id) and noticed little difference, I thought that would be the solution too, maybe in another transactional situation. Commented Apr 26, 2020 at 22:23
  • Please post TEXT results of A) SHOW CREATE TABLE users; so we can see the structure and indexes and B) EXPLAIN (your query); Commented Apr 27, 2020 at 21:47

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thats actually perfect, I didn't even think about that. Thanks!
COUNT(*) is optimized in MyISAM only if there is no WHERE. So, switching engines will not help.
1 
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?

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.