cloud programming sysadmin

How I reduced a WordPress database size by 85% and memory consumption by 20x

I was helping a friend to troubleshoot their e-commerce site. It was running on WordPress using WooCommerce as the e-commerce backend. Like most WordPress sites, it was installed with a ton of plugins. My friend complained that the site performance has been getting slower and slower, to the point where a page load can take anywhere from 2-3 seconds to a failing to load at all. Getting to wp-admin also took forever.

At first, there are a lot of pieces to unravel, since the cause might be anything. The backend was running on AWS. The WordPress site is running as a docker container on the EC2, while the database is running on a RDS instance. It uses Cloudflare tunnel to connect the public hostname to the docker container. Seems like a decent setup.

While I do use WordPress (this site runs on WordPress), I am not a WordPress developer so I was not familiar with where things might go wrong. My first intuition was to check the plugins, since not all WordPress plugins are well written and some are notorious for taking up a lot of resources. Unfortunately isolating plugin resource usage by instrumentation was not possible as far as I know, due to the way WordPress/PHP works. After comparing the set of plugins with another site which did not exhibit the same behaviour, I decided to try other approaches.

I tried the usual tricks, like enabling proxying in Cloudflare, using a caching plugin, upping the EC2 instance size and RDS instance size. I even added a robot.txt to prevent bots from crawling the site for the time being. Those tricks helped a little, but did not resolve the problem.

Using docker stats, I noticed that CPU and memory usage is extremely high for the container, compared to others. CPU consumption is often >100% with every page load and memory usage spiked to 14GB after a while. Another unusual sign is the size of the database. For a site with around 500 products, the database size is >600MB.

That is when I chanced upon this article when searching for the symptoms.

The problem WordPress sites can run into is when there is a large amount of autoloaded data in the wp_options table.

If you return anything below 1 MB you shouldn’t be worried. However, if the result was much larger, continue on with this tutorial.

I ran the query in the article and it returned the following.

Wait. The autoload_size is ~570MB (!). I wrote a SQL command to find all the options which are larger than 1MB.

The results range from 1MB all the way to 13MB.

For the uninitiated, wp_options is akin to Windows registry, and it has become a dumping ground for plugins to store values that they might need. Most of the values in this option should be configuration values (like siteurl) which should take up just a few bytes. wp_option also has a field “autoload” which states whether the option should be loaded on every page. Storing 13MB in an option value and setting it to autoload is just insane. The total size of autoload options in the table turns out to be >500MB. Every page load is querying >500MB of data from the database and processing those data. No wonder the site is crawling!

Inspecting those options shows them most of them have the prefix _transient, which means they can be safely deleted. After making a backup of the database, I deleted all transient options. wp_options went from 556MB to 46MB, a reduction of >90%. The total database size went from 645MB to 84MB, a reduction of >85%. Memory consumption also dropped by 20x (from ~14GB to ~700MB). More importantly, the site is now super fast which is extremely important for an e-commerce site.

The results are very telling from the RDS dashboard.

Average CPU utilization has dropped to <3% and average database connections is now near zero.

Aside from noticeable performance boost for the site – average page loads within 1s – another bonus from these optimizations is that we can now use smaller EC2 and RDS instance types for better cost savings. Hopefully this article is useful as a reference for others in similar situations.

cloud sysadmin

New – Visualize Your VPC Resources from Amazon VPC Creation Experience | AWS News Blog

Finally. Amazon Web Services has released a new feature called Amazon Virtual Private Cloud (VPC) resource map, which simplifies the VPC creation experience in the AWS sonsole. This feature displays existing VPC resources and their routing visually on a single page, allowing users to quickly understand the architectural layout of the VPC.

The new VPC creation experience streamlines the process of creating and connecting VPC resources with just one click, even across multiple Availability Zones (AZs). The VPC resource map also allows users to quickly understand the architectural layout of the VPC, including the number of subnets, which subnets are associated with the public route table, and which route tables have routes to the NAT Gateway. Additionally, users can customize a Name tag per resource in the preview and easily change the default CIDR value and subnet mask. The Amazon VPC resource map is now available in all AWS Regions where Amazon VPC is available.

diy internet sysadmin

Switching from Windows to Linux Desktop

After years of procrastination, I finally did it. I am now using Linux not just on the server side, but as my primary OS, and I can’t be happier.

I have been a Linux user for most of my professional life, but my usage has been limited to the server side of things. Like most people, my working OS has been Windows from day one. There have been attempts to integrated the *nix way of doing things over the years: cygwin, git bash, WSL, running Linux in a VM under Windows. However, the user experience is clunky and there are always issues to work around.

It happens that the time has come to replace my primary working machine – currently a 5 year old notebook running Windows 10. I am a long time fan of the Intel NUC and thought it’s a good opportunity to kill 2 birds with one stone – setup a Linux desktop distro on the Intel NUC to try out the experience.


I managed to buy a 2nd hand Intel NUC (NUC8i3BEH), complete with 500GB SSD and 8GB RAM. It is not high-end or even mid-end by today’s standards, but I figured it should be good enough for testing.

The immediate problem is deciding which Linux desktop distro to install. And there are a lot of options out there. Since I’m familiar with Ubuntu I decided to limit my options to Ubuntu-based ones. I’m not a fan of the default Ubuntu experience with Unity. After much evaluation I decided on Linux Mint, as it is Windows-like, has LTS support, and does not use snap.


Downloading and install Linux Mint is straightforward. I chose the Cinnamon edition, as I wanted the default and up-to-date experience with Mint. On hindsight, I might have done better with Xfce, as it is uses less resources. More importantly, I discovered later that Cinnamon does not have the ability to restore applications (session restoration) after reboot, which Xfce does. It is not a showstopper, but would be a nice to have.

The default appearance and behaviour of Linux Mint is familiar enough that most Windows users would have no problem using it. However, I personally dislike the Mint start icon, and wanted to have a more Windows-like experience (the irony). Here’s what I did:

  • Change the start button
  • Change the trashbin icon
  • Configure a more Explorer look-and-feel
  • Change application icons (Thunderbird, Firefox)
  • Change padding around icons in the taskbar
  • Change shortcut for screen lock, workspace switch

Here is the initial result:

Linux desktop running Windows 10 in QEMU, Firefox, and Gnome terminal
Virtual desktop, or Workspaces, in Cinnamon

First Impressions

I am really surprised that everything feels so snappy. And this is on a low-end i3 processor from 5 generations ago. Going from cold boot to login screen takes 4 seconds. Booting a freshly installed Windows 10 in QEMU takes about 10 seconds. Firefox, Thunderbird, VS code all feels like they have been given a new lease of life. CPU and memory usage is low, compared to Windows 10 with the same number of applications opened. Bluetooth setup took a bit of getting used to, but after it is done everything just works.

Linux Mint comes batteries included, so as to speak. Some may not like it as it does have quite a number of applications that you may not use. But there are surprises like hypnotix which allows me to watch Bloomberg, CNN, CNBC for free, among others. It also comes installed with LibreOffice, which some may not like. Fortunately uninstalling software in Linux is normally a breeze. Mint comes with Software Manager, which makes finding, installing and uninstalling software very easy.

Other Setup

As with any new OS, there are lots of tinkering after the initial setup. Some other things I set up include:

  • Flatseal – extremely useful to manage flatpak permissions.
  • zram – extends swap with compressed RAM. Honestly I haven’t seen real benefits, but that’s probably due to the low memory usage at the moment.
  • Samba – QEMU comes with Samba, so it’s just a matter of configuring it to share my folders with other Windows clients.
  • Tailscale – Tailscale provides a way for all my devices to behave as though they are on the same network, even when they are not (eg. when I bring my notebook to office). It also works for phones. Read my other review.
  • Remmina – Remmina is a remote viewer client that supports RDP and VNC and it works better than the default Remote Viewer client in Linux Mint.
  • Barrier – virtual mouse/keyboard that works across Windows and Linux desktop.
  • Syncthing – to synchronize files across multiple clients, for situations where the device might be used in an offline environment.
  • PlayOnLinux – provides a persistent environment to run Windows applications in Linux via Wine.
  • x11vnc – Linux doesn’t come with Remote Desktop built-in. One popular option is to use one of the VNC servers. x11vnc is a non-commercial solution and is as simple as it gets.
  • Many others like Firefox, Chrome, Thunderbird, git, vscode, vim-gtk etc.

The Good Side

After using Linux Mint daily for 2 weeks, I have fully embraced it and notice I am not using my Windows notebook that much. Some benefits I noted so far:

  • Fantastic developer experience
    • docker, symlink etc just works
    • QEMU is amazingly fast, compared to VirtualBox
    • no more second class citizen using things like git bash, WSL
    • Gnome terminal replaces command prompt, git bash and PuTTy (no more PuTTy key conversion)
  • Control
    • no unexpected Windows update happening at the most inopportune time
    • no funny search indexer or software reporter running in the background causing CPU spikes
    • no disappearing disk space due to WinSxS
    • no more rebooting multiple times after installing applications
    • no more extra folders/files like System Information, $RECYCLE.BIN and Thumbs.db littered everywhere
  • Customization
    • almost anything can be customized to your liking. You might have to find the right docs though
  • Clean install/uninstall
    • you don’t think twice about installing software ‘cos you can always uninstall them cleanly afterwards


It is not all a bed of roses however. There are some gotcha moments too, some which are unexpected:

  • Installing software can be confusing for beginners, ‘cos there are so many ways to do it. You can do it either via a package manager like flatpak, snap, apt/deb, or portable style like AppImage, or adhoc-ly via tarball, curl/bash or compiling from source. It can also be hard to figure out where the config files are (/etc, .local, .config, dconf, within flatpak, etc.)
  • Flatpak packages do not have access to the host file system by default. So if you drag a file from the desktop to your Flatpak app it might not work. This is a common gotcha that will catch Flatpak newbies off guard. Thankfully, you can easily manage permissions using Flatseal.
  • The size of software packages installed can vary wildly depending on packaging type. In one rather extreme example, for the same package, it can take either 1.1MB or 2.3GB(!). More than 2000x difference!

  • Obviously the biggest drawback of a Linux desktop is the inability to run native Windows programs. Well, Wine does a pretty admirable job, but it cannot cover the huge surface area of the Windows API and ecosystem. Running stuff in a VM is sometimes not ideal. I end up falling back to my Windows notebook for the following software:
    • Microsoft Office (yes I know you may be able to run Office 2016 32-bit using Wine, but I don’t really want to go that route)
    • Hyper-V manager
    • SketchUp
    • 3DS Max
    • iTunes
    • Teams Microsoft actually has a Linux version of Teams. Good job!

Remaining Issues

There are some unresolved problems at the time of writing:

  • VPN gets disconnected after my NIC link goes down and up. I have yet to find a good way to restart VPN automatically in network manager.
  • XMind does not open a document that is double-clicked in Files. It just launches the application without opening the document.
  • There is an ever-so-slight initial delay when moving the mouse from rest. Not sure if it’s a bluetooth, driver, or window manager issue. Not a showstopper, but can be annoying.
  • Systray integration in Linux is surprisingly weak. The official Thunderbird doesn’t have systray integration, which means you can’t tell when there’s a new mail or how many unread mails there are. There are unofficial solutions like BirdTray but it doesn’t seem to work with the latest Thunderbird versions.


Given the list of issues I’ll still take Linux over Windows any day. The amount of control – and peace of mind! – you get is irreplaceable. Not to mention good performance, low CPU/memory footprint, amazing developer experience, and stability you get (bye to BSOD). For those who are sitting on the fence, my advice is this: don’t wait! There might be a bit of learning curve, but it is well worth it.

cloud sysadmin

Granting AWS billing access to IAM (non-root account) users

By default, IAM users will not be allowed to access the Billing dashboard. This is true even if the user has AdministratorAccess permission. If you use AWS as a non-root/owner account user, but require access to billing and payment, here’s how you can do it.

Create billing IAM policies

  1. Go to IAM:
  2. Select Policies > Create policy
    1. Choose a service > Enter “Billing”
    2. Check All billing actions
  3. Review > name it “BillingFullAccess” > Create policy

Attach billing policy

You can attach billing policy to users or user groups. For simplicity, let’s assume we are applying it a user.

  1. Go to IAM:
  2. Select users > choose the user that you want to apply
  3. Select Add permissions > Attach existing policies directly
  4. Check BillingFullAccess
  5. Review > Add permission

Activating access to the AWS billing console

From AWS documentation,

By default, IAM users and roles within an AWS account can’t access the Billing console pages. This is true even if the IAM user or role has IAM policies that grant access to certain Billing features.

The last step is to enable this permission. To do so,

  1. Sign in as root/account owner
  2. Click on your username on the top right and select Account
  3. Scroll down to IAM User and Role Access to Billing Information
  4. Click Edit, check Activate IAM Access
  5. Update

And it’s done. You can now login as the IAM user and access the billing dashboard.

cloud security sysadmin

New for Amazon GuardDuty – Malware Detection for Amazon EBS Volumes | AWS News Blog

Once configured, AWS GuardDuty will now scan EBS volumes for malware and report its findings when it detects suspicious activities. If you haven’t enable it already, you should do so.

When you have GuardDuty Malware Protection enabled, a malware scan is initiated when GuardDuty detects that one of your EC2 instances or container workloads running on EC2 is doing something suspicious.

cloud sysadmin

AWS Perspective | Implementations | AWS Solutions

AWS just released AWS Perspective, a new tool to help you create diagrams about your AWS workload/architecture.

In a way this is long overdue. There are quite a number of companies in this space already. AWS’ version supposedly is better integrated and allows you to link to the resource directly in AWS console.

It seems to be quite useful, though it doesn’t come cheap. Someone posted on Twitter that the cost estimate comes up to USD500+ (every month) to use it.

network sysadmin

Is Your Cat 6 Cable a Dog? — Blue Jeans Cable

The state of Cat 6 cables sold in the market is appallingly bad.

Plainly enough, most of these cables aren’t designed to meet Cat 6 or 6a specifications, and they’re certainly not tested for compliance before leaving the plant.

Source: Is Your Cat 6 Cable a Dog? — Blue Jeans Cable

security sysadmin


A collection of eye-opening NTFS tricks to do things that you normally can’t.

TRICK 1: CREATE FOLDERS WITHOUT PERMISSIONS (CVE-2018-1036/NTFS EOP) On Windows you can assign “special permissions” to folders like permissions that a user is allowed to create files in a folder, …

Source: MOV AX, BX Code depilation salon: Articles, Code samples, Processor code documentation, Low-level programming, Working with debuggers WINDOWS NTFS TRICKS COLLECTION


Apache vs Nginx: Practical Considerations | DigitalOcean

A comparison of Apache vs Nginx and what they are suitable for.

Apache and Nginx are the two most common open source web servers in the world. Together, they are responsible for serving over 50% of traffic on the internet. Both solutions are capable of handling diverse workloads and working with other software to

Source: Apache vs Nginx: Practical Considerations | DigitalOcean

cloud sysadmin

AWS EC2 Virtualization 2017

A very good summary of the advancement of virtualization technologies used in AWS EC2. The newest instance type offered is simply AWS Bare Metal, which provides all the hardware access with little performance overhead, while still retaining the benefits of cloud – elasticity etc.

AWS EC2 Virtualization 2017: explaining the different virtualization types, from emulation and binary substitution, paravirtualization and Xen, PV, HVM, and PVHVM modes, and the new Nitro hypervisor