Need advices for mysqldb connection best practice

[Romaric DEFAUX]

Le 20/01/2011 18:58, Dennis Lee Bieber a écrit :
> On Thu, 20 Jan 2011 10:04:12 +0100, Romaric DEFAUX<rde at audaxis.com>
> declaimed the following in gmane.comp.python.general:
>
>
>> So , I thought about some solutions :
>> - restarting the server every sometimes (but it's the worst solution in
>> my mind)
>> - creating a connection (not only cursor) at each client connection (but
>> I'm afraid it overloads the mysql server)
>> - trying to find where I did a mistake, and correct the bug (that why
>> I'm doing by writing this list :), or send me a link that could help me
>> (before writing I googled for one hour and found nothing interresting in
>> my case...)
>>
> 	Do you have multiple clients active at the same time -- using a
> common code/process... (does each client connection start a thread)?
>
>>>> import MySQLdb
>>>> MySQLdb.threadsafety
> 1
> > From PEP 249:
> """
>          threadsafety
>
>              Integer constant stating the level of thread safety the
>              interface supports. Possible values are:
>
>                  0     Threads may not share the module.
>                  1     Threads may share the module, but not connections.
>                  2     Threads may share the module and connections.
>                  3     Threads may share the module, connections and
>                        cursors.
>
>              Sharing in the above context means that two threads may
>              use a resource without wrapping it using a mutex semaphore
>              to implement resource locking. Note that you cannot always
>              make external resources thread safe by managing access
>              using a mutex: the resource may rely on global variables
>              or other external sources that are beyond your control.
>
> """
>
>
> 	Also:
>
>>       con.cursor().execute('SET AUTOCOMMIT=1')
> 	Using .execute() for that may set the MySQL side for autocommit, but
> the MySQLdb adapter will likely still be in the db-api specified mode of
> NO autocommit. There is a low-level (that is, it is part of the DLL/SO
> and not Python source) function for connections:
>
> 	con.autocommit(True)
>
> (the db-api creates connections and invokes con.autocommit(False))
>
> 	This function should both set MySQL AND the db-api adapter for
> autocommit operations.
>
> 	Personally -- it is better when running multiple clients to ensure
> that each client is running as a complete transaction. That means the
> each get their own connection and cursor(s), and manually do
> con.commit() at the end of the transaction; if any errors happen, one
> does a con.rollback() and can inform the user that the sequence failed.
Thanks Dennis for your reply.
I don't use thread. The reason is :
- the time of connection between client and server is really quick, 
around one second
- I've only around 120 clients, updating once an hour, so percent of 
collision is really low, and client can wait few seconds for the connection

Now, I create a new db_connection at each client connection and it seems 
stable (no crash since yesterday vs 1 crash every 2 hours before).
I understand why it's better to commit manually, but if I want to do 
that I have to rewrite lots of things, and it's not my priority at this 
time, because it's stable enough. So I kept the con.autocommit(True).
But I keep your advices in an "improvements list" :)
I know if number of clients increase a lot, I can search in these 
directions :
- using thread
- commiting manually to avoid inconsistents datas
- using a pool of connections to reduce MySQL load

Thanks again

Romaric



-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5361 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20110121/c66f13a5/attachment-0001.bin>