Moving from MySQL to MariaDB, better hardware but slower performance. Why?

Question

I am in the process of migrating our servers to a new hosting provider, and they gave me exceptional hardware on the new node with the lasted Debian 10 and MariaDB 10.3.25. The database is on its own virtual machine and has 16GB RAM and 170GB SSD disk space. The database is about 57GB in size and includes a few hundreds of different tables.

I have migrated the database from our current old, live server with Debian 7 and MySQL 5.5.50 (with a slower CPU but same ram and SSD disk) onto the new server without issues, and I have copied pretty much the same settings inside the my.cnf file into the MariaDB's setting file.

But, I must say, the performance of simple select statements is much slower on the new server with MariaDB. I have registered a range of 25-300% slowness on the new MariaDB compared to the live server using MySQL. And if you consider that the old server has very high user load (over 100 unique users at any moment as average, thousands of hits a minute), and the new server instead has zero load because hasn't been put online yet, it is staggering to me to have such bad results.

Here is a simple statement on a InnoDB table (not using the cache):

    SELECT  SQL_NO_CACHE id, title, composer, instruments, PDFdir,
            PDFs
        FROM  virtualsheetmusic_optimizations.scores3_new2_optINNODB
        WHERE  type LIKE '%Christmas%'
          AND  (instruments LIKE '%Accordion%'
                  OR  AltInstruments LIKE '%Accordion%'
               )
          AND  tempo < 1606032000
        ORDER BY  product_source_id ASC, instruments LIKE 'Accordion %' DESC,
            popularity DESC, title LIKE '%Christmas%' DESC
        LIMIT  20;

On the old server takes 0.3 seconds to execute (first and subsequent runs). On the new server, it takes over 1 second on the first run and around 0.6 seconds on the subsequent ones. it's strange MariaDB always takes more time to execute the first query than the subsequent ones if performed within a few minutes from each other, even without using the cache and the query being exactly the same.

I get even worse results with more complex queries, but I thought to start with the simpler one above.

Here an explain on the above select statement on the old server:

mysql> explain SELECT SQL_NO_CACHE id, title, composer, instruments, PDFdir, PDFs FROM virtualsheetmusic_optimizations.scores3_new2_optINNODB WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%') AND tempo < 1606032000 ORDER BY product_source_id ASC, instruments LIKE 'Accordion %' DESC, popularity DESC, title LIKE '%Christmas%' DESC LIMIT 20;
+----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+
| id | select_type | table                  | type  | possible_keys | key   | key_len | ref  | rows  | Extra                       |
+----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | scores3_new2_optINNODB | range | Tempo         | Tempo | 4       | NULL | 90943 | Using where; Using filesort |
+----+-------------+------------------------+-------+---------------+-------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

And here is the same on the new server (I see no interesting differences):

MariaDB [(none)]> explain SELECT SQL_NO_CACHE id, title, composer, instruments, PDFdir, PDFs FROM virtualsheetmusic_optimizations.scores3_new2_optINNODB WHERE type LIKE '%Christmas%' AND (instruments LIKE '%Accordion%' OR AltInstruments LIKE '%Accordion%') AND tempo < 1606032000 ORDER BY product_source_id ASC, instruments LIKE 'Accordion %' DESC, popularity DESC, title LIKE '%Christmas%' DESC LIMIT 20;
+------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+
| id   | select_type | table                  | type  | possible_keys | key   | key_len | ref  | rows  | Extra                                              |
+------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+
|    1 | SIMPLE      | scores3_new2_optINNODB | range | Tempo         | Tempo | 4       | NULL | 85893 | Using index condition; Using where; Using filesort |
+------+-------------+------------------------+-------+---------------+-------+---------+------+-------+----------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>

And here is the CREATE statement for that table:

CREATE TABLE `scores3_new2_optINNODB` (
  `Composer` varchar(256) NOT NULL,
  `compURL` varchar(50) NOT NULL DEFAULT '',
  `URL` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(256) DEFAULT NULL,
  `Instruments` varchar(150) NOT NULL DEFAULT '',
  `instrURL` varchar(50) NOT NULL DEFAULT '',
  `Type` varchar(300) NOT NULL,
  `Skill` varchar(50) NOT NULL DEFAULT '',
  `SkillNo` int(11) NOT NULL DEFAULT '0',
  `keywords` varchar(3000) DEFAULT NULL,
  `Free` varchar(5) NOT NULL DEFAULT '',
  `AltTitle` varchar(3000) NOT NULL,
  `Tempo` int(11) NOT NULL DEFAULT '0',
  `CompOrdine` varchar(256) NOT NULL,
  `AltInstruments` varchar(300) DEFAULT NULL,
  `ContEnsemble` varchar(50) DEFAULT NULL,
  `Exclusive` varchar(5) DEFAULT NULL,
  `ID` varchar(60) DEFAULT NULL,
  `Price` decimal(8,2) DEFAULT NULL,
  `MemberPrice` decimal(9,2) NOT NULL,
  `Sub_Title` text,
  `sub_instrument` varchar(500) DEFAULT NULL,
  `Arrangement_Type` varchar(30) DEFAULT NULL,
  `Alt_Image` varchar(30) DEFAULT NULL,
  `PDFs` text NOT NULL,
  `PDFdir` varchar(10) NOT NULL DEFAULT '',
  `SkillOrd` int(11) NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL DEFAULT '0',
  `scorch` tinyint(4) NOT NULL,
  `has_pdf` tinyint(4) NOT NULL,
  `has_mp3` tinyint(4) NOT NULL,
  `has_mp3acco` tinyint(4) NOT NULL,
  `has_midi` tinyint(4) NOT NULL,
  `has_key_video` tinyint(4) NOT NULL DEFAULT '0',
  `product_source_id` int(11) NOT NULL DEFAULT '1',
  `product_type_id` tinyint(4) NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `restricted` tinyint(4) NOT NULL,
  `new_viewer` tinyint(4) NOT NULL,
  `extra_score` tinyint(4) NOT NULL,
  `minimum_quantity` int(11) NOT NULL DEFAULT '1',
  `TOTnoterange` text NOT NULL,
  `TOTnoterangen` varchar(7) NOT NULL,
  `TOTtemporangebpm` varchar(7) NOT NULL,
  `TOTtemporealrangebpm` varchar(7) NOT NULL,
  `TOTtimesignaturerange` text NOT NULL,
  `TOTkeysignaturerange` text NOT NULL,
  `TOTduration` varchar(8) NOT NULL,
  `TitleOrdine` varchar(256) DEFAULT NULL,
  `display_priority` int(11) DEFAULT NULL,
  `is_interactive` tinyint(4) NOT NULL,
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `preview_width` int(11) NOT NULL DEFAULT '0',
  `preview_height` int(11) NOT NULL DEFAULT '0',
  `performer` varchar(100) NOT NULL,
  `duration` int(11) NOT NULL,
  `page_tempo_update` int(11) NOT NULL,
  `meta_title` varchar(200) NOT NULL,
  `meta_description` text NOT NULL,
  `header` varchar(100) NOT NULL,
  `subheader` varchar(100) NOT NULL,
  `link_id` varchar(200) NOT NULL,
  `link_text` varchar(300) NOT NULL,
  `has_custom_mp3` tinyint(4) NOT NULL,
  `title_dup_fix` tinyint(4) NOT NULL,
  `popularity` int(11) NOT NULL,
  `icon` tinyint(4) NOT NULL,
  `exclusive_OPT` int(11) NOT NULL,
  `free_OPT` int(11) NOT NULL,
  `Composer_OPT` varchar(300) NOT NULL,
  `title_OPT` varchar(200) NOT NULL,
  `Instruments_OPT` varchar(100) NOT NULL,
  `Type_OPT` varchar(350) NOT NULL,
  `Skill_OPT` varchar(50) NOT NULL,
  `keywords_OPT` varchar(3500) NOT NULL,
  `AltTitle_OPT` varchar(3500) NOT NULL,
  `AltInstruments_OPT` varchar(400) NOT NULL,
  `Sub_Title_OPT` varchar(3500) NOT NULL,
  `sub_instrument_OPT` varchar(600) NOT NULL,
  KEY `ID` (`ID`),
  KEY `title` (`title`),
  KEY `Composer` (`Composer`),
  KEY `Instruments` (`Instruments`),
  KEY `has_mp3acco` (`has_mp3acco`),
  KEY `exclusive_OPT` (`exclusive_OPT`),
  KEY `free_OPT` (`free_OPT`),
  KEY `display_priority` (`display_priority`),
  KEY `rating` (`rating`),
  KEY `CompOrdine` (`CompOrdine`),
  KEY `Skill` (`Skill`),
  KEY `SkillOrd` (`SkillOrd`),
  KEY `Price` (`Price`),
  KEY `MemberPrice` (`MemberPrice`),
  KEY `Tempo` (`Tempo`),
  KEY `Composer_OPT` (`Composer_OPT`),
  KEY `title_OPT` (`title_OPT`),
  KEY `Instruments_OPT` (`Instruments_OPT`),
  KEY `Type_OPT` (`Type_OPT`),
  KEY `Skill_OPT` (`Skill_OPT`),
  KEY `keywords_OPT` (`keywords_OPT`(767)),
  KEY `AltTitle_OPT` (`AltTitle_OPT`(767)),
  KEY `AltInstruments_OPT` (`AltInstruments_OPT`),
  KEY `Sub_Title_OPT` (`Sub_Title_OPT`(767)),
  KEY `sub_instrument_OPT` (`sub_instrument_OPT`),
  KEY `product_source_id` (`product_source_id`),
  KEY `is_interactive` (`is_interactive`),
  KEY `product_type_id` (`product_type_id`),
  KEY `popularity` (`popularity`),
  KEY `display_priority_2` (`display_priority`,`product_type_id`,`Instruments`,`popularity`,
      `title_OPT`,`Composer_OPT`,`exclusive_OPT`,
      `keywords_OPT`(767),`title`,`rating`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here is a SHOW CREATE TABLE of it on the old server:

mysql> SHOW CREATE TABLE scores3_new2_optINNODB;
+------------------------+
| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------------------+
| scores3_new2_optINNODB | CREATE TABLE `scores3_new2_optINNODB` (
  `Composer` varchar(256) NOT NULL,
  `compURL` varchar(50) NOT NULL DEFAULT '',
  `URL` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(256) DEFAULT NULL,
  `Instruments` varchar(150) NOT NULL DEFAULT '',
  `instrURL` varchar(50) NOT NULL DEFAULT '',
  `Type` varchar(300) NOT NULL,
  `Skill` varchar(50) NOT NULL DEFAULT '',
  `SkillNo` int(11) NOT NULL DEFAULT '0',
  `keywords` varchar(3000) DEFAULT NULL,
  `Free` varchar(5) NOT NULL DEFAULT '',
  `AltTitle` varchar(3000) NOT NULL,
  `Tempo` int(11) NOT NULL DEFAULT '0',
  `CompOrdine` varchar(256) NOT NULL,
  `AltInstruments` varchar(300) DEFAULT NULL,
  `ContEnsemble` varchar(50) DEFAULT NULL,
  `Exclusive` varchar(5) DEFAULT NULL,
  `ID` varchar(60) DEFAULT NULL,
  `Price` decimal(8,2) DEFAULT NULL,
  `MemberPrice` decimal(9,2) NOT NULL,
  `Sub_Title` text,
  `sub_instrument` varchar(500) DEFAULT NULL,
  `Arrangement_Type` varchar(30) DEFAULT NULL,
  `Alt_Image` varchar(30) DEFAULT NULL,
  `PDFs` text NOT NULL,
  `PDFdir` varchar(10) NOT NULL DEFAULT '',
  `SkillOrd` int(11) NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL DEFAULT '0',
  `scorch` tinyint(4) NOT NULL,
  `has_pdf` tinyint(4) NOT NULL,
  `has_mp3` tinyint(4) NOT NULL,
  `has_mp3acco` tinyint(4) NOT NULL,
  `has_midi` tinyint(4) NOT NULL,
  `has_key_video` tinyint(4) NOT NULL DEFAULT '0',
  `product_source_id` int(11) NOT NULL DEFAULT '1',
  `product_type_id` tinyint(4) NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `restricted` tinyint(4) NOT NULL,
  `new_viewer` tinyint(4) NOT NULL,
  `extra_score` tinyint(4) NOT NULL,
  `minimum_quantity` int(11) NOT NULL DEFAULT '1',
  `TOTnoterange` text NOT NULL,
  `TOTnoterangen` varchar(7) NOT NULL,
  `TOTtemporangebpm` varchar(7) NOT NULL,
  `TOTtemporealrangebpm` varchar(7) NOT NULL,
  `TOTtimesignaturerange` text NOT NULL,
  `TOTkeysignaturerange` text NOT NULL,
  `TOTduration` varchar(8) NOT NULL,
  `TitleOrdine` varchar(256) DEFAULT NULL,
  `display_priority` int(11) DEFAULT NULL,
  `is_interactive` tinyint(4) NOT NULL,
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `preview_width` int(11) NOT NULL DEFAULT '0',
  `preview_height` int(11) NOT NULL DEFAULT '0',
  `performer` varchar(100) NOT NULL,
  `duration` int(11) NOT NULL,
  `page_tempo_update` int(11) NOT NULL,
  `meta_title` varchar(200) NOT NULL,
  `meta_description` text NOT NULL,
  `header` varchar(100) NOT NULL,
  `subheader` varchar(100) NOT NULL,
  `link_id` varchar(200) NOT NULL,
  `link_text` varchar(300) NOT NULL,
  `has_custom_mp3` tinyint(4) NOT NULL,
  `title_dup_fix` tinyint(4) NOT NULL,
  `popularity` int(11) NOT NULL,
  `icon` tinyint(4) NOT NULL,
  `exclusive_OPT` int(11) NOT NULL,
  `free_OPT` int(11) NOT NULL,
  `Composer_OPT` varchar(300) NOT NULL,
  `title_OPT` varchar(200) NOT NULL,
  `Instruments_OPT` varchar(100) NOT NULL,
  `Type_OPT` varchar(350) NOT NULL,
  `Skill_OPT` varchar(50) NOT NULL,
  `keywords_OPT` varchar(3500) NOT NULL,
  `AltTitle_OPT` varchar(3500) NOT NULL,
  `AltInstruments_OPT` varchar(400) NOT NULL,
  `Sub_Title_OPT` varchar(3500) NOT NULL,
  `sub_instrument_OPT` varchar(600) NOT NULL,
  KEY `ID` (`ID`),
  KEY `title` (`title`),
  KEY `Composer` (`Composer`),
  KEY `Instruments` (`Instruments`),
  KEY `has_mp3acco` (`has_mp3acco`),
  KEY `exclusive_OPT` (`exclusive_OPT`),
  KEY `free_OPT` (`free_OPT`),
  KEY `display_priority` (`display_priority`),
  KEY `rating` (`rating`),
  KEY `CompOrdine` (`CompOrdine`),
  KEY `Skill` (`Skill`),
  KEY `SkillOrd` (`SkillOrd`),
  KEY `Price` (`Price`),
  KEY `MemberPrice` (`MemberPrice`),
  KEY `Tempo` (`Tempo`),
  KEY `Composer_OPT` (`Composer_OPT`),
  KEY `title_OPT` (`title_OPT`),
  KEY `Instruments_OPT` (`Instruments_OPT`),
  KEY `Type_OPT` (`Type_OPT`),
  KEY `Skill_OPT` (`Skill_OPT`),
  KEY `keywords_OPT` (`keywords_OPT`(767)),
  KEY `AltTitle_OPT` (`AltTitle_OPT`(767)),
  KEY `AltInstruments_OPT` (`AltInstruments_OPT`),
  KEY `Sub_Title_OPT` (`Sub_Title_OPT`(767)),
  KEY `sub_instrument_OPT` (`sub_instrument_OPT`),
  KEY `product_source_id` (`product_source_id`),
  KEY `is_interactive` (`is_interactive`),
  KEY `product_type_id` (`product_type_id`),
  KEY `popularity` (`popularity`),
  KEY `display_priority_2` (`display_priority`,`product_type_id`,`Instruments`,`popularity`,
      `title_OPT`,`Composer_OPT`,`exclusive_OPT`,`keywords_OPT`(767),
      `title`,`rating`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------------+
1 row in set (0.00 sec)

And here is the same on the new server:

MariaDB [virtualsheetmusic_optimizations]> SHOW CREATE TABLE scores3_new2_optINNODB;
+------------------------+
| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------------+
| scores3_new2_optINNODB | CREATE TABLE `scores3_new2_optINNODB` (
  `Composer` varchar(256) CHARACTER SET latin1 NOT NULL,
  `compURL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `URL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `title` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  `Instruments` varchar(150) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `instrURL` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `Type` varchar(300) CHARACTER SET latin1 NOT NULL,
  `Skill` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `SkillNo` int(11) NOT NULL DEFAULT 0,
  `keywords` varchar(3000) CHARACTER SET latin1 DEFAULT NULL,
  `Free` varchar(5) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `AltTitle` varchar(3000) CHARACTER SET latin1 NOT NULL,
  `Tempo` int(11) NOT NULL DEFAULT 0,
  `CompOrdine` varchar(256) CHARACTER SET latin1 NOT NULL,
  `AltInstruments` varchar(300) CHARACTER SET latin1 DEFAULT NULL,
  `ContEnsemble` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `Exclusive` varchar(5) CHARACTER SET latin1 DEFAULT NULL,
  `ID` varchar(60) CHARACTER SET latin1 NOT NULL,
  `Price` decimal(8,2) DEFAULT NULL,
  `MemberPrice` decimal(9,2) NOT NULL,
  `Sub_Title` text CHARACTER SET latin1 DEFAULT NULL,
  `sub_instrument` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `Arrangement_Type` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `Alt_Image` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `PDFs` text CHARACTER SET latin1 NOT NULL,
  `PDFdir` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `SkillOrd` int(11) NOT NULL DEFAULT 0,
  `rating` int(11) NOT NULL DEFAULT 0,
  `scorch` tinyint(4) NOT NULL,
  `has_pdf` tinyint(4) NOT NULL,
  `has_mp3` tinyint(4) NOT NULL,
  `has_mp3acco` tinyint(4) NOT NULL,
  `has_midi` tinyint(4) NOT NULL,
  `has_key_video` tinyint(4) NOT NULL DEFAULT 0,
  `product_source_id` int(11) NOT NULL DEFAULT 1,
  `product_type_id` tinyint(4) NOT NULL DEFAULT 0,
  `description` text CHARACTER SET latin1 NOT NULL,
  `restricted` tinyint(4) NOT NULL,
  `new_viewer` tinyint(4) NOT NULL,
  `extra_score` tinyint(4) NOT NULL,
  `minimum_quantity` int(11) NOT NULL DEFAULT 1,
  `TOTnoterange` text CHARACTER SET latin1 NOT NULL,
  `TOTnoterangen` varchar(7) CHARACTER SET latin1 NOT NULL,
  `TOTtemporangebpm` varchar(7) CHARACTER SET latin1 NOT NULL,
  `TOTtemporealrangebpm` varchar(7) CHARACTER SET latin1 NOT NULL,
  `TOTtimesignaturerange` text CHARACTER SET latin1 NOT NULL,
  `TOTkeysignaturerange` text CHARACTER SET latin1 NOT NULL,
  `TOTduration` varchar(8) CHARACTER SET latin1 NOT NULL,
  `TitleOrdine` varchar(256) DEFAULT NULL,
  `display_priority` int(11) DEFAULT NULL,
  `is_interactive` tinyint(4) NOT NULL,
  `hidden` tinyint(4) NOT NULL DEFAULT 0,
  `preview_width` int(11) NOT NULL DEFAULT 0,
  `preview_height` int(11) NOT NULL DEFAULT 0,
  `performer` varchar(100) NOT NULL,
  `duration` int(11) NOT NULL,
  `page_tempo_update` int(11) NOT NULL,
  `meta_title` varchar(200) NOT NULL,
  `meta_description` text NOT NULL,
  `header` varchar(100) NOT NULL,
  `subheader` varchar(100) NOT NULL,
  `link_id` varchar(200) NOT NULL,
  `link_text` varchar(300) NOT NULL,
  `has_custom_mp3` tinyint(4) NOT NULL,
  `title_dup_fix` tinyint(4) NOT NULL,
  `popularity` int(11) NOT NULL,
  `icon` tinyint(4) NOT NULL,
  `exclusive_OPT` int(11) NOT NULL,
  `free_OPT` int(11) NOT NULL,
  `Composer_OPT` varchar(300) NOT NULL,
  `title_OPT` varchar(200) NOT NULL,
  `Instruments_OPT` varchar(100) NOT NULL,
  `Type_OPT` varchar(350) NOT NULL,
  `Skill_OPT` varchar(50) NOT NULL,
  `keywords_OPT` varchar(3500) NOT NULL,
  `AltTitle_OPT` varchar(3500) NOT NULL,
  `AltInstruments_OPT` varchar(400) NOT NULL,
  `Sub_Title_OPT` varchar(3500) NOT NULL,
  `sub_instrument_OPT` varchar(600) NOT NULL,
  KEY `title` (`title`),
  KEY `Composer` (`Composer`),
  KEY `Instruments` (`Instruments`),
  KEY `has_mp3acco` (`has_mp3acco`),
  KEY `exclusive_OPT` (`exclusive_OPT`),
  KEY `free_OPT` (`free_OPT`),
  KEY `display_priority` (`display_priority`),
  KEY `rating` (`rating`),
  KEY `CompOrdine` (`CompOrdine`),
  KEY `Skill` (`Skill`),
  KEY `SkillOrd` (`SkillOrd`),
  KEY `Price` (`Price`),
  KEY `MemberPrice` (`MemberPrice`),
  KEY `Tempo` (`Tempo`),
  KEY `Composer_OPT` (`Composer_OPT`),
  KEY `title_OPT` (`title_OPT`),
  KEY `Instruments_OPT` (`Instruments_OPT`),
  KEY `Type_OPT` (`Type_OPT`),
  KEY `Skill_OPT` (`Skill_OPT`),
  KEY `keywords_OPT` (`keywords_OPT`(768)),
  KEY `AltTitle_OPT` (`AltTitle_OPT`(768)),
  KEY `AltInstruments_OPT` (`AltInstruments_OPT`),
  KEY `Sub_Title_OPT` (`Sub_Title_OPT`(768)),
  KEY `sub_instrument_OPT` (`sub_instrument_OPT`),
  KEY `product_source_id` (`product_source_id`),
  KEY `is_interactive` (`is_interactive`),
  KEY `product_type_id` (`product_type_id`),
  KEY `popularity` (`popularity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------------+
1 row in set (0.001 sec)

And finally, here is the my.cnf on the old server with MySQL (I have removed the comments):

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 0.0.0.0
key_buffer      = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
expire_logs_days    = 10
max_binlog_size         = 100M

symbolic-links=0
max_allowed_packet=1000M
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query-cache-type=1
query-cache-size=20M

myisam_recover_options=OFF
thread_stack=262144


innodb_buffer_pool_size=1GB
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

And here is the same on the new server (MariaDB):

log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size        = 100M


character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci



symbolic-links=0
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query-cache-type=1
query-cache-size=20M

myisam_recover_options=OFF
thread_stack=262144


innodb_buffer_pool_size=10GB
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M

innodb_thread_sleep_delay = 0
innodb_file_per_table = 1
skip-name-resolve

innodb_strict_mode = 0




[embedded]

[mariadb]

[mariadb-10.3]

Eager to know your thoughts about all this.

Thank you in advance to everyone!

All the best, Fab.

Answer 1

(This addresses how to speed up the query significantly.)

The slow part is the WHERE. Change from

    WHERE  type LIKE '%Christmas%'
      AND  (instruments LIKE '%Accordion%'
              OR  AltInstruments LIKE '%Accordion%'
           )
      AND  tempo < 1606032000

to

    WHERE  MATCH(type, instruments, AltInstruments)
           AGAINST ("+Christmas +Accordion" IN BOOLEAN MODE)
      AND  type LIKE '%Christmas%'
      AND  (instruments LIKE '%Accordion%'
              OR  AltInstruments LIKE '%Accordion%'
           )
      AND  tempo < 1606032000

And add

FULLTEXT(type, instruments, AltInstruments)

The MATCH will run first, and be very fast. The rest will double-check things.

Using "Prefix indexing", such as (767), is virtually useless has become less necessary with newer versions. See http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Changing 767 to 768 won't make much difference.

Leading wildcard in LIKE prevents use of an index.

Having a prefix in the anywhere in an index keeps it from being "covering".

more time to execute the first query than the subsequent ones

This is simply because the first execution needed to fetch data from disk; the subsequent ones did not. This applies to both MySQL and MariaDB. I would guess that the MySQL timing run was on a machine where the table had been already cached.

I see latin1 vs utf8mb4. The may have an impact on this, and other, queries.

TOTtemporangebpm varchar(7) -- If this is a "time", the use of varchar could hurt.

The ROW_FORMAT for the table changed from COMPACT to DYNAMIC?

The main difference between the two is what happens when a row is bigger than about 8KB. And it looks like your table has that happening a lot. For Compact, it tries to put up to 768 bytes in the "on-record" part of the row, spilling the rest to an "off-record" place (a separate block(s)). For Dynamic, any column that needs to be offloaded is moved entirely.

The impact of this comes when you SELECT any such column that is handled differently. It is the difference of an extra disk hit; these can mount up.

Especially bad is SELECT * when you don't really need all the columns. That is SELECT a,b,c will run faster simply because of how the rest of the columns are being stored.

80 columns is a lot. Especially since many are 'large' VARCHARs'.

Glancing at the column names, I don't see any obvious suggestions. But consider this... If you have some clumps of columns that are rarely used or "belong together" or are often NULL, consider moving such a clump to another table. The new table would share the same PRIMARY KEY (ID) for ease of JOINing.

Also, we should talk about what the ID looks like. Is it a UUID? Something else? Is it really that long? etc. There are performances to be discussed here.

Answer 2

(Not an Answer; just analysis of VARIABLES and GLOBAL STATUS)

Observations:

• Version: 10.3.25-MariaDB-0+deb10u1
• 16 GB of RAM
• Uptime = 04:44:58; some GLOBAL STATUS values may not be meaningful yet.
• You are not running on Windows.
• Running 64-bit version
• You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

• Suggest increasing innodb_log_file_size to 500M

• With SSDs, turn off innodb_flush_neighbors

• max_allowed_packet is currently 1G? That is dangerously large. Make it no more than 1% of RAM.

Details and other observations:

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 8)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 475,671 * 16384 / 10240M = 72.6% -- buffer pool free -- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 475,671 / 655280 = 72.6% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size (now 10737418240) is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 2,898,952,192 / 10240M = 27.0% -- Percent of buffer pool taken up by data -- A small percent may indicate that the buffer_pool is unnecessarily big.

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3% -- Buffer is in RAM; file is on disk. -- The buffer_size should be smaller and/or the file_size should be larger.

( Innodb_log_writes ) = 828,130 / 17098 = 48 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,844,398,080 / (17098 / 3600) / 2 / 48M = 3.86 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,098 / 60 * 48M / 1844398080 = 7.78 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_allowed_packet ) = 1,024M / 16384M = 6.2% -- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 10737418240) to make room. Swapping is terrible for performance.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?) -- Lower the setting.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Qcache_lowmem_prunes/Qcache_inserts ) = 135,164/146466 = 92.3% -- Removal Ratio (frequency of needing to prune due to not enough memory)

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (20M - 9161096) / 10579 / 16384 = 0.0681 -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size (now 16384)

( tmp_table_size ) = 64M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 67108864) to avoid running out of RAM. Perhaps no more than 64M.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional". -- Galera desires 2; 2 requires BINLOG_FORMAT=ROW or MIXED

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( thread_cache_size / Max_used_connections ) = 200 / 5 = 4000.0% -- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.

Abnormally small:

((query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) / query_cache_min_res_unit = 0.273
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache = 1,116
Acl_database_grants = 1
Handler_tmp_write = 3.8 /sec
Handler_update = 17 /HR
Innodb_rows_updated = 3.2 /HR
Rows_tmp_read = 4 /sec
Sort_priority_queue_sorts = 5.7 /HR
eq_range_index_dive_limit = 0
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400

Abnormally large:

10 * read_buffer_size = 160MB
Com_show_keys = 47 /HR
Com_show_slave_hosts = 0.21 /HR
Com_show_storage_engines = 2.7 /HR
Feature_locale = 20 /HR
Handler_discover = 11 /HR
Handler_read_next / Handler_read_key = 6,572
Innodb_pages_created = 6.7 /sec
Tc_log_page_size = 4,096
innodb_background_scrub_data_check_interval = 0.21 /sec
innodb_background_scrub_data_interval = 35 /sec
innodb_read_io_threads = 64
innodb_write_io_threads = 64
max_long_data_size = 1024MB
net_buffer_length = 1.05e+6

Abnormal strings:

Innodb_have_snappy = ON
aria_recover_options = BACKUP,QUICK
innodb_fast_shutdown = 1
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
plugin_maturity = gamma

Answer 3

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
innodb_change_buffer_max_size=50  # from 25 percent to accomodate innodb_pages_created at 7 RPS
read_buffer_size=256K  # from 16M to reduce handler_read_next RPS of 57,676
innodb_io_capacity=3900  # from 2000 to use more of your SSD IOPS capacity

From your OS Command Prompt,

ulimit -n 48000 and press Enter to enable more than 1024 Open Files

OS stop/start not required for this dynamic variable.

ulimit -a and press Enter to verify you have Open Files of 48000

For this value to persist over OS stop/start, review this URL - https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ Please set your values to 48000 rather than 500000 as in their example.

For additional assistance, view profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning.