ZFS 上 MariaDB 中 MyISAM 表的 Wordpress 查詢執行時間過長

我使用了一個非常常見的設置，一些 wordpress 站點在 Debian 10 (Buster) 上執行，mariadb 10.3.27 在 ZFS 上，在具有 SSD 和大量 RAM 的不錯的伺服器上，執行非常流暢，CPU 使用率低。wordpress 有這個查詢，

SELECT DISTINCT meta_key 
FROM wp_postmeta 
INNER JOIN  wp_posts ON post_id = ID 
WHERE post_type <> 'product' 
 AND post_type <> 'product_variation';

出於某種原因，我無法想像，在數據庫 A（InnoDB 表）上它執行大約 3 秒，而在數據庫 B（MyISAM 表）上它需要超過 2 分鐘。wp_postmeta在數據庫 B 中有 198938 條記錄和wp_posts25189 條記錄，在數據庫 A 中它們都有更多記錄。我試過轉儲，刪除數據庫並重新創建它無濟於事。如果我使用 EXT4 將數據庫移動到另一台類似的伺服器，則對 MyISAM 表的查詢將在大約 4 秒內完成。如果我將表更改為 InnoDB，查詢執行將在大約 4 秒內完成。此外，在 ZFS 上的 MyISAM 表上執行查詢時，我注意到磁碟讀取在查詢執行時飆升至約 50MB/s 和高達 80MB/s。在總大小小於 500MB 的數據庫中，以 50MB/s 的速度讀取 2 分鐘是 6GB 的數據。

MyISAM 和 InnoDB 之間的執行時間差異在 ZFS 0.6 和 0.7 的 Debian 系統上很明顯。FreeBSD/ZFS 伺服器上完全相同的數據庫沒有顯示出這種差異，MyISAM 和 InnoDB 表的查詢都在幾秒鐘內執行。

有誰知道在 Linux 上的 ZFS 上的 MyISAM 和 InnoDB 之間有什麼巨大的差異，以及為什麼 FreeBSD 上的 ZFS 沒有表現出這種行為？

創建的表如下：

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=MyISAM AUTO_INCREMENT=33318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

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=MyISAM AUTO_INCREMENT=279035 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

簡短的回答

• ZFS 等不是主要問題。 （有關 ZFS 的反駁，請參閱評論。）
• 使用 InnoDB，而不是 MyISAM。
• 修復 postmeta 中的索引，如下所述：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
• 避免索引前綴。（上面的連結也討論了這一點。）

（如果您必須使用 MyISAM，那麼推薦的索引是不夠的。如果您需要更多幫助，請告訴我。）

長答案

這個問題的幾個維度：

• 版本——MariaDB 10.3 與其他版本的 MySQL/MariaDB。這可能並不重要。
• 作業系統——Linux vs FreeBSD vs Windows——作業系統只是一個中間人；所有人都非常成熟，並且大部分都已經趕上了對方。
• HDD 與 SDD - SDD 驅動器本質上對於任何訪問都更快。此外，由於缺乏物理“搜尋” ，它們對於隨機訪問的速度更快。
• 文件系統——ZFS 與許多其他的。“日誌”等先進技術會對有效性能產生一定的影響。
• RAID，尤其是“電池支持的寫入記憶體”對讀取有一些影響，並且可能對寫入有很大影響。這適用於所有應用程序中的所有磁碟操作。
• InnoDB vs MyISAM——它們在鎖定、記憶體和數據組織方面有很大不同。索引差異很大。在 MyISAM 中，獲取數據塊使用 FS 的塊大小並將其記憶體在作業系統中。MyISAM 索引塊為 1KB，可能比 FS 塊幻燈片小，從而可能導致額外級別的記憶體。對於 InnoDB，所有塊都是 16KB。這些 MariaDB 塊大小和 FS 塊之間的相互作用很難預測。許多級別的性能取決於數據或索引是需要線性掃描還是隨機探測。
• –INDEXes上面，我專注於這個主題，因為這是可以進行主要改進的地方。在這種情況下（訪問 WP’s postmeta），將產生重大而深遠的性能影響。

指數的影響

• 我的建議包括擁有這個複合索引：(post_id, meta_key)它最適合您的慢查詢。此外…
• 當使用二級或主索引時，MyISAM 通過**隨機搜尋進入數據文件並獲取“塊”，因此性能取決於 FS 和 HDD/SSD。
• 使用 InnoDB，它PRIMARY KEY與數據“聚集”在一起，從而消除了額外的隨機獲取。我的一些建議利用了這種效率。
• 使用 InnoDB，二級索引需要通過 PK 進行第二次查找。
• 因此，一般來說，以某種集群方式佈置數據以提高性能是有益的。更改 PK開頭 post_id，在這方面有所幫助。此外，InnoDB 避免了 MyISAM 所需的隨機搜尋。
• “索引前綴”（例如，“meta_key(191)”）實際上是沒有用的。因此，部分性能增強涉及消除前綴的大約 5 種技術中的任何一種。我很確定 MariaDB 10.2.2 已經解決了這個問題；這將讓您(191)從任何索引中刪除它。第二個最好的方法是，如果您可以確保所有鍵始終小於 191 個字元，那麼更改 的定義meta_key就是一個簡單的答案。
• 以上所有因素加起來可能會顯著減少 6GB 的磁碟活動。抱歉，我不能具體說明 ZFS 是否有任何影響。

具體查詢

1. 掃描整個posts表。沒有辦法解決這個問題。這將是對“數據”的線性掃描。引擎完成這項任務的方式完全不同，但是這兩種引擎都沒有很強的優勢。
2. 對於每個文章行，進入postmeta。這是引擎和索引產生顯著差異的地方。
3. 刪除值列表meta_key——這可以在優化器的突發奇想的情況下在 RAM 或磁碟上完成。您可以更改設置以影響該決定，但它會產生潛在影響所有其他查詢性能的副作用，因此我不會討論它。

引用自：https://dba.stackexchange.com/questions/285404