1

I use a pretty common setup, some wordpress sites running on Debian 10 (Buster) with mariadb 10.3.27 on ZFS, on a decent server with SSDs and lots of RAM, pretty smooth operation with low CPU utilization. There is this query wordpress makes,

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

For some reason I cannot possibly imagine, on database A (InnoDB tables) it runs in about 3 seconds, and on database B (MyISAM tables) it takes over 2 minutes. wp_postmeta has 198938 records and wp_posts 25189 in database B, where on database A they both have many more records. I've tried dumping, deleting the database and recreating it to no avail. If I move the database to another similar server with EXT4, the query on MyISAM tables finishes in about 4 seconds. If I alter the tables to InnoDB, query execution finishes in about 4 seconds. Also, when running the query on MyISAM tables on ZFS I noticed the disk reads spike to about 50MB/s and up to 80MB/s while the query is running. 2 minutes of reads at 50MB/s is 6GB of data, in a database with a total size less than 500MB.

This difference in execution time between MyISAM and InnoDB was noticed on Debian systems with ZFS 0.6 and 0.7. The exact same databases on a FreeBSD/ZFS server display no such difference, with queries executed within a a few seconds for both MyISAM and InnoDB tables.

Does anybody know what could make such a huge difference between MyISAM and InnoDB on ZFS on Linux, and why ZFS on FreeBSD does not show this behavior?

The tables created as:

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
6
  • Edited to replace describe + indexes with SHOW CREATE TABLE as pointed.
    – gmelis
    Feb 15, 2021 at 7:58
  • I think these two links offer a very good explanation for the discrepancies in execution time I I noticed above: patpro.net/blog/index.php/2014/03/09/… and patpro.net/blog/index.php/2014/03/19/… . The problem is how MyISAM reads from the disk and how primarycache works in ZFS. Setting primarycache=all solves the problem. BTW, for an unknown reason on the Linux systems it was -by default I guess- primarycache=metadata.
    – gmelis
    Feb 16, 2021 at 19:40
  • 1
    While I understand some of what those links are saying, I dispute some of the MyISAM claims. (However, we should be moving away from MyISAM to InnoDB.) In the case of InnoDB, a setting like O_DIRECT is generally appropriate. I do question whether "mirror" obviates the need for InnoDB's "doublebuffer"; instead, when the FS can guarantee atomic writes of 16KB, "torn pages" are avoided.
    – Rick James
    Feb 17, 2021 at 18:55
  • Disabling doublebuffer on a mirrored device does seem a bit precarious on his part, since it protects from database crashes, too. Although MyISAM has been long deprecated, I was stumped to see a good proportion of the recently created databases using MyISAM. Web developers never stop to amaze me. If MyISAM is using an 8k block size and relies on the file system's caching, the relation in execution time, amount of data read and ZFS's primarycache setting seems logical and actually explain a lot. For good measure, I made a note to periodically check and alter my hosted databases to InnoDB.
    – gmelis
    Feb 17, 2021 at 21:32
  • When a new version changed to say "default_engine = InnoDB", the number of MyISAM tables in this and other forums tropped precipitously. OTOH, 3rd party software (Wordpress?) my be using old schema patterns. And, of course, dump+reload is likely to preserve the old engine. Now, there is an option to prevent overriding the default.
    – Rick James
    Feb 17, 2021 at 21:55

1 Answer 1

3

Short answer

(If you must use MyISAM, then the indexes recommended there are not adequate. Let me know if you need more help.)

Long Answer

Several dimensions to this question:

  • Version -- MariaDB 10.3 versus other versions of MySQL/MariaDB. This is probably not significant.

  • OS -- Linux vs FreeBSD vs Windows -- The OS is just a middleman; all are quite mature and have mostly caught up with each other.

  • HDD vs SDD -- SDD drives are inherently faster for any access. Plus they are faster for random accesses due to the lack of a physical "seek".

  • FileSystem -- ZFS versus many others. "Journaling" and other advanced techniques will have some impact on the effective performance.

  • RAID, and especially "battery-backed write cache" has some impact on reads and possibly a big impact on writes. This applies across all disk operations in all applications.

  • InnoDB vs MyISAM -- These differ significantly in locking, caching, the organization of data. Indexes differ a lot. In MyISAM, fetching a block of data uses the FS's blocksize and caches it in the OS. A MyISAM index block is 1KB, which is likely to be smaller than the FS block slide, thereby leading perhaps to an extra level of caching. For InnoDB, all blocks are 16KB. The interactions between these MariaDB block size and the FS block is hard to predict. Peformance at many levels depends on whether the data or index needs to be scanned linearly or probed randomly.

  • The INDEXes -- Above, I focused on this topic because it is where the main improvement can be had. In the situation here (accessing WP's postmeta), will have a significant and far-reaching performance impact.

Impact of INDEX

  • My recommendations include having this composite index: (post_id, meta_key) which is optimal for your slow query. Furthermore...
  • When using a secondary or primary index, MyISAM reaches into the data file with a random seek and fetches a "block", hence performance is dependent on FS and HDD/SSD.
  • With InnoDB, the PRIMARY KEY is "clustered" with the data, thereby eliminating the extra random fetch. Some of my recommendations take advantage of this efficiency.
  • With InnoDB, a secondary index requires a second lookup via the PK.
  • So, in general, it is beneficial to lay out the data in some clustered manner to improve performance. Changing the PK to start with post_id, helps in this respect. Furthermore, InnoDB avoids the random seek that MyISAM requires.
  • "Index prefixing" (eg, "meta_key(191)") is virtually useless. So, part of the performance enhance involves any of about 5 techniques to eliminate prefixing. I'm pretty sure MariaDB 10.2.2 has already fixed the issue; this would let you remove the (191) from any indexes having it. Second best is if you can assure that all keys will always be under 191 characters, then changing the definition of meta_key is a simple answer.
  • All of the above add up to decreasing the 6GB of disk activity, probably significantly. Sorry, but I cannot say specifically if ZFS has any impact.

The specific Query

  1. Scan the entire posts table. There is no getting around this. This will be a linear scan through the "data". The engines do this task quite differently, but neither engine has a strong advantage here.
  2. For each post row, reach into postmeta. This is where the engine and indexes make a significant difference.
  3. Dedup the list of meta_key values -- This may be done in RAM or on disk, at the whim of the Optimizer. You could change a setting to influence that decision, but it would have the side effect of potentially impacting the performance of all other queries, so I won't discuss it.
8
  • The MariaDB version is 10.3.27, it says so in the question. The tables are created by wordpress, no intervention and no tuning by me. I 've changed them to InnoDB and they work as expected. My question is about their performance on ZFS and why they differ so much. Why do MyISAM tables perform so bad on ZFS on Linux and not on FreeBSD? Why read 6GB of data to perform this query on Linux and not on FreeBSD?
    – gmelis
    Feb 15, 2021 at 8:19
  • @gmelis - I added a lot to my answer.
    – Rick James
    Feb 15, 2021 at 19:15
  • Thank you for the detailed description @Rick James. To test the case of dedup I run the query without DISTINCT and tt cut ~10s from the execution time. Adding an INDEX (post_id, meta_key) worked wonders; it dropped the execution time to about 2s for MyISAM -and 300ms for InnoDB. So, for this query, point 2 seems to hit the nail. But still, what, if any, interference has ZFS into this? Why the huge difference between MyISAM and InnoDB on ZFS/ Linux, but not on ZFS/FreeBSD? This is what's eating me. I'll wait a week or so to see if anybody answers that before accepting this answer.
    – gmelis
    Feb 15, 2021 at 21:01
  • @gmelis - I am still confused on which dimension is causing the timing diff -- MyISAM vs InnoDB on the same machine? Or Linux vs FreeBSD with the same engine. Or are you changing two things? Can you provide a list of the timings for the 8 combinations of engine, FS, OS.
    – Rick James
    Feb 15, 2021 at 22:44
  • I'm running mariadb on ZFS on Linux and FreeBSD. For this query the InnoDB tables perform similarly on either OS. The MyISAM tables perform miserably on the 2 Linux servers I tried, and nicely on the FreeBSD. That is the mystery I'm trying to solve.
    – gmelis
    Feb 16, 2021 at 9:06

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.