Mariadb
MariaDB 數據庫遷移 - 在 MySQL 正常工作時無限掛起
我已經將一個網站從執行 MySQL 5. 的伺服器遷移到 MariaDB 10.3，我期待 MariaDB 是否有任何表現優於 MySQL 以及它擁有的平台上的許多其他站點。但是，這個特定的查詢它似乎真的不喜歡並且無限掛起。數據庫、鎖等上沒有其他活動。這是目前對其執行的唯一查詢，MariaDB 處於空閒狀態。
舊伺服器規格
- 6 核 VM - Intel Xeon E5-2630 @ 2.2GHz
- 14 GB DDR4 記憶體
- MySQL 5.6.44
新的伺服器規格
- 12 核 VM - Intel Xeon Gold 5118 @ 2.3GHz
- 16GB DDR 記憶體
- MariaDB 10.3.16
舊伺服器 InnoDB 配置
Removed as post was too long..新伺服器 InnoDB 配置
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)表定義
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查詢（禁用記憶體以進行測試）
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老伺服器解釋
+----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+ | 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 | +----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+新伺服器說明
+------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+---------------------------------------------------------------------+ | 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 | +------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+--------------------------------------------------------------------基準
- 老伺服器：212ms
- 新伺服器：無限期執行並在執行 30 秒後取消
刪除
ORDER BY CLAUSE
- 舊伺服器：7.4ms
- 新伺服器：55.1ms
因此，儘管舊伺服器似乎更快，這很奇怪，但奇怪的是，該
ORDER BY子句是導致它無法工作的原因。我唯一能看到的是，當我查看程序日誌時，新伺服器似乎卡在了SENDING DATA階段。老伺服器說明無
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 | +----+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+新伺服器說明無
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 | +------+-------------+-------+--------+--------------------------+----------+---------+-----------------------------------------------------+------+------------------------------------+任何想法為什麼會發生這種情況？幫助將不勝感激！
編輯：經過測試，如果我刪除了 pm2 JOIN，查詢會在 order by 下快速執行。我在上面注意到 pm2 的解釋與舊伺服器不匹配……
克里斯。
由於使用 EAV 模式模式，WordPress 存在固有的性能問題。然後它無法充分索引其元表。我在這裡討論，但由於您有一個額外的專欄，我將詳細說明：
將索引更改為
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)這應該使您的查詢在兩台伺服器上執行得更快。我希望 10.3 的執行速度與 5.6 一樣快。
性能差異的一般原因是 MariaDB 和 MySQL 在 5.5 左右分叉。每個都添加了新的優化。您實現了在 5.6 中比在 10.3 中做得更好的優化。