1

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

11
  • What is the column type of languageID? Does your update improve if you remove the single quotes around the 12? If the column is a numeric type the quotes aren't needed and really shouldn't be used.
    – Dave
    Dec 12, 2020 at 13:36
  • Is the ~0.2s "slowness" with that query repeatedly reproducible?
    – J.D.
    Dec 12, 2020 at 14:08
  • Please provide SHOW CREATE TABLE. It sounds like you are using MyISAM instead of InnoDB.
    – Rick James
    Dec 13, 2020 at 2:04
  • @Dave it is an INT. No removing the queotes does not make a difference.
    – S. Roose
    Dec 13, 2020 at 11:13
  • @J.D. Yes this query keeps returning in the logs. I have also see it in other tables, eg small Wordpress tables where an update is performed.
    – S. Roose
    Dec 13, 2020 at 11:13

1 Answer 1

1

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.

4
  • Increasing the buffer_pool may keep other tables/queries from interfering with the UPDATE in question -- that is what I expect is the issue.
    – Rick James
    Dec 14, 2020 at 5:03
  • checked reads/read_requests, it is only 0.0002%. Edit: I have multiple databases on this server; Is there a way to get these statistics for a specific database?
    – S. Roose
    Dec 14, 2020 at 11:52
  • @S.Roose - Clarification, please. "Database" could mean "server", "VM with MariaDB", "instances of MariaDB", or "CREATE DATABASE". Which? The slowlog and the SHOW GLOBAL STATUS refer to all databases within one MariaDB instance.
    – Rick James
    Dec 20, 2020 at 18:13
  • @RickJames that is 1 MariaDB server: 1 instance with many databases (schemas in Oracle lingo)
    – S. Roose
    Dec 21, 2020 at 16:48

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.