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.
Best Answer
(This addresses how to speed up the query significantly.)
The slow part is the
WHERE. Change fromto
And add
The
MATCHwill 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_indexesChanging
767to768won't make much difference.Leading wildcard in
LIKEprevents use of an index.Having a prefix in the anywhere in an index keeps it from being "covering".
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
latin1vsutf8mb4. The may have an impact on this, and other, queries.TOTtemporangebpmvarchar(7) -- If this is a "time", the use ofvarcharcould hurt.The
ROW_FORMATfor the table changed fromCOMPACTtoDYNAMIC?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
SELECTany 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 isSELECT a,b,cwill 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 samePRIMARY KEY (ID)for ease ofJOINing.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.