Improving your WordPress workload performance on Azure Database for MySQL

WordPress is one of the most popular blogging tools in the world. Azure Database for MySQL allows users to create, configure and optimize a cloud-based WordPress site that enables easy customization and the ability to handle a large number of site visitors. 

WordPress workloads tend to be read-heavy, which occasionally causes issues with performance.  With research, and by using optimization techniques and performance testing, we’ve been able to identify some parameters that you can change to improve performance for your WordPress workloads.  

How to leverage the research we've done

To improve the out-of-the-box experience that customers have for a newly provisioned WordPress instance, we've set the following parameters and values by default for both General Purpose 2 vCore and 4 vCore instances.  You may notice a resource tag with the label: ‘AppProfile’: ‘Wordpress’ for your Azure Database for MySQL instance.  Behind the scenes, this enables us to set the optimal parameter values automatically for you.   

For any existing General Purpose 2 vCore and 4 vCore servers you already have, there are two options available for you to take advantage of the performance optimizations we will be explaining in this blog.  

• If you want the MySQL service to handle updating the server parameters on your behalf, you can update your server's resource tag with the following name and value. Once you've saved the resource tag, the service backend will need to be triggered to allow for automatic server parameter defaults. You can trigger this by scaling the vCores of your server up to 4 vCores and back down to 2 vCores.   
  • Name: AppProfile  
  • Value: Wordpress 
• Update the server parameters manually based on the ranges we describe in the rest of the blog. Learn about how to update server parameters yourself by referring to the Azure portal and Azure CLI documentation.   

Follow the recommended tuning guidance below to see performance improvements within your WordPress workload.   

Understand metrics for your WordPress workload 

With a read-heavy workload, queries executing against your WordPress server could exceed the in-memory temporary table sizes. This will cause your server to switch to writing temporary tables to disk, thus affecting the performance for your WordPress instance. 

To determine if your server is writing to disk as a result of exceeding temporary table size, look at the following metrics: 

show global status like 'created_tmp_disk_tables'; 

show global status like 'created_tmp_tables'; 

The created_tmp_disk_tables metric indicates how many tables were created on disk, while the created_tmp_table metric tells you how many temporary tables have to be formed in memory given your workload. 

To determine if running a specific query will use temporary tables, run explain on the query. The detail in the 'extra' column indicates 'Using temporary' if the query will run using temporary tables. 

To calculate the percentage of your workload with queries spilling to disks, use your metric values in the formula below: 

(created_tmp_disk_tables / (created_tmp_disk_tables + created_tmp_tables)) * 100 

Ideally, this percentage should be less 25%. If you see that the percentage is 25% or greater, we suggest modifying two server parameters, tmp_table_size and max_heap_table_size. 

Calculate optimal parameter values 

To calculate the values required for tmp_table_size and max_heap_table_size, look at the total per-connection memory values and the base memory. The sum of per-connection memory parameters, excluding tmp_table_size, combined with the base memory accounts for total memory of the server.   

To calculate the largest possible size of tmp_table_size and max_heap_table_size, use the following formula: 

(total memory - (base memory + (sum of per-connection memory * # of connections)) / # of connections 

Total memory indicates the total amount of memory the server has across the vCores provisioned.  For example, in a General Purpose 2 vCore Azure Database for MySQL server, the total memory will be 5GB * 2, and in a General Purpose 4 vCore Azure Database MySQL server, the total memory will be 5GB * 4. More details about memory for each tier can be found in the pricing tier documentation. 

Base memory indicates the memory variables, like query_cache_size and innodb_buffer_pool_size, that MySQL will initialize and allocate at server start.  Per connection memory, like sort_buffer_size and join_buffer_size, is memory that is allocated only when a query requires it. 

Adjust parameter values for performance improvement 

Using the information above, we ran a few experiments using General Purpose 2 vCore and 4 vCore Azure Database for MySQL servers separately with a workload that consisted of ~90% read queries across 150 concurrent connections.  

First, we increased both tmp_table_size and max_heap_table_size. Next, since we increased tmp_table_size and max_heap_table_size, we also decreased innodb_buffer_pool_size.  The values we set were within the range: 

2vCore 

innodb_buffer_pool_size: 6442450944 - 6979321856  
max_heap_table_size: 41943040 - 54525952  
tmp_table_size: 41943040 - 54525952  

4vCore 

innodb_buffer_pool_size: 15111379968 - 16106127360 
max_heap_table_size: 114322780 - 134217728 
tmp_table_size: 114322780 - 134217728 

Making this adjustment improved performance in our read-heavy scenario and enhanced our out-of-the-box experience using WordPress with Azure Database for MySQL. 

How to provision a server with these optimized server parameters

To take advantage of the adjustments we've made, you can set the following resource tag on your General Purpose 2 vCore or 4 vCore server at time of server creation: 

• Name: AppProfile
• Value: Wordpress

We're always looking to improve, so let us know if you have other parameters you tune to get better performance with WordPress by leaving a comment below!  You can also provide us feedback on Uservoice.

Thanks for reading!

Mollee Jain & Yanwen Jin

Software Engineer, Azure OSS Databases team