1

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 (Old server)

New 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)"
        }
      }
    }
  }
}
  • You can use SQL_NO_CACHE after select for more legible queries to avoid cache. – jynus Dec 31 '19 at 12:02
  • Please also paste the full explain in text format, as well as the following: flush status; SELECT ... (your query); SHOW STATUS LIKE 'Hand%';, for both servers. – jynus Dec 31 '19 at 12:09
  • You might like to try the MariaDB Aria engine which is their replacement for MyISAM and may help resolve your issue? – Vérace Dec 31 '19 at 19:18
  • Additional information request from MariaDB server. RAM size, # cores, any SSD devices on MySQL Host server? Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for workload configuration tuning analysis. – Wilson Hauck Jan 1 at 20:13
  • 1
    Eh? You say that ICP is disabled, yet Handler_icp_attempts is non zero? – Rick James Jan 4 at 5:53
0

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.

  • Unfortunately it didn't change anything. I've updated my original post with informations you asked. The only difference left between the two EXPLAIN is the Using temporary, do you think it has an impact? – Sicaa Dec 31 '19 at 15:22
  • 1
    @Sicaa - Handler_tmp_write implies that a temp table was used by the slower one, but not the faster one. This concurs with the EXPLAIN. The question remains as to "why". – Rick James Jan 4 at 5:52
  • The same query without the SELECT MATCH() and ORDER BY MATCH() parts runs instantaneously and without creating en temp table on the new server. I still don't understand why the two servers acts differently with the same config. – Sicaa Jan 8 at 15:05
  • @Sicaa - You may have stripped down the query too much; I see "index_condition": "wfplace = 'live'" in the EXPLAIN, but not the query. – Rick James Jan 8 at 16:23
  • @Rick James - My bad, I renamed some fields in my initial post. I've edited the initial query with the correct field name. – Sicaa Jan 13 at 8:57
0

(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).

  • See if this helps: ANALYZE TABLE news. – Rick James Jan 8 at 16:24
0

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 :/.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.