naviram naviram - 1 month ago 25
MySQL Question

Atomic counter - redis vs postgres or other?

I need an implementation of an Atomic Counter on cloud to produce a serial integer from concurrent connections. The business behind is a tracking server.

Requirements by priority:




  1. (MUST) Durable - be sure that once a clients gets a number, no other client will ever going to get this same number. no duplicates...

  2. (MUST) Scaleable - current load is 10K/second and 1M/second in future from 200-1000 concurrent client connections. A scaleablity feature of incrementing by 100

  3. (MUST) < +-15ms average (postgres/mysql/redis are great, http latency like DynamoDB is out of the question) this is just to filter out slow solutions

  4. (nice to have) increment by This is a scalability where the client increments by a chunk(e.g. 100) and manages the incrementation in application's memory.

  5. (nice to have) A fare price < 150$ for 5k/s and expecting leaner pricing growth beyond.

  6. (nice to have) HA (High Availability) - I can handle 0.01% failures, but durability is important, I need to have no duplicate numbers.



My alternatives are:




  1. Sequence of postgres
    CREATE SEQUENCE serial CACHE 100; SELECT nextval(sequence)
    - 140$/m MultiAZ AWS RDS db.m3.medium not not as fast as redis but I think is < 7ms in average. the "cache" is a strong feature that should boost performance.

  2. Redis INCR with Redis Sentinel/RDS MultiAZ - cache.m3.medium MultiAZ - 120$/m - Durablity is in question.



redis has INCRBY, and postgres only has the "cache" feature of sequence which requires roundtrip to the DB.

Any input? regarding those two alternatives or others?

Related references:




  1. Atomic counters Postgres vs MongoDB

  2. http://redis.io/topics/persistence

  3. https://www.quora.com/When-should-I-use-redis-as-my-primary-data-store

  4. https://muut.com/blog/technology/redis-as-primary-datastore-wtf.html

  5. https://discuss.elastic.co/t/replacing-redis-with-elasticsearch-get-query-speed-counters-and-lists/5609/2


Answer

I think you are overestimating the risk of a redis failure that leaves it unable to flush to disk and underestimating the risk of any RDBMS doing the same. The risk can be mitigated in both by syncing writes to disk.

In redis this means switching to AOF (Append Only File) mode, as described in the persistence link you already link to.

There is no need to do any expiring key trickery. The atomic behavior of incr and incrby are more than sufficient to ensure uniqueness and durability, especially when combined with AOF persistence.

Redis is just about perfect for this use case. It is fast enough and scaleable. Redis has been around a while now. There are no legitimate durability concerns that wouldn't also be concerns for PostgreSQL or MySQL.

Comments