Database/data storage for high volume of simple transactions

Question

I have written a PHP application which requires storage of millions of integers between 0 and 10,000,000 inclusive. Each number is incremented by one very frequently (on average 100 values are updated every second) and read very frequently (20,000 reads per second). The numbers are reset to 0 either nightly, weekly, monthly or annually.

I've got a fairly good handle on MySQL but it feels like overkill, and not very efficient in the process.

Has anyone had to deal with this before, and/or could shed some light on a suitable data storage system?

Many thanks in advance!

Answer 1

Depending on how much memory you have, and how long you have to keep them around - you could just use the APC cache, or memcache. memcache has a nice increment operation, so it's crazy efficient doing it.

BTW, you said you need 20K reads. And you are doing it in PHP? on one server or on a cluster? Sounds like you need some architectural guidance..... :)

If it's a web-app, you are going to be using a cluster. And if it's not a web app, I'd suggest you think about re-doing it in something which isn't PHP. Java, C++ and c# come to mind.