0

I have two VPS servers (LEMP) running Wordpress sites. Specs:

Old

  • 4GB RAM
  • Ubuntu 18.04
  • MariaDB 10.3

New

  • 8GB RAM
  • Ubuntu 22.04
  • MariaDB 10.6

I migrated a site and found that it was around 10 times slower on the new server. I enabled slow query logs with log-queries-not-using-indexes enabled and nearly every query was being logged.

Using a slow query as a test case, I ran it on both databases and it was 10 times slower on the new server. Then I did 'explain' on both servers and found that on the new server, it wasn't using any indexes. I ran mysqlcheck --optimize and the query time improved (along with the Explain) but still 4 times slower than on the old server.

Apart from more recent OS and software versions, the server configs are roughly the same, apart from the newer server having more RAM, which has been allocated to MariaDB.

Here is the server.cnf file for the new server. I have tweaked it based on performance tuning recommendations, but nothing has helped speed up the queries apart from optimizing the entire database.

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

Here is the Explain query without optimizing the database first. The Select query takes 0.133 seconds:

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

Same explain after optimization. Select query now takes 0.048 seconds:

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

Here is the same query from the old server (Mariadb 10.3). The select query takes 0.013 seconds.

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

Finally, here are the create table statements for the three tables used above (same on both databases):

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

There is another recent question here regarding 10.3 vs 10.6 performance, so I'm wondering if something changed between these versions? Why do I need to optimize the tables to improve performance? Are there any config changes I can make to help? Note that I can't change queries or tables themselves since they're provided by Wordpress and related plugins.

Thanks in advance.

EDIT Here is output of show engine innodb status

Old

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

New

=====================================
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
============================
3
  • show innodb status might help you identify the bottleneck(s).
    – mustaccio
    Sep 21, 2022 at 14:33
  • @mustaccio - I added the output from both servers. Do you see any issues?
    – mattf10
    Sep 21, 2022 at 15:04
  • The only issue I see is that there's no useful data, because you didn't wait long enough to collect the statistics. You should run your normal workload for few hours or so, may be a day, and look at them then.
    – mustaccio
    Sep 21, 2022 at 15:51

1 Answer 1

0

It turned out to be a problem with the physical host of my VPS. After moving the VPS to a physical server that matched the old one, query speed has improved dramatically, and is now faster than on the original server, as expected.

EDIT Here's some more info on the physical host problem. I opened a ticket with my host and they asked me to run some diagnostics:

hdparm -Tt /dev/sda

and

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

Both commands were significantly slower than the old server. They also confirmed that the physical server was under a much greater load than the original, so they agreed to migrate the VPS to a server with a lower load.

3
  • do you know what was the nature of the physical host problem? How was it determined? I'm just looking at how to make this question/answer useful for the next person that comes across it.
    – danblack
    Sep 24, 2022 at 22:19
  • 1
    @danblack - I added some more info to the answer
    – mattf10
    Sep 26, 2022 at 7:39
  • @mattf10 You can accept your own answer: Accept Your Own Answers. Nov 1, 2023 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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