Dimensional Modelling Case Study Part 1 - Age G roup (2024-01-12)

Continuity Across Restarts

There are a lot of database platforms, and each tries to convince you theirs is better. As Brent points out in that link, sometimes they just skip comparing themselves to other platforms because it makes them look better. They only look at the platforms they compete well against.

For most of us, we often just need basic CRUD operations. I know that most RDBMS platforms would work for us, and sometimes NoSQL ones work as well, though I think that NoSQL isn't necessarily better for many applications (maybe most). You may feel differently, but that's my view. While I use SQL Server, I think the majority of systems I've managed or built could easily run on MySQL, PostgreSQL, or many other platforms.

In any case, we want to store some data, get it back, maybe change it, and sometimes delete it. I think that last operation is fairly rare as most of us use soft deletes and because of this, our databases continue to grow like crazy. There was an interesting post from Brent on MySQL in AWS Aurora, where the buffer pool cache can survive a restart of the database engine. This is for the cloud version from AWS, not MySQL in general, but that's fairly cool to me.

And scary.

If you read more, there are some limitations, and the value of this, to me, is limited. However, it's a neat idea, since I could see some sort of restart of the database from a patch for the engine or even a configuration change. For MySQL, clients can't connect while the buffer pool is initialized, which isn't something that we deal with in SQL Server, but I can see this being useful. Or perhaps this helps if an Aurora node goes down and the next one picks up the database engine with a full buffer pool? Not sure if that actually happens from the docs, but if they kept the buffer pool separate enough or up to date on a second node, that would be great.

In the real world, I wonder to what extent we find an empty buffer pool at startup to be a problem. Sure the first few queries are slow, but most of the workload isn't the first few queries. It's all the clients that come in the next 5, 10, 20 minutes.

As Brent notes, this might not be something we want in SQL Server, but it is good to see cloud-enabled platforms trying something new. Some of those will turn out to not work very well, but for those that do, I bet we see other platforms start to adopt some of the ideas behind the technology.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums