Multithreaded MySql Inserts with Python

Question

Below is the multi-thread script I use to upload data into MySQL. Using threading to do multiple inserts, sounds good to me.

But there are no performance gains. MySql is set to accept multi connections, but when I check the process list I don't see the 5-10 connections I would expect. The cxn string is

Is there any way to fix this?

import  sys, threading, Queue pyodbc

class WorkerThread(threading.Thread):
 def __init__(self, queue):
    threading.Thread.__init__(self)
    self.queue = queue

 def run(self):
    while 1:
        try: # take a job from the queue
            id, null, null2, null3 = self.queue.get_nowait()

        except Queue.Empty:
            raise SystemExit


           In Here I have MySQl connecctions
                *** cxn = pyodbc.connect('DSN=MySQL;PWD=MLML;Option=3') 
            csr = cxn.cursor()
       typical insert , selects Updates


if __name__ == '__main__':
  connections =  25 

  # build a queue with tuples
  queue = Queue.Queue()

        queue.put(row[:3])

   # print queue   

 threads = []
 for dummy in range(connections):
    t = WorkerThread(queue)
    t.start()
    threads.append(t)

# wait for all threads to finish
 for thread in threads:
    thread.join()

Cxn String is set on top. I have tried to have cxn string within the Worker thread, but there that much of improvement. In worker thread, MySQL is one directional in. The tables are truncate then insert. There is usually only one table per worker thead. Its fast and the system is local. But i dont see mutli connections, I would expect to see.

Queue = 30-400 items.

Martin · Accepted Answer · 2011-04-01 18:56:06Z

3

How many items are there in your queue ?

Are all the operations on the same table ? If so, multi-threading might not help if you have select and insert/updates/delete because of locks on the table.

From your exemple, we don't see where you create your connection. Is it created in each thread or are you using the same connection for all threads ?

With 25 threads, your threads might also be fighting for the lock on the queue.

answered Apr 1, 2011 at 18:56

Martin

6,0625 gold badges33 silver badges47 bronze badges

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

6 Comments

g.d.d.c Over a year ago

Is it also possible that because he's using threading instead of multiprocessing that he's encountering issues with the GIL?

Martin Over a year ago

Maybe, but since the threads are IO bound, I'm not sure. Maybe the GIL causes problems since items seem to be fetched one by one on the queue.

Martin Over a year ago

In your process list you don't see many connections ? Which process list are you referring to ? ps, windows taks manager ?

Merlin Over a year ago

Process list on MySql -- direct cxn

Martin Over a year ago

You create multiple MySql connections, one per thread, right?

|

Collectives™ on Stack Overflow

Multithreaded MySql Inserts with Python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related