MEMSQL and AWS - practicality and configuration questions

Help
1

I created the AWS Application https://www.fansrave.com It uses 2 instances of Amazon’s version of MySQL - a Read-Write instance which we change and a read-only instance which is refreshed automatically from the R/W as data change. This serves as a live backup. Performance is fine, but we are hoping that we will grow enough that it will no longer be sufficient.

The thought would be to replace the R/W instance of MySQL with something that runs out of RAM. We’ve looked at AWS implementation of memcacache but that would be a painful change to our software. A RAM SQL engine would minimize software changes and be a lot easier to field and to maintain.

AWS spins up additional instances of our application as needed by load. The plan would be for some number of our instances to share a memory database instance with all changes being fed to the MySQL read-only instance as non-volatile backup.

  1. Does that make sense?

  2. Have you defined and tested a configuration which would use AWS memory to store your database? Or would that be too exotic?

  3. Your literature suggests that we could add additional instances of memsql as needed as load increased. Could that also be done automatically? If we had more than one, how would our application instances find one? Service discovery?

  4. Establishing a JDBC MySQL connection takes significant time. We maintain connection pools in Tomcat. Is the overhead of establishing a memsql connection enough less that we would not need a connection pool? That is, could we establish a session-duration connection for each user interaction?

  5. MySQl connections impose significant load on the database engine. How many connections can you support? I realize that the answer is “it all depends;” I am addressing the configuration I’m describing.

  6. You will note that our main page shows view counts for each deal. Each instance accumulates count changes in its memory and then adds the total update count to the related MySQL row once per minute. The idea is to minimize count update requests. We assume that memsql supports ACID to the degree that a flurry of simultaneous increment instructions would be executed properly.

If what we want to do doesn’t seem ridiculous, I’ll want to know more about pricing and the degree to which you’re a subset of MySQL. We tried HARD not to use anything exotic in our SQL, but one never knows…

2

Using MemSQL as a replica of a master database stored on some other database like MySQL and directing queries to MemSQL to get better scalability and response time does make sense. Others have done that. You can also just use MemSQL to store the data if you want; data is persistent even though we store data in memory. It is persisted using snapshots to disk and a log file on disk. Of course, you would need to take periodic backups just like with any other database.

I’m not sure what you mean about AWS memory. Normally people just run MemSQL on standard AWS instances, like m4.2xlarge, m4.4xlarge etc. E.g. you can make a cluster with 1 agg and 4 leaves, each on their own m4.2xlarge. The storage would be set up on EBS.

Adding additional instances of MemSQL (multiple clusters) can be done but first you should consider just running one cluster and if you need to make it bigger you can add leaves and rebalance the data online.

I don’t have a good answer about JDBC connection pooling. We can handle very large numbers of concurrent connections. Support for high concurrency is one of our strengths.

We can easily handle a lot of concurrent increment operations on the same row. If you are doing multi-statement updates and are worried about intervening updates causing a concurrency problem then you can use SELECT FOR UPDATE to hold locks on a row for a read-only SELECT and then update later with an UPDATE statement, then commit to unlock.

Fanatics.com is using us heavily and your app seems to have a fair bit in common with theirs. https://www.memsql.com/blog/how-fanatics-powered-their-way-to-a-better-future/

We support a broad SQL implementation and the same data types as MySQL. If you want to know about pricing then contact info at memsql dot com and they’ll set up a conversation with you.

3

Thank you, that was most helpful. Do you know if anyone can post sample roles and AWS setup instructions to get it working? Getting AWS RDS to support my specific MySQL instanced was a bit of a slog.

4

Not sure what you mean. Here’s the AWS install guide. SingleStoreDB Cloud · SingleStore Documentation

You’d need to use your application code to replicate into MemSQL from MySQL.