BuddyPress Privacy Offers You the Power of the Force
By Jeff Sayre
Okay, that title may not be accurate. But for those advanced BuddyPress administrators and site owners who are performance focused, the BuddyPress Privacy Component will offer the option of creating the ACL (access control list) tables with the InnoDB storage engine instead of the MyISAM storage engine which WordPress and BuddyPress use as the default. This offers a number of advantages such as referential integrity with cascading deletes and updates and row-level locking instead of table-wide locking—which increases performance by facilitating multi-user concurrency, a crucial point for under-powered servers or highly-trafficked sites.
InnoDB offers many advantages including one of my favorites as a developer—cascading deletes and updates.The conventional wisdom that the MyISAM storage engine is always the preferred type is outdated. I will not go into the whys in this article as it is discussed in many places. Here is an article that discusses the benefits of the InnoDB storage engine type in great detail.
Refer to the MySQL documentation for more details on the InnoDB storage engine.
Suffice it to say, that two years ago, the Drupal project decided to make the InnoDB storage engine the default type for Drupal 7 and have not looked back since. You can read more about that decision here.
Use the Force, Luke
To use the InnoDB storage engine type for the BP Privacy tables, you must manually configure before installing. Details will be provided in the readme.txt file.
InnoDB offers many advantages including one of my favorites as a developer—cascading deletes and updates. To take advantage of this, I’ve coded dual delete methods within each class model. The method that is fired depends upon which storage engine a given install has chosen. If the InnoDB storage engine is in use, then record deletion is a very simple process as deletes are automatically cascaded down through the child tables. If the default MyISAM storage engine is in use, then multi-table deletes require a more complex looping routine to ensure that associated records are deleted.
Do WordPress and BuddyPress really need to use the BIGINT numeric field type for UserID? Is it even realistic to make allowances for the possibility of 18.446 quintillion unique user records?Although MySQL allows for the mixing of storage engine types across tables, there are solid reasons for standardizing on one storage engine type across your entire database. It is up to you to decide if you want to mix and match storage engine types and to determine which tables may benefit from one storage engine type versus another.
If you decide to install BP Privacy using the InnoDB option, then you should consider whether it makes sense to convert all of your existing WordPress and BuddyPress tables to use the InnoDB storage engine. If you are doing a clean install of WordPress and/or BuddyPress, you can set the default storage engine type to the storage engine of your choice for each table before running the install script. Future core upgrades, unfortunately, will then require extra vigilance. So, proceed with extreme caution.
Here are some additional resources to help you better understand the risks, benefits, and issues involved (read the comments in the below articles as well):
Not All Lightsabers are Equal
To understand the next section, you need to heed the words of this ancient Jedi saying. It means, do not compare apples to oranges. Note: I’m not sure if that phrase is a Jedi saying as I made it up.
The MyISAM and InnoDB storage engines are different storage technologies. Therefore, whereas overall performance results can be compared between these two different storage engines, what I discuss in the next section pertains to performance tuning a table within a given storage engine type.
These Aren’t the Droids You’re Looking For
To do my part in reducing data bloat, and helping those users that have lower-powered servers, or are on shared hosting, I’ve structured BP Privacy’s tables to be as compact as possible—whether using the MyISAM or InnoDB storage engine type. How? Well for starters, by using the INT, instead of BIGINT, numeric field type for the Main ACL table’s primary key field and by using partial indexing where practical for both the Main and Lists ACL tables.
The benefit of using the INT numeric field type is that it’s a 50% reduction in storage space compared to BIGINT (not a fifty percent reduction in the maximum number of unique possible records within a table, but in the number of bits required to store the data in the ID field of each record in the table). Using the INT numeric field type for the table ID still allows for 4.29 billion unique records within each table. This should be more than adequate for 99.5% (likely even more) of all BuddyPress installs.
The other performance gain comes from the fact that in the latest version of BP Privacy (starting with v1.0), I’ve done away with storing user lists in a serialized array within the userlist field of the single table. Instead, I’ve normalized that bit of data, storing any group or user lists in a separate ACL Lists child table. The benefits of this approach are many.
Whereas serialized data can be useful when storing disparate data in a settings field, for instance, it is not a great idea when the type of data will always be the same in a given field. Why? Because when you serialize data, you are storing a table within a table. This can lead to all sorts of performance issues and makes it very difficult to perform meaningful searches with the data “locked” within the serialized array. In fact, once you start storing serialized objects in a relational database on a regular basis, then you have moved beyond the utility of a table-based model. Instead, a schemaless system would prove more beneficial.
There is another performance hit when choosing to serialize the same type of data within a table’s field. Whether a given record stores any data in the serialized field or not, the overall record overhead size is much larger as a result of a set minimum number of stored bytes for the LONGTEXT data type. This means that space is used in the table even if data is not stored in that field. As most users will more than likely not be setting any group or user list data, it makes more sense to only take up space when actual data is required to be saved. Therefore, the child ACL Lists table.
Given the fact that these improvements are for a few, small privacy tables, it may not seem like that little of a space savings makes a difference in the grander picture. But imagine if WordPress and BuddyPress core made similar changes and all plugin developers took the time to optimize their plugin’s data schema (if they have one). Every bit saved on better field type and index sizes can add up to big performance gains, especially as a WordPress-powered site begins to get noticed and needs to scale.
Ask yourself this. Do WordPress and BuddyPress each really need to use the BIGINT numeric field type for UserID? Is it even realistic to make allowances for the possibility of 18.446 quintillion unique user records? Or, would larger benefits accrue to WordPress and BuddyPress users if the data size for that field was reduced by 50% (by using INT) or even better 62.5% (by using MEDIUMINT). Imagine where else data schema optimization could occur! By thinking about data storage issues, you can help to reduce table size and thereby improve performance.
To get to the heart of the discussion above–the infamous serialized fields–start the below-linked-to video at timecode 32:48, which is point nine in his list of ten. The section is entitled EAV. The most pertinent discussion will start at timecode 35:22, with what the author calls EAV 2.0: E-BLOB, but it is a good idea to start a few minutes earlier at the beginning of point nine.
This hour-long video became the source of a latter OSCON presentation. The audio is pretty bad, but it gives some great pointers on database best-management practices, 10 Ways to Wreck Your Database.
A Jedi Uses the Force for Knowledge and Defense, Never for Attack
As this famous quote from Yoda implies, with great power comes great responsibility (how about that for mixing SciFi and comic book metaphors). So, here it goes.
Warning: If attempting anything mentioned in this article or in the linked-to articles, the typical caveats apply: backup your existing WordPress and BuddyPress database(s) before you do anything, know how to restore your database(s) from your backups, know what you are doing and what you’re getting yourself into, don’t cry if you break something, it is entirely your responsibility to fix anything that breaks, and there is no one you can blame except yourself if something goes wrong.
I will not answer any questions about how to do anything discussed above or in the linked-to articles, nor provide any assistance in helping you do this, nor provide any help if you do this and your site breaks. It is up to you to know what you are doing. Proceed at your own risk. You have been warned.