Best way of storing incremental numbers?

Question

I'm implementing a voting system for a relatively large website and I'm wondering where should I store the vote count. The main problem is that storing them in the main database would put a lot of strain on it, as MySQL isn't very good at handing lots and lots of simple queries.

My best option so far is to use memcached as it seems perfect for this task (very fast and key/value oriented). The only problem with this solution is that memcached is non-persistent and there is no easy way of saving these values.

Is there something that is specifically designed for this task, preferably with a Python back end?

Have you thought about just using the [`sqlite`](http://docs.python.org/library/sqlite3.html) database built right into Python? — agf, Aug 18 '11 at 18:47
I need something that can handle about 4,000 votes per minute. — Kristina, Aug 18 '11 at 18:49

score 2 · Accepted Answer · answered Aug 18 '11 at 18:39

2

Can you accept some degree of vote loss? If so, you can do a hybrid solution. Every modulo 100 (10, something), update the SQL database with the current memcache value. You can also have a periodic script scan and update if required.

answered Aug 18 '11 at 18:39

Yann Ramin

32,895
3
59
82

score 2 · Answer 2 · answered Aug 18 '11 at 18:58

I just ran this, which is basically the sqlite example from the docs except inserting 5000 rows:

import time
import sqlite3

conn = sqlite3.connect('example')

c = conn.cursor()

# Create table
c.execute('''create table stocks 
    (date text, trans text, symbol text, qty real, price real)''')

print time.time()

for i in xrange(5000):

    # Insert a row of data
    c.execute("""insert into stocks
              values ('2006-01-05','BUY','RHAT',100,35.14)""")


# We can also close the cursor if we are done with it
# Save (commit) the changes
conn.commit()
c.close()

print time.time()

in four tenths of a second on my laptop.

The vast majority of that time is spent in the database commit. So as long as you don't commit to the database too often (once every couple of seconds or less), SQLite can easily handle a load of 5000 votes per second.

4000 votes per minute won't phase it at all, so long as you're not committing after every vote.

score 2 · Answer 3 · answered Aug 18 '11 at 19:00

MySQL isn't very good at handing lots and lots of simple queries

You may have something drastically misconfigured in your MySQL server. MySQL should easily be able to handle 4000 queries per minute. There are benchmarks of MySQL handling over 25k INSERTs per second.

score 1 · Answer 4 · answered Aug 18 '11 at 18:54

1

You can have a look at CUBRID. I haven't tried it, but it seems promising, they advertise almost 100% MySQL compatibility, plus some nice stuff like SELECT INCR(field)

answered Aug 18 '11 at 18:54

Gabi Purcaru

30,940
9
79
95

score 0 · Answer 5 · answered Aug 18 '11 at 18:47

0

Mongodb can work well.Since it can be faster or Google App Engine was designed to scale.

answered Aug 18 '11 at 18:47

Kracekumar

19,457
10
47
56

score 0 · Answer 6 · answered Aug 19 '11 at 03:33

If you like memcached but don't like the fact that it doesn't persist data then you should consider using Membase. Membase is basically memcached with sqlite as the persistence layer. It is very easy to set up and supports the memcached protocol so if you already have memcached set up you can use Membase as a drop in replacement.

Best way of storing incremental numbers?

6 Answers6