From MySQL to MariaDB : bad performances issue with MyISAM tables

Question

I'm currently migrating a Debian 6 MySQL server (5.1.73) to a fresh new Debian 10 MariaDB server (10.3.18). Please note that I'm not a database expert.

All tables are under MyISAM engine storage, for a total of approximately 5 Gb. A migration to InnoDB is planned, but for now I want to migrate on the new server without updating tables and make sure performances are keeped.

Here is my problem: I have a SELECT query that runs for ~0.2 seconds on the old server, where this same query runs for ~1.4 seconds on the new server. I don't know how to explain that: that's about 7 times higher in the fresh install!

Here is the (simplified) query:

SELECT   NOW(), uid, 
         ((MATCH (title) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 4 + (MATCH (metakeywords) AGAINST ('\"keyword1\" \"keyword2\" \"keyword3\"')) * 2 AS MATCH 
FROM     news 
WHERE    wfplace = 'live' 
AND      deleted = 0 
AND      hidden = 0 
AND      (magazine IS NOT NULL AND magazine <> '') 
AND      (start_time = 0 OR start_time <= 1577454941) 
AND      (end_time = 0 OR end_time  >= 1577454941) 
ORDER BY MATCH DESC LIMIT 10

The perfs seems equivalent between the two servers without the MATCH...AGAINST stuff. I use the NOW() part to be sure there is no cache involved in result.

Note that to migrate the data, I've simply synced .frm, .MYD and MYI files from a mysqlhotcopy dump to the new /var/lib/mysql/my_db.

Here is the old my.cnf (custom settings only):

skip-external-locking
skip-name-resolve
skip-innodb
skip-locking

key_buffer_size = 2048M
max_allowed_packet = 32M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 256
query_cache_size = 256M
query_cache_limit = 8M

thread_concurrency = 8

interactive_timeout     = 90
connect_timeout         = 90
wait_timeout            = 90
max_connections         = 150
long_query_time         = 2

Conf: Intel(R) Xeon(R) CPU with 16 cores and 47 Gb RAM.

Here is the new custom settings:

performance_schema=off
skip-name-resolve

query_cache_size=0
query_cache_type=0

key_buffer_size=20G

innodb_buffer_pool_size=20G
innodb_log_file_size=2G
innodb_buffer_pool_instances=20

table_open_cache=2048
max_allowed_packet=32M

sort_buffer_size=8M
myisam_sort_buffer_size=256M
read_buffer_size=8M
read_rnd_buffer_size=8M

wait_timeout=90
interactive_timeout=90
connect_timeout=90
long_query_time=2

Conf: Intel(R) Xeon(R) Silver 4110 CPU with 16 cores and 93 Gb RAM.

I've tried to keep some old settings to have similar speeds but no success.

The only clue I have is a different EXPLAIN result on this same query between the two servers on the last "Extra" column:

(Old server)

(New server)

What am I missing? Thanks for your help!

EDIT:

Raw EXPLAIN (old server):

+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                        | key     | key_len | ref   | rows  | Extra                       |
+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | t0_   | ref  | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,magazine | wfplace | 27      | const | 28749 | Using where; Using filesort |
+----+-------------+-------+------+--------------------------------------------------------------------------------------+---------+---------+-------+-------+-----------------------------+

Raw EXPLAIN (new server, with index condition pushdown disabled):

+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys                                                                                | key     | key_len | ref   | rows  | Extra                                                               |
+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+
|    1 | SIMPLE      | t0_   | ref  | hidden,deleted,optim1,deleted_2,topcanonique,sort_date,idx_magazine,wfplace,endtime,magazine | wfplace | 27      | const | 25572 | Using where; Using temporary; Using filesort                        |
+------+-------------+-------+------+----------------------------------------------------------------------------------------------+---------+---------+-------+-------+---------------------------------------------------------------------+

Result of flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%'; (old server):

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_next          | 34980 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 10    |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

Result of flush status; SELECT ... (query); SHOW STATUS LIKE 'Hand%'; (new server):

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 34960 |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 10    |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 33195 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 33194 |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

Result of EXPLAIN FORMAT=JSON SELECT as asked by @Rick James:

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "(match title against ('"keyword1" "keyword2" "keyword3"')) * 4 + (match metakeywords against ('"keyword1" "keyword2" "keyword3"')) * 2",
      "temporary_table": {
        "table": {
          "table_name": "news",
          "access_type": "ref",
          "possible_keys": [
            "hidden",
            "deleted",
            "optim1",
            "deleted_2",
            "topcanonique",
            "sort_date",
            "idx_magazine",
            "wfplace",
            "magazine"
          ],
          "key": "wfplace",
          "key_length": "27",
          "used_key_parts": ["wfplace"],
          "ref": ["const"],
          "rows": 28727,
          "filtered": 100,
          "index_condition": "wfplace = 'live'",
          "attached_condition": "deleted = 0 and hidden = 0 and magazine is not null and magazine <> '' and (starttime = 0 or starttime <= 1577454941) and (endtime = 0 or endtime >= 1577454941)"
        }
      }
    }
  }
}

Answer 1

Try disabling index condition pushdown on the new server (it is enabled by default on 5.6+, unavailable on <=5.5).

MyISAM didn't get much love on newer versions of MySQL, you may want to test the migration on a separate server, including the InnoDB conversion. Otherwise all optimizations you fix may be useless in the end, as some query features will not be available on all engines.

Answer 2

(Too big for Comment)

This would improve performance: Change the test of `start time to simply

 AND start_time <= 1577454941

And have

INDEX(deleted, hidden, place,   -- in any order
      start_time)               -- after the others

You will find that the order (of MATCH) will be different from InnoDB than with MyISAM.

Also, InnoDB acts differently when multiple tables are involved. The will need two separate, single column, indexes: FULLTEXT(title), FULLTEXT(metakeywords).

Answer 3

Just to let you know that I've found the issue: related tables on the new server were not broken so everything worked "fine", but the indexes needed to by repaired after the copy from old server (probably because of the big MySQL version upgrade). I just ran mysqlcheck -A --auto-repair and the perf are at least equivalent now! The EXPLAIN SELECT doesn't show the "Using temporary" anymore.

Thank you all for your time and replies, especially @jynus and @Rick James. And sorry for the obvious step I was missing, as I said I'm not a DB expert :/.