MEMSQL 7.0

Why MemSQL? Part 3: MemSQL 7.0 In Depth

Rick Negrin
Rick Negrin

MemSQL VP of Product Rick Negrin describes the upcoming MemSQL 7. 0 in depth. He describes crucial features such as fast synchronous replication, for transaction resilience, and SingleStore, which leads to lower total cost of ownership (TCO) and more operational flexibility, at both the architectural and operational levels. Negrin also describes new tools for deploying and managing MemSQL and new features in MemSQL Studio for monitoring, investigating active processes, and editing SQL.

This blog post focuses on MemSQL 7.0 and the new features in MemSQL Studio. It’s based on the third part of our recent webinar, MemSQL Helios and MemSQL 7.0 Overview. (Part 1 is here; Part 2 is here.) This blog post includes our most detailed description yet of the upcoming MemSQL 7.0, and new features in MemSQL Studio.

Let’s dig in and see what’s new in MemSQL 7.0 and MemSQL Studio. So before we do that, I just want to set a little context for how we think about database workloads that are out there, and what’s driving the features that are built into them. If you look at the history of databases, there have been two common workloads that people use a database for. One is around analytics, also known as either Online Analytical Processing (OLAP), or data warehousing. That typically is comprised of requirements around needing queries to be very fast, particularly large and complex queries that are doing aggregations, or group-bys, or a large set of filters.

MemSQL handles transactional (OLTP) and analytics (OLAP) workloads.

Now you often have a large data size these days measured in terabytes, or hundreds of terabytes, sometimes even petabytes. Usually a company has large, infrequent batch loads of data, with large data loads coming in periodically. And then a need to resource-govern the different workloads that are making use of the system, because you often have different users running different types of queries.

Now the other side is the transactional applications – the Online Transaction Processing (OLTP) workload. That has a difference in its requirements. So in that case, the reads are coming in from the application, and the writes are coming in from the application, as opposed to being different. The reason why is that it’s coming from different sources and the types of queries tend to be less complex queries, and more focused on things like fast record lookup or small, narrow-range queries. But there are much stronger requirements around the service level agreements (SLAs), around concurrency and the availability and resiliency of the system. The more mission-critical, the more there’s less tolerance for downtime.

Whereas on the data warehouse side, the OLAP or Online Analytics Processing side, you’re often running things at night. It’s often offline at night. If an analyst is forced offline for an hour, they go and get coffee and maybe are unhappy but, you know, it’s not the end of the world. Whereas with the transactional side, often it’s an application, and sometimes it’s customer-facing, or internal-facing to many users within your organization. If it’s down, it can be very bad to catastrophic for the business. And so the SLAs around durability, resilience, extensibility are pretty critical.

And what we’re seeing with the new, modern workloads is that often they have a combination of needs around the transactionality and the analytics, that they need the fast query and aggregations and the large data size, but there’s a change in one of the key requirements here. It’s not just large data load but they need fast data load. They need the data to come in within a certain SLA, but there’s an SLA not just on how fast the query is, but also on the ingest, how quickly the data gets into the system in order to meet that near-real-time requirement that people need.

MemSQL combines OLTP and OLAP for operational analytics.

And then combine that with some use cases where, in addition to needing the aggregations, they also need the fast record lookups and all the availability and durability and resiliency that we expect from an operational system. And so it’s the combination of the same requirements that you have for the data warehouse as well, plus the operational ones, all combined in one system. There really aren’t many systems that can do that.

MemSQL has made solid progress pretty much across all of these requirements. At this point, we can do a vast majority of the workloads out there. But that’s not enough for us. We’re not going to be happy or settled until we can do all the workloads, which means being able to be as available and as resilient as the most mission-critical, complicated, highest-tier enterprise applications that are out there. And to do that, we need to get even more investments in things like resiliency. That’s why we focused on the two key features of 7.0, which are around fast synchronous replication and incremental backup.

MemSQL 7.0 adds fast synchronous replication.

So up until this current version, we’ve had synchronous and asynchronous replication. The difference being is when we mentioned we have high availability (HA), in which we keep two copies of the data. With async replication, you return back success to the user as soon as at least one copy of the data’s written. Asynchronous replication, you wait until both copies are written before you return back success of the user. This guarantees the user that if there’s a problem or a failover, you’re guaranteed that no data will get lost because you have the data in both copies.

Now, we’ve always offered both mechanisms and the customers will choose, which one worked best for them, making a trade-off between performance and durability. And in some cases, customers made one choice versus the other but it was always unfortunate that they had to make that trade-off. Trade-offs are hard and nobody wants to have to choose between those two things they wanted. And so with 7.0, we revamped how we do synchronous replication, so it’s so close to the speed of async that there’s really just a negligible difference between them.

We’ve enabled synchronous replication as the default so that everybody gets the durability guarantees without having to trade out performance. And this enables you to basically survive any one machine failure without having to worry about any loss of data. And additionally, we move from having full backups only, to also offering incremental backups.

Full backups are great. They allow you to make sure that you have a copy of your data off the cluster in the event of a total cluster or major disaster. But having to do full backups only … Even though our backups are online operations that don’t stop you from running your existing workload, they do take up resources within the cluster. Moving to a model with incremental backup allows you to run the backups more often, reducing your recovery point objective (RPO) – the age of the files that you have to recover, before you can return to normal operations – and reducing the amount of load that you have in the cluster, so you don’t need as much capacity in order to maintain the SLA that you need without being impacted by running back-ups and restores. So really driving down the overall total cost of ownership (TCO) of the system and making it more resilient and driving down the RPO.

Now, there’s a lot more stuff in 7.0, but synchronous replication and incremental backups are the two marquee features that we’ve delivered that will make a huge difference to customers at that upper tier of enterprise workloads.

MemSQL SingleStore in (Some) Depth

Now, the other big investment we made was around the storage mechanisms that we have in the MemSQL. So again, if you look at MemSQL 6.8, which is still the current version, we have what we call Dual Store. We allow you to have a row-oriented table or a column-oriented table within your database and you can choose for every table that you create. So you create which one you want. Most of our customers end up choosing a mix of the two, because they get a certain set of trade-offs and advantages from a rowstore versus a columnstore table.

Row-oriented tables to tend to be good for more OLTP-type applications where you need fine-grain aggregation or seeks, for updates or deletes on a set of rows. But the downside is that you get a higher TCO because rowstore is all stored in RAM, and memory can get expensive when you get to large data sizes.

The column-oriented tables are much better for big data aggregation scanning billions of rows. You get much better compression on the column-oriented table, but you don’t get very good seek time if you need to seek just one or two rows, or a small number of rows. You don’t get secondary indexes. And so this put customers in an unfortunate position where they’ve had to choose between row-oriented and column-oriented tables. And if you need, for example, the big data aggregation because you’re doing table scans sometimes, but then doing seeks other times, you’re kind of stuck. You have to give up one or the other when you choose the solution for your application.

Coming up, with MemSQL 7.0, we’ve made investments to make those trade-offs less harsh, investing in compression within the rowstore to drive down the TCO and implementing fast seeks and secondary hash indexes so that users who need those can just use columnstore data.

Here’s the comparison, for rowstore tables (transactional):

  • Benefits: Fine-grain aggregation: seek, update, and delete up to millions of rows, fast; secondary indexes (regular & spatial)
  • Former detriment (pre-MemSQL 7.0): High TCO
  • Now (with MemSQL 7.0 and SingleStore): Average 50% storage compression, with new null compression feature

And here’s a similar comparison, for columnstore tables (analytical):

  • Benefits: Big data aggregation; scan billions of rows, very fast; full-text indexes; 5x-10x compression
  • Former detriments (pre-MemSQL 7.0): Slow seeks; no secondary indexes
  • Now (with MemSQL 7.0 and SingleStore): Fast seeks; secondary hash indexes

MemSQL combines rowstore and columnstore tables.

We’re not going to stop there. Long term, what we want is to have a single table that has all those capabilities. Under the covers, we autonomously and automatically will use the right rowstore or columnstore format and make use of memory and disk so that you don’t have to make the choice at design time. We make the choice for you, in operational time, based on how your workload is working. Progress in that direction is what you’re going to see over the next several versions of MemSQL.

Here are the benefits for SingleStore tables (transactional + analytical), with their original source – either rowstore or columnstore: Fine-grain aggregation (from rowstore) and big data aggregation (from columnstore); seek, update, delete up to millions of rows, fast (rowstore) and scan billions of rows (columnstore); secondary indexes, regular + spatial + full-text (rowstore) and 5x-10x compression (columnstore).

MemSQL SingleStore will encompass OLTP and OLAP in a single table type. And last, but not least, of course, is this: in order to manage and make use of a distributed database, you need to have the right tools for both deploying and managing it. And so we have a number of new capabilities within our tool chain to make it easier to set up your cluster, if you’re using the self-managed software (rather than MemSQL Helios, where MemSQL does those things for you). Tools that also allow you to do things like online upgrade and do monitoring of your data over time, so you can do capacity management and troubleshoot problems that are intermittent.

MemSQL utilities help with setup

And then, on the visual side, the MemSQL Studio tool, which I briefly showed you during the demo, allows you to do things like logical monitoring to visualize the component states of the nodes within a cluster, to make sure there’s no hotspots or data skew or other problems that need attention.

MemSQL Studio supports logical monitoring.

Physical monitoring of the actual hosts, so you can see if any one of them is using more resources whether it’s CPU or memory or disk or I/O than it should be using, and take action if needed. (Of course, the physical monitoring is only something that you can do when you’re self-managed; when using Helios, the physical monitoring is taken care of by MemSQL.)

MemSQL Studio supports physical monitoring.

We also let you have tools to let you look for long-running queries, so you can troubleshoot if a query has problems. Perhaps there’s been a plan change, and you now have less optimal plan, using too much capacity or too much resources. So you can find the query, figure out what the problem is, and kill it if needed.

Look for and terminate long-running queries in MemSQL Studio.

And of course, the SQL Editor, which you saw in the demo, that allows you to write queries and experiment with the system as well as manage it.

And that concludes our whirlwind tour of MemSQL Helios and the upcoming MemSQL 7.0. Of course, you don’t have to believe anything I say. You can try it for you today yourself. You can access MemSQL Helios. We made our trial available to you, which is available at MemSQL.com/free, or you can get started with the MemSQL 7.0 beta at MemSQL.com/7-beta-2. If you’re not a customer of MemSQL, you can still access the beta for free, or download our free tier to use MemSQL. Thank you very much.

MemSQL Q&A

Q. Is the software the same for MemSQL Helios, your elastic managed service in the cloud, as in the self-managed software? (Which you can deploy yourself, in the cloud or on-premises. Ed.) Are there any differences in capability?

A. Self-managed MemSQL the exact same engine used in the Helios. It’s what we ship on-prem. As we upgrade software, those versions show up in the cloud, in MemSQL Helios. In fact, they’re likely to show up on the managed cloud first, before they show up in the self-managed software that we ship.

There are some slight differences. They’re mostly temporary. There are features that are disabled, that will be in the future. So for example, the transforms feature, where you can put in arbitrary code that will enable transformation of the data as it’s going to the pipeline. We don’t enable that on Helios because we’re not yet ready to support that. We only take arbitrary code and run it inside of our manage service. However, in time, we’ll figure out how to do that safely, and we’ll enable that feature.

There are a few features like that aren’t supported yet but we’ll be supporting in the future. All this is documented in our documentation.

There are also some features that are more around managing the physical aspects of the cluster – for example, the ability to just take a node and add it to the cluster or move it towards a cluster. All these are managed for you by the system, with MemSQL Helios.

Q. Does MemSQL support high availability (HA), and how does it work?

A. HA is built into the system. With the self-managed software, you can choose whether or not you want HA at all, or if you want one or two copies. With Helios, HA is always turned on. You can’t turn it off and it’s turned on automatically, so you won’t have to do anything. It just happens, transparently.

Q. Is there a plan to allow for real-time web based replication for MySQL to MemSQL? If not, do we have current workarounds that can get this done for us, such as MySQL to Kafka to MemSQL, or another solution that is already proven?

A. Yes. You can use existing replication tools, such as Attunity and other replication tools that support MySQL. (You can use these tools because MemSQL supports the MySQL wire protocol – Ed.) You can also stage data to Kafka or S3, and use MemSQL Pipelines to move it into MemSQL. In addition, MemSQL will have a solution that will move data from MySQL, Oracle, SQL Server, and other relational databases to MemSQL later this year.

Q. I was told common table expression (CTE) materialization is coming in MemSQL 7.0. Is this true?

A. Recursive CTE virtualization is not coming in MemSQL 7.0, but it’s on the roadmap for the future.

Q. When is MemSQL 7.0 reaching general availability (GA)?

A. We’re in Beta 2 right now, we’ll have a release candidate (RC) in the next month or two, and then the GA should be up soon after that.

Q. Can you explain how you do fast synchronous replication in MemSQL 7.0, without any penalties?

A. This blog post talks about our sync replication design: https://www.memsql.com/blog/replication-system-of-record-memsql-7-0/.

Q. For MemSQL Helios, do you provide an SLA, and if so, what is it? When will MemSQL 7.0 be available on Helios? Will EU regions be available soon? And also, when will MemSQL Helios be on Azure?

A. MemSQL Helios provides an SLA of 99.9% availability. MemSQL 7.0 will be available on MemSQL Helios when MemSQL 7.0 goes GA – in fact, MemSQL 7.0 is likely to be available on MemSQL Helios first. EU regions will be available in 2019. And MemSQL Helios will be available on Azure in the first half of 2020.

Q. Does MemSQL provide data masking on personally identifiable information (PII) columns?

A. MemSQL does not have masking as a built-in feature, but you can use views on top of tables to accomplish the same effect.

Q. Are RabbitMQ pipelines in the works?

A. These are on the roadmap, but not in the near-term.

Q. Is there a cost calculator for Helios?

A. Cost is # of units * hours used * price per unit. For more information on pricing, please contact MemSQL Sales.

If you have any questions, send an email to team@MemSQL.com. We invite you to learn more about MemSQL at MemSQL.com and give us a try for free at MemSQL.com/free. Again, thank you for participating, and have a great remainder of the day.

MemSQL Helios eclipse
Introducing
MemSQL Helios
The World’s Fastest Cloud Database