MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!

Photo of author
Written By Jan Reilink

Windows Server systems administrator & enthusiast.

Are you running into MySQL load problems? Learn how to tune MySQL servers for a heavy InnoDB workload, by configuring innodb_buffer_pool_instances. Dividing the InnoDB buffer pool into multiple instances improves Disk I/O. By doing so, you run your database and website more efficiently and faster. Here is a little help for you.

Tune MySQL InnoDB buffer pool instances & size for heavy workloads

All for more InnoDB Disk I/O performance on MySQL 5.5+.

Tuning MySQL servers is an ever ongoing process. Every new MySQL version brings new configuration settings you can use to improve its performance. As a MySQL DBA you want your database server and databases to perform better than well, don’t you?

MariaDB/MySQL 5.5.4 introduces new configuration settings for the InnoDB storage engine. This can greatly improve MySQL’s InnoDB performance, both in read and write operations.

One of those settings is innodb_buffer_pool_instances. The innodb_buffer_pool_instances divides the InnoDB buffer pool into separate instances. Dividing your buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option.

You might also want to adjust the innodb_buffer_pool_size value:

The larger the InnoDB buffer pool, the more InnoDB acts like an in-memory database. It reads data from disk once and then accesses the data from memory during subsequent reads. Buffer pool size is configured using the innodb_buffer_pool_size configuration option.

Back to increasing innodb_buffer_pool_instances.

The innodb_buffer_pool_instances divides the InnoDB buffer pool in a number of regions.

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

In MySQL versions prior to 5.5.4 this was not configurable and thus set to just one instance. Now you can increase innodb_buffer_pool_size, and you can divide the InnoDB buffer pool into multiple regions by setting innodb_buffer_pool_instances to 2, 3, 4 or 8. As long as innodb_buffer_pool_size is set high enough, and you have enough memory available in your MySQL database server. This increases InnoDB read/write threads.

To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum).

For example, you can set innodb_buffer_pool_size to 6 GB and innodb_buffer_pool_instances to 4 in your my.cnf MySQL configuration file:

; InnoDB buffer pool size in bytes. The primary value to adjust on a database server, ; can be set up to 80% of the total memory in these environments innodb_buffer_pool_size = 6000M ; If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances ; divides the InnoDB buffer pool into this many instances. innodb_buffer_pool_instances = 4
Code language: plaintext (plaintext)

In this example, I’ve used an innodb_buffer_pool_size of 6000M (6 GB), so there is 1500M available per innodb_buffer_pool_instance, which is more than the minimum 1 GB. As a rule of thumb, set your innodb_buffer_pool_size to approximately 70 – 80% of the RAM available.

Innodb_buffer_pool_instances defaults

Various MySQL versions have different innodb_buffer_pool_instances default values, here is an overview – listing – for you:

MySQL version# InnoDB buffer pool instancesNotes
MySQL 5.5 (<= 5.5.4)1not configurable
MySQL 5.51
MySQL 5.6 (<= 5.6.5)1
MySQL 5.6 (>= 5.6.6)8or 1 if innodb_buffer_pool_size < 1GB
MySQL 5.78or 1 if innodb_buffer_pool_size < 1GB
MariaDB 10 (<= MariaDB 10.0.3)1
MariaDB 10 (>= MariaDB 10.0.4)8

The functionality described below was disabled in MariaDB 10.5, and removed in MariaDB 10.6, as the original reasons for for splitting the buffer pool have mostly gone away.

InnoDB read and write I/O threads in MySQL

Besides innodb_buffer_pool_instances, you can also increase the number of InnoDB read I/O threads and write I/O threads. These are configured with innodb_write_io_threads and innodb_read_io_threads.

Both settings default to 4 threads. We can increase these to, for example, 8:

; Number of I/O threads for writes innodb_write_io_threads = 8 ; Number of I/O threads for reads innodb_read_io_threads = 8
Code language: plaintext (plaintext)

innodb_read_io_threads

The number of I/O threads for read operations in InnoDB. The default value is 4.

innodb_write_io_threads

The number of I/O threads for write operations in InnoDB. The default value is 4.

When should you increase the number of innodb_read_io_threads? If you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS, you might gain by increasing the value of innodb_read_io_threads.

InnoDB innodb_log_file_size

It is important to set a proper InnoDB log file size in your my.cnf. You need to make the logs (there are two) are big enough to hold at most an hour of so of logs. That is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process.

You can also check the recommended innodb_log_file_size with the following command:

mysql -e 'show engine innodb status\G' | awk ' BEGIN { } /Log sequence number/ {st=$4 } /Last checkpoint at/ { ed=$4; print (st-ed)/1024/1024 } '
Code language: Bash (bash)

If the Checkpoint Age is getting close to Async point, I would consider to increase innodb_log_file_size, say by 20%.

You can set the InnoDB log file size using innodb_log_file_size in your my.cnf configuration file. You need to do some calculations and monitoring before you can set its value. After setting or changing innodb_log_file_size you have to restart mysqld, and don’t forget to delete its current log files ib_logfile0 and ib_logfile1.

Setting a good innodb_log_file_size value will also resolve the MySQL error:

InnoDB: ERROR: the age of the last checkpoint is ‘n’, which exceeds the log group capacity ‘o’.

Update 1 Jun 2016: Percona’s Vadim Tkachenko posted an interesting article called “What is a big innodb_log_file_size?“. The article dives pretty deep into the inner workings of InnoDB and its logs.

Optimizing InnoDB Disk I/O

If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound, Optimizing InnoDB Disk I/O.

Starting from MariaDB 10.0, the default number of innodb_buffer_pool_instances is 8. This means you have to configure your innodb_buffer_pool_size to at least 8 GB, see the defaults above.

Don’t over optimize: never make too many configuration changes at once. After changing one or two settings, let the server run for a few days so you can learn the effects of the changes. Then, if necessary, make additional changes to the configuration.

Innodb_buffer_pool_instances is deprecated in MariaDB 10.5.1+

Please note that in MariaDB 10.5.1 and up, innodb_buffer_pool_instances is deprecated and ignored (MDEV-15058). Oracle MySQL 8.0 still seem to support it though.

Extra tips for MySQL performance tuning

More tips? Yes!

Besides optimizing InnoDB for a high-performance workload, there is more you can do to tune MySQL server and database performance. Here are some extra MySQL configuration tips for you. Some information might be outdated and obsolete but may hold valuable information for tuning your MySQL servers. Some settings may be ignored if you’re only using InnoDB as storage engine (tip: convert MyISAM tables to InnoDB).

This is a translation and rewrite of my older Dutch post “MySQL performance en optimalisatie tips“, which is now deleted and links to here. Just in case you were wondering why you arrived here instead of the Dutch post after clicking a link :-) Some tips might be outdated.

1: No two MySQL servers are the same

When optimizing MySQL database servers, keep in mind that no server is equal to another. Settings that work well on one server, may degrade performance on a second. If you manage multiple servers with its configuration under version control (e.g almost -or exactly- the same MySQL configuration for all servers), choose what works best on all servers.

To determine what you can improve, you first need to know how the server performs now. You can use some MySQL commands for this on your MySQL cli (data comes from my very old post).

mysql> SHOW STATUS LIKE '%key_read%'; +-------------------+-------------+ | Variable_name | Value | +-------------------+-------------+ | Key_read_requests | 11810240259 | | Key_reads | 9260357 | +-------------------+-------------+
Code language: SQL (Structured Query Language) (sql)

These two variables and values relate to the configured key_buffer_size

In this old example, the database server has 4 GB of RAM and a configured key_buffer_size of 512 MB. The ratio (Key_read_requests / Key_reads) is approximately 1/1275, which is good but the key_buffer_size value may be increased to 768 MB. Even though this is not yet necessary.

mysql> SHOW STATUS LIKE 'thread%'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Threads_cached | 0 | | Threads_connected | 76 | | Threads_created | 6234040 | | Threads_running | 2 | +-------------------+---------+
Code language: SQL (Structured Query Language) (sql)

These Threads_* variable values show you there are currently 76 connected threads, of which only 2 are really running a thread (executing a statement). This means 74 connections are idle.

Here you can also see that there is no “thread cache” set up for MySQL: Threads_cached | 0

You can use the MySQL Server System variable thread_cache_size to configure how many threads must be cached by MySQL. This is one of those configuration settings that, probably, provides the least performance gain, but still…

Don’t set this one too high, somewhere between 20 and 40 is often good enough:

thread_cache_size = 20
Code language: plaintext (plaintext)

When you execute the previous statement again, the values will be:

mysql> SHOW STATUS LIKE 'thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 14 | | Threads_connected | 98 | | Threads_created | 2896 | | Threads_running | 1 | +-------------------+-------+
Code language: SQL (Structured Query Language) (sql)

You now have 14 threads cached :)

2: Miscellaneous MySQL configuration settings

A few words on some miscellaneous configuration settings.

2.1: tmp_table_size and max_heap_table_size
The default tmp_table_size and max_heap_table_size values are 16M. These two have to be equal in size! It sets the maximum size for internal in-memory tables, resulting in less creation of temporarily MyISAM tables on the file system. That in return, results in less disk I/O.

2.2: join_buffer_size
The join_buffer_size sets a maximum buffer size for plain index scans, range index scans and joins without indices (and therefore perform full table scans). Keep this one low, 1M for example.

3. Use Diagnostics for improvements

It is important to frequently run diagnostics and/or look up diagnostic data (for example in your information_scheme table). Percona has a lot of information about some key metrics:

MySQL tuning, the conclusion

Tuning MySQL and the InnoDB storage engine is an important step in further optimizing your hosting environment. Every new MySQL version brings new settings to improve your MySQL configuration, so be sure to read those changelogs.

In this article we went over InnoDB Buffer Pool Size and InnoDB Buffer Pool Instances. Setting these properly greatly improves your MySQL server’s performance!

But never (ever, ever) over-optimize! Please don’t make too many configuration changes at once. Make one or two and restart mysqld. After monitoring your system for a few days, running with the new configuration, you have data available to further optimize other MySQL settings.

With InnoDB being the default storage engine, you also have to make sure you make use of this storage engine in MySQL. Therefore it is important to convert old MyISAM tables to InnoDB.


Did you like: MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!

Then please, take a second to support Sysadmins of the North and donate!

Your generosity helps pay for the ongoing costs associated with running this website like coffee, hosting services, library mirrors, domain renewals, time for article research, and coffee, just to name a few.



5 thoughts on “MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!”

  1. Optimized WordPress hosting is a subject on which a lot is written about. And therefore, this post is not about where to host your WordPress blog, or who offers the best WordPress hosting. This post is for you developers, what you can do to optimize your WordPress hosting stack.

    (adsbygoogle = window.adsbygoogle || []).push({});

    Or for any other PHP web application for that matter.

    This post is not about setting up high-availability, fail-over, clustering, IIS versus Nginx versus Apache, RAID 1, 5, 6, 10, different types of storage, and so on. It’s about solving performance issues! This post tackles most of your performance issues, by explaining important configuration settings for PHP & MySQL, server, security, and so on.

    17 valuable WordPress snippets

    11+ Tips to optimize Umbraco 7 CMS

    Discover how this very blog has optimized its WordPress hosting environmentand how you can too…

    When reading Facebook groups like Advanced WordPress (AWP) or WordPress Development Stack Exchange, you often find questions like:

    Will it affect the performance of the site if we increase the number of records in wp_posts table by 10000 records? At this moment there are 200 posts.

    Of course it will affect performance, doh! :) But, you can do a lot to optimize & tune your server/VPS and WordPress hosting.

    Server hardware & separate web and MySQL services

    One of the most important tips I can give you: separate services.

    Whether you run your WordPress blogs on a VPS or bare-metal server, make sure you separate different server types and services. A lot of problems arise from having both Apache/PHP and MySQL on the same server, because MySQL loves memory. Approximately 80% memory needs to be available to MySQL.

    My advice for you is to separate them to two servers, dedicated and optimized for their purpose (web or MySQL). Sure it’ll cost you an additional $10/m for an extra DigitalOcean droplet, but trust me: it’s worth it.

    Isn’t it true that, when you (start to) develop WordPress websites for clients, and you host them yourself, you find yourself in a situation where you need to know a lot about “stuff” other than WordPress development? These 10 practical tips helps you tackle the most common performance issues.

    MySQL optimization

    As always, to tune MySQL you need to run an as high version of MySQL as possible. That means running MySQL 5.7, MariaDB 10.1 or Percona Server 5.6, at the time of this writing. Some MySQL administration experience is recommended for optimizing these settings.

    InnoDB storage engine

    Make sure your MySQL database tables are of the InnoDB storage engine. Nowadays MyISAM is outdated, just like PHP 5.3/5.4, .NET 3.5 and Windows Server 2003 are. All MySQL optimizations are for the InnoDB storage engine, so switch to that.

    Execute the result set of the following statement on your database to convert your old MyISAM tables to InnoDB:

    SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql','performance_schema');

    The output procuded will be something like:

    ALTER TABLE db-name.table1 ENGINE=InnoDB;
    ALTER TABLE db-name.table2 ENGINE=InnoDB;
    ALTER TABLE db-name.table3 ENGINE=InnoDB;

    InnoDB Disk I/O performance:As of MySQL 5.5, InnoDB disk i/o performance can be increased by increasing the innodb_write_io_threads and innodb_read_io_threads settings in your MySQL server’s my.cnf file. These are very important settings.

    Starting with MySQL 5.5.4, you can also divide the InnoDB buffer pool into multiple instances with multiple innodb_buffer_pool_instances.

    However, one important detail to remember is:

    This options takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more.
    MySQL InnoDB performance improvement: InnoDB buffer pool instances

    So when dividing your InnoDB buffer pool into 6 instances, you’ll need at least 6 GiB RAM…

    Convert MySQL MyISAM tables to InnoDB

    MySQL query cache

    You want to have MySQL query cache enabled on your MySQL database server, and have it configured with a proper value.

    MySQL Query Cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

    RAM, SSD, and CPU

    MySQL loves memory (RAM). Make sure your server has plenty of it, and some more. About 80% of the RAM needs to be available for MySQL, and when you start dividing Innodb_buffer_pool_instances, query-cache, and various buffer sizes, RAM consumption increases rapidly.

    Because SSD offers the best read/write (disk I/O) performance, SSD’s are a must too. If you can, switch to NVME disks.

    If you don’t need MySQL’s binlog -or binary log, then disable it. Having MySQL’s binary log enabled will increase read/write operations on your disk. Do enable the slow query log to identify slow running database queries. The following example enables the slow query log for queries running three (3) seconds or more:

    slow_query_log = 1
    long_query_time = 3.000000
    slow_query_log_file = /var/log/mysql/slow.log

    Through this MySQL slow log, I once found that the autoload column of the wp_options table wasn’t indexed. You can fix that easily too: wp_options table autoload index.

    Don’t save on CPU power, because MySQL likes to use the CPU for heavy calculations as well.

    Web server optimization – PHP

    No Apache, Nginx or IIS optimizations here, as I feel the web server is not often the bottleneck when a WordPress site is slow – but maybe you have a great tip for this? Share a comment!.

    PHP Optimization:As with MySQL versions, go with the highest possible version: 5.6.18, or preferably 7.0.3 -at the time of this writing, if all your code is PHP 7 compatible. Some PHP settings need optimization too, I discuss them below:

    PHP OPcache

    Zend OPcache PHP extension is in PHP nowadays, so why not enable OPcache and use it? I recommend you do. In your php.ini verify the following line is not commented out:

    zend_extension = php_opcache.dll

    You have to properly configure and optimize OPcache to take fully advantage of this opcode cache.

    For IIS, set up WinCache and be sure to disable WinCache opcode cache in favor of OPcache’s opcode cache:

    extension=php_wincache.dll
    wincache.ocenabled = 0
    wincache.ocachesize = 0

    Note: The .dll-part comes from my Windows Server hosting environment. Because of slow development, I stopped using WinCache on our web servers. OPcache is good enough, even on Windows Server.

    Be careful when enabling WinCache server-wide though, it might consume all available memory.

    Set a good realpath_cache_size for PHP

    PHP realpath_cache_size sets the size of the realpath cache to be used by PHP (e.g, the file locations on disk). Increasing realpath_cache_size can improve PHP performance a lot, and this value should be increased on systems where PHP opens many files.

    Check and set a good PHP realpath_cache_size

    Your WordPress Hosting at Warp Speed – img. credits: Christian Daryanto Limas @ flickr

    WordPress optimization

    The following section will give you some quick tips on how to improve the speed of your website.

    Optimization is key.

    check out my WordPress and MySQL categories to learn more about various WordPress and MySQL optimization techniques. For example: index autoload column wp_options table.delete transients regularly (automatically using Pressjitsu’s wp-transients-cleaner)delete post revisions regularly, delete spam comments and meta-data too. move the database from MySQL to Elasticsearch, especially with large WooCommerce ecommerce WordPress shops. Not only is Elasticsearch much faster than MySQL, it also provides and/or searches that are not possible using the normal WordPress MySQL search (like select all products with tag A and tag C, but not tag B or E).use Redis as a full page cache, or install Varnish Cache and deploy it in front of your web server. (Core Caching Concepts in WordPress is a great article introducing you into WordPress caching – Caching concepten in WordPress: wat is caching en hoe maakt dit WordPress sneller? is my Dutch translation on ITFAQ.nl).install a cache plugin like WP-Super-Cache or Cache Enabler (they’re the cache plugins I prefer, but there are many other cache plugins out there). With WP-Super-Cache and Cache Enabler you can speed up WordPress by serving gzip compressed files.Use SSL in WordPress, it’s the de-facto standard nowadays!

    Enable GZIP compression in WordPress .htaccess

    The easiest way to enable gzip compression in WordPress, for your images, CSS and JS files, is to add these lines to your .htaccess file in the root WordPress folder:

    ## ENABLE GZIP COMPRESSION ##
    <IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/plain
    AddOutputFilterByType DEFLATE text/html
    AddOutputFilterByType DEFLATE text/xml
    AddOutputFilterByType DEFLATE text/css
    AddOutputFilterByType DEFLATE application/xml
    AddOutputFilterByType DEFLATE application/xhtml+xml
    AddOutputFilterByType DEFLATE application/rss+xml
    AddOutputFilterByType DEFLATE application/javascript
    AddOutputFilterByType DEFLATE application/x-javascript
    </IfModule>
    ## / ENABLE GZIP COMPRESSION ##

    If your server supports it, enable BROTLI compression instead of gzip. Set an Brotli output filter in .htaccess:

    SetOutputFilter BROTLI_COMPRESS

    Caveat: be aware that not all plugins and external optimization sites / services recognize Brotli compression yet. You may get notices about not having gzip enabled…

    Leverage browser caching

    Add the following lines to your WordPress .htaccess to enable browser caching for resources:

    ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING
    <IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType image/jpg "access 1 year"
    ExpiresByType image/jpeg "access 1 year"
    ExpiresByType image/gif "access 1 year"
    ExpiresByType image/png "access 1 year"
    ExpiresByType text/css "access 1 month"
    ExpiresByType text/html "access 1 month"
    ExpiresByType application/pdf "access 1 month"
    ExpiresByType text/x-javascript "access 1 month"
    ExpiresByType application/x-shockwave-flash "access 1 month"
    ExpiresByType image/x-icon "access 1 year"
    ExpiresDefault "access 1 month"
    # Web feeds
    ExpiresByType application/atom+xml "access plus 1 hour"
    ExpiresByType application/rss+xml "access plus 1 hour"
    </IfModule>
    ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING

    Enable HTTP Connection keep-alive:

    Set the following header directive in your .htaccess file to enable HTTP Connection keep-alive:

    Header set Connection keep-alive

    Remember that for everything you add to your .htaccess file to optimize WordPress, you don’t have to install a plugin! This saves on PHP files and CPU processing time. Win win!

    WordPress development tips – DevOps :)

    An area I’m not very experienced in, but here goes… A tip: always (try to) follow WordPress development guidelines. For optimized PHP code, check out the PHP Benchmark tests, it’s a real must!

    Also, it’s recommended to try to avoid catching exceptions in PHP. Exception handling can cause a performance penalty: Checking the performance of PHP exceptions, Speed performance of Trying and catching Exceptions in PHP.

    Conclusion

    When you develop WordPress websites for clients, and you want to host them yourself, you find yourself in a situation where you need to know a lot about systems administration “stuff“. Completely different topics than WordPress development. Like MySQL configuration, web server configuration, PHP settings, server security, and so on.

    I often receive, and read about, questions from developers saying their hosting performance has degraded drastically over the course of time, and have no idea how to resolve the performance issues. In this optimized WordPress hosting post, I provided some important tips to improve hosting performance, for when you plan to host websites yourself.

    If you have a valuable tip, please do share! And don’t forget to share this post with your friends and co-workers, thanks!

    Related

    Reply
    • Optimized WordPress hosting is a subject on which a lot is written about. And therefore, this post is not about where to host your WordPress blog, or who offers the best WordPress hosting. This post is for you developers, what you can do to optimize your WordPress hosting stack.

      (adsbygoogle = window.adsbygoogle || []).push({});

      Or for any other PHP web application for that matter.

      This post is not about setting up high-availability, fail-over, clustering, IIS versus Nginx versus Apache, RAID 1, 5, 6, 10, different types of storage, and so on. It’s about solving performance issues! This post tackles most of your performance issues, by explaining important configuration settings for PHP & MySQL, server, security, and so on.

      17 valuable WordPress snippets

      11+ Tips to optimize Umbraco 7 CMS

      Discover how this very blog has optimized its WordPress hosting environmentand how you can too…

      When reading Facebook groups like Advanced WordPress (AWP) or WordPress Development Stack Exchange, you often find questions like:

      Will it affect the performance of the site if we increase the number of records in wp_posts table by 10000 records? At this moment there are 200 posts.

      Of course it will affect performance, doh! :) But, you can do a lot to optimize & tune your server/VPS and WordPress hosting.

      Server hardware & separate web and MySQL services

      One of the most important tips I can give you: separate services.

      Whether you run your WordPress blogs on a VPS or bare-metal server, make sure you separate different server types and services. A lot of problems arise from having both Apache/PHP and MySQL on the same server, because MySQL loves memory. Approximately 80% memory needs to be available to MySQL.

      My advice for you is to separate them to two servers, dedicated and optimized for their purpose (web or MySQL). Sure it’ll cost you an additional $10/m for an extra DigitalOcean droplet, but trust me: it’s worth it.

      Isn’t it true that, when you (start to) develop WordPress websites for clients, and you host them yourself, you find yourself in a situation where you need to know a lot about “stuff” other than WordPress development? These 10 practical tips helps you tackle the most common performance issues.

      MySQL optimization

      As always, to tune MySQL you need to run an as high version of MySQL as possible. That means running MySQL 5.7, MariaDB 10.1 or Percona Server 5.6, at the time of this writing. Some MySQL administration experience is recommended for optimizing these settings.

      InnoDB storage engine

      Make sure your MySQL database tables are of the InnoDB storage engine. Nowadays MyISAM is outdated, just like PHP 5.3/5.4, .NET 3.5 and Windows Server 2003 are. All MySQL optimizations are for the InnoDB storage engine, so switch to that.

      Execute the result set of the following statement on your database to convert your old MyISAM tables to InnoDB:

      SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
      FROM information_schema.tables WHERE engine='MyISAM'
      AND table_schema NOT IN ('information_schema','mysql','performance_schema');

      The output procuded will be something like:

      ALTER TABLE db-name.table1 ENGINE=InnoDB;
      ALTER TABLE db-name.table2 ENGINE=InnoDB;
      ALTER TABLE db-name.table3 ENGINE=InnoDB;

      InnoDB Disk I/O performance:As of MySQL 5.5, InnoDB disk i/o performance can be increased by increasing the innodb_write_io_threads and innodb_read_io_threads settings in your MySQL server’s my.cnf file. These are very important settings.

      Starting with MySQL 5.5.4, you can also divide the InnoDB buffer pool into multiple instances with multiple innodb_buffer_pool_instances.

      However, one important detail to remember is:

      This options takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more.
      MySQL InnoDB performance improvement: InnoDB buffer pool instances

      So when dividing your InnoDB buffer pool into 6 instances, you’ll need at least 6 GiB RAM…

      Convert MySQL MyISAM tables to InnoDB

      MySQL query cache

      You want to have MySQL query cache enabled on your MySQL database server, and have it configured with a proper value.

      MySQL Query Cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

      RAM, SSD, and CPU

      MySQL loves memory (RAM). Make sure your server has plenty of it, and some more. About 80% of the RAM needs to be available for MySQL, and when you start dividing Innodb_buffer_pool_instances, query-cache, and various buffer sizes, RAM consumption increases rapidly.

      Because SSD offers the best read/write (disk I/O) performance, SSD’s are a must too. If you can, switch to NVME disks.

      If you don’t need MySQL’s binlog -or binary log, then disable it. Having MySQL’s binary log enabled will increase read/write operations on your disk. Do enable the slow query log to identify slow running database queries. The following example enables the slow query log for queries running three (3) seconds or more:

      slow_query_log = 1
      long_query_time = 3.000000
      slow_query_log_file = /var/log/mysql/slow.log

      Through this MySQL slow log, I once found that the autoload column of the wp_options table wasn’t indexed. You can fix that easily too: wp_options table autoload index.

      Don’t save on CPU power, because MySQL likes to use the CPU for heavy calculations as well.

      Web server optimization – PHP

      No Apache, Nginx or IIS optimizations here, as I feel the web server is not often the bottleneck when a WordPress site is slow – but maybe you have a great tip for this? Share a comment!.

      PHP Optimization:As with MySQL versions, go with the highest possible version: 5.6.18, or preferably 7.0.3 -at the time of this writing, if all your code is PHP 7 compatible. Some PHP settings need optimization too, I discuss them below:

      PHP OPcache

      Zend OPcache PHP extension is in PHP nowadays, so why not enable OPcache and use it? I recommend you do. In your php.ini verify the following line is not commented out:

      zend_extension = php_opcache.dll

      You have to properly configure and optimize OPcache to take fully advantage of this opcode cache.

      For IIS, set up WinCache and be sure to disable WinCache opcode cache in favor of OPcache’s opcode cache:

      extension=php_wincache.dll
      wincache.ocenabled = 0
      wincache.ocachesize = 0

      Note: The .dll-part comes from my Windows Server hosting environment. Because of slow development, I stopped using WinCache on our web servers. OPcache is good enough, even on Windows Server.

      Be careful when enabling WinCache server-wide though, it might consume all available memory.

      Set a good realpath_cache_size for PHP

      PHP realpath_cache_size sets the size of the realpath cache to be used by PHP (e.g, the file locations on disk). Increasing realpath_cache_size can improve PHP performance a lot, and this value should be increased on systems where PHP opens many files.

      Check and set a good PHP realpath_cache_size

      Your WordPress Hosting at Warp Speed – img. credits: Christian Daryanto Limas @ flickr

      WordPress optimization

      The following section will give you some quick tips on how to improve the speed of your website.

      Optimization is key.

      check out my WordPress and MySQL categories to learn more about various WordPress and MySQL optimization techniques. For example: index autoload column wp_options table.delete transients regularly (automatically using Pressjitsu’s wp-transients-cleaner)delete post revisions regularly, delete spam comments and meta-data too. move the database from MySQL to Elasticsearch, especially with large WooCommerce ecommerce WordPress shops. Not only is Elasticsearch much faster than MySQL, it also provides and/or searches that are not possible using the normal WordPress MySQL search (like select all products with tag A and tag C, but not tag B or E).use Redis as a full page cache, or install Varnish Cache and deploy it in front of your web server. (Core Caching Concepts in WordPress is a great article introducing you into WordPress caching – Caching concepten in WordPress: wat is caching en hoe maakt dit WordPress sneller? is my Dutch translation on ITFAQ.nl).install a cache plugin like WP-Super-Cache or Cache Enabler (they’re the cache plugins I prefer, but there are many other cache plugins out there). With WP-Super-Cache and Cache Enabler you can speed up WordPress by serving gzip compressed files.Use SSL in WordPress, it’s the de-facto standard nowadays!

      Enable GZIP compression in WordPress .htaccess

      The easiest way to enable gzip compression in WordPress, for your images, CSS and JS files, is to add these lines to your .htaccess file in the root WordPress folder:

      ## ENABLE GZIP COMPRESSION ##
      <IfModule mod_deflate.c>
      AddOutputFilterByType DEFLATE text/plain
      AddOutputFilterByType DEFLATE text/html
      AddOutputFilterByType DEFLATE text/xml
      AddOutputFilterByType DEFLATE text/css
      AddOutputFilterByType DEFLATE application/xml
      AddOutputFilterByType DEFLATE application/xhtml+xml
      AddOutputFilterByType DEFLATE application/rss+xml
      AddOutputFilterByType DEFLATE application/javascript
      AddOutputFilterByType DEFLATE application/x-javascript
      </IfModule>
      ## / ENABLE GZIP COMPRESSION ##

      If your server supports it, enable BROTLI compression instead of gzip. Set an Brotli output filter in .htaccess:

      SetOutputFilter BROTLI_COMPRESS

      Caveat: be aware that not all plugins and external optimization sites / services recognize Brotli compression yet. You may get notices about not having gzip enabled…

      Leverage browser caching

      Add the following lines to your WordPress .htaccess to enable browser caching for resources:

      ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING
      <IfModule mod_expires.c>
      ExpiresActive On
      ExpiresByType image/jpg "access 1 year"
      ExpiresByType image/jpeg "access 1 year"
      ExpiresByType image/gif "access 1 year"
      ExpiresByType image/png "access 1 year"
      ExpiresByType text/css "access 1 month"
      ExpiresByType text/html "access 1 month"
      ExpiresByType application/pdf "access 1 month"
      ExpiresByType text/x-javascript "access 1 month"
      ExpiresByType application/x-shockwave-flash "access 1 month"
      ExpiresByType image/x-icon "access 1 year"
      ExpiresDefault "access 1 month"
      # Web feeds
      ExpiresByType application/atom+xml "access plus 1 hour"
      ExpiresByType application/rss+xml "access plus 1 hour"
      </IfModule>
      ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING

      Enable HTTP Connection keep-alive:

      Set the following header directive in your .htaccess file to enable HTTP Connection keep-alive:

      Header set Connection keep-alive

      Remember that for everything you add to your .htaccess file to optimize WordPress, you don’t have to install a plugin! This saves on PHP files and CPU processing time. Win win!

      WordPress development tips – DevOps :)

      An area I’m not very experienced in, but here goes… A tip: always (try to) follow WordPress development guidelines. For optimized PHP code, check out the PHP Benchmark tests, it’s a real must!

      Also, it’s recommended to try to avoid catching exceptions in PHP. Exception handling can cause a performance penalty: Checking the performance of PHP exceptions, Speed performance of Trying and catching Exceptions in PHP.

      Conclusion

      When you develop WordPress websites for clients, and you want to host them yourself, you find yourself in a situation where you need to know a lot about systems administration “stuff“. Completely different topics than WordPress development. Like MySQL configuration, web server configuration, PHP settings, server security, and so on.

      I often receive, and read about, questions from developers saying their hosting performance has degraded drastically over the course of time, and have no idea how to resolve the performance issues. In this optimized WordPress hosting post, I provided some important tips to improve hosting performance, for when you plan to host websites yourself.

      If you have a valuable tip, please do share! And don’t forget to share this post with your friends and co-workers, thanks!

      Related

      Reply
  2. Optimized WordPress hosting is a subject on which a lot is written about. And therefore, this post is not about where to host your WordPress blog, or who offers the best WordPress hosting. This post is for you developers, what you can do to optimize your WordPress hosting stack.

    (adsbygoogle = window.adsbygoogle || []).push({});

    Or for any other PHP web application for that matter.

    This post is not about setting up high-availability, fail-over, clustering, IIS versus Nginx versus Apache, RAID 1, 5, 6, 10, different types of storage, and so on. It’s about solving performance issues! This post tackles most of your performance issues, by explaining important configuration settings for PHP & MySQL, server, security, and so on.

    17 valuable WordPress snippets

    11+ Tips to optimize Umbraco 7 CMS

    Discover how this very blog has optimized its WordPress hosting environmentand how you can too…

    When reading Facebook groups like Advanced WordPress (AWP) or WordPress Development Stack Exchange, you often find questions like:

    Will it affect the performance of the site if we increase the number of records in wp_posts table by 10000 records? At this moment there are 200 posts.

    Of course it will affect performance, doh! :) But, you can do a lot to optimize & tune your server/VPS and WordPress hosting.

    Server hardware & separate web and MySQL services

    One of the most important tips I can give you: separate services.

    Whether you run your WordPress blogs on a VPS or bare-metal server, make sure you separate different server types and services. A lot of problems arise from having both Apache/PHP and MySQL on the same server, because MySQL loves memory. Approximately 80% memory needs to be available to MySQL.

    My advice for you is to separate them to two servers, dedicated and optimized for their purpose (web or MySQL). Sure it’ll cost you an additional $10/m for an extra DigitalOcean droplet, but trust me: it’s worth it.

    Isn’t it true that, when you (start to) develop WordPress websites for clients, and you host them yourself, you find yourself in a situation where you need to know a lot about “stuff” other than WordPress development? These 10 practical tips helps you tackle the most common performance issues.

    MySQL optimization

    As always, to tune MySQL you need to run an as high version of MySQL as possible. That means running MySQL 5.7, MariaDB 10.1 or Percona Server 5.6, at the time of this writing. Some MySQL administration experience is recommended for optimizing these settings.

    InnoDB storage engine

    Make sure your MySQL database tables are of the InnoDB storage engine. Nowadays MyISAM is outdated, just like PHP 5.3/5.4, .NET 3.5 and Windows Server 2003 are. All MySQL optimizations are for the InnoDB storage engine, so switch to that.

    Execute the result set of the following statement on your database to convert your old MyISAM tables to InnoDB:

    SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
    FROM information_schema.tables WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','mysql','performance_schema');

    The output procuded will be something like:

    ALTER TABLE db-name.table1 ENGINE=InnoDB;
    ALTER TABLE db-name.table2 ENGINE=InnoDB;
    ALTER TABLE db-name.table3 ENGINE=InnoDB;

    InnoDB Disk I/O performance:As of MySQL 5.5, InnoDB disk i/o performance can be increased by increasing the innodb_write_io_threads and innodb_read_io_threads settings in your MySQL server’s my.cnf file. These are very important settings.

    Starting with MySQL 5.5.4, you can also divide the InnoDB buffer pool into multiple instances with multiple innodb_buffer_pool_instances.

    However, one important detail to remember is:

    This options takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more.
    MySQL InnoDB performance improvement: InnoDB buffer pool instances

    So when dividing your InnoDB buffer pool into 6 instances, you’ll need at least 6 GiB RAM…

    Convert MySQL MyISAM tables to InnoDB

    MySQL query cache

    You want to have MySQL query cache enabled on your MySQL database server, and have it configured with a proper value.

    MySQL Query Cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

    RAM, SSD, and CPU

    MySQL loves memory (RAM). Make sure your server has plenty of it, and some more. About 80% of the RAM needs to be available for MySQL, and when you start dividing Innodb_buffer_pool_instances, query-cache, and various buffer sizes, RAM consumption increases rapidly.

    Because SSD offers the best read/write (disk I/O) performance, SSD’s are a must too. If you can, switch to NVME disks.

    If you don’t need MySQL’s binlog -or binary log, then disable it. Having MySQL’s binary log enabled will increase read/write operations on your disk. Do enable the slow query log to identify slow running database queries. The following example enables the slow query log for queries running three (3) seconds or more:

    slow_query_log = 1
    long_query_time = 3.000000
    slow_query_log_file = /var/log/mysql/slow.log

    Through this MySQL slow log, I once found that the autoload column of the wp_options table wasn’t indexed. You can fix that easily too: wp_options table autoload index.

    Don’t save on CPU power, because MySQL likes to use the CPU for heavy calculations as well.

    Web server optimization – PHP

    No Apache, Nginx or IIS optimizations here, as I feel the web server is not often the bottleneck when a WordPress site is slow – but maybe you have a great tip for this? Share a comment!.

    PHP Optimization:As with MySQL versions, go with the highest possible version: 5.6.18, or preferably 7.0.3 -at the time of this writing, if all your code is PHP 7 compatible. Some PHP settings need optimization too, I discuss them below:

    PHP OPcache

    Zend OPcache PHP extension is in PHP nowadays, so why not enable OPcache and use it? I recommend you do. In your php.ini verify the following line is not commented out:

    zend_extension = php_opcache.dll

    You have to properly configure and optimize OPcache to take fully advantage of this opcode cache.

    For IIS, set up WinCache and be sure to disable WinCache opcode cache in favor of OPcache’s opcode cache:

    extension=php_wincache.dll
    wincache.ocenabled = 0
    wincache.ocachesize = 0

    Note: The .dll-part comes from my Windows Server hosting environment. Because of slow development, I stopped using WinCache on our web servers. OPcache is good enough, even on Windows Server.

    Be careful when enabling WinCache server-wide though, it might consume all available memory.

    Set a good realpath_cache_size for PHP

    PHP realpath_cache_size sets the size of the realpath cache to be used by PHP (e.g, the file locations on disk). Increasing realpath_cache_size can improve PHP performance a lot, and this value should be increased on systems where PHP opens many files.

    Check and set a good PHP realpath_cache_size

    Your WordPress Hosting at Warp Speed – img. credits: Christian Daryanto Limas @ flickr

    WordPress optimization

    The following section will give you some quick tips on how to improve the speed of your website.

    Optimization is key.

    check out my WordPress and MySQL categories to learn more about various WordPress and MySQL optimization techniques. For example: index autoload column wp_options table.delete transients regularly (automatically using Pressjitsu’s wp-transients-cleaner)delete post revisions regularly, delete spam comments and meta-data too. move the database from MySQL to Elasticsearch, especially with large WooCommerce ecommerce WordPress shops. Not only is Elasticsearch much faster than MySQL, it also provides and/or searches that are not possible using the normal WordPress MySQL search (like select all products with tag A and tag C, but not tag B or E).use Redis as a full page cache, or install Varnish Cache and deploy it in front of your web server. (Core Caching Concepts in WordPress is a great article introducing you into WordPress caching – Caching concepten in WordPress: wat is caching en hoe maakt dit WordPress sneller? is my Dutch translation on ITFAQ.nl).install a cache plugin like WP-Super-Cache or Cache Enabler (they’re the cache plugins I prefer, but there are many other cache plugins out there). With WP-Super-Cache and Cache Enabler you can speed up WordPress by serving gzip compressed files.Use SSL in WordPress, it’s the de-facto standard nowadays!

    Enable GZIP compression in WordPress .htaccess

    The easiest way to enable gzip compression in WordPress, for your images, CSS and JS files, is to add these lines to your .htaccess file in the root WordPress folder:

    ## ENABLE GZIP COMPRESSION ##
    <IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE text/plain
    AddOutputFilterByType DEFLATE text/html
    AddOutputFilterByType DEFLATE text/xml
    AddOutputFilterByType DEFLATE text/css
    AddOutputFilterByType DEFLATE application/xml
    AddOutputFilterByType DEFLATE application/xhtml+xml
    AddOutputFilterByType DEFLATE application/rss+xml
    AddOutputFilterByType DEFLATE application/javascript
    AddOutputFilterByType DEFLATE application/x-javascript
    </IfModule>
    ## / ENABLE GZIP COMPRESSION ##

    If your server supports it, enable BROTLI compression instead of gzip. Set an Brotli output filter in .htaccess:

    SetOutputFilter BROTLI_COMPRESS

    Caveat: be aware that not all plugins and external optimization sites / services recognize Brotli compression yet. You may get notices about not having gzip enabled…

    Leverage browser caching

    Add the following lines to your WordPress .htaccess to enable browser caching for resources:

    ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING
    <IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType image/jpg "access 1 year"
    ExpiresByType image/jpeg "access 1 year"
    ExpiresByType image/gif "access 1 year"
    ExpiresByType image/png "access 1 year"
    ExpiresByType text/css "access 1 month"
    ExpiresByType text/html "access 1 month"
    ExpiresByType application/pdf "access 1 month"
    ExpiresByType text/x-javascript "access 1 month"
    ExpiresByType application/x-shockwave-flash "access 1 month"
    ExpiresByType image/x-icon "access 1 year"
    ExpiresDefault "access 1 month"
    # Web feeds
    ExpiresByType application/atom+xml "access plus 1 hour"
    ExpiresByType application/rss+xml "access plus 1 hour"
    </IfModule>
    ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING

    Enable HTTP Connection keep-alive:

    Set the following header directive in your .htaccess file to enable HTTP Connection keep-alive:

    Header set Connection keep-alive

    Remember that for everything you add to your .htaccess file to optimize WordPress, you don’t have to install a plugin! This saves on PHP files and CPU processing time. Win win!

    WordPress development tips – DevOps :)

    An area I’m not very experienced in, but here goes… A tip: always (try to) follow WordPress development guidelines. For optimized PHP code, check out the PHP Benchmark tests, it’s a real must!

    Also, it’s recommended to try to avoid catching exceptions in PHP. Exception handling can cause a performance penalty: Checking the performance of PHP exceptions, Speed performance of Trying and catching Exceptions in PHP.

    Conclusion

    When you develop WordPress websites for clients, and you want to host them yourself, you find yourself in a situation where you need to know a lot about systems administration “stuff“. Completely different topics than WordPress development. Like MySQL configuration, web server configuration, PHP settings, server security, and so on.

    I often receive, and read about, questions from developers saying their hosting performance has degraded drastically over the course of time, and have no idea how to resolve the performance issues. In this optimized WordPress hosting post, I provided some important tips to improve hosting performance, for when you plan to host websites yourself.

    If you have a valuable tip, please do share! And don’t forget to share this post with your friends and co-workers, thanks!

    Related

    Reply
    • Optimized WordPress hosting is a subject on which a lot is written about. And therefore, this post is not about where to host your WordPress blog, or who offers the best WordPress hosting. This post is for you developers, what you can do to optimize your WordPress hosting stack.

      (adsbygoogle = window.adsbygoogle || []).push({});

      Or for any other PHP web application for that matter.

      This post is not about setting up high-availability, fail-over, clustering, IIS versus Nginx versus Apache, RAID 1, 5, 6, 10, different types of storage, and so on. It’s about solving performance issues! This post tackles most of your performance issues, by explaining important configuration settings for PHP & MySQL, server, security, and so on.

      17 valuable WordPress snippets

      11+ Tips to optimize Umbraco 7 CMS

      Discover how this very blog has optimized its WordPress hosting environmentand how you can too…

      When reading Facebook groups like Advanced WordPress (AWP) or WordPress Development Stack Exchange, you often find questions like:

      Will it affect the performance of the site if we increase the number of records in wp_posts table by 10000 records? At this moment there are 200 posts.

      Of course it will affect performance, doh! :) But, you can do a lot to optimize & tune your server/VPS and WordPress hosting.

      Server hardware & separate web and MySQL services

      One of the most important tips I can give you: separate services.

      Whether you run your WordPress blogs on a VPS or bare-metal server, make sure you separate different server types and services. A lot of problems arise from having both Apache/PHP and MySQL on the same server, because MySQL loves memory. Approximately 80% memory needs to be available to MySQL.

      My advice for you is to separate them to two servers, dedicated and optimized for their purpose (web or MySQL). Sure it’ll cost you an additional $10/m for an extra DigitalOcean droplet, but trust me: it’s worth it.

      Isn’t it true that, when you (start to) develop WordPress websites for clients, and you host them yourself, you find yourself in a situation where you need to know a lot about “stuff” other than WordPress development? These 10 practical tips helps you tackle the most common performance issues.

      MySQL optimization

      As always, to tune MySQL you need to run an as high version of MySQL as possible. That means running MySQL 5.7, MariaDB 10.1 or Percona Server 5.6, at the time of this writing. Some MySQL administration experience is recommended for optimizing these settings.

      InnoDB storage engine

      Make sure your MySQL database tables are of the InnoDB storage engine. Nowadays MyISAM is outdated, just like PHP 5.3/5.4, .NET 3.5 and Windows Server 2003 are. All MySQL optimizations are for the InnoDB storage engine, so switch to that.

      Execute the result set of the following statement on your database to convert your old MyISAM tables to InnoDB:

      SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
      FROM information_schema.tables WHERE engine='MyISAM'
      AND table_schema NOT IN ('information_schema','mysql','performance_schema');

      The output procuded will be something like:

      ALTER TABLE db-name.table1 ENGINE=InnoDB;
      ALTER TABLE db-name.table2 ENGINE=InnoDB;
      ALTER TABLE db-name.table3 ENGINE=InnoDB;

      InnoDB Disk I/O performance:As of MySQL 5.5, InnoDB disk i/o performance can be increased by increasing the innodb_write_io_threads and innodb_read_io_threads settings in your MySQL server’s my.cnf file. These are very important settings.

      Starting with MySQL 5.5.4, you can also divide the InnoDB buffer pool into multiple instances with multiple innodb_buffer_pool_instances.

      However, one important detail to remember is:

      This options takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more.
      MySQL InnoDB performance improvement: InnoDB buffer pool instances

      So when dividing your InnoDB buffer pool into 6 instances, you’ll need at least 6 GiB RAM…

      Convert MySQL MyISAM tables to InnoDB

      MySQL query cache

      You want to have MySQL query cache enabled on your MySQL database server, and have it configured with a proper value.

      MySQL Query Cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

      RAM, SSD, and CPU

      MySQL loves memory (RAM). Make sure your server has plenty of it, and some more. About 80% of the RAM needs to be available for MySQL, and when you start dividing Innodb_buffer_pool_instances, query-cache, and various buffer sizes, RAM consumption increases rapidly.

      Because SSD offers the best read/write (disk I/O) performance, SSD’s are a must too. If you can, switch to NVME disks.

      If you don’t need MySQL’s binlog -or binary log, then disable it. Having MySQL’s binary log enabled will increase read/write operations on your disk. Do enable the slow query log to identify slow running database queries. The following example enables the slow query log for queries running three (3) seconds or more:

      slow_query_log = 1
      long_query_time = 3.000000
      slow_query_log_file = /var/log/mysql/slow.log

      Through this MySQL slow log, I once found that the autoload column of the wp_options table wasn’t indexed. You can fix that easily too: wp_options table autoload index.

      Don’t save on CPU power, because MySQL likes to use the CPU for heavy calculations as well.

      Web server optimization – PHP

      No Apache, Nginx or IIS optimizations here, as I feel the web server is not often the bottleneck when a WordPress site is slow – but maybe you have a great tip for this? Share a comment!.

      PHP Optimization:As with MySQL versions, go with the highest possible version: 5.6.18, or preferably 7.0.3 -at the time of this writing, if all your code is PHP 7 compatible. Some PHP settings need optimization too, I discuss them below:

      PHP OPcache

      Zend OPcache PHP extension is in PHP nowadays, so why not enable OPcache and use it? I recommend you do. In your php.ini verify the following line is not commented out:

      zend_extension = php_opcache.dll

      You have to properly configure and optimize OPcache to take fully advantage of this opcode cache.

      For IIS, set up WinCache and be sure to disable WinCache opcode cache in favor of OPcache’s opcode cache:

      extension=php_wincache.dll
      wincache.ocenabled = 0
      wincache.ocachesize = 0

      Note: The .dll-part comes from my Windows Server hosting environment. Because of slow development, I stopped using WinCache on our web servers. OPcache is good enough, even on Windows Server.

      Be careful when enabling WinCache server-wide though, it might consume all available memory.

      Set a good realpath_cache_size for PHP

      PHP realpath_cache_size sets the size of the realpath cache to be used by PHP (e.g, the file locations on disk). Increasing realpath_cache_size can improve PHP performance a lot, and this value should be increased on systems where PHP opens many files.

      Check and set a good PHP realpath_cache_size

      Your WordPress Hosting at Warp Speed – img. credits: Christian Daryanto Limas @ flickr

      WordPress optimization

      The following section will give you some quick tips on how to improve the speed of your website.

      Optimization is key.

      check out my WordPress and MySQL categories to learn more about various WordPress and MySQL optimization techniques. For example: index autoload column wp_options table.delete transients regularly (automatically using Pressjitsu’s wp-transients-cleaner)delete post revisions regularly, delete spam comments and meta-data too. move the database from MySQL to Elasticsearch, especially with large WooCommerce ecommerce WordPress shops. Not only is Elasticsearch much faster than MySQL, it also provides and/or searches that are not possible using the normal WordPress MySQL search (like select all products with tag A and tag C, but not tag B or E).use Redis as a full page cache, or install Varnish Cache and deploy it in front of your web server. (Core Caching Concepts in WordPress is a great article introducing you into WordPress caching – Caching concepten in WordPress: wat is caching en hoe maakt dit WordPress sneller? is my Dutch translation on ITFAQ.nl).install a cache plugin like WP-Super-Cache or Cache Enabler (they’re the cache plugins I prefer, but there are many other cache plugins out there). With WP-Super-Cache and Cache Enabler you can speed up WordPress by serving gzip compressed files.Use SSL in WordPress, it’s the de-facto standard nowadays!

      Enable GZIP compression in WordPress .htaccess

      The easiest way to enable gzip compression in WordPress, for your images, CSS and JS files, is to add these lines to your .htaccess file in the root WordPress folder:

      ## ENABLE GZIP COMPRESSION ##
      <IfModule mod_deflate.c>
      AddOutputFilterByType DEFLATE text/plain
      AddOutputFilterByType DEFLATE text/html
      AddOutputFilterByType DEFLATE text/xml
      AddOutputFilterByType DEFLATE text/css
      AddOutputFilterByType DEFLATE application/xml
      AddOutputFilterByType DEFLATE application/xhtml+xml
      AddOutputFilterByType DEFLATE application/rss+xml
      AddOutputFilterByType DEFLATE application/javascript
      AddOutputFilterByType DEFLATE application/x-javascript
      </IfModule>
      ## / ENABLE GZIP COMPRESSION ##

      If your server supports it, enable BROTLI compression instead of gzip. Set an Brotli output filter in .htaccess:

      SetOutputFilter BROTLI_COMPRESS

      Caveat: be aware that not all plugins and external optimization sites / services recognize Brotli compression yet. You may get notices about not having gzip enabled…

      Leverage browser caching

      Add the following lines to your WordPress .htaccess to enable browser caching for resources:

      ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING
      <IfModule mod_expires.c>
      ExpiresActive On
      ExpiresByType image/jpg "access 1 year"
      ExpiresByType image/jpeg "access 1 year"
      ExpiresByType image/gif "access 1 year"
      ExpiresByType image/png "access 1 year"
      ExpiresByType text/css "access 1 month"
      ExpiresByType text/html "access 1 month"
      ExpiresByType application/pdf "access 1 month"
      ExpiresByType text/x-javascript "access 1 month"
      ExpiresByType application/x-shockwave-flash "access 1 month"
      ExpiresByType image/x-icon "access 1 year"
      ExpiresDefault "access 1 month"
      # Web feeds
      ExpiresByType application/atom+xml "access plus 1 hour"
      ExpiresByType application/rss+xml "access plus 1 hour"
      </IfModule>
      ## ENABLE EXPIRES HEADERS - LEVERAGE BROWSER CACHING

      Enable HTTP Connection keep-alive:

      Set the following header directive in your .htaccess file to enable HTTP Connection keep-alive:

      Header set Connection keep-alive

      Remember that for everything you add to your .htaccess file to optimize WordPress, you don’t have to install a plugin! This saves on PHP files and CPU processing time. Win win!

      WordPress development tips – DevOps :)

      An area I’m not very experienced in, but here goes… A tip: always (try to) follow WordPress development guidelines. For optimized PHP code, check out the PHP Benchmark tests, it’s a real must!

      Also, it’s recommended to try to avoid catching exceptions in PHP. Exception handling can cause a performance penalty: Checking the performance of PHP exceptions, Speed performance of Trying and catching Exceptions in PHP.

      Conclusion

      When you develop WordPress websites for clients, and you want to host them yourself, you find yourself in a situation where you need to know a lot about systems administration “stuff“. Completely different topics than WordPress development. Like MySQL configuration, web server configuration, PHP settings, server security, and so on.

      I often receive, and read about, questions from developers saying their hosting performance has degraded drastically over the course of time, and have no idea how to resolve the performance issues. In this optimized WordPress hosting post, I provided some important tips to improve hosting performance, for when you plan to host websites yourself.

      If you have a valuable tip, please do share! And don’t forget to share this post with your friends and co-workers, thanks!

      Related

      Reply

Hi! Join the discussion, leave a reply!

%d bloggers like this: