CODEWITHSUNDEEP
pythonmysqlmultithreading
Merlin
Merlin

Reputation: 25639

Multithreaded MySql Inserts with Python

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.

Upvotes: 4

Views: 6550

Answers (1)

Martin
Martin

Reputation: 6032

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.

Upvotes: 3

Related Questions