It’s hard to believe that a relational database in personal use at home will ever have much of a load when it comes to transaction processing but our home RDBMS is surprisingly busy, with more than a hundred database interactions per second. It’s still not even within an order of magnitude as busy as many business databases but it is likely is as busy as half of them.
Our “home” is actually a boat and the boat is fairly heavily instrumented and automated, Just about everything is stored in the relational database. We have every data point from every 5 seconds going back many years stored in the on-board RDMS. The data that is collected and stored includes:
- Tank levels: Fresh water, black water, grey water, starboard fuel, port fuel, supply fuel, and day tank fuel
- Main engine data: RPM, load percentage, hours, coolant temp, oil pressure, intake air temperature, transmission temperature, transmission pressure, boost pressure, fuel pressure, and fuel temperature
- 24V DC electrical: house voltage, house amperage, house state of charge start alternator temperature, house alternator temperature, start voltage, start amperage, and start state of charge
- 120V AC: voltage, amperage, frequency, inverter temperature, and kWhrs.
- 240V AC: voltage, amperage, frequency, inverter A temperature, Inverter B temperature, and kWhrs.
- Generator: rpm, oil pressure, coolant temperature, battery voltage, hours, voltage, amperage, frequency, and kWhrs.
- Shore Power: volts, amps, frequency, and kWhrs
- Wing Engine: rpm, oil pressure, coolant temperature, hours, and start battery voltage
- Location: Latigude, longitude, speed, course, heading, rate of turn, number of satellites in view, horizontal dilution of precision, and position dilution of position
There hundreds of other data points ranging from network router traffic over its three wide area network connections (satellite, cellular, and WiFi) to tens of parameters on satellite connectivity status, furnace operational data, and hundreds of control system state parameters. The boat is heavily automated and the control systems all run off database data. Example applicationss include load shedding (shedding least important electrical loads when drawing more power than available), generator autostart (starting generator when the batteries need charging), operator indicator and warning lights (status lights showing health of boat systems), alarming, remote monitoring (show remote state of boat systems and email if there is a problem), and remote actuation (for example, the ability to turn on the furnace from a restaurant if on the way home on a cold day).
In the end, there is lot of database traffic. I wrote the first version of this system back in 2005 when I was working on the SQL Server engineering team and, no big surprise, I chose to use SQL Server as the database. SQL Server is a pretty good database management system and it did a respectable job in this application for nearly a decade.
When I left Microsoft to join Amazon Web Services back in January 2009, I moved all the boat applications to open source languages and databases. I did this partly to learn, but mostly because I think that open source services are the right place to be whether at home or in a business setting. Open source solutions are often available on more platforms, they are more flexible, more cost effective, and often have far richer informal support networks. For the database, I chose MySQL mostly because it was, at the time, the broadest used open source relational database management system.
Overall, it was great to be back on open source but MySQL was a little bit like a step backwards in time. I just kept bumping my head into missing features that I remember implementing in DB2 during the late 80s. However, MySQL is open source, the price is right and every problem I ran into had an easy to find work around or someone in the informal support system had already solved the problem. Despite all the limitations, I’ve come to like MySQL and use it all over the place these days.
However, MySQL has developed some issues over the years. In 2008, MySQL was acquired by Sun Microsystems. MySQL has a fairly tightly controlled code base and so the commercial interest that owns the company have considerable control over the MySQL base even though it is open source. But, Sun didn’t abuse its control of the MySQL code base and, generally, I’ve always really liked Sun. I first used their systems back in the mid-80s and they were excellent, good price/performers, and the founders were easy to respect, particularly the super innovative Andy Bechtolsheim. But, when Oracle bought Sun in 2010, it then really was a problem. MySQL is now owned by a company that is famous for high prices, aggressive sales tactics, and customer unfriendly practices.
Given my less than flattering summary of Oracle, why was I still using a product controlled by Oracle in early 2018? Well, it’s sort of embarrassing but, in the end whether a company or an individual, we are all busy. It’s the same reason why some companies that have been through surprise price increases and license audits might still be using Oracle today. There are lots of reasons to move to better price/performing solutions but we’re all busy and don’t always have time to invest in a database platform change. These moves between databases require a massive amount of work and often other business priorities are more important. No company likes poor treatment but you can’t solve every problem they day it occurs. Unfortunately, the commercial database companies are well aware that customer inertia is one of the most powerful forces in the database world.
However, in the end, the action always follows long periods of problems or the existence of better alternatives. Companies and individuals eventually elect to make a change, if change is needed. It may not happen on day one but the history of our industry shows that change does happen and, once it starts, the transitions can move faster than one would expect. In my early days in the database world, IBM effectively owned most workloads at finance and insurance companies. Later, Sybase became the darling of the financial community and won many of these workloads. Yet today they are hard to find anywhere. Toward the end of the Sybase supremacy, Informix had a fine RDBMS that topped the transaction processing benchmarks but they too are close to invisible today. Oracle still has a very large share of the relational database market but, there are alternatives and this share is shrinking.
In the open source database world, MySQL has the dominant share but, Oracle owns it and there are many excellent alternatives available. PostgreSQL is a technical strong alternative with growing market share. I’ve come close to moving my personal systems to PostgreSQL for years. The challenge with PostgreSQL is it is quite different from MySQL. I still recall the massive job of moving from SQL Server to MySQL. It was huge so I’ve not been excited about the prospect of moving from MySQL to PostgreSQL even though PostreSQL appears to be a better offering with a faster growing user base.
On the day Oracle bought Sun (and MySQL) back in 2010, the founders of MySQL started MariaDB. I’ve met with the Maria leadership and like them. I’ve always viewed it as an excellent alternative to MySQL but, you know the story, the work of moving to a different code base is pretty serious in the RDBMS world. I just never got around to it and was still running MySQL until yesterday.
I been on MySQL 5.6 for a long time and promised myself that the next upgrade was going to be to a different code base. I don’t want to feel owned by Oracle so it’ll be either PostgreSQL or MariaDB. Yesterday, I decided to make the move and, since MariaDB claims to be compatible with MySQL and is actually a fork of that code base, I decided to head in that direction.
“Claims to be compatible” is normally a fairly useless statement in the database world. When it comes to real applications, they are always deeply tied to the underlying features, implementation quirks, bugs, and performance characteristics of the underlying RDMBS. As an example, years ago I was working on SQL Server 7.0 and many of our customers came from the previous release, version 6.5. This version only supported B+ trees whereas 7.0 has different storage options including heaps (unorder storage of rows). With 6.5 “select * from table” will produce exactly the same result as “select * from table order by primary key.” With 7.0, we implanted more storage options and an optimizer that would make cost based decision. It had the advantage of being slightly faster on transaction workloads and it was vastly faster on more complex decision support workloads. But, “select * from table” now produced results that were not necessarily ordered and the order of returned rows might change over time as the table size changed. Technically correct and completely compliant with the SQL standard but an amazing number of SQL Server 6.5 programs didn’t work on SQL Server 7.0. The lesson is a “compatible” database is similar but big applications very seldom “just run”.
I’ve seen this for decades so I fully expected that moving to MariaDB will be some considerable work even though it is a fork of the same base code and has some of the original team members working on it. So I allocated a half day and I felt like I had a good shot of getting the important features up and running by end of day.
Moving to Maria DB
Just after lunch, I installed the MariaDB install on a non-production laptop. It was installed in under a minute and it offered to either upgrade the MySQL 5.6 database this laptop or install a new instance. I went for the upgrade and, naturally, it blew up without a useful message. So, I was denied that easy way out. I installed the separate instance and played with it using the packaged console HeidiSQL. I was fairly impressed with performance and ease of use of HeidiSQL so I figured I would try to figure out why the MySQL database had failed to upgrade.
I installed the system on a different laptop, again tried to upgrade and it again failed. It fails in an ugly way where the previously running instance is left stopped and unable to run. I tried to start the service and it failed. Getting output from the service during start up, it’s easy to see it’s complaining about the use of a deprecated my.ini configuration option “Innodb_additional_mem_pool_size” likely left lying around from some ancient MySQL version. I’m not 100% in favor of “not running” as the right solution for the use of a deprecated option but I commented it out and MariaDB started up perfectly with access to all database. Pretty easy.
This hadn’t taken much time so I still have most of the afternoon to correct application problems from the upgrade if I do the production system so I decided to get on it. The production systems are all backed up nightly so I don’t really have much at risk if things fail unrecoverably.
I decided to take the lazy engineer path and just run the install, take the upgrade option, and see what fails (after comenting out “innodb_additional_mem_pool_size” from my.ini). I didn’t even both to bring down any of the production applications that were running against the MySQL system. I just ran the install, accepted the license, selected upgrade rather than new install, and it was done in under two minutes. Predictably all the applications were complaining about loss of connection and other errors. We had warning lights and error messages all over the place but, over the course of the next 60 seconds they all cleared and everything was working.
On the boat, there are three main and numerous minor applications that operate against this central database. The MariaDB install and upgrade of MySQL 5.6 to MariaDB 10.2.13 was complete in under two minutes without any application changes. I never even bothering to bring down the more than 10,000 lines of applications that were running against the instance during the upgrade and, 60 to 90 seconds after the instance was installed, the applications all reconnected, the error messages all cleared, and everything was back to normal. You can go from running in production on MySQL having never even played with MariaDB, to running in MariaDB in under 60 minutes.
There has not been a single error or issue in the day since the upgrade. Moving from MySQL to MariaDB is absolutely painless. Everything just runs in my multiple applications of more than 10,000 lines. Some queries that previously performed so poorly on MySQL that I couldn’t use them, now run fine. I’m already a huge fan of MariaDB. If you are on MySQL today and want to get away from Oracle or just want to run the latest code from the MariaDB contributors, get going. If you have a spare 30 minutes you may still have time for a coffee once you are done.