Parallelizing reads from MySQL database via Python

Question

I need to read data from a huge table (>1million rows, 16 cols of raw text) and do some processing on it. Reading it row by row seems very slow (python, MySQLdb) indeed and I would like to be able to read multiple rows at a time (possibly parallelize it).

Just FYI, my code currently looks something like this:

cursor.execute('select * from big_table')
rows = int(cursor.rowcount)
for i in range(rows):
    row = cursor.fetchone()
    .... DO Processing ...

I tried to run multiple instances of the program to iterate over different sections of the table (for example, the 1st instance would iterate over 1st 200k rows, 2nd instance would iterate over rows 200k-400k ...) but the problem is that the 2nd instance (and 3rd instance and so on) takes FOREVER to get to a stage where it starts looking at row 200k onwards. It almost seems like it is still doing the processing of 1st 200k rows instead of skipping over them. The code I use (for 2nd instance) in this case is something like:

for i in range(rows):
    #Fetch the row but do nothing (need to skip over 1st 200k rows)
    row = cur.fetchone()
    if not i in range(200000,400000):
       continue
    .... DO Processing ...

How can I speed up this process? Is there a clean way to do faster/parallel reads from MySQL database through python?

EDIT 1: I tried the "LIMIT" thing based on the suggestions below. For some reason though when I start 2 processes on my quad core server, it seems like only 1 single process is being run at a time (CPU seems to be time sharing between these processes, as opposed to each core running a separate process). The 2 python processes are using respectively 14% and 9% of the CPUs. Any thoughts what might be wrong?

I would answer about the LIMIT clause but Ignacio already did that. The expense of a DB read occurs when you call a function like fetchone. Your code doesn't skip rows of the database. It simply skips your processing. The expensive DB stuff (IO and memory thrashing) are occurring for each row in each process. — D.Shawley
– D.Shawley, Commented Nov 20, 2011 at 1:23
I know this is an old question, but for posterity - if you don't need i for something specific in your loop, you can simply write for row in cursor instead of for i in range(rows): row = cursor.fetchone(). — Air
– Air, Commented Nov 7, 2013 at 22:17

Ignacio Vazquez-Abrams · Accepted Answer · 2011-11-20 01:19:16Z

2

The LIMIT clause can take two parameters, where the first is the start row and the second is the row count.

SELECT ...
 ...
LIMIT 200000,200000

answered Nov 20, 2011 at 1:19

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

catchdave · Accepted Answer · 2011-11-20 01:28:04Z

1

You may also run into i/o contention on the DB server (even though you are getting the data in chunks, the disks need to serialize the reads at some level). So, rather than reading from mysql in parallel, a single read may work better for you.

Rather than reading 200K rows at a time, you could dump the whole of the data in one hit and process the data (possibly in parallel) in memory, in python.

Potentially, you can use something like psycopg.copy_expert(). Or alternatively, do a mysql dump in a single file and use csv.reader to iterate over it (or sections of it if you're processing it in parallel).

answered Nov 20, 2011 at 1:28

catchdave

9,3552 gold badges29 silver badges16 bronze badges

Comments

sarnold · Accepted Answer · 2011-11-20 01:21:41Z

0

You're exactly right that your attempt to parallelize the second chunk is requesting the first 200k records before it begins processing. You need to use the LIMIT keyword to ask the server to return different results:

select * from big_table LIMIT 0,200000
...
select * from big_table LIMIT 200000,200000
...
select * from big_table LIMIT 400000,200000
...

And so on. Pick the numbers however you wish -- but be aware that memory, network, and disk bandwidths might not give you perfect scaling. In fact, I'd be wary of starting more than two or three of these simultaneously.

answered Nov 20, 2011 at 1:21

sarnold

105k23 gold badges187 silver badges243 bronze badges

Collectives™ on Stack Overflow

Parallelizing reads from MySQL database via Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related