Shelve is too slow for large dictionaries, what can I do to improve performance?

Question

I am storing a table using python and I need persistence.

Essentially I am storing the table as a dictionary string to numbers. And the whole is stored with shelve

self.DB=shelve.open("%s%sMoleculeLibrary.shelve"%(directory,os.sep),writeback=True) 

I use writeback to True as I found the system tends to be unstable if I don't.

After the computations the system needs to close the database, and store it back. Now the database (the table) is about 540MB, and it is taking ages. The time exploded after the table grew to about 500MB. But I need a much bigger table. In fact I need two of them.

I am probably using the wrong form of persistence. What can I do to improve performance?

Pox

Have you encountering an inadequate CPU usage with large dicts using shelve? – Grisette

Answer 1

For storing a large dictionary of string : number key-value pairs, I'd suggest a JSON-native storage solution such as MongoDB. It has a wonderful API for Python, Pymongo. MongoDB itself is lightweight and incredibly fast, and json objects will natively be dictionaries in Python. This means that you can use your string key as the object ID, allowing for compressed storage and quick lookup.

As an example of how easy the code would be, see the following:

d = {'string1' : 1, 'string2' : 2, 'string3' : 3}
from pymongo import Connection
conn = Connection()
db = conn['example-database']
collection = db['example-collection']
for string, num in d.items():
    collection.save({'_id' : string, 'value' : num})
# testing
newD = {}
for obj in collection.find():
    newD[obj['_id']] = obj['value']
print newD
# output is: {u'string2': 2, u'string3': 3, u'string1': 1}

You'd just have to convert back from unicode, which is trivial.

Phipps

Thank you. The data is actually a symmetric table number*number-->number, but since shelve wanted strings as keys I was somehow induced in writing it as a table string-->number, where the string is "a_b" with a and b numbers and a<b. I don't know json, pymongo nor mongodb. I will study, test, and then see if it works. Also the size of the table can be massive. 500 MB now is probably only about 1/10th of the total size (of this (!) experiments, other can be bigger), so I suspect the best result would be if it was possible to store everything on disk directly. Thanks again, Pietro – Pox

Answer 2

Based on my experience, I would recommend using SQLite3, which comes with Python. It works well with larger databases and key numbers. Millions of keys and gigabytes of data is not a problem. Shelve is totally wasted at that point. Also having separate db-process isn't beneficial, it just requires more context swaps. In my tests I found out that SQLite3 was the preferred option to use, when handling larger data sets locally. Running local database engine like mongo, mysql or postgresql doesn't provide any additional value and also were slower.

Nocturnal

Could you explain more in detail why postgresql or mysql wouldn't give better performance? – Restrictive

Answer 3

I think your problem is due to the fact that you use the writeback=True. The documentation says (emphasis is mine):

Because of Python semantics, a shelf cannot know when a mutable persistent-dictionary entry is modified. By default modified objects are written only when assigned to the shelf (see Example). If the optional writeback parameter is set to True, all entries accessed are also cached in memory, and written back on sync() and close(); this can make it handier to mutate mutable entries in the persistent dictionary, but, if many entries are accessed, it can consume vast amounts of memory for the cache, and it can make the close operation very slow since all accessed entries are written back (there is no way to determine which accessed entries are mutable, nor which ones were actually mutated).

You could avoid using writeback=True and make sure the data is written only once (you have to pay attention that subsequent modifications are going to be lost).

If you believe this is not the right storage option (it's difficult to say without knowing how the data is structured), I suggest sqlite3, it's integrated in python (thus very portable) and has very nice performances. It's somewhat more complicated than a simple key-value store.

See other answers for alternatives.

Cluster

Answer 4

How much larger? What are the access patterns? What kinds of computation do you need to do on it?

Keep in mind that you are going to have some performance limits if you can't keep the table in memory no matter how you do it.

You may want to look at going to SQLAlchemy, or directly using something like bsddb, but both of those will sacrifice simplicity of code. However, with SQL you may be able to offload some of the work to the database layer depending on the workload.

Levins

I am developing a theoretical algorithm, so the problem I am using now has probably tables of a few giga. But as people will use the algorithm for other problems (mainly in systems biology, think big, then increase) it is important to find a solution that can scale up. The access is random, and each term will be accessed few times. The only computation I need to do is to get the value, calculate the value if it is not there, and store it. I was considering using MySQL so that the DB was not in memory. But it would make the code more complex, and slower. Thanks. – Pox