Magic and Math

Earlier today a number of updates to 10C rolled out to fix a handful of bugs and introduce new functionality in both the API and the Social site. While the features are certainly nice, one of the most rewarding aspects to today's release is seeing the average CPU load on the web server drop from a pretty steady 1.1 to just 0.28. Memory usage is down as are response times. Today's update saw the next push to use the system's MySQL database engine more like I would SQL Server.

I've been on record to say that SQL Server is the best relational database engine on the planet and I stand by this assessment. It's incredibly fast, powerful, and comes with a slew of excellent tools that I find to be incredibly reliable. Licensing and other aspects can be quite complex at times, but the technology itself is rock solid. For a while, I actually had 10Cv5 running completely off a SQL Server on Linux installation during the year-long alpha period, but opted to change the database engine to something a little more common in the open source world.

MySQL is an interesting tool in that it powers a lot of the Internet, is relatively easy to set up, and doesn't cost a dime. I've been using this database engine for 15 years and seen it develop quite a lot over this period of time. One area where it has seriously lagged behind SQL Server is with the performance of stored procedures and triggers. These programmable objects were just slow and easy to break in the MySQL 5.x versions, which meant that a lot of people — myself included — would write their web applications to use a series of ad hoc queries that are created in the API layer and passed to the database when needed. The more recent 8.0 release of MySQL has completely changed this paradigm. The database engine is faster. Memory is better used. Indexes remain in memory longer. All in all, I feel that 8.0 is the best release of MySQL there has ever been.

Since the beginning of v5's planning, I wanted to make more use of the features and functions that are found in a traditional relational database management system. Objects like triggers, views, and stored procedures are excellent tools to enforce consistency in a database at the database layer, freeing up the API layer to be more of a communications bus than anything else. Triggers were relatively easy to set up on the tables and they're providing some of the much-needed functionality to keep people's data safe and organized.

One area that I've stayed away from until recently has been stored procedures. In SQL Server these are wonderfully powerful objects that allow for a series of complex operations to take place completely in the database before a set of data is returned. In the MySQL 5.x versions, a stored procedure could be used for a complex task but was often slower than simply sending an ad hoc query and processing the results in the API middle layer. With version 8.0, this resistance is no longer necessary. Stored procedures in MySQL 8 rock. They're incredibly fast, reliable, and require less memory and fewer disk reads on average than sending a raw query string to the database. The change has been night and day, and it's reflected in the load times across the myriad of sites and services powered by 10C.

Being able to use stored procedures with 10C and not battle with a performance hit is going to completely change how I write this application and how people who choose to self-host administer the software. If MySQL continues to see the sort of improvements witnessed between 5.7 and 8.0, then other relational database engines will need to see similar changes in order to keep up.