MySql UPDATE of tiny table is costly (0.2sec) – how to analyse further

mariadbMySQL

While optimizing some indexes I detected a curious case in the slow_query_log.

These are not the actual tablenames but for simplicity:

# Thread_id: 1293932  Schema: xx  QC_hit: No
# Query_time: 0.205087  Lock_time: 0.000046  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1  Bytes_sent: 52
UPDATE language SET lastUpdate = current_timestamp() WHERE languageID = '12';

Table 'language' has exactly 12 rows. It has only a few columns. It has only 1 index: the primary key on languageID (INT). This is thus the simplest table you can imagine. Why is this update statement taking 0.2 seconds?

For reference, select statements on rows of 10k-100k's of rows are quite fast:

# Thread_id: 1294423  Schema: xx  QC_hit: No
# Query_time: 0.111802  Lock_time: 0.000266  Rows_sent: 0  Rows_examined: 14279
# Rows_affected: 0  Bytes_sent: 50
INSERT INTO session (sessionID, personID, lastDate)
    SELECT 8, a.personID, current_timestamp() FROM personA a INNER JOIN personP p on p.personID = a.personID LEFT OUTER JOIN view_bigViewB ON view_bigViewB.personID = a.personID WHERE view_bigViewB.someValue = 'x' ;

Above query is simplified a lot, and please do not pay too much atention to it, but let's say this is really a dramatic big data set that is being processed in only 0.1sec with full table scans. It is an INSERT that did not insert any rows, so no write operation was needed.

Of course, a write operation is more costly but 0.2s seems just too slow. How can I analyze what exactly is making up this .2 seconds?

Using MariadDb 10.3.27

SHOW CREATE TABLE

CREATE TABLE `language` 
( 
   `languageID` int(11) NOT NULL AUTO_INCREMENT, 
   `name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL, 
   `lastUpdate` datetime DEFAULT NULL, 
    PRIMARY KEY (`languageID`) 
) 
ENGINE=InnoDB 
AUTO_INCREMENT=16 
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Output of innodb-parameters: https://pastebin.com/ReGHc1xp

Best Answer

Your innodb system variables indicate the innodb buffer pool is at 128M (the default). Its quite possible that the slowness of this single PK based update was due to this table not being in the innodb buffer pool. Look at the SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%' and adjust the pool size up until the Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests is very small (<1%).

This will be the case if 128M of more recently used innodb tables are in memory.

Another potential cause if there is a significant amount of write activity on the the server the innodb flushing /writing could be causing a delay because of storage limits.

If a f{data}sync of the disk can take 0.2 seconds occasionally that could correspond to the query time.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – How to optimize this MySQL query further

After looking over the query, the tables, and the WHERE AND GROUP BY clauses, I recommend the following:

Recommendation #1) Refactor the Query

I reorganized the query to do three(3) things:

create smaller temp tables
Process the WHERE clause on those temp tables
Delay joining to the very last

Here is my proposed query:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

Recommendation #2) Index the sounds table with an index that will accommodate the WHERE clause

The columns of this index include all the columns from the WHERE clause with static values first and moving target last

ALTER TABLE sounds ADD INDEX support_index
(blacklisted,ready_for_deployment,deployed,type,created_at);

I sincerely believe you will be pleasantly surprised. Give it a Try !!!

UPDATE 2011-05-21 19:04

I just saw the cardinality. OUCH !!! Cardinality of 1 for rateable_id. Boy, I feel stupid !!!

UPDATE 2011-05-21 19:20

Maybe making the index will be enough to improve things.

UPDATE 2011-05-21 22:56

Please run this:

EXPLAIN SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

UPDATE 2011-05-21 23:34

I refactored it again. Try This One Please:

EXPLAIN
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
;

UPDATE 2011-05-21 23:55

I refactored it again. Try This One Please (Last Time):

EXPLAIN
  SELECT A.id,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) B
  ON A.id = B.rateable_id
  GROUP BY B.rateable_id;

UPDATE 2011-05-22 00:12

I hate giving up !!!!

EXPLAIN
  SELECT A.*,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A,
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
    AND AAA.rateable_id = A.id
  ) B
  GROUP BY B.rateable_id;

UPDATE 2011-05-22 07:51

It has been bothering me that ratings is coming back with 2 million rows in the EXPLAIN. Then, it hit me. You might need another index on the ratings table which starts with rateable_type:

ALTER TABLE ratings ADD INDEX
rateable_type_rateable_id_ndx (rateable_type,rateable_id);

The goal of this index is to reduce the temp table that manipulates ratings so that it is less that 2 million. If we can get that temp table significantly smaller (at least half), then we can have a better hope in your query and mine working faster too.

After making that index, please Retry my original proposed query and also try yours:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

UPDATE 2011-05-22 18:39 : FINAL WORDS

I had refactored a query in a stored procedure and added an index to help answer a question on speeding things up. I got 6 upvotes, had the answer accepted,and picked up a 200 bounty.

I had also refactored another query (marginal results) and added an index (dramatic results). I got 2 upvotes and had the answer accepted.

I added an index for yet another query challange and was upvoted once

and now your question.

Wanting to answers all questions like these (including yours) were inspired by a YouTube video I watched on refactoring queries.

Thank you again, @coneybeare !!! I wanted to answer this question to fullest extent possible, not just accept points or accolades. Now, I can feel that I earned the points !!!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – How to optimize this MySQL query further

Related Question