We talked about InnoDB via several articles such as InnoDB vs Percona XtraDB, InnoDB Optimization etc. A practical question is, which is better MySQL engine for WordPress – MyISAM or InnoDB? In the year 2014, we have written about MyISAM – “MyISAM is ACID compliant. ACID stands for Atomicity, Consistency, Isolation, Durability. Most Plugins works great for Innodb MySQL Engine except Yet Another Related Posts Plugin (YARPP). This is a logically right plugin which can make a website slowest possible. With Innodb MySQL Engine it runs somehow now, but there is no compromise in speed. Do not convert InnoDB to MyISAM, this is possibly a worst idea!”
5 years passed since then. WordPress does not decide what databases it will take. Your server defaults determine the database table format. InnoDB is capable of taking advantage of multiple cores, but MyISAM can only use one core. This just means that a 2-core or 4-core server (or more cores) can use InnoDB better. Remember, InnoDB likes RAM. InnoDB can recover better from a crash and is more stable in general. More stable is said, but improperly set up InnoDB can lead to more frequent crashes leading to WordPress Database Connection Error. InnoDB has referential integrity which involves supporting foreign keys (RDBMS) and relationship constraints. InnoDB supports transactions, which means you can rollback. Innodb is the winner in performance for the average user. But remember, MyISAM is easier to backup raw while running for server snapshots, where InnoDB data may get corrupted in backups performed in that way – you need manual checking.
Switching the format of database tables to InnoDB will decrease overall CPU load on the server, the site may become faster. Although there are some risks of crashes, it is generally accepted that InnoDB is the better engine for WordPress databases.
---
It is quite easy to check whether you have “mixture” of MyISAM and InnoDB tables by running this query:
|
1 2 3 4 |
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your-database-name' and ENGINE = 'myISAM' |
You can change a single table to InnoDB with this command :
|
1 |
ALTER TABLE wp_table-name ENGINE=InnoDB; |
A major factor in database performance is the storage engine used by the database. More specifically, its tables. Different storage engines provide better performance in a different situation over another. Today, the default MySQL storage engine has been InnoDB. I believe that many “forced” InnoDB users of today not even have the basic settings optimized, such as innodb_force_recovery option can force the InnoDB storage engine to start up after a crash :
|
1 2 3 4 5 6 7 8 9 |
cat /etc/my.cnf [mysqld] log-error=/var/lib/mysql/mysql.err innodb_file_per_table=1 default-storage-engine=innodb innodb_buffer_pool_size=128M innodb_force_recovery = 1 |
In short, InnoDB going to prevent corruption of the database of a WordPress installation with a few thousand posts. That point makes it powerful. InnoDB is not closest to ideal and maybe the reason behind a few undiagnosed issues of your WordPress installation like I pointed one above.
Tagged With which is faster innodb or myisam , speed up wordpress mysql , innodb vs myisam , MyISAM wordpress , mysql myisam vs innodb