Re: 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<r...@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 :



But I keep your advices in an "improvements list" :)

- using thread
- commiting manually to avoid inconsistents datas
- using a pool of connections to reduce MySQL load

Thanks again

Romaric

- 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). I know if number of clients increase a lot, I can search in these directions :

smime.p7s
Description: S/MIME Cryptographic Signature

-- 
http://mail.python.org/mailman/listinfo/python-list