MyISAM queries are always at least 100ms

Question

I have some old software that use MyISAM tables on Mariadb 10.6

When I am checking query digest from pt-query-digest Percona Toolkit, I always see execution time from 100ms.

What can be wrong here?

Innodb tables show lower time.

This one is for simple count(*) query over small table(under 30k records):

# Query 5: 0.69 QPS, 0.17x concurrency, ID 0x761C0D5EAEC0B8FBB64279CF7F7B2727 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2024-02-07T15:00:14 to 2024-02-07T15:19:49
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     807
# Exec time      4    200s   100ms   509ms   248ms   393ms   116ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1  89.05k     113     113     113     113       0     113
# id             4  10.18G  12.57M  13.30M  12.92M  13.08M 278.63k  12.46M
# String:
# Databases    asterisk
# Hosts        45.77.125.169:37888 (7/0%)... 266 more
# Users        cron
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `asterisk` LIKE 'vicidial_carrier_log'\G
#    SHOW CREATE TABLE `asterisk`.`vicidial_carrier_log`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT dialstatus,count(*) from vicidial_carrier_log where call_date >= "2024-02-06 15:11:04" group by dialstatus\G

It does not matter how many rows in table and what is real query.

The question is:

Why MINIMUM value is always 100ms, not why maximum sometimes over 3s. Maximum is because of concurrent locks.

The command line is :

/usr/bin/perl /usr/bin/pt-query-digest --processlist h=127.0.0.1,u=queryprofiler,p=mqueryprofiler --run-time=598s --interval 0.03

Answer 1

I suspect there were no indexes on your small 30K-row MyISAM table. Since the row count is [I think] stored in the .MYI file that does not exist, it must read the data to count the rows.

InnoDB is slower than MyISAM for virtually all simple SELECT COUNT(*) FROM tbl.

It may be that some other activity has a LOCK on the table. This can slow down many operations. (InnoDB allows concurrent access to a table, hence faster in many situations.)

Why only 100ms? Was long_query_time set to 0.1 (seconds)? If so, maybe only a few SELECTs happened to barely reach 100ms. How many times was the COUNT run?

The Query_cache, if in effect adds wrinkles to this discussion. See both query_cache_type and query_cache_size.

Neither data nor indexes are "kept in memory" for either MyISAM or InnoDB. In all combinations the bytes are cached in a manner akin to "least recently used". (With sufficient RAM, all will stay cached in ram.)

More

This is not a simple COUNT(*):

SELECT  dialstatus, count(*)
    from  vicidial_carrier_log
    where  call_date >= "2024-02-06 15:11:04"
    group by  dialstatus;

But it can be optimized (for either MyISAM or InnoDB) with the addition of

INDEX(dialstatus, call_date),
INDEX(call_date, dialstatus)

The Optimizer will pick the index that will work better with the distribution of data relative to that particular date.

Answer 2

Okay, I have found the issue

The MyISAM table need command be like

pt-query-digest --interval 0.05

If you want 50ms minimum. Maybe that bug, maybe it is feature, but it is like that.