Webware for Python

Hi Gregory,

> Here's the code I'm using, which I believe it set up correctly
> following the docs, but I'm hitting the following errors:
> 
> Error 1:
> 
> Exception exceptions.TypeError: "'NoneType' object is not callable" in
> <bound method PooledDB.__del__ of <DBUtils.PooledDB.PooledDB instance
> at 0x85095cc>> ignored

In which situation do you get this error? I could not reproduce this.

> Error 2:
> 
> ...
> [Tue Nov 20 17:07:23 2007] [error] [client 127.0.0.1]
> OperationalError: (1040, 'Too many connections')
> 
> I set my.conf to have 16 max connections so I don't know why it says
> there are too many since I only gave the pool 10.

There are two reasons why this can happen.

First, make sure you are setting up POOL only one time, globally. Put a 
print statement after the "POOL = " statement and make sure it prints 
only once.

Second, in your POOL object, you don't allow shared connections 
(maxshared = 0), and in your query function you are also requesting 
dedicated connections. Now if you are calling the function very 
frequently from many threads (and the query time is longer than this 
frequency), then the following will happen: The POOL will first give 
away the 4 connections you put in your pool initially. Then there are no 
connections in the pool any more, and DBUtils will request new 
connections from the database until you reach maxconnections=10, at 
which point it will raise an error, because you have set blocking=False.

As a solution, you can set blocking=True, i.e. DBUtils waits until 
another thread has released its connection so it can be reused. This 
will never throw an exception, but will of course not give the best 
performance it you are not allowing for enough connections.

To improve performance, you can increase the max number of connections 
of MySQL to 100 or so, and set maxconnections a little bit lower (so you 
can still log in as an administrator).

A better solution would be to use shared connections (setting maxshared 
to a positive value), but this works only with a database module that 
provides thread-safe connections. MySQLdb will probably not do it (check 
MySQLdb.threadsafety to be sure, it should be > 1 to allow this, if it's 
not, then DBUtils will assume maxshared = 0 no matter how you set it).

You can also try PersistentDB instead of PooledDB and check whether it 
gives better performance for you.

-- Chris

On Nov 20, 2007 8:11 PM, Christoph Zwerschke <cito@...> wrote:
> Hi Gregory,
>
> > Here's the code I'm using, which I believe it set up correctly
> > following the docs, but I'm hitting the following errors:
> >
> > Error 1:
> >
> > Exception exceptions.TypeError: "'NoneType' object is not callable" in
> > <bound method PooledDB.__del__ of <DBUtils.PooledDB.PooledDB instance
> > at 0x85095cc>> ignored
>
> In which situation do you get this error? I could not reproduce this.

It just started appearing in my apache error log file.  I can't really
tell where it's coming from.  I guess I'll worry about that one later.

I also see that message for another unrelated module (flup).  Perhaps
it's something mod_wsgi is doing.

>
> > Error 2:
> >
> > ...
> > [Tue Nov 20 17:07:23 2007] [error] [client 127.0.0.1]
> > OperationalError: (1040, 'Too many connections')
> >
> > I set my.conf to have 16 max connections so I don't know why it says
> > there are too many since I only gave the pool 10.
>
> There are two reasons why this can happen.
>
> First, make sure you are setting up POOL only one time, globally. Put a
> print statement after the "POOL = " statement and make sure it prints
> only once.

So I'm using Apache (MPM=Prefork) with mod_wsgi and web.py.  So after
further research it looks like, each Apache child process is going to
have it's own global variables.  So perhaps it's not really feasible
for me to have a pool in this setup.

I found child process stuff from reading here:
http://code.google.com/p/modwsgi/wiki/ProcessesAndThreading

> Second, in your POOL object, you don't allow shared connections
> (maxshared = 0), and in your query function you are also requesting
> dedicated connections. Now if you are calling the function very
> frequently from many threads (and the query time is longer than this
> frequency), then the following will happen: The POOL will first give
> away the 4 connections you put in your pool initially. Then there are no
> connections in the pool any more, and DBUtils will request new
> connections from the database until you reach maxconnections=10, at
> which point it will raise an error, because you have set blocking=False.

My error seems to be coming from MySQL though, and not from DBUtils.
It's probably my last point that each apache process is actually
making its own pool.

> As a solution, you can set blocking=True, i.e. DBUtils waits until
> another thread has released its connection so it can be reused. This
> will never throw an exception, but will of course not give the best
> performance it you are not allowing for enough connections.
>
> To improve performance, you can increase the max number of connections
> of MySQL to 100 or so, and set maxconnections a little bit lower (so you
> can still log in as an administrator).

I'm on a pretty low memory machine (300MB) but I could try going a bit
higher on the connections.

> A better solution would be to use shared connections (setting maxshared
> to a positive value), but this works only with a database module that
> provides thread-safe connections. MySQLdb will probably not do it (check
> MySQLdb.threadsafety to be sure, it should be > 1 to allow this, if it's
> not, then DBUtils will assume maxshared = 0 no matter how you set it).

I got:

>>> MySQLdb.threadsafety
1

So that means it's not thread safe?  So no sharing?


> You can also try PersistentDB instead of PooledDB and check whether it
> gives better performance for you.

So I'm thinking either to go with PersistentDB and then have max mysql
connections = max apache servers - 1.

Or I could put a small pool in each child.  So say a pool of 3, and
then have max mysql connections = max apache servers * 3

Any ideas which would give better performance offhand?

Thanks again for all the help.  I think I'm starting to figure this stuff out.

-Greg

Gregory Piñero wrote:
>>> [Tue Nov 20 17:07:23 2007] [error] [client 127.0.0.1]
>>> OperationalError: (1040, 'Too many connections')
>>>
>>> I set my.conf to have 16 max connections so I don't know why it says
>>> there are too many since I only gave the pool 10.
>> There are two reasons why this can happen.
>>
>> First, make sure you are setting up POOL only one time, globally. Put a
>> print statement after the "POOL = " statement and make sure it prints
>> only once.
> 
> So I'm using Apache (MPM=Prefork) with mod_wsgi and web.py.  So after
> further research it looks like, each Apache child process is going to
> have it's own global variables.  So perhaps it's not really feasible
> for me to have a pool in this setup.

Yes, mod_python and mod_wsgi run in the child processes that Apache is 
spawning with the Prefork module. So you are actually living in a 
multi-processing environment here, not a multi-threading environment.

DBUtils has originally been written for multi-threading environments 
such as Webware's ThreadedAppServer (which is not directly coupled to 
Apache's processes even if you use the Apache mod_webkit module). So I 
haven't really thought about multi-processing environments yet.

But that does not mean that DBUtils is not suited here. Each process 
will then have its own pool that you must make accordingly smaller (the 
maximum number of pools will be MaxClients/ThreadsPerChild). Only if 
ThreadsPerChild is very low or even=1 (as I think it is in the Prefork 
module), then using DBUtils will be of no use.

I'll probably need to add something about multi-processing in the 
DBUtils documentation, as we are going more into the direction of 
multi-processing with Python, due to the new multi-core CPUs and the old 
Python GIL problem.

> My error seems to be coming from MySQL though, and not from DBUtils.
> It's probably my last point that each apache process is actually
> making its own pool.

Yes, you're right, this error message is coming from MySQL. I should 
have looked more carefully, since DBUtils would have raised a different 
exception. Each of your Apache processes creates its own pool of 
initially 4 connections, and the 5th process will get into troubles 
then. Plus, you cannot make use of the pools at all, since the processes 
are not creating extra threads.

> I got:
> 
>>>> MySQLdb.threadsafety
> 1
> 
> So that means it's not thread safe?  So no sharing?

Yes. No sharing possible then.

>> You can also try PersistentDB instead of PooledDB and check whether it
>> gives better performance for you.
> 
> So I'm thinking either to go with PersistentDB and then have max mysql
> connections = max apache servers - 1.

PersistentDB will not be of great use either as long as you are using 
the Prefork MPM, since there you will have only one Thread per child.

One way to go would be to switch over to a different MPM and use small 
PooledDB pools or PersistentDB.

I'll think about other solutions in the multi-process environment. 
Probably we can also find something in the Internet, I haven't googled 
yet. If you find something, let me know so I can mention it in the 
DBUtils documentation.

-- Chris

On Nov 21, 2007 3:09 AM, Christoph Zwerschke <cito@...> wrote:
> PersistentDB will not be of great use either as long as you are using
> the Prefork MPM, since there you will have only one Thread per child.
>
> One way to go would be to switch over to a different MPM and use small
> PooledDB pools or PersistentDB.
>
> I'll think about other solutions in the multi-process environment.
> Probably we can also find something in the Internet, I haven't googled
> yet. If you find something, let me know so I can mention it in the
> DBUtils documentation.
>

Thanks for the help.  Let me know what you find when you get a chance
to look around.  I went ahead and put in PersistentDB for now.  I
doesn't seem to hurt performance.

-Greg