CODEWITHSUNDEEP
phpmysqlsqlperformanceinnodb
maddo7
maddo7

Reputation: 4963

Optimize mysql settings/table for performance under load

I have the following query for a queue system. The slowest query that troubles me is this one:

UPDATE workers SET work = $workid, last_used = NOW() 
WHERE status = 1 AND work IS NULL ORDER BY last_used ASC LIMIT 1

When there's no load, the query executes within about 0.04 seconds but when many php scripts are executing this query the execution time goes higher and higher up to 40.0 seconds which is a big problem.

The table has around 40.000 entries and there is an index for status and for liking_media. The EXPLAIN for the query shows the parser is using an intersect with status and liking_media and gets about 3000 rows to process with ORDER_BY. EXPLAIN shows further using where; using filesort.

The VPS behind it has 8 cores @ 2.5ghz, 12GB RAM. When the query runs very slow there's only low CPU usage. The CPU usage is much higher when the load begins to ramp up.

How can I greatly improve the performance of this query under load when many php scripts are running it? Can I tweak general mysql settings to fix it? Or is the table architecture bad or is an index missing? I'd like to be able to run about 300 of this queries per second without loosing performance.

Upvotes: 2

Views: 254

Answers (2)

Anthony
Anthony

Reputation: 37045

I think this should avoid the ORDER BY, which is forcing the query to get all results instead of just the one with newest last_used:

UPDATE workers
    INNER JOIN (
        SELECT MIN(last_used), worker_id 
            FROM workers
    ) AS newest_worker 
    ON newest_workder.worker_id = workers.worker_id
SET workers.work = $workid, workers.last_used = NOW() 
WHERE workers.status = 1 AND workers.work IS NULL

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The problem with the query is finding the appropriate row(s) to update. For this query:

UPDATE workers
    SET work = $workid, last_used = NOW() 
    WHERE status = 1 AND work IS NULL
    ORDER BY last_used ASC
    LIMIT 1;

You want the following composite index: workers(status, work, last_used).

This should speed the query and prevent multiple updates from locking each other out.

Upvotes: 2

Related Questions