What is our primary use case?
When I was working at Wag, MySQL had trouble scaling. We were using an M4-10X RDS instance and three replicas. These machines are expensive and with the growth that the company was experiencing, it was clear we would not be able to scale properly.
There were heavy writes done against MySQL that should not have been there; an event table for example. This table was close to 900M rows, taking more than 300GB. I decided to migrate this table to MemSQL, freeing up both lots of space and write resources on MySQL.
After the event table was migrated to MemSQL, using the MemSQL built-in S3 pipeline, the table was compressed down to 30GB.
How has it helped my organization?
Prior, the Support team would use MySQL to run queries against the very large MySQL event table. The query would take more than two minutes to run. After the table was migrated to MemSQL, queries would run under three seconds.
Developers saw quickly the benefits of MemSQL and started using the memory engine to cache tables.
Moving the heavy writes to MemSQL freed up lots of write resources on MySQL. The replica lags, which would hover between two and ten seconds, were consistently below one second.
We were also able to ingest free weather data and enhance it to compare it against real-time data. (Weather affects dog walks). We then built some weather models to create some trends and predict weather patterns.
What is most valuable?
The most valuable features are:
- MySQL drop-in solution: MemSQL supports the MySQL protocol, and many functions are similar, so the learning curve is very short. Developers were able to code against MemSQL the same day. They configured MemSQL in Docker and were up and running.
- MemSQL built-in pipeline: You can connect MemSQL directly to an S3 bucket and it will ingest its data automatically. There is no need to create a lambda function. It just works. You can connect to Kafka, HDS, and other protocols.
- Support for CTE, Window, Stored Procedures, functions (UDF, Table) are all included. MemSQL is a full-blown database solution.
- Replication: A MemSQL cluster can replicate to another cluster. This is a great feature, allowing you to have one cluster that does all the heavy work, and a Replica for reads.
- MemSQL supports high concurrency, and scaling horizontally is limitless.
- I really like the MemSQL portal (portal.memsql.com). Creating more labs is the best way to introduce people to MemSQL.
What needs improvement?
There should be more pipelines available because I think that if MemSQL can connect to other services, that would be great.
MySQL to MemSQL replication would be great. If it could replicate from MySQL then this would allow me to move all of the heavy counts, full-text search, aggregate, and analytical queries off of MySQL. We would not need many replicas, and you could have near-real-time (less than a few seconds) data you that can report on, and generate these reports in less than a few seconds.
For how long have I used the solution?
I've been using MemSQL for the past four years
What do I think about the stability of the solution?
I have never had an issue with stability. A leaf node could go down and you can still operate. MemSQL provides full redundancy across the Cluster using a share nothing approach with its leaf nodes. Leaf nodes, that store the data, can be taken offline, maintained, upgraded, scaled, and put back in, while the cluster still server queries.
What do I think about the scalability of the solution?
MemSQL scales very well. I did some benchmarks with 128 concurrent users on an 80M row table doing counts "Where date between x and y, group by 1", and 75% of the queries would return in less than two seconds.
How are customer service and technical support?
Their support team is top-notch. They provide excellent support, and the feedback would be thorough. I have no complaints about the support.
Which solution did I use previously and why did I switch?
I had been using MySQL before to run reports. I switched because MySQL could not deliver reports fast enough.
How was the initial setup?
The setup is pretty straightforward, MemSQL provides excellent documentation.
What about the implementation team?
Our implementation and deployment were done in-house.
What was our ROI?
ROI? Less headache, fewer worries about scaling. No learning curve and no need for specialized resources in a language. You can be up and running in a few minutes, or you can use the Cloud solution.
What's my experience with pricing, setup cost, and licensing?
I would advise users to try the free 128GB version. MemSQL also comes with "Cluster-in-a-box", which allows you to run it on a single instance. This is a great way to save money and get your hands dirty, until you need to scale bigger.
Which other solutions did I evaluate?
I looked at PostgreSQL but since I don't know it well, I decided to use MemSQL. The learning curve is short and once the cluster is up and running, you don't need to worry.
MemSQL supports backup, restore, and enterprise-grade fail-over. It's very stable and with one node down, you can still serve traffic.
What other advice do I have?
I discovered this product at Strata Data in 2012 and followed the product evolution. I've tested many versions and recommended them to companies I've worked with.
MemSQL provides excellent documentation and they upgrade the product frequently. Their cloud solution offering, MemSQL Helios, allows you to create a cluster with a few clicks. MemSQL is a very powerful database.
There are a lot of solutions out there including ClickHouse, Aurora, and PostgreSQL, but none of them provide the rich features that MemSQL has. The pipeline by itself is awesome.
I can write an SQL statement to create a pipeline and in less than 15 seconds I have data flowing into my tables (or stored procedure). You can ingest data into MemSQL stored procedures and do transformations on the fly. For example, counters.
So give it a try, you won't be disappointed.
Which deployment model are you using for this solution?
If public cloud, private cloud, or hybrid cloud, which cloud provider do you use?
Amazon Web Services (AWS)