Question 336457 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – MyISAM queries are always at least 100ms

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 dialstatusG

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search