Fantastic Failures: WordPress Web Farm

June 15, 2024 Leave a comment

MySQL and MariaDb has a cool feature called master-slave replication by log shipping. This is where you can always keep a near identical copy of a live database, on another. The master ships all changes to the slave server. And it is updated. This used to be a feature reserved for commercial RDBMS in my day (like I was from the age of the model T or something). So I thought I could have a farm of wordpress server with a MySQL readonly slave.

TLDR: Let me tell you. It might look like it works, but no guarantees. WordPress wants to save things, no matter whether you are just an anonymous user visiting or a logged-in user accessing a content asset. But for a few test requests, it looks like a readonly web-farm might work, but it isn’t worth the trouble to figure out if the hacks made are adverse or not, unless you need a web farm of WordPress servers serving millions of viewers.

The hypothesis:

6 visitors                3 servers
[visitor browser] --+    
[visitor browser] --+--> [wordpress anonymous 1 --user w readonly--> mysql slave 1] <--+ shipped logs
[visitor browser] --+                                                                  |
[visitor browser] --+--> [wordpress anonymous 2 --user w readonly--> mysql slave 2] <--+ shipped logs
[visitor browser] --+                                                                  |
[visitor browser] --+--> [wordpress anonymous 3 --user w readonly--> mysql slave 3] <--+ shipped logs
                                                                                       | 
2 logged in users                                                                      |
[content editor browser] --+                                                           |
[content editor browser] --+--> [wordpress logged in 1 --user w access--> mysql master]-------+ content changes

TLDR: The reality, is the grey arrows added to the diagram below, which mostly represents the concept to be implemented.

WordPress insists on writing to database, even with regular visitor. But the writes are isolated to 2 tables. Making exceptions for those 2 tables, seems to allows WordPress to work, with a mostly readonly MySQL user, rather than a MySQL user with read/write privileges

The Problems Discovered with this approach

In my experimentation, there are 2 tables that WordPress insists on writing to, upon visits to any page or post, despite the fact that the visitor is not logged in. 

WordPress database error UPDATE command denied to user for table 'wp_options' for query UPDATE `wp_options` SET `option_value` = '1715738331.5516290664672851562500' WHERE `option_name` = '_transient_doing_cron' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, _wp_cron, spawn_cron, set_transient, update_option

slavewordpress_1  | [Wed May 15 02:53:13.392931 2024] [php:notice] [pid 19] [client 172.20.0.1:39870] WordPress database error INSERT command denied to user 'wpslaveuser'@'172.20.0.10' for table 'wp_postmeta' for query INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (2332, '_oembed_a730927af9a1c3ea8dbb1571fc4f3764', '<iframe title=\\"How to Borrow Wi-fi (in case of shifty neighbors)\\" width=\\"500\\" height=\\"281\\" src=\\"https://www.youtube.com/embed/ju8mdjAkDjY?feature=oembed\\" frameborder=\\"0\\" allow=\\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\\" referrerpolicy=\\"strict-origin-when-cross-origin\\" allowfullscreen></iframe>') made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('wp-includes/template-canvas.php'), get_the_block_template_html, do_blocks, render_block, WP_Block->render, WP_Block->render, render_block_core_post_content, apply_filters('the_content'), WP_Hook->apply_filters, WP_Embed->autoembed, preg_replace_callback, WP_Embed->autoembed_callback, WP_Embed->shortcode, update_post_meta, update_metadata, add_metadata, referer: http://localhost:808/blog/simplified-diff/

slavewordpress_1  | [Wed May 15 02:53:13.395620 2024] [php:notice] [pid 19] [client 172.20.0.1:39870] WordPress database error INSERT command denied to user 'wpslaveuser'@'172.20.0.10' for table 'wp_postmeta' for query INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (2332, '_oembed_time_a730927af9a1c3ea8dbb1571fc4f3764', '1715741593') made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('wp-includes/template-canvas.php'), get_the_block_template_html, do_blocks, render_block, WP_Block->render, WP_Block->render, render_block_core_post_content, apply_filters('the_content'), WP_Hook->apply_filters, WP_Embed->autoembed, preg_replace_callback, WP_Embed->autoembed_callback, WP_Embed->shortcode, update_post_meta, update_metadata, add_metadata, referer: http://localhost:808/blog/simplified-diff/

slavewordpress_1  | [Wed May 15 02:53:14.007987 2024] [php:notice] [pid 19] [client 172.20.0.1:39870] WordPress database error INSERT command denied to user 'wpslaveuser'@'172.20.0.10' for table 'wp_postmeta' for query INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (2332, '_oembed_d230efdfe75e749283059556bb10e5e9', '<iframe title=\\"TP-Link 5GHz AC867 Long Range Outdoor CPE for PtP and PtMP Transmission | Point to Point Wireless Bridge | 23dBi | Passive PoE Powered w/Free PoE Injector | Pharos Control (CPE710)\\" type=\\"text/html\\" width=\\"500\\" height=\\"550\\" frameborder=\\"0\\" allowfullscreen style=\\"max-width:100%\\" src=\\"https://read.amazon.ca/kp/card?preview=inline&linkCode=kpd&ref_=k4w_oembed_sy88qEeOsuirGy&asin=B08D71HC9L&tag=kpembed-20\\"></iframe>') made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('wp-includes/template-canvas.php'), get_the_block_template_html, do_blocks, render_block, WP_Block->render, WP_Block->render, render_block_core_post_content, apply_filters('the_content'), WP_Hook->apply_filters, WP_Embed->autoembed, preg_replace_callback, WP_Embed->autoembed_callback, WP_Embed->shortcode, update_post_meta, update_metadata, add_metadata, referer: http://localhost:808/blog/simplified-diff/

slavewordpress_1  | [Wed May 15 02:53:14.010393 2024] [php:notice] [pid 19] [client 172.20.0.1:39870] WordPress database error INSERT command denied to user 'wpslaveuser'@'172.20.0.10' for table 'wp_postmeta' for query INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (2332, '_oembed_time_d230efdfe75e749283059556bb10e5e9', '1715741594') made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('wp-includes/template-canvas.php'), get_the_block_template_html, do_blocks, render_block, WP_Block->render, WP_Block->render, render_block_core_post_content, apply_filters('the_content'), WP_Hook->apply_filters, WP_Embed->autoembed, preg_replace_callback, WP_Embed->autoembed_callback, WP_Embed->shortcode, update_post_meta, update_metadata, add_metadata, referer: http://localhost:808/blog/simplified-diff/

which are tables wp_postmeta and wp_options.

The partial readonly user is actually the easiest of all the processes to check. Create a user (the WordPress docker installation process already does this for you, based on the username/password you provide in docker-compose.yaml) and only grant SELECT access to tables in the database(below). And use that user, for WordPress to access the database with. You will get errors. You’ll find granting the user the ability to update and delete on those 2 tables(2nd figure below), clears up the errors(above).

So this needs to be executed on the readonly replicas. First to make the WordPress database user, readonly:

REVOKE all privileges ON exampledb.* FROM `<wordpress-readonly-user>`@`%`;
GRANT SELECT ON `exampledb`.* TO `<wordpress-readonly-user>`@`%`

Then to grant those 2 tables the access, that WordPress insists on making.

GRANT update,insert,delete ON `exampledb`.wp_postmeta TO `<wordpress-readonly-user>`@`%`

These table updates seem to be some sort of metadata that is not critical to the operation of WordPress, though I can’t confirm it, and don’t feel comfortable proceeding further reading more thousands of lines of php, while unemployed. These changes have to be allowed for the visitor server, to be able to make changes to the slave MySQL databases. The thing is, slaves never send their changes to the master. It is always the other way around. So whatever changes those users make to those tables, will not just get lost, but those tables will lose sync w the master. The latter may not matter, b/c as soon as a change on the master is made, the changes is replicated to slaves and any changes made to those 2 tables are overwritten. But the concept of a readonly wordpress web-farm does seem to work, and based on the tenuous assumption that these tables don’t contain any information critical to operation of wordpress and are only used, to store information like : when the page was last accessed.

There is also another consideration. Creating a slave, isn’t as automatic as you think. You think, you can just go on the slave and tell it: be slave to database X on server Y, AND it will do it, blocking all access until it is done. No, you as a newly elected MySQL dba have to block all changes to the master, export all the data to a file, ship the file to the slave, import all the data in the file to the slave, and then manually sync the replication process by setting the log end point on the master, to the slave, and finally activate both. AND remind yourself that this process has to be done EVERY time you spin up a new webfarm. This is a manual process you have to write yourself, which may or may not be error prone. And is certainly time consuming to spin up a new webfarm, bc the entire data has to be transferred, by a process that was not designed to make it fast nor seamless.

Then there is the wp-content directory, that needs to be synced. WordPress doesn’t store it’s media (pictures, videos, uploads) into the database, which is a central location. Instead, as a odd design decision, it chooses to store the media on the file system. Which means all media uploads also have to be copied from the “master(or wordpress connect to master MySQL)” wordpress server, to the “slave” wordpress servers. This likely will be addressed w rsync. Or the media files have to be served from central location, like NFS server (a constraint I were trying to lift from the MySQL database, but database accesses are more expensive anyway, so the database replicas take read burdens off the master MySQL).

Plus this really doesn’t give you redundancy for failure of the master WordPress silo. Unless you plan to change one of the slave silos to the master, change the DNS, and re-sync all the slaves to new master.

This is a real issue to consider, as all the MySQL slaves refer to the MySQL master by it’s DNS name, which is specified in the MySQL master’s docker-compose.yaml entry. This means the DNS is managed by Docker. And you have to choose between the traditional way of changing the DNS in Docker of one of the slave containers, to a master MySQL and making it the new master. This is how it is done with physical hosts.

Or probably what probably is more apropos w the new technology of containers (or even VM’s), is committing(copying) the images of one of the slaves, copying it’s volume, and spinning up a new master with it.

However, with all this in mind, if you still want to experiment

with it, this is how you create a readonly web farm of docker containers, all replicated from a master WordPress…

BTW: AWS has actually done what I was experimenting with, and create a AMI with WordPress software, that seemingly works well with readonly replicas



However, I do not know what customizations or work AWS put into their WordPress image to support this architecture. Which seems to use AWS elasti-cache for single server memcache session server support, if it needs to be shared among servers. And has read replicas of the wordpress database in their AuroraDB product, instead of MySQL. And for sharing the wp-contents folder, NFS server functionality is provided by AWS EFS system

This is still the most common simple architecture for a wordpress farm:

* visitors/* logged in users        1/* servers
[visitor browser] ---------+    
[visitor browser] ---------+--> [wordpress anonymous/logged in 1] --user w full access--+ 
[visitor browser] ---------+                                                            |
[visitor browser] ---------+--> [wordpress anonymous/logged in 2] --user w full access--+ 
[visitor browser] ---------+                                                            |
[visitor browser] ---------+--> [wordpress anonymous/logged in 3] --user w full access--+ 
[content editor browser] --+                                                            |
[content editor browser] --+--> [wordpress anonymous/logged in 4] --user w full access--+---> [mysql master]---+
                                                                                        |                      | Replicated
                                                                                        |     [mysql slave]<---+
                                                                                        +---> [NFS share for wp-content]

or

Simple WordPress farm architecture, implemented in Docker Swarm, in earlier article

It involves several wordpress servers sharing the same MySQL database, and NFS share.

First, if you want re-assurance that master-slave replication works as a concept in MySQL. Go read this post(TBD), where I create 2 MySQL containers, and create a master-slave replication between the two, and insert a records, and check that it exists on the slave moments later. But the TLDR, is that the process has no practical purpose, but as a test case to see that master-slave replication works. You would rarely load your database server w 2 database processes, doing the exact same thing. A more practical purpose based on overhearing professionals talk, is to have the master and slave on 2 separate physical hosts in 2 different datacenters, to be able to promote the slave to master, if the master encounters a disaster such as hardware failure, or power failure, or network failure.

But rather than multiple WordPress servers, burdening one MySQL, the experiment here, is to have every WordPress Server, have its own copy of MySQL database, and wp-content media on local storage (below). So the master WordPress “silo” is only burdened with content updates, not visitor requests.

A conceptual different way of doing WordPress farm, distributing database reads among several databases, rather than 1

What is a silo?

No one else uses this term. I don’t even know what people in my own industry calls it. Cluster nodes? It is basically the minimum you have to add, to expand horizontal scaling of an application. Every time you want to add user capacity to WordPress blog, you have to add a minimum of a WordPress PHP server (but this doesn’t ease any burden off the MySQL server).

This article explores expanding WordPress farm capacity, by making the minimum you have to add, to add horizontal scaling, to add both a WordPress PHP server AND a MySQL database.

Now each WordPress “silo” is comprised 2 major software components. WordPress software, written in the PHP platform, installed on a HTTPD server (either apache2 or nginx) which manages requests from a Web Browser. And a MySQL database, which contains your content. A third component is a strange artifact that WordPress doesn’t save pictures and videos (what it calls media) in the database. Instead it stores it on the HTTPD server’s file system. So, you will notice, to export your WordPress posts, you need to export the posts, separate from the media. This is unimportant for this proof of concept, b/c though the software can be physically separated into two tiers, we plan to keep the tiers on 1 server. Each silo consists of 1 HTTP server and 1 MySQL server, and one silo per physical server, is our concept. Every time we plan to add extra web capacity, we add 1 physical server, with 2 containers, each containing 1 HTTPD server and 1 MySQL database, and both are replicated from the master silo host, as needed.

Steps

TBD… project in future

This seems to be an ideal experiment in docker swarm, but I haven’t figured out a way to tell swarm to only create 1 silo (I think Kubernetes calls this pods) only on 1 host. In swarm, you should be able to tell swarm to change network setting such as DNS, to communicate on swarm network, rather than external DNS service.

This likely will be a future article in TictAwf, if we find a solution to scale with.

TBD.

The MySQL master slave steps were referenced from here:
https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql
https://phoenixnap.com/kb/mysql-master-slave-replication

First, you need one copy of a working WordPress silo with content, running in two containers. Do not use working production machines or processes! Never do this. Always work on copies.

Second, create your own empty wordpress silo, with exact same container images as the working production one.

Third. Start the replication process between the working wordpress silo, with the empty one.

Fourth. rsync the master php container wp-content directory with the empty copy

Fifth. automate syncing the wp-content directory

Sixth. Automate the process with a single line, to create a new copy.
I called it quits here, as I decided it was impossible to spin up a new “silo” without major delays on the master MySQL. Plus I don’t know what happens if you lock a master MySQL, what happens to existing slaves’ replication (best case, they just delay replication). Plus unknown effects, of having 2 tables not synced with master.

Leave a Reply

Your email address will not be published. Required fields are marked *