Mysql – From MySQL to MariaDB : bad performances issue with MyISAM tables

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)"
        }
      }
    }
  }
}