I have around 30 years experience running web servers and for a very large fraction of that, I have been handling web sites that use MySQL or MariaDB databases. Over that time, I have encountered a number of things that totally clobber web site performance and needlessly increase load on web servers. These are largely specific to WordPress but the general behaviours that lead to them are applicable across the board.
Constant Database Updates
One of the biggest problems I see in web site code, and in some of the most popular WordPress themes and plugins, is updating information in the database on every single request. This often manifests as storing some sort of cached ephemeral information in the wp_options table or some private table used by the plugin or theme. Sometimes this is storing statistics to provide analytics. Whatever the reason, this will almost certainly completely tank your web site’s performance by adding database update queries to the process of generating the page to send back to the browser. Update queries have a substantially higher impact on a busy database server than simple select queries because they have to acquire a write lock of some kind, write the data, commit the data cleanly somehow, and release whatever lock was acquired in the first place. There can only be one writer at any given time. Contrast that with read queries like simple selects while only need a read lock. Unlike writes, there can be multiple readers accessing data at the same time.
So if you are a plugin or theme writer and you are doing update queries during the normal page delivery process, stop doing it. Yes, you. Even you. Stop doing it. No, you don’t actually have a Really Good™ reason. No, not even that. Don’t do it.
Poorly Constructed Queries
This one is, perhaps, even worse than update queries in the normal page generation path. These are basically endemic across just about every ecosystem and are not limited to WordPress. They are often even worse in systems that have Clever™ database handling schemes, and especially in systems using an Object Relational Model (ORM). These poor queries are often characterized by retrieving far more data than required from the database, often using “SELECT *” when only one or two columns are required. This can often be further exacerbated by retrieving all, or far more than required, rows from large tables. Pagination schemes often fall victim to this by not using the “LIMIT” clause in SQL statements but, instead, retrieving the entire result set and paring it down in PHP code or similar. There are, of course, plenty of other ways to construct terrible queries and there is no shortage of innovation in the field of terrible queries.
The problem with terrible queries is that they create more work for the database server, the communication link between the database server and the web server, or the code running on the web server. It often leads to substantially increased memory usage. (If you’re using a site builder or something that says to raise the PHP memory limit to multiples of a gigabyte, there’s a good chance something like this is contributing.)
Buffering and String Editing
This one is probably one of the biggest contributors to memory bloat and web site slowdowns. Basically, this is where the plugin or theme buffers all output and then runs various processing on it. This may even happen at multiple levels, thus running multiple passes of processing on the output before it even gets out of PHP and to the web server software to send to the browser. Site builder and WYSIWYG editors are some of the worst offenders here. This doesn’t seem to be much of a problem while your content pages are small or relatively simple, but as complexity and content size increases, this can have some seriously deleterious effects on web site performance. Where possible, it is usually better to generate content correctly the first time than to run multiple post-processing steps. Further, minimizing the amount of content that is buffered in memory can substantially reduce memory footprint and, thus, overall server resources required.
This one does have a large wibbly “well, it might be better sometimes” area to it, however. The important takeaway is that you don’t use buffering and post-processing unless you have to.
Loopback Requests
This final one is probably the one that makes me want to kneecap developers more than anything. A loopback request is when the site makes an HTTP request back to itself as part of the content generation process. That means that instead of one processing slot to display a page, each request to that page requires two processing slots. It gets worse if there are multiple levels of loopback requests.
You might be wondering why this is bad if your server has oodles of resources and isn’t busy. Well, if you happen to run out of processing slots, you can end up with a deadlock where one or more pages are waiting for a loopback request to finish but there are no processing slots available for those loopback requests. This may be web server processing slots, but it may also be PHP processing slots. It also needlessly increases the load on the web server and can lead to very hard to debug breakage on your web site.
This is one of those cases where if you ever find yourself doing a loopback request to generate page output, you are almost certainly doing it wrong. Yes, even that. No, that’s wrong too. No, not even that. Yes, very funny, but that means the rest of the site needs redoing so you don’t need a loopback request.
There are a couple of very limited cases where it makes sense to issue loopback requests and none of them are involved in normal web site page delivery.
Conclusion
There really isn’t much of a conclusion here. Basically don’t make inefficient queries, unneeded queries, loopback HTTP requests, or unneeded post-processing. And next time you complain because your WordPress (or Drupal or ….) web site is slow, consider whether you are using one of the many popular plugins or themes that is guilty of one or more or even all of the above. And that includes nearly every single “site builder” or “page builder” or kitchen sink all in one theme or plugin.
Rant mode disengaged.