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

Question

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

Answer 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.