WordPress using MyISAM sooo much faster than InnoDB

5 posts / 0 new
Last post
#1 Thu, 09/19/2019 - 11:12
applejack

WordPress using MyISAM sooo much faster than InnoDB

Hi

I have been running some tests and found that using MyISAM vs InnoDB for the core WordPress tables is sooo much faster than InnoDB and I mean 40 times faster.

I used the WP Performance Tester which for testing WP performs 250 insert, select, update and delete operations through $wpdb

https://wordpress.org/plugins/wpperformancetester/

The server performance and MySQL benchmarks were pretty much the same but using InnoDB the WP benchmark for execution time was over 60 seconds and 15 queries per second whereas using MyISAM was 1.5 seconds and over 660 queries per second.

There is a noticeable difference in load time in the WP admin of pages etc etc.

From what I understand is that InnoDB is supposed to be faster so I am presuming there must be an issue with my my.cnf settings.

The server has 12 cores and 64GB RAM, MySQL 5.6 and most databases are not that large < 100MB and many much smaller.

innodb-buffer-pool-size          = 16G
innodb-file-format                   = Barracuda
innodb-file-per-table               = 1
innodb-flush-method              = O_DIRECT
innodb-log-file-size                 = 4G
innodb-log-buffer-size            = 16M
innodb-fast-shutdown    = 0
innodb-buffer-pool-instances = 16

Using mysqltuner I get the output below.

[OK] Maximum reached memory usage: 18.0G (28.66% of installed RAM)
[OK] Maximum possible memory usage: 28.4G (45.26% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (269/207M)
[OK] Highest usage of available connections: 5% (51/1000)
[OK] Aborted connections: 0.00%  (93/3709689)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (283 temp sorts / 29M sorts)
[!!] Joins performed without indexes: 138451
[!!] Temporary tables created on disk: 59% (11M on disk / 18M total)
[OK] Thread cache hit rate: 99% (51 created / 3M connections)
[!!] Table cache hit rate: 6% (32K open / 534K opened)
[OK] Open file limit used: 20% (13K/66K)
[OK] Table locks acquired immediately: 99% (199M immediate / 199M locks)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/505.9M
[OK] Read Key buffer hit rate: 99.9% (614M cached / 380K reads)
[!!] Write Key buffer hit rate: 40.8% (27M cached / 11M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 16.0G/3.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 4.0G * 2/16.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 16
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (10239411453 hits/ 10239567084 total)
[OK] InnoDB Write log efficiency: 97.68% (346342374 hits/ 354556297 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8213923 writes)

If anyone can shed any light on why InnoDB is so much slower in my case I would be most grateful.

Mon, 09/23/2019 - 09:05
OliverF

By any mean, InnoDB should be at least equally faster, the myisam's development is is now stagnant, so it's pretty weird, yeah.

I have no magical explanation to pull out, but I can however throw suggestions at least...
- besides mysqltuner, you should also try this nice one called tuning-primer, more or less doing the same, but I've found they're complementary
- tests should be run over a relatively long period of time to be significant, 24 hours of use. Maybe - maybe - you've had a random unrelated peak CPU just as you were testing InnoDB
- as always, watch out for bottlenecks, they may have "falsified" your test results. From personal experience, mysqltuner/tuning-primer recommendations aren't enough if something else limits how much your machine can pull on the resources, or how many resources are available. I'd recommend adding a utility such as Munin for instance, or any other, it whould show you if you're hitting on apache processes limits (when available = used), or slow queries, or spare RAM (yeah, I love Munin ^^). A iotop and mpstat -P ALL 1 also, just in case there are other bottlenecks.

Sorry, not a magical answer, but a suggestion to run longer tests and check if something isn't making a bottleneck, in short...

Mon, 09/23/2019 - 14:05
applejack

Thanks I made some changes to my.cnf and managed to get InnoDB running much much faster but still not quite as fast as MyISAM with the test that is being performed.

Tue, 10/22/2019 - 05:26
willwelker

The same to me. Using InnoDB the WP benchmark for execution time was over 60 seconds.

Tue, 10/22/2019 - 05:47 (Reply to #4)
applejack

What DB + version are you using and can you post your my.cnf file content as well as letting me know your server specs such as amount of RAM and number of cores.

Topic locked