InnoDB Table Optimization for WordPress is followup for our previous guide to use InnoDB Engine. Most plugins will not support optimization. The previous article was – MyISAM vs Innodb MySQL Engine For WordPress (Rackspace Cloud). So, we practically suggested to use InnoDB. One of our regular reader since 2011 ( Pritam from Bengal ) asked on Twitter, how to manage the optimization part for InnoDB. Quite normal question, because most WordPress Plugins will not work on InnoDB Table. Again, we are not database Guru. This is a working experience. InnoDB on rightly configured MySQL Server, never require optimization for only a WordPress blog. If you simply run service mysql restart, you will notice, the WordPress is running like a new, fresh installation. Running service mysql restart is not a standard method! It is a credit to keep the database server up and running steadily. There is no optimization for InnoDB, it is defragmentation :
1 | http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html |
Obviously on Rackspace with a separate database server with InnoDB Engine, you will never use Disk Cache feature of W3 Total Cache Plugin.
InnoDB Table Optimization for WordPress
If you have optimized my.cnf using Major Hayden’s script or Major Hayden or someone like him has optimized for you on Rackspace ( Read this article Tweak MySQL my.cnf using Major Hayden™s MySQL Tuner Script if you are not understanding what we are talking about ).
---
There is a nice readymade script ( read this article first, then go there… hummm ) :
1 | https://github.com/AbhishekGhosh/mysqlfragfinder |
Minimum, you should know up to this level – How to Export WordPress MySQL Database on Rackspace Deployment. If you are not comfortable to use MySQL in this way, do not experiment on production server / Live Website.
InnoDB Table Optimization for WordPress is Kind of Wrong Post Title!
1 | optimize table; |
You will see, it is giving you return that it is not supported, because you might have worked with innodb_file_per_table.
Or config :
1 2 3 4 5 | [mysqld] innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G |
OPTIMIZE probably calls ALTER TABLE command when executing against InnoDB tables. Probably, because except Database Administrators, so much minute theory is not possible to tell. Database Administrator is not needed for only WordPress unless one has very difficult problem. Running OPTIMIZE TABLE against an InnoDB table does two things.
So – InnoDB tables do not support optimization. You can optimize MySQL tables only.
If we frequently deleted rows (or update rows with variable-length data types), we could end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation. Actually with WordPress we do not perform such very complicated work.
If we are not using the innodb_file_per_table option, the only thing we can do about it is export and import the database, a time-and-disk-intensive procedure. But if we are using innodb_file_per_table, we can identify and reclaim this space.
But, unlike other engines, we actually need to shutdown the incoming and outgoing calls to the database. That simply means we can not directly optimize InnoDB. We can not because we usually do not need at all. If running service mysql restart hugely fast, then you need to fine tune.
