If you host your site with a managed hosting provider, then this article is probably not for you. This is for the brave souls who stray from the beaten path to manage their own servers and setup their own databases.
I ❤️ Databases
Prior to working on membership systems, I got started with dBase3, and then later I was a Unix Database Administrator (DBA) working with NCR Unix and Unify back when big servers were 16Mhz and had 16MB of RAM (yes, megs not gigs). Later the first membership systems I managed were built on TBBS which was based on a dBase like system and ran ontop of MS-DOS. While the hardware has grown in power astronomically, there is a clear and bright line of lineage from those older databases to our modern systems.
It may seem hard to imagine servers with so little resources, but that mindset and practice of conservation and keeping an eye on squeezing as much performance as possible has served me well even as systems grew, because the demands on those servers has grown far faster than the servers themselves with the rise of the Internet.
Data Sources – Fastest to Slowest
The faster the resource, the less space it typically has to store your data. Because of this it’s always a balancing act to keep data as fast as possible without overloading your individual systems. It is always our active design goal to keep our data in the fastest, but most efficient location, while using strategies like lazy and just in time loading, caching, memoization and rollups to maximize the use of our faster resources.
Whither Database
Historically WordPress was built on top of MySQL. MySQL was created by the company MySQL as a successor to mYSQL in 1995. Over the years became the world’s mot popular open source database, eventually transforming into an enterprise solution used by companies like Google and Facebook. In 2008, Sun Microsystems acquired MySQL, and was then acquired by Oracle in 2009.
Although Oracle kept MySQL open source, there were growing concerns of bad behavior on the part of Oracle’s stewardship since Oracle’s primary revenue stream was from a highly priced database solution. As a response to the conflict of interest that Oracle represented, MariaDB was forked from the main MySQL codebase in 2009.
MariaDB is a faster, more featureful version of MySQL that maintains compatibility.
Memberium and WordPress work with both databases, we recommend MariaDB if your web host offers it, or if you’re setting up and managing your own server.
As MariaDB continues to pull away from MySQL in capabilities you’ll want to migrate sooner rather than later to minimize your headaches.
Storage Considerations
One of the best things you can do for your database is to upgrade your disk storage from traditional hard disks (using spinning magnetic platters) to solid state drives (SSD). SSD drives are essentially very, very fast flash drives that can easily and more consistently outperform magnetic platter drives by a factor of 100x.
In addition to their increased throughput performance, SSD drives behave more consistently than magnetic platter drives. Magnetic drives are much like a CD player or record player; requiring the read head of the drive to be over the spinning data on the disk. To compensate for this, drive makers have made faster and faster spinning disks, which originally started at 3600RPM and now exceed 15,000RPM. There are upper limits to how fast these drives can spin and this limits their ability to find and load data on demand. In addition, any given piece of data is often scattered around the disk, not together in one nice packaged block, so the heads must seek across the spinning platter, like the needle of a record player to find the next piece of data.
Solid State (SSD) to the Rescue
SSD Drives eliminate all of these physical limitations by replacing the spinning platters and read heads with memory chips The memory chips provide massively faster data delivery. In addition because all memory can be accessed equally quickly, the latencies caused by trying to position the heads over the spinning disk are completely eliminated.
If you are stuck with magnetic disks, there are still ways to maximize performance, SATA 3, RAID based striping and other strategies can lift you above the common hard disk if you’re not ready to make the quantum leap to solid state.
I have a lot of RAM, do I need SSD?
Let’s not beat around the bush… YES! RAM is great for holding your running code and the data you’re currently using, however it’s not permanent storage. At some point your database needs to be saved to your storage system so that it’s protected in the event of power loss or other unscheduled reboot. SSD storage provides a slower (but very, very fast) storage tier for your data behind your main memory. If you’re serious about your site performance, SSD is a must-have in your speed arsenal.
Where to find SSD-based Web Hosting
SSD based hosting is increasingly popular and inexpensive, with providers like WP Engine (our preferred choice), Digital Ocean, Amazon AWS, and LiquidWeb all offer SSD based solutions.
Memory
More server memory is always to your advantage. Memory is a heavily shared resource between the code running on your server (database, web server, PHP, etc.) and the data that those programs are using to operate.
Additionally, your database system can be configured to use main memory for index buffers (more on that later) and query caches.
Processing Power
Most databases’ performance is really not driven so much by CPU power. CPU’s are really good at moving relatively VERY SMALL amounts of data inside themselves at blistering speeds. Significantly slower, is their ability to move data between your main memory and the CPU to be processed. CPU power has more of an effect on scaling your code then scaling your data. Of course your code is manipulating that data, but for our purposes it’s a smaller factor.
CPU’s (and more of them) isn’t a silver bullet however. Your database can only handle a certain number of simultaneous requests based on the number of processors you have, and how fast queries can be completed. Database queries seldom lend themselves to being divided among multiple processors, so a slow running query may completely saturate your storage bandwidth AND tie up a processor,.
Minimizing Contention
Like anything else, you want to minimize multitasking as much as possible at every level of the system. Dedicated servers are preferrable to VPS servers, which are preferable to shared hosting. Dedicating your database to your website is preferable to having several applications running on the same database. Anything that competes with your database software for CPU, memory or IO is something that will slow down your site.
The corollary of this rule is that if you can setup a master-slave database system, that’s preferable to running a single database server, since different database nodes can serve different requests, and ideally you can also isolate writes from reads altogether.
Indexes
Some of the most common fears we hear revolve around database size. People naturally fear that a larger database is like a larger full garage, harder to sort through and slower to find things. They look at large multi-gigabyte data tables, or their many page revisions and focus on that as a problem to be solved. Unfortunately it’s largely not that simple, and large databases with millions or 10’s of millions of records don’t necessarily imply a slow database. Querying out all of this data will be slow, but fortunately Memberium like most database applications never queries out ALL the data, just the most specific data it needs immediately.
The Solution
The secret to this is the index. Each database table has several indexes that provide a super fast way to look up the exact desired data you want from the millions of other rows of data in the system. If a database query is performing slowly, then the most likely cause is a failure to use an index for any number of reasons, including:
Most of these are pretty obvious. Any time your database can’t find a matching index, it has to scan through every single record in order to find the ones that match your criteria. This is a very slow, very resource intensive process known as a full table scan. Full table scans can impact not only the immediate query, but the rest of your system as the disk IO is saturated with data being checked.
Cardinality
Indexes with low cardinality represent a similar problem. Cardinality is a measure of how unique the data in the index is. For example if you have a lot of identical data, then it’s harder to find the unique piece that you want because you have to sort through more of it to find the record you want. High cardinality is the ideal because the minimum number of records to sort through is retrieved with the least effort and cost possible.
What’s often overlooked is that not only is it beneficial to cache your database data, it’s also very beneficial to cache your indexes. The more of your indexes can be buffered into memory the faster your sorting process will be, and the faster your data retrieval is.
How Memberium handles Cardinality
In the Memberium code we always strive to only execute queries based on indexes to ensure the queries are as fast as possible. We use highly unique pieces of data such as the WordPress user id, Infusionsoft contact id, and the member’s email address to quickly look up and isolate the data we want to retrieve.
Memberium Database Tables
Memberium installs several database tables that can be shared between multiple sites on a single or multiple Infusionsoft applications. These tables start with “memberium_”. The i2SDK also installs several tables which can also be shared and start with “i2sdk_”.
Most of the Memberium database tables grow very slowly or not at all. They store information that grows at a slow rate, such as individual invoices, tags, tag categories, actionsets and other data that is slow to look up from Infusionsoft when it’s needed. There is no configuration data stored in these tables, and you can safely truncate (empty) them as needed. They data in them will be repopulated as your members use your system. Similarly they don’t strictly need to be backed up.
During server migrations it’s not uncommon for these database tables to be accidentally missed during the backup. To recreate them just go to the Memberium Dashboard and click “Reactivate Memberium”. It will detect any missing tables and recreate them for you. Once that’s done you can click the “Synchronize Infusionsoft” button on the dashboard to reload them with fresh data from your Infusionsoft app.
Other Memberium Tables
An exception to this are the memberium_contacts table, and the wp_memberium_sessions table.
The Memberium Sessions table holds the session data of your currently logged in users. You can control how long Memberium holds onto this session data in the Memberium settings, but if you find it climbing out of control, you can easily clean it out. The worst impact of doing this is that it may log out the user who the session belongs to.
The Memberium Contacts table stores cached data that is updated every time the member logs in, or when and update_contact HTTP POST is made. This data is used to generate the session and is available for various uses. Example uses of this data are for Umbrella Accounts (to quickly look up the parent account), and for user directories. This table is simply a faster, local version of the contact list in Infusionsoft.
Optimizing Your Memberium Contact Table
The memberium_contacts table will grow depending on your user-base and how much data you’re telling Memberium to store. If you have 139 fields on each contact, Memberium by default will sync and store all of those fields for every contact belonging to a member. Because we (Memberium) don’t know which fields you need, we do this by default.
You can tell Memberium which fields to ignore, and reduce your storage footprint. Reducing the number of fields will not have a significant and direct performance benefit but it will ease the pressure across the system. Fewer fields means smaller indexes, higher cardinality, less data pulled from Infusionsoft with faster API responses, and leaner user sessions.
Best Practices when Tuning your Database
Popular Tactics
This section represents low hanging fruit of settings to adjust. It’s not intended to be anything more than a generic place to start and should be adapted for your unique hosting requirements.
Query Cache
The query cache can be a powerful tool to speed up you read results, but may have issues if you have a lot of writes, as the cache will be heavily invalidated. Newer versions of MySQL discourage the query cache and instead recommend indexing, external caches, and replicas to spread the read load or using an external cache. Best recommendation: Use this as long as it works, then shift to one of the other strategies.
innodb_buffer_pool_size
The buffer pool is the memory on your server that caches your database data and indexes for your InnoDB tables. You want this value to be as large as possible, ideally so that as much of your indexes and data can be cached into memory and eliminate disk accesses. This has to be balanced with providing memory for your PHP and web server software, but on a dedicated server, using 2/3 of your memory for the buffer pool is not unreasonable.
max_connections
This is the number of simultaneous database connections that can be made to your MySQL server. If you get a “Too many connections” error, then your max_connections setting is too low. Setting this value too high (1000+) may cause your server to become unresponsive.
innodb_file_per_table
By default MySQL will put all of your InnoDB tables into a single giant file, which is hard to manage. By turning this setting on, each database table to will go into a separate file which can be backed up more easily, and lets you reclaim your disk space if you drop the table.
max_connections
This is the number of simultaneous database connections that can be made to your MySQL server. If you get a “Too many connections” error, then your max_connections setting is too low. Setting this value too high (1000+) may cause your server to become unresponsive.
skip_name_resolve
When your site connects to the database server, MySQL will attempt to do a DNS lookup on your site, which in extreme circumstances can cause delays in excess of 30 seconds per connection. This setting will disable this unnecessary behavior.
