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.
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;
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.
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:
- Export your existing site content and download the XML file
- Create a Lightsail instance for WordPress
- Fetch your WordPress login (username: user , password: stored in a file on the server you get to with a web-based SSH console)
- Login to the new instance and import the content
- Optional (sort of) assign a static IP to the new lightsail instance
- 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:
- Install themes from the original site
- Install plugins from the original site
- 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.
- Add an SSL certificate via the SSH login
- Remove the Bitnami badge from the site via the SSH login
- Add a second admin login/password only known to the site owner
- 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.)
- Install the All-In-One WP Migration plugin on your original site.
- Export the site, I prefer to a download file but they have paid extensions to export to many places like S3 or Dropbox.
- Create your new Lightsail instance
- Assign the instance a static IP once is is running.
- Login via SSH to get your password for the admin user “user” on WordPress
- 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.
- Import your site via the download file — this method also gets all plugins and themes from the original site
- Point your domain name to the new static IP.
- Configure the SSL certificate.
- Login to the Lightsail instance with SSH
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.
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.
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.
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
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.
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.
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.
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.
Lesson learned — if you are using EFS on production systems you want to be using provisioned throughput mode.
But, before we get into that, let’s go over the details of this implementation…
We utilize AWS EC2 instances to run multiple WordPress sites hosted in different directories. The configuration is fairly standard: 2+ servers configured as part of an load-balanced cluster. The servers run from the same image meaning they use the same underlying software stack.
Part of that image includes a mounted EFS (Elastic File Storage) directory , used to share WordPress resources between all nodes in the cluster. The original architecture was designed to host not only the typically-shared wp-content/uploads folder of WordPress via this EFS mount but also the code. The thought was that sharing the code in this way would allow a system admin to easily update WordPress core, plugins, or themes from the typical wp-admin web login. Any code updates would immediately be reflected across all nodes.
EFS Web App Code Hosting – A Bad Idea
Turns out this is a bad idea for a few reasons. First of all, EFS volumes are mounted using the NFS4 (network file storage) protocol — this defines how the operating system handles file read/write operations for a network mounted drive. While NFS4 is fairly robust, the throughput of ANY network drive, even on a high speed AWS data center backbone, is much slower than a local drive such as an EBS volume.
However, the bigger problem comes to light if you happen to choose the default, and pushed as “the mode to use” by Amazon, EFS throughput mode known as “Burst mode”.
Creating a new web application that resides on an AWS load balanced cluster is easy with the Elastic Beanstalk assistant. That is a great solution if you want to run every web service or application on their own instances. It is not a great fit for complex environments like the one being used for Store Locator Plus®.
Store Locator Plus® has several environments running within the same master domain. Multiple servers and load balancers creates a security certificate nightmare. Not too mention it starts racking up EC2 server fees quickly if they each became their own cluster. The better option is to retain a server instance that allows us to run our SaaS offering, our buy-and-own plugin store, our documentation site, and our demo site from a single disk image. We want to setup a full EC2 Load Balanced Cluster to gain the benefits of horizontal scaling on a server hosting multiple domains and web apps.
While this is easy to do with a single EC2 instance that hosts multiple host names for the storelocatorplus.com domain, making it scalable under load is the trick. It turns out Elastic Beanstalk is not a good fit. Instead we need to build a load balanced cluster “from scratch”. We’ll need to combine a machine image from a running server with a Launch Template. We will need an Application Load Balancer that will have instances attached and detached automatically from an Auto Scaling Group that we will also create.
Our environment also has a configured EC2 instance to run the web application stacks, mostly WordPress, locally on an EBS volume that uses an Amazon Aurora MySQL RDS database in multiple zones for performance and reliability. These two features make it easy to replicate the disk image for the software portion and maintain a persistent DB store across all instances.Read More