python efficiency and large objects in memory

Question

i have a multiple processes each dealing with lists that have 40000 tuples. this nearly maxes the memory available on the machine. if i do this:

        while len(collection) > 0:
            row = collection.pop(0)
            row_count = row_count + 1
            new_row = []
            for value in row:
                if value is not None:
                    in_chars = str(value)
                else:
                    in_chars = ""

                #escape any naughty characters
                new_row.append("".join(["\\" + c if c in redshift_escape_chars else c for c in in_chars]))
            new_row = "\t".join(new_row)
            rows += "\n"+new_row
            if row_count % 5000 == 0:
                gc.collect()

does this free more memory ?

Answer 1

Since the collection is shrinking at the same rate that rows is growing, your memory usage will remain stable. The gc.collect() call is not going to make much difference.

Memory management in CPython is subtle. Just because you remove references and run a collection cycle doesn't necessarily mean that the memory will be returned to the OS. See this answer for details.

To really save memory, you should structure this code around generators and iterators instead of large lists of items. I'm very surprised you say you're having connection timeouts because fetching all the rows should not take much more time than fetching a row at a time and performing the simple processing you are doing. Perhaps we should have a look at your db-fetching code?

If row-at-a-time processing is really not a possibility, then at least keep your data as an immutable deque and perform all processing on it with generators and iterators.

I'll outline these different approaches.

First of all, some common functions:

# if you don't need random-access to elements in a sequence
# a deque uses less memory and has faster appends and deletes
# from both the front and the back.
from collections import deque
from itertools import izip, repeat, islice, chain
import re

re_redshift_chars = re.compile(r'[abcdefg]')

def istrjoin(sep, seq):
    """Return a generator that acts like sep.join(seq), but lazily

    The separator will be yielded separately
    """
    return islice(chain.from_iterable(izip(repeat(sep), seq)), 1, None)

def escape_redshift(s):
    return re_redshift_chars.sub(r'\\\g<0>', s)

def tabulate(row):
    return "\t".join(escape_redshift(str(v)) if v is not None else '' for v in row)

Now the ideal is row-at-a-time processing, like this:

cursor = db.cursor()
cursor.execute("""SELECT * FROM bigtable""")
rowstrings = (tabulate(row) for row in cursor.fetchall())
lines = istrjoin("\n", rowstrings)
file_like_obj.writelines(lines)
cursor.close()

This will take the least possible amount of memory--only a row at a time.

If you really need to store the entire resultset, you can modify the code slightly:

cursor = db.cursor()
cursor.execute("SELECT * FROM bigtable")
collection = deque(cursor.fetchall())
cursor.close()
rowstrings = (tabulate(row) for row in collection)
lines = istrjoin("\n", rowstrings)
file_like_obj.writelines(lines)

Now we gather all results into collection first which remains entirely in memory for the entire program run.

However we can also duplicate your approach of deleting collection items as they are used. We can keep the same "code shape" by creating a generator that empties its source collection as it works. It would look something like this:

def drain(coll):
    """Return an iterable that deletes items from coll as it yields them.

    coll must support `coll.pop(0)` or `del coll[0]`. A deque is recommended!
    """
    if hasattr(coll, 'pop'):
        def pop(coll):
            try:
                return coll.pop(0)
            except IndexError:
                raise StopIteration
    else:
        def pop(coll):
            try:
                item = coll[0]
            except IndexError:
                raise StopIteration
            del coll[0]
            return item
    while True:
        yield pop(coll)

Now you can easily substitute drain(collection) for collection when you want to free up memory as you go. After drain(collection) is exhausted, the collection object will be empty.

Answer 2

If your algorithm depends on pop'ing from the left side or beginning of a list, you can use deque object from collections as a faster alternative.

As a comparison:

import timeit

f1='''
q=deque()
for i in range(40000):
    q.append((i,i,'tuple {}'.format(i)))

while q:
    q.popleft()
'''

f2='''
l=[]
for i in range(40000):
    l.append((i,i,'tuple {}'.format(i)))

while l:
    l.pop(0)
'''

print 'deque took {:.2f} seconds to popleft()'.format(timeit.timeit(stmt=f1, setup='from collections import deque',number=100))
print 'list took {:.2f} seconds to pop(0)'.format(timeit.timeit(stmt=f2,number=100))

Prints:

deque took 3.46 seconds to to popleft()
list took 37.37 seconds to pop(0)

So for this particular test of popping from the beginning of the list or queue, deque is more than 10x faster.

This large advantage is only for the left side however. If you run this same test with pop() on both the speed is roughly the same. You can also reverse the list in place and pop from the right side to get the same results as popleft from the deque.

---

In term of 'efficiency', it will be far more efficient to process single rows from the database. If that is not an option, process your list (or deque) 'collection' in place.

Try something along these lines.

First, break out the row processing:

def process_row(row):
    # I did not test this obviously, but I think I xlated your row processing faithfully
    new_row = []
    for value in row:
        if value:
            in_chars = str(value)        
        else
            in_char=''
        new_row.append("".join(["\\" + c if c in redshift_escape_chars else c for c in in_chars]))  
    return '\t'.join(new_row)    

Now look at using a deque to allow fast pops from the left:

def cgen(collection):
    # if collection is a deque:
    while collection:
        yield '\n'+process_row(collection.popleft())

Or if you want to stick to a list:

def cgen(collection):
    collection.reverse()
    while collection:
        yield '\n'+process_row(collection.pop())

I think that your original approach of pop(0), process the row, and call gc every 5000 rows is probably suboptimal. The gc will be called automatically far more often than that anyway.

My final recommendation:

1. Use a deque. It just like a list but faster for left side push or pops;
2. Use popleft() so you do not need to reverse the list (if the order of collection is meaningful);
3. Process your collection in place as a generator;
4. Throw out the notion of calling gc since it is not doing anything for you.
5. Throw out 1-4 here if you can just call the db and get 1 row and process 1 row at a time!