when I am trying to fetch data from mysql table using MySQLDB or ZMySqlDA, it is giving errors while excuting SQLs. Whenever, there are concurrent request for connection, if failed to fetch data. If I go for explicit connection for each query, it is giving frequent core dump. Is MySQLDB not good for lighly demanding environment? Please give some tips to solve the problems.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In straight Python I use the following scheme. In Zope your mileage may vary, though I suspect the solution will be similar. You can't share a connection among several threads (each creating its own cursor).
Maintain a Queue.Queue instance containing several MySQLdb.Connection instances. Something like
dbpool = Queue.Queue()
for i in range(10):
dbpool.put(MySQLdb.Connection(...))
Each thread of execution would execute
connection = dbpool.get()
curs = connection.cursor()
curs.execute(...)
when it needs to execute some SQL, and
dbpool.put(connection)
when it's through. The number of connections you place in the Queue instance limits the number of simultaneous requests to MySQL.
I use a scheme like this with no problem.
Skip
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, it should work that way. But I am surprised why it is not implemented in the DA itself. We thought the DA will take care of the connection pooling.
I will try your solution and getback to you.
Thanks for your solution.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
when I am trying to fetch data from mysql table using MySQLDB or ZMySqlDA, it is giving errors while excuting SQLs. Whenever, there are concurrent request for connection, if failed to fetch data. If I go for explicit connection for each query, it is giving frequent core dump. Is MySQLDB not good for lighly demanding environment? Please give some tips to solve the problems.
In straight Python I use the following scheme. In Zope your mileage may vary, though I suspect the solution will be similar. You can't share a connection among several threads (each creating its own cursor).
Maintain a Queue.Queue instance containing several MySQLdb.Connection instances. Something like
dbpool = Queue.Queue()
for i in range(10):
dbpool.put(MySQLdb.Connection(...))
Each thread of execution would execute
connection = dbpool.get()
curs = connection.cursor()
curs.execute(...)
when it needs to execute some SQL, and
dbpool.put(connection)
when it's through. The number of connections you place in the Queue instance limits the number of simultaneous requests to MySQL.
I use a scheme like this with no problem.
Skip
Yes, it should work that way. But I am surprised why it is not implemented in the DA itself. We thought the DA will take care of the connection pooling.
I will try your solution and getback to you.
Thanks for your solution.