2

I've migrated a website from a server running MySQL 5. to MariaDB 10.3, I was expecting if anything for the MariaDB to outperform MySQL and on lots of other sites on the platform it has. However, this particular query it really doesn't seem to be liking and hangs infinitely. There's no other activity on the database, locks etc. This is the only query being run against it at this time and MariaDB is idling.

Old server specs

  • 6 Core VM - Intel Xeon E5-2630 @ 2.2GHz
  • 14 GB DDR4 RAM
  • MySQL 5.6.44

New server specs

  • 12 Core VM - Intel Xeon Gold 5118 @ 2.3GHz
  • 16GB DDR RAM
  • MariaDB 10.3.16

Old server InnoDB config

Removed as post was too long..

New server InnoDB config

MariaDB [(none)]> SHOW VARIABLES LIKE 'inno%';
--------------
SHOW VARIABLES LIKE 'inno%'
--------------

+---------------------------------------------+------------------------+
| Variable_name                               | Value                  |
+---------------------------------------------+------------------------+
| innodb_adaptive_flushing                    | ON                     |
| innodb_adaptive_flushing_lwm                | 10.000000              |
| innodb_adaptive_hash_index                  | ON                     |
| innodb_adaptive_hash_index_parts            | 8                      |
| innodb_adaptive_max_sleep_delay             | 150000                 |
| innodb_autoextend_increment                 | 64                     |
| innodb_autoinc_lock_mode                    | 1                      |
| innodb_background_scrub_data_check_interval | 3600                   |
| innodb_background_scrub_data_compressed     | OFF                    |
| innodb_background_scrub_data_interval       | 604800                 |
| innodb_background_scrub_data_uncompressed   | OFF                    |
| innodb_buf_dump_status_frequency            | 0                      |
| innodb_buffer_pool_chunk_size               | 134217728              |
| innodb_buffer_pool_dump_at_shutdown         | ON                     |
| innodb_buffer_pool_dump_now                 | OFF                    |
| innodb_buffer_pool_dump_pct                 | 25                     |
| innodb_buffer_pool_filename                 | ib_buffer_pool         |
| innodb_buffer_pool_instances                | 1                      |
| innodb_buffer_pool_load_abort               | OFF                    |
| innodb_buffer_pool_load_at_startup          | ON                     |
| innodb_buffer_pool_load_now                 | OFF                    |
| innodb_buffer_pool_size                     | 2952790016             |
| innodb_change_buffer_max_size               | 25                     |
| innodb_change_buffering                     | all                    |
| innodb_checksum_algorithm                   | crc32                  |
| innodb_checksums                            | ON                     |
| innodb_cmp_per_index_enabled                | OFF                    |
| innodb_commit_concurrency                   | 0                      |
| innodb_compression_algorithm                | zlib                   |
| innodb_compression_default                  | OFF                    |
| innodb_compression_failure_threshold_pct    | 5                      |
| innodb_compression_level                    | 6                      |
| innodb_compression_pad_pct_max              | 50                     |
| innodb_concurrency_tickets                  | 5000                   |
| innodb_data_file_path                       | ibdata1:12M:autoextend |
| innodb_data_home_dir                        |                        |
| innodb_deadlock_detect                      | ON                     |
| innodb_default_encryption_key_id            | 1                      |
| innodb_default_row_format                   | dynamic                |
| innodb_defragment                           | OFF                    |
| innodb_defragment_fill_factor               | 0.900000               |
| innodb_defragment_fill_factor_n_recs        | 20                     |
| innodb_defragment_frequency                 | 40                     |
| innodb_defragment_n_pages                   | 7                      |
| innodb_defragment_stats_accuracy            | 0                      |
| innodb_disable_sort_file_cache              | OFF                    |
| innodb_disallow_writes                      | OFF                    |
| innodb_doublewrite                          | ON                     |
| innodb_encrypt_log                          | OFF                    |
| innodb_encrypt_tables                       | OFF                    |
| innodb_encryption_rotate_key_age            | 1                      |
| innodb_encryption_rotation_iops             | 100                    |
| innodb_encryption_threads                   | 0                      |
| innodb_fast_shutdown                        | 1                      |
| innodb_fatal_semaphore_wait_threshold       | 600                    |
| innodb_file_format                          |                        |
| innodb_file_per_table                       | ON                     |
| innodb_fill_factor                          | 100                    |
| innodb_flush_log_at_timeout                 | 1                      |
| innodb_flush_log_at_trx_commit              | 1                      |
| innodb_flush_method                         | fsync                  |
| innodb_flush_neighbors                      | 1                      |
| innodb_flush_sync                           | ON                     |
| innodb_flushing_avg_loops                   | 30                     |
| innodb_force_load_corrupted                 | OFF                    |
| innodb_force_primary_key                    | OFF                    |
| innodb_force_recovery                       | 0                      |
| innodb_ft_aux_table                         |                        |
| innodb_ft_cache_size                        | 8000000                |
| innodb_ft_enable_diag_print                 | OFF                    |
| innodb_ft_enable_stopword                   | ON                     |
| innodb_ft_max_token_size                    | 84                     |
| innodb_ft_min_token_size                    | 3                      |
| innodb_ft_num_word_optimize                 | 2000                   |
| innodb_ft_result_cache_limit                | 2000000000             |
| innodb_ft_server_stopword_table             |                        |
| innodb_ft_sort_pll_degree                   | 2                      |
| innodb_ft_total_cache_size                  | 640000000              |
| innodb_ft_user_stopword_table               |                        |
| innodb_idle_flush_pct                       | 100                    |
| innodb_immediate_scrub_data_uncompressed    | OFF                    |
| innodb_io_capacity                          | 200                    |
| innodb_io_capacity_max                      | 2000                   |
| innodb_large_prefix                         |                        |
| innodb_lock_schedule_algorithm              | fcfs                   |
| innodb_lock_wait_timeout                    | 50                     |
| innodb_locks_unsafe_for_binlog              | OFF                    |
| innodb_log_buffer_size                      | 16777216               |
| innodb_log_checksums                        | ON                     |
| innodb_log_compressed_pages                 | ON                     |
| innodb_log_file_size                        | 50331648               |
| innodb_log_files_in_group                   | 2                      |
| innodb_log_group_home_dir                   | ./                     |
| innodb_log_optimize_ddl                     | ON                     |
| innodb_log_write_ahead_size                 | 8192                   |
| innodb_lru_scan_depth                       | 1024                   |
| innodb_max_dirty_pages_pct                  | 75.000000              |
| innodb_max_dirty_pages_pct_lwm              | 0.000000               |
| innodb_max_purge_lag                        | 0                      |
| innodb_max_purge_lag_delay                  | 0                      |
| innodb_max_undo_log_size                    | 10485760               |
| innodb_monitor_disable                      |                        |
| innodb_monitor_enable                       |                        |
| innodb_monitor_reset                        |                        |
| innodb_monitor_reset_all                    |                        |
| innodb_old_blocks_pct                       | 37                     |
| innodb_old_blocks_time                      | 1000                   |
| innodb_online_alter_log_max_size            | 134217728              |
| innodb_open_files                           | 2000                   |
| innodb_optimize_fulltext_only               | OFF                    |
| innodb_page_cleaners                        | 1                      |
| innodb_page_size                            | 16384                  |
| innodb_prefix_index_cluster_optimization    | OFF                    |
| innodb_print_all_deadlocks                  | OFF                    |
| innodb_purge_batch_size                     | 300                    |
| innodb_purge_rseg_truncate_frequency        | 128                    |
| innodb_purge_threads                        | 4                      |
| innodb_random_read_ahead                    | OFF                    |
| innodb_read_ahead_threshold                 | 56                     |
| innodb_read_io_threads                      | 4                      |
| innodb_read_only                            | OFF                    |
| innodb_replication_delay                    | 0                      |
| innodb_rollback_on_timeout                  | OFF                    |
| innodb_rollback_segments                    | 128                    |
| innodb_scrub_log                            | OFF                    |
| innodb_scrub_log_speed                      | 256                    |
| innodb_sort_buffer_size                     | 1048576                |
| innodb_spin_wait_delay                      | 4                      |
| innodb_stats_auto_recalc                    | ON                     |
| innodb_stats_include_delete_marked          | OFF                    |
| innodb_stats_method                         | nulls_equal            |
| innodb_stats_modified_counter               | 0                      |
| innodb_stats_on_metadata                    | OFF                    |
| innodb_stats_persistent                     | ON                     |
| innodb_stats_persistent_sample_pages        | 20                     |
| innodb_stats_sample_pages                   | 8                      |
| innodb_stats_traditional                    | ON                     |
| innodb_stats_transient_sample_pages         | 8                      |
| innodb_status_output                        | OFF                    |
| innodb_status_output_locks                  | OFF                    |
| innodb_strict_mode                          | ON                     |
| innodb_sync_array_size                      | 1                      |
| innodb_sync_spin_loops                      | 30                     |
| innodb_table_locks                          | ON                     |
| innodb_temp_data_file_path                  | ibtmp1:12M:autoextend  |
| innodb_thread_concurrency                   | 0                      |
| innodb_thread_sleep_delay                   | 10000                  |
| innodb_tmpdir                               |                        |
| innodb_undo_directory                       | ./                     |
| innodb_undo_log_truncate                    | OFF                    |
| innodb_undo_logs                            | 128                    |
| innodb_undo_tablespaces                     | 0                      |
| innodb_use_atomic_writes                    | ON                     |
| innodb_use_native_aio                       | ON                     |
| innodb_version                              | 10.3.16                |
+---------------------------------------------+------------------------+
155 rows in set (0.002 sec)

Table definitions

CREATE TABLE `wp_network_postmeta` (
  `blog_id` bigint(20) unsigned NOT NULL,
  `meta_id` bigint(20) unsigned NOT NULL,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`blog_id`,`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `wp_network_posts` (
  `BLOG_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ID` bigint(20) unsigned NOT NULL,
  `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 NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text 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 NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`BLOG_ID`,`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The query (disabled caching for testing)

SELECT SQL_NO_CACHE p.* FROM wp_network_posts p

INNER JOIN wp_network_postmeta AS pm1 ON (pm1.post_id = p.ID AND pm1.blog_id = p.BLOG_ID)
INNER JOIN wp_network_postmeta AS pm2 ON (pm2.post_id = p.ID AND pm2.blog_id = p.BLOG_ID)

WHERE p.post_type = 'post'
AND p.post_status = 'publish'

AND pm1.meta_key = 'carousel_image' AND pm1.meta_value != ''
AND pm2.meta_key = 'hide_on_hpr' AND pm2.meta_value = 0

ORDER BY p.post_date DESC
LIMIT 5

Old server explain

+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+
| id | select_type | table | type   | possible_keys            | key      | key_len | ref                                                 | rows | Extra                                                               |
+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | pm1   | ref    | PRIMARY,post_id,meta_key | meta_key | 768     | const                                               | 2252 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 16      | redacted.pm1.blog_id,redacted.pm1.post_id           |    1 | Using where                                                         |
|  1 | SIMPLE      | pm2   | ref    | PRIMARY,post_id,meta_key | post_id  | 16      | redacted.pm1.post_id,redacted.pm1.blog_id           |   13 | Using where                                                         |
+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+

New server explain

+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys            | key      | key_len | ref                                                 | rows | Extra                                                               |
+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | pm1   | ref    | PRIMARY,post_id,meta_key | meta_key | 768     | const                                               | 2250 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 16      | redacted.pm1.blog_id,redacted.pm1.post_id           |    1 | Using where                                                         |
|    1 | SIMPLE      | pm2   | ref    | PRIMARY,post_id,meta_key | PRIMARY  | 8       | redacted.pm1.blog_id                                |  189 | Using where                                                         |
+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+--------------------------------------------------------------------

Benchmarks

  • Old server: 212ms
  • New server: runs indefinitely and cancelled after 30 seconds of running

Removing the ORDER BY CLAUSE

  • Old server: 7.4ms
  • New server: 55.1ms

So although the old server seems to be much quicker which is odd, strangely the ORDER BY clause is what causes it to not work. The only thing I can see is when I look at the process log, the new server seems to be stuck in the SENDING DATA phase.

Old server explain without ORDER BY

+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+
| id | select_type | table | type   | possible_keys            | key      | key_len | ref                                                 | rows | Extra                              |
+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+
|  1 | SIMPLE      | pm1   | ref    | PRIMARY,post_id,meta_key | meta_key | 768     | const                                               | 2252 | Using index condition; Using where |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 16      | redacted.pm1.blog_id,redactred.pm1.post_id          |    1 | Using where                        |
|  1 | SIMPLE      | pm2   | ref    | PRIMARY,post_id,meta_key | post_id  | 16      | redacted.pm1.post_id,redacted.pm1.blog_id           |   13 | Using where                        |
+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+

New server explain without ORDER BY

+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+
| id   | select_type | table | type   | possible_keys            | key      | key_len | ref                                                 | rows | Extra                              |
+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+
|    1 | SIMPLE      | pm1   | ref    | PRIMARY,post_id,meta_key | meta_key | 768     | const                                               | 2250 | Using index condition; Using where |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 16      | redacted.pm1.blog_id,redacted.pm1.post_id           |    1 | Using where                        |
|    1 | SIMPLE      | pm2   | ref    | PRIMARY,post_id,meta_key | PRIMARY  | 8       | redacted.pm1.blog_id                                |  189 | Using where                        |
+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+

Any ideas why this would be happening? Help would be greatly appreciated!

Edit: After testing, if I remove the pm2 JOIN the query runs quickly WITH the order by. I notice above that the explain for pm2 doesn't match the old server...

Chris.

  • It would be worth adding the EXPLAIN output for the runs without the ordering clause, the indexes in use may be different. Also, detail if there is other activity while you are running these tests (as if so, there may be something else long-running holding a lock that the variant with the ordering clause encounters due to different index use), i.e. is the migrated copy already live and in use by others? As an aside: "30 seconds", while several orders longer than 55ms, is hardly indefinite! Add extra detail by editing the question: detail in comment responses can get hidden more easily. – David Spillett Jul 26 at 9:35
  • @DavidSpillett Hi David - when I say indefinite it's just because I've cancelled the query after 30 seconds. I've let it run for a minute or more and still nothing. I'll add up the explains without order clause now. Thanks! – Chris Jul 26 at 9:38
  • @DavidSpillett I've updated the post with new explains. Chris. – Chris Jul 26 at 9:47
  • Is meta_id an AUTO_INCREMENT? – Rick James Jul 26 at 19:11
  • @RickJames - no meta_id is simply indexed because the ID corresponds with another table. – Chris Jul 27 at 14:18
2

WordPress has an inherent performance problem due to using the EAV schema pattern. Then it fails to adequately index its meta tables. I discuss that here , but since you have an extra column, I will elaborate:

Change the indexes to

    PRIMARY KEY(blog_id, post_id, meta_key, meta_id),  -- to allow dup meta_key for a post
    INDEX(meta_id),    -- to keep AUTO_INCREMENT happy
    INDEX(meta_key)

This should make your queries run faster on both servers. I would expect the 10.3 to run as fast as 5.6.

The general reason for the performance difference is that MariaDB and MySQL forked at about 5.5. Each added new optimizations. You hit an optimization that was done better in 5.6 than in 10.3.

  • Thanks Rick for the feedback - I'll have a look at getting this implemented. I agree WordPress's database pattern leaves a lot to be desired - hence why we don't develop on WordPress for our more recent projects. Unfortunately we have to maintain these WP sites! – Chris Jul 27 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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