文章/答案/技术大牛

发布

社区首页 >问答首页 >从MySQL到MariaDB，硬件更好，但性能更慢。为什么？

问从MySQL到MariaDB，硬件更好，但性能更慢。为什么？
EN

Database Administration用户

提问于 2020-11-19 20:16:31

回答 2查看 2.5K关注 0票数 3

我正在将我们的服务器迁移到一个新的主机提供商，它们在新节点上为我提供了异常的硬件，包括持续的Debian 10和MariaDB 10.3.25。数据库位于自己的虚拟机上，拥有16 on和170 on磁盘空间。该数据库的大小约为57 of，包括几百个不同的表。

我已经使用Debian 7和MySQL 5.5.50 (使用较慢的CPU，但使用相同的ram和SSD磁盘)将数据库从当前的老服务器迁移到新服务器上，没有出现问题，而且我已经将my.cnf文件中的几乎相同的设置复制到MariaDB的设置文件中。

但是，我必须说，在使用MariaDB的新服务器上，简单select语句的性能要慢得多。与使用MariaDB的实时服务器相比，我已经在新的MySQL上注册了25-300%的慢度。如果你考虑到旧服务器的用户负载非常高(平均每分钟有100多个唯一用户，每分钟有数千次点击)，而新服务器的负载为零，因为还没有上线，那么我很惊讶有这么糟糕的结果。

下面是InnoDB表上的一个简单语句(不使用缓存)：

    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;

在旧服务器上，执行时间为0.3秒(第一次运行和后续运行)。在新服务器上，第一次运行需要超过1秒，后续运行则需要0.6秒。奇怪的是，如果在几分钟内执行第一个查询，MariaDB执行第一个查询所花费的时间总是更多，即使没有使用缓存和查询完全相同。

对于更复杂的查询，我会得到更糟糕的结果，但我想从上面的更简单的查询开始。

下面是对旧服务器上的select语句的解释：

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)

在新服务器上也是如此(我看不出有什么有趣的区别)：

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)]>

下面是该表的CREATE语句：

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;

下面是它在旧服务器上的创建表：

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)

在新服务器上也是这样：

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)

最后，下面是带有my.cnf的旧服务器上的MySQL (我已经删除了注释)：

[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/

在新服务器(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]

想知道你对这一切的想法。

提前谢谢大家！

一切顺利，法布。

mysql

performance

mariadb

debian

回答 2

Database Administration用户

发布于 2020-11-21 00:03:24

(不是答案；只是分析变量和全局状态)

观测：

版本: 10.3.25-MariaDB-0+deb10u1
16 GB内存
正常运行时间= 04:44:58；一些全局状态值可能还没有意义。
您不在Windows上运行。
运行64位版本
您似乎正在完全(或大部分)运行InnoDB。

更重要的问题：

建议将innodb_log_file_size提高到500米
使用SSD，关闭innodb_flush_neighbors
max_allowed_packet目前是1G？这是危险的大。使其不超过内存的1%。

详细信息和其他观察：

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 --每秒钟页面清理器的工作量。- "InnoDB: page_cleaner: 1000预定循环.“可以通过降低lru_scan_depth来修复:考虑1000 / innodb_page_cleaners (现在是4)。也检查一下是否有交换。

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 - innodb_page_cleaners -建议将innodb_page_cleaners (现在4)设置为innodb_buffer_pool_instances (现在8)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000的预定循环.“可以通过降低lru_scan_depth来固定

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 475,671 * 16384 / 10240M = 72.6% --缓冲池空闲-- buffer_pool_size比工作集大，可以减少它。

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 475,671 / 655280 = 72.6% --目前还没有使用的buffer_pool的Pct -- innodb_buffer_pool_size (现在是10737418240)比需要的大吗？

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 2,898,952,192 / 10240M = 27.0% --数据占用的缓冲池的百分比--一小部分可能表明buffer_pool不必要地大。

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3% --缓冲区在内存中，文件在磁盘上.-- buffer_size应该更小，file_size应该更大。

( 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 -比率-(见记录)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,098 / 60 * 48M / 1844398080 = 7.78 -从5.6.8开始的InnoDB日志轮转之间的分钟时间，这可以动态更改；确保也要更改my.cnf。-- (轮流60分钟的建议有点武断)。调整innodb_log_file_size (现在是50331648)。(AWS中无法更改)

( innodb_flush_method ) = innodb_flush_method = fsync -- InnoDB应该如何要求操作系统编写块。建议O_DIRECT或O_ALL_DIRECT (Percona)避免双重缓冲。(至少对于Unix.)关于O_ALL_DIRECT的警告见chrischandler

( innodb_flush_neighbors ) = 1 --将块写入磁盘时的次要优化。- SSD驱动器使用0；HDD使用1。

( sync_binlog ) = 0 --使用1来增加安全性，而I/O =1的代价可能会导致大量的“查询结束”；=0可能导致“无法定位的二进制日志”，并在崩溃时丢失事务，但速度更快。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF --是否记录所有死锁。--如果你被死锁所困扰，把这个打开警告:如果您有很多死锁，这可能会写入很多磁盘。

( max_allowed_packet ) = 1,024M / 16384M = 6.2%否则，减少innodb_buffer_pool_size (现在10737418240)，以腾出空间。用交换来换取表演是很糟糕的。

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% --全文结果集的字节限制。(可能不是预先分配的，而是增长的？) --降低设置。

( local_infile ) = local_infile = ON - local_infile (现在开始)= ON是一个潜在的安全问题

( Qcache_lowmem_prunes/Qcache_inserts ) = 135,164/146466 = 92.3% --去除率(由于内存不足需要修剪的频率)

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (20M - 9161096) / 10579 / 16384 = 0.0681 -- query_alloc_block_size对公式--调整query_alloc_block_size (现在为16384)

( tmp_table_size ) = 64M -限制内存临时表的大小，用于支持选择-减少tmp_table_size (现在是67108864)，以避免耗尽内存。大概不超过6400万。

( binlog_format ) = binlog_format = MIXED --语句/行/混合。-行优先5.7 (10.3)

( innodb_autoinc_lock_mode ) = 1 -- Galera:愿望2 -- 2=“交织”；1=“连续”是典型的；0=“传统”。- Galera欲望2；2需要BINLOG_FORMAT=ROW或混合

( slow_query_log ) = slow_query_log = OFF --是否记录慢速查询。(5.1.12)

( long_query_time ) = 10 --定义“慢速”查询的截止值(秒)。-建议2

( thread_cache_size / Max_used_connections ) = 200 / 5 = 4000.0% -线程缓存大于可能的连接数没有好处。浪费空间是不利的。

异常小：

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

异常大：

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

异常字符串：

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

票数 1

Database Administration用户

发布于 2020-11-20 17:22:12

每秒速率= RPS

关于my.cnf 米舍尔德部分应考虑的建议

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

从你的操作系统命令提示符

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

此动态变量不需要OS停止/启动。

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

要使此值在OS停止/启动过程中持续存在，请查看此https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/ --请将您的值设置为48000，而不是其示例中的500000。

更多的帮助，查看配置文件，网络配置文件的联系信息和免费下载的实用程序脚本，以协助性能调优。

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/280071

复制

相似问题

问从MySQL到MariaDB，硬件更好，但性能更慢。为什么？
EN

回答 2

Database Administration用户

观测：

更重要的问题：

详细信息和其他观察：

异常小：

异常大：

异常字符串：

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从MySQL到MariaDB，硬件更好，但性能更慢。为什么？EN

回答 2

Database Administration用户

观测：

更重要的问题：

详细信息和其他观察：

异常小：

异常大：

异常字符串：

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从MySQL到MariaDB，硬件更好，但性能更慢。为什么？
EN