MariaDB 10.6连接查询比10.3慢10倍

Question

我有两个VPS服务器(LEMP)运行Wordpress站点。规格：

旧

• 4GB RAM
• Ubuntu 18.04
• MariaDB 10.3

新

• 8GB RAM
• Ubuntu 22.04
• MariaDB 10.6

我迁移了一个站点，发现它在新服务器上慢了10倍左右。我启用了启用log-queries-not-using-indexes的慢速查询日志，并且几乎记录了每个查询。

使用慢速查询作为测试用例，我在两个数据库上运行它，在新服务器上运行慢了10倍。然后，我在两台服务器上做了“解释”，发现在新服务器上，它没有使用任何索引。我运行了mysqlcheck --optimize并改进了查询时间(连同解释)，但仍然比旧服务器慢4倍。

除了最新的操作系统和软件版本之外，服务器配置大致相同，除了拥有更多内存的较新服务器外，这些内存已分配给MariaDB。

以下是新服务器的server.cnf文件。我根据性能优化建议对其进行了调整，但除了优化整个数据库之外，没有任何东西有助于加快查询速度。

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#

#user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
#datadir                 = /var/lib/mysql
#tmpdir                  = /tmp

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#

#key_buffer_size        = 128M
#max_allowed_packet     = 1G
#thread_stack           = 192K
#thread_cache_size      = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections        = 100
#table_cache            = 64

#
# * Logging and Replication
#

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log                  = 1
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 3
#log_slow_verbosity    = query_plan,explain
#log-queries-not-using-indexes = 1
#min_examined_row_limit = 1000

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size        = 100M

#
# * SSL/TLS
#

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G

#
# Performance Tuning 
#

# few myisam tables, set this low
key_buffer_size     = 32M

# increased from 2000
table_open_cache    = 4000

# Note: query cache deprecated, s/b disabled acc. to perf tuner
query_cache_limit       = 0
query_cache_size        = 0
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type       = OFF

# kill long running queries (30 seconds)
max_statement_time = 30

# also change aria page cache from 128M (helps w/temp tables)
aria_pagecache_buffer_size = 256M

#Note: disabled bin logs
skip-log-bin

innodb_log_file_size    = 512M
innodb_buffer_pool_size = 3G
# This is ignored in 10.5 and above (auto calced based on pool size, so ignore perf script rec to raise it)
#innodb_buffer_pool_instances = 2

# increased both after server upgrade
tmp_table_size      = 1572M
max_heap_table_size = 1572M
# > total num tables
table_definition_cache  = 3200

# END changes


# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.6 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.6]

下面是在没有首先优化数据库的情况下的解释查询。选择查询需要0.133秒：

 explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key
 = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private')))
_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0,
12;
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| id   | select_type | table             | type   | possible_keys               | key              | key_len | ref                                | rows | Extra                                                     |
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | wp_posts          | range  | PRIMARY,type_status_date    | type_status_date | 164     | NULL                               | 1    | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | wp_postmeta       | ALL    | post_id,meta_key            | NULL             | NULL    | NULL                               | 1    | Using where; Using join buffer (flat, BNL join)           |
|    1 | SIMPLE      | mt1               | ALL    | post_id,meta_key            | NULL             | NULL    | NULL                               | 1    | Using where; Using join buffer (incremental, BNL join)    |
|    1 | SIMPLE      | wpml_translations | eq_ref | el_type_id,id_type_language | el_type_id       | 251     | func,mamaisonfrancaise.wp_posts.ID | 1    | Using index condition; Using where                        |
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+

优化后的相同解释。选择查询现在需要0.048秒：

explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0,
12;
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+
| id   | select_type | table             | type       | possible_keys               | key              | key_len | ref                                | rows   | Extra                                                     |
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+
|    1 | SIMPLE      | wp_posts          | range      | PRIMARY,type_status_date    | type_status_date | 164     | NULL                               | 64     | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | mt1               | ref|filter | post_id,meta_key            | post_id|meta_key | 8|767   | mamaisonfrancaise.wp_posts.ID      | 3 (0%) | Using where; Using rowid filter                           |
|    1 | SIMPLE      | wp_postmeta       | ref        | post_id,meta_key            | post_id          | 8       | mamaisonfrancaise.wp_posts.ID      | 3      | Using where                                               |
|    1 | SIMPLE      | wpml_translations | ref        | el_type_id,id_type_language | id_type_language | 251     | mamaisonfrancaise.wp_posts.ID,func | 1      | Using where; Using index                                  |
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+

下面是来自旧服务器的相同查询(MariaDB10.3)。select查询需要0.013秒。

 explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 12;
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+
| id   | select_type | table             | type   | possible_keys               | key        | key_len | ref                                            | rows | Extra                                        |
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | wp_postmeta       | ref    | post_id,meta_key            | meta_key   | 767     | const                                          |   62 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | wp_posts          | eq_ref | PRIMARY,type_status_date    | PRIMARY    | 8       | mamaisonfrancaise.wp_postmeta.post_id          |    1 | Using where                                  |
|    1 | SIMPLE      | wpml_translations | eq_ref | el_type_id,id_type_language | el_type_id | 251     | func,mamaisonfrancaise.wp_postmeta.post_id     |    1 | Using index condition; Using where           |
|    1 | SIMPLE      | mt1               | ref    | post_id,meta_key            | post_id    | 8       | mamaisonfrancaise.wpml_translations.element_id |    4 | Using index condition; Using where           |
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+

最后，下面是上面使用的三个表的create语句(在两个数据库上相同)：

| wp_posts | CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=6739 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| wp_postmeta | CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=86406 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| wp_icl_translations | CREATE TABLE `wp_icl_translations` (
  `translation_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `element_type` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post_post',
  `element_id` bigint(20) DEFAULT NULL,
  `trid` bigint(20) NOT NULL,
  `language_code` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_language_code` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `trid_lang` (`trid`,`language_code`),
  UNIQUE KEY `el_type_id` (`element_type`,`element_id`),
  KEY `trid` (`trid`),
  KEY `id_type_language` (`element_id`,`element_type`,`language_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6566 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

这里有关于10.3vs10.6性能的另一个最近的问题，所以我想知道这些版本之间是否有什么变化？为什么我需要优化表以提高性能？是否有任何配置更改可以帮助？注意，我不能更改查询或表本身，因为它们是由Wordpress和相关插件提供的。

提前谢谢。

编辑这里是show engine innodb status的输出

旧

=====================================
2022-09-21 16:57:37 0x7facd4664700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9256 srv_active, 0 srv_shutdown, 19604 srv_idle
srv_master_thread log flush and writes: 28853
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 618180
OS WAIT ARRAY INFO: signal count 287213
RW-shared spins 12767, rounds 300017, OS waits 8937
RW-excl spins 5573, rounds 14028, OS waits 158
RW-sx spins 95, rounds 883, OS waits 17
Spin rounds per wait: 23.50 RW-shared, 2.52 RW-excl, 9.29 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2104026620
Purge done for trx''s n:o < 2104026620 undo n:o < 0 state: running
History list length 30
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421855290974312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421855290982664, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
29223 OS file reads, 462720 OS file writes, 190797 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.14 writes/s, 5.82 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 185, seg size 187, 219 merges
merged operations:
 insert 81, delete mark 750, delete 359
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 332147, node heap has 26 buffer(s)
Hash table size 332147, node heap has 95 buffer(s)
Hash table size 332147, node heap has 80 buffer(s)
Hash table size 332147, node heap has 81 buffer(s)
Hash table size 332147, node heap has 289 buffer(s)
Hash table size 332147, node heap has 61 buffer(s)
Hash table size 332147, node heap has 428 buffer(s)
Hash table size 332147, node heap has 18 buffer(s)
475.39 hash searches/s, 8.59 non-hash searches/s
---
LOG
---
Log sequence number 857452474234
Log flushed up to   857452474234
Pages flushed up to 857452455417
Last checkpoint at  857452429869
0 pending log flushes, 0 pending chkp writes
138348 log i/o's done, 3.27 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1403912192
Dictionary memory allocated 1209824
Buffer pool size   81920
Free buffers       51281
Database pages     29561
Old database pages 10932
Modified db pages  7
Percent of dirty pages(LRU & free pages): 0.009
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 28619, created 942, written 306353
0.00 reads/s, 0.00 creates/s, 8.82 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 29561, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1063, Main thread ID=140378813626112, state: sleeping
Number of rows inserted 36282, updated 85523, deleted 13204, read 154819934
0.00 inserts/s, 1.77 updates/s, 0.00 deletes/s, 475.71 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

新

=====================================
2022-09-21 16:56:54 0x7f6678db4640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 28897 srv_idle
srv_master_thread log flush and writes: 28902
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 7375478
Purge done for trx''s n:o < 7375478 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7f667a556130), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
30553 OS file reads, 56567 OS file writes, 65170 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.14 writes/s, 0.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4501511543
Log flushed up to   4501511543
Pages flushed up to 4263277735
Last checkpoint at  4263277723
0 pending log flushes, 0 pending chkp writes
56569 log i/o's done, 0.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3254779904
Dictionary memory allocated 22318968
Buffer pool size   194688
Free buffers       151928
Database pages     42760
Old database pages 15764
Modified db pages  14896
Percent of dirty pages(LRU & free pages): 7.651
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 9152, not young 518978
0.00 youngs/s, 0.00 non-youngs/s
Pages read 29150, created 13816, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 42760, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 275257, updated 33966, deleted 4640, read 269777358
0.04 inserts/s, 0.02 updates/s, 0.04 deletes/s, 1330.07 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

结果是我的VPS的物理主机出了问题。在将VPS移动到与旧服务器匹配的物理服务器上之后，查询速度已经大大提高，现在比原来服务器上的速度要快得多，正如预期的那样。

编辑以下是有关物理主机问题的更多信息。我和我的主机开了一张票，他们让我做一些诊断：

hdparm -Tt /dev/sda

和

iostat 1 10
for x in `seq 1 1 30`; do ps -eo state,pid,cmd | grep "^D"; echo "-"; sleep 2; done
top -bn 1 | head -15

这两个命令都比旧服务器慢得多。他们还确认物理服务器的负载比原始服务器大得多，因此他们同意将VPS迁移到负载较低的服务器上。