Lance Cleveland

Aurora MySQL Storage Monitoring & Reduction

Using Aurora MySQL is a great way to host a MySQL database at low cost while providing a scalable and fault tolerant environment. Once you’ve setup your multi-zone cluster AWS takes care of much of the scaling for the database server. One of the components it manages automatically is the volume, or disk storage, up to a maximum of 128TB. However, this does not mean volume storage is fully managed.

All AWS RDS implementations have several components that can impact the monthly billing. While things like CPU and memory are based on the workload and often require application restructuring to reduce consumption, disk space — or volume storage — can often be reduced with routine maintenance.

Implementing routine maintenance to reduce volume consumption should be part of a monthly health-check for your AWS services.

Checking The Aurora MySQL Volume Size

The easiest way to check the amount of storage being used by the nodes in the data cluster is to use CloudWatch. You can follow this AWS article on How To View Storage to view the volume sizes. For monthly reviews, add a Number widget for this metric to your main Cloudwatch dashboard.

The Store Locator Plus® CloudWatch dashboard.

Checking MySQL Database and Table Sizes

You can check table sizes with this MySQL command:

SELECT *
FROM   (SELECT table_name, TABLE_SCHEMA, ENGINE,
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `Size MB`
        FROM   information_schema.tables
        GROUP  BY table_name) AS tmp_table
ORDER  BY `Size MB` DESC;

Checking database sizes in MySQL can be done with this command:

SELECT *
FROM   (SELECT TABLE_SCHEMA,
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `Size MB`
        FROM   information_schema.tables
        GROUP  BY TABLE_SCHEMA) AS tmp_table
ORDER  BY `Size MB` DESC;
Our database sizes

Additional File & Table Size Commands

Temp files in Aurora MySQL

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files;

Table and fragmented space (GB)

SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

Reducing Volume Size

Yes, Amazon did add automatic volume reduction as part of the Aurora MySQL and PostgreSQL implementations a couple of years ago, but there are some caveats. First, deleting records from a table does not release the disk space; MySQL keeps that space for future inserts and updates. Second, Optimize Table should help but it temporarily increases volume usage as it replicates the table during the process; Also InnoDB tables — the default unless overridden should be auto-optimized so you won’t gain much if anything from this step.

This Stack Overflow post has some great details about MySQL and volume storage.

Dropping unused tables will reduce volume size IF innodb_file_per_table is on. It should be for default Aurora MySQL instances. You can check that setting with this MySQL command.

show variables like "innodb_file_per_table";

Cloning a database with dump/restore and dropping the original will reduce volume size and may be the only option to “reclaim” space if a lot of records have been dropped. You’ll be doubling volume use during the operation, but once the original source database is removed you should see a reduction in space.

You may be interested in following this AWS article on performance and scaling for Aurora DB clusters as well.

Or this article from AWS on troubleshooting storage space.

Testing MySQL Connectivity From WordPress Docker Containers

cargo container lot

Moving forward with feature development for the Store Locator Plus® SaaS service includes moving forward with our tool kits and environments. Docker containers have become a bigger part of our development toolkit. They make for rapid deployment of isolated environments for testing various concepts without the overhead of virtual machines. While we have been working on Virtualbox virtualized machines for years, Docker is far faster to spin up and far less resource intensive.

Along the way we have found the need to perform deeper dives into the interplay of Docker, WordPress, and MySQL. The latest endeavor, discovering why our WordPress container is not communicating with our AWS hosted Aurora MySQL test data set.

While we can connect to our Aurora MySQL database using the credentials is our docker-compose YAML file, the WordPress instance running in docker on localhost is throwing connectivity errors.

Assumptions

For this article we assume a rudimentary knowledge of Docker, a baseline container with WordPress installed — likely from the default WordPress image, and a GUI interface such as Docker Desktop for managing instances and providing quick access to the shell command line of the container.

We also assume you have a docker-compose.yaml config file that is used to startup the container that looks similar to this:

version: '3'

services:

  wp:
    image: wordpress:6.0.1-php7.4-apache
    container_name: wp-slpsaas
    platform: linux/amd64
    ports:
      - '80:80'
    environment:
      WORDPRESS_DB_HOST: 'some_rds_instance.c0glwpjjxt7q.us-east-1.rds.amazonaws.com'
      WORDPRESS_DB_USER: wp_db_user
      WORDPRESS_DB_PASSWORD: '9aDDwTdJenny8675309'
      WORDPRESS_DB_NAME: wp_db_name

And you’ve started the container either via Docker Desktop or the command line with
docker-compose up -d

Installing MySQL Client On Docker WordPress Containers

Connect to the shell for the container after the container has been started. The easiest way to do this is with Docker Desktop and clicking the shell icon for the instance.

Our SLP SaaS container running WordPress in the Docker Desktop. The red arrow is the icon to connect to the container’s shell command line.

By default the WordPress Docker image does not contain very many tools. That is to be expected as the whole idea behind the containers is lightweight environments. As such we’ll need to install the MySQL client with apt:

apt-get update
apt-get install default-mysql-client

Checking Connectivity To The WordPress Database

With the MySQL client installed we can now test connectivity from within the container to our AWS database directly. This ensures our network configuration and docker-compose environment variables are set properly to communicate with the database.

mysql -A -h $WORDPRESS_DB_HOST -u $WORDPRESS_DB_USER --password=$WORDPRESS_DB_PASSWORD $WORDPRESS_DB_NAME

MySQL > show tables;

If you get a list of WordPress tables the connectivity is working.

Hosting WordPress On AWS

worms eye view of spiral stained glass decors through the roof

As fate would have it, Summer 2022 has come with some life changes that include a renewed focus on the long-established business at Charleston Software Associates (CSA). In the decade-plus that the company has been in place there have been several key shifts in focus including the launch of Store Locator Plus® as a multi-faceted location mapping software and SaaS platform. Now, as the Store Locator Plus® business is back as the center of focus for the company, it is time to rebuild the infrastructure that supports it; Including moving all non-locator related assets off of the primary server cluster that “host all things CSA”.

Among the many changes that are underway, moving websites hosted on a classic shared web server to independent hosting on AWS is being implemented.

Existing Website Environment

Many of the websites that are hosted on the shared CSA web presence servers are independent entities unrelated to the Store Locator Plus® project. These sites are hosted on a cluster of EC2 instances with a virtualized web service in place. The configuration works very much like most web hosting companies; One big server (or cluster) with many websites stored in different directories that are managed by the web services configuration.

As such, the details provided below should be nearly identical for any WordPress site hosted at a web presence provider. While some steps may be different, the details provided herein should provide insight into hosting WordPress on AWS.

WordPress On Lightsail

While there are several ways to get WordPress up-and-running on AWS, including using independent servers like EC2, Lightsail is by far the easiest to work while providing extra services every web presence should have.

WordPress instances on Lightsail start at a super-low price of $3.50/month (July 2022). This is vary competitive with nearly any self-managed WordPress solution. In reality, most people never utilize the additional services of a fully managed WordPress installation. The only feature some of those “fully managed” WordPress services offer that is not readily available on AWS Lightsail is a one-click “mirror this site on a staging site” option. For full production business sites this can be useful if your site appearance of software stack is often changing. For most sites hosting simple blogs (like this one) the service is never used.

AWS Lightsail provides a simplified interface for managing your web instance. It includes the ability to add advanced features from the AWS services with a few clicks. The initial configuration sets up a basic web server that is deployed on AWS container services; Don’t worry you don’t need the details as they take care of the setup and management for you. The default configuration will install and configure the services (web server, database, installing WordPress, the OS) needed for a full WordPress hosting presence within a few minutes of creating an instance.

One the service is up and running you can add extra features such as a static IP address to make DNS routing easier. If you business grows you can easily add things like cloud front distribution, a shared database instance and load balancer if the site gets popular enough to need multiple servers. Lightsail walks you through it. If the website gets features on a runaway Tik Tok viral video you can even convert it to the full-fledged EC2-based service stack with minimal effort.

Moving An Existing WordPress Site To Lightsail, Export Tool

AWS provides some great documentation to assist with moving an existing WordPress site over to Lightsail. However, this methodology does NOT copy all the software onto the new server and thus requires extra work to get things “exactly the same”.

The basic steps:

  1. Export your existing site content and download the XML file
  2. Create a Lightsail instance for WordPress
  3. Fetch your WordPress login (username: user , password: stored in a file on the server you get to with a web-based SSH console)
  4. Login to the new instance and import the content
  5. Optional (sort of) assign a static IP to the new lightsail instance
  6. Update the website’s DNS record to point the domain to your new lightsail instance.

Necessary additional steps:

While the instructions above will bring over all of the data you need for the site, what it does NOT do is install all the secondary software that was on the main site. As such you’ll need to:

  1. Install themes from the original site
  2. Install plugins from the original site
  3. Drop any unused themes or plugins that came pre-installed on the Bitnami Lightsail stack

Additional steps to consider:

Since the images provided by Bitnami are generic, there are some tweaks that are common before publishing the site to the world.

  1. Add an SSL certificate via the SSH login
  2. Remove the Bitnami badge from the site via the SSH login
  3. Add a second admin login/password only known to the site owner
  4. Deactivate the user account

Moving An Existing WordPress Site, All-In-One by Servmask

An alternative route for copying over the website is to use the All-In-One WP Migration plugin by Servmask. This tool comes with the default Bitnami image that is installed by AWS Lightsail, saving an additional step on the new server. (Note: I am not affiliated with Servmask or this plugin. Use at your own risk.)

Basic steps:

  1. Install the All-In-One WP Migration plugin on your original site.
  2. Export the site, I prefer to a download file but they have paid extensions to export to many places like S3 or Dropbox.
  3. Create your new Lightsail instance
    1. Assign the instance a static IP once is is running.
  4. Login via SSH to get your password for the admin user “user” on WordPress
  5. While you are there you will likely need to increase the PHP file size limit if your export is more than the 80MB limit all Bitnami WordPress Lightsail images come with and restart the services.
  6. Import your site via the download file — this method also gets all plugins and themes from the original site
  7. Point your domain name to the new static IP.
  8. Configure the SSL certificate.
    1. Login to the Lightsail instance with SSH
    2. sudo /opt/bitnami/bncert-tool

Summary

While AWS Lightsail may not be perfect for every WordPress-based website, it does a great job at providing a solid web presence for most businesses. It can easily be backed up and replicated for testing purposes. The container spin up very quickly. It is low cost. And despite the container-based-all-in-one solution, it performs extremely well even under high volumes of traffic.

Unless you are running a SaaS service, update your website frequently, or a running a web presence for a company with millions of page views every month — Lightsail may work for you. Anyone can get started with this service with minimal technical skills required.

Hosting WordPress on AWS Lightsail is worth a try next time you need a WordPress site.

React Build Settings on Amplify

man jumping on intermodal container

The technology stack that was inherited from the prior tech team at Research Blocks came with some outdated methodologies. One of those areas was in the deployment of React applicationa on EC2 instances. For Single Page Applications (SPAs), like React, there are better options for rapidly deploying a scalable production app. The Amplify environment also makes it very easy to stand up multiple instances of the application container. These canrepresent different builds such as a development, staging, or test environments.

While there is plenty of documentation available on how to go about building an React app, there is very little on problem solving or details on what is going on “inside the black box”. This article is a collection of notes on what was found delving deeper into the AWS tech stack for Amplify.

Amplify Hosting

The first thing to know about Amplify is it is a hosting service for STATIC WEB ASSETS; as they like to say “Amplify Hosting provides a git-based workflow for hosting full-stack serverless web apps with continuous deployment.”. In other words, it does not work like a traditional web service such as Apache or nginx.

Amplify uses CI/CD to continually monitor your repo, clone that code when a branch changes, run commands specified in the Amplify console Build settings, and copies the files out to a CDN. From here, Amplify serves up the entire thing as a static web page. For React apps that typically means it zips up your build folder including an index.html loader and stack of JavaScript files compiled via a utility such as webpack.

This also means it is not going to do well with actively listening to incoming web request. Amplify is not going to play a good host as an Express server. There are ways to do it using Lambda functions and some additional configuration that will be content for another article.

Build settings and Amplify.yml

Build settings determine what commands are run to build your app after the code repo has been cloned. Normally this includes the install command to install a million mostly-useless node files into your project; These files are necessary so it can leverage twenty-lines of actual useful functions needed to make your JavaScript app work. The build command then links all that code together, runs through something like Webpack to rip out lot of that useless previously-downloaded Node code, and ends up with a somewhat useful app for your customers.

While you can enter the various commands in the YML formatted Build settings on the Amplify console, learn from my mistakes and instead put an amplify.yml file in your source root directory. That way when you inadvertently tell it to start the node server within the build file you can go back and look at an older commit and revert to the working copy of the file.

Regardless of which method you use, a simple Amplify.yml (Build settings) should look similar to the following image.

What To Put In Build Settings

The build settings should only contain the commands that are needed to download required code libraries and subsequently compile the code into a set of static files. This is where your React app’s package.json comes into play; package.json will tell Node which modules are needed for the yarn install command (we use yarn vs. npm) as well as how to build the static files for the SPA with the yarn build command. In our case, run webpack and set some environment parameters.

What NOT To Put In Build Settings

Do NOT put in any commands that START a node server into the build settings. The build manager for Amplify looks for any specified commands to finish executing. Starting a node server runs a command that , as far as the “build monitor” is concerned, never finishes.

The following is an ASSUMPTION about how Amplify works. I have yet to see official documentation that states the following…

LC

Remember, Amplify is going to stand up a static website for you. Along the way it will setup an https/http listener and route traffic to your Single Page Application accordingly. There is no need for a node server in this situation. Amplify is going to take over the responsibility for the apps it hosts.

Artifacts

Not super well documented, but again based on observations and assumptions, the main setting here is the baseDirectory. This tells the Amplify build manager “copy all the files under the specified directory and go through them out on the CDN as a static file set”. The subsequent files specification is a ‘stack’ of file paths you want included where the **/* specification means “any file in this directory and anything under it”.

Amplify’s build manager seems to us this setting to execute a zip command which grabs all the files in the directory and files list specified, zips them up and distributes it to the CDN.

Cache

The cache section tells Amplify which directories and files to carry over from one build to the next. While most of the building/distribution manager services on Amplify start with a fresh container, anything in the cache appears to be copied from one build to the next.

Almost all the Amplify documentation cites putting node_modules/**/* in this list to save the previously-mentioned yarn install command from having to download the same million-plus node files on every build. Instead it copies them from one container to the next saving all those electrons from flying around Internet servers to recreate the files every time. Instead the electrons all stay within Amazon’s borders and maybe speeds things up a bit.

On to bigger & better things… next up trying to see if we can trick Amplify into hosting an Express server WITHOUT lambda proxy functions.

MacOS Monterey Node Port 9000 Refused

I’ve been working on a React App for months that connects on localhost port 9000. Recently the app stopped working after an upgrade to Monterey. Turns out the upgrade also required a number of services to be re-installed via brew. One of those services, php-fpm, is now taking over port 9000 automatically on startup despite not having explicitly set the updated PHP version to run at start.

As such, opening http://localhost:9000/ on the browser was routing to PHP apps and not the node app I was looking for.

MacOS Listing Port Usage

The first step to finding what other services might be fighting with the node app is to run the lsof command.

sudo lsof -i :9000

The :9000 should be replaced with whichever port you are investigating. Port 9000 is the default for most node servers.

In my case the results looked like this:

This tells us that php-fpm will first answer any port 9000 queries on IPV4 localhost.

Node server has been relegated to only answer port 9000 on IPV6.

The node server on IPV6 can be verified by pointing the browser to http://[::1]:9000/ and connecting to the app.

[::1] is the IPV6 syntax for localhost.

Listing Homebrew Services

To stop the php-fpm service it needs to be managed via brew since that is where I started the service from in the first place. The service php-fpm does not exist, so what is calling it? Let’s check brew services…

brew services list

Turns out is is the php@7.4 service causing the issue.

Stopping Brew Services

Now that we know which service is fighting with node server we can stop it.

brew services stop php@7.4

That stops php-fpm from intercepting port 9000 traffic on localhost and the original http://localhost:9000 access I was running previously is now back in action.

%d bloggers like this: