gmane.comp.python.sqlalchemy.user

gatto | 5 Sep 21:18

Too many connections


hi everyone.  just started using sqlalchemy and elixir recently.  i'm
not having any issues with coding yet, just this one:

OperationalError: (OperationalError) (1040, 'Too many connections')

as a workaround for this problem, i created a cron job to restart
apache once every hour and that is mostly working.  but i really need
to find out what is causing this.  i'm using a custom built mvc
framework that runs atop mod_python, and in the base application class
i have the following code, which executes once at the beginning of the
request:

	metadata.bind = 'mysql://' + app_config.database.user + ':' +
app_config.database.password + '@' + app_config.database.host + ':
3306/' + app_config.database.schema
	metadata.bind.echo = True
	metadata.bind.recycle = 3600

i had thought setting recycle to 3600 in this way would cause the
connections to be reused every 5 minutes if they had remained open.
we're only getting a couple hundred unique visitors per day currently
so i'm surprised that the error happens so quickly.  how can i figure
this out?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---

(Continue reading)

headers

gatto | 6 Sep 14:03

Re: Too many connections


On Sep 5, 12:19 pm, gatto <thegattoma...@gmail.com> wrote:
> hi everyone.  just started using sqlalchemy and elixir recently.  i'm
> not having any issues with coding yet, just this one:
>
> OperationalError: (OperationalError) (1040, 'Too many connections')
>
> as a workaround for this problem, i created a cron job to restart
> apache once every hour and that is mostly working.  but i really need
> to find out what is causing this.  i'm using a custom built mvc
> framework that runs atop mod_python, and in the base application class
> i have the following code, which executes once at the beginning of the
> request:
>
>         metadata.bind = 'mysql://' + app_config.database.user + ':' +
> app_config.database.password + '@' + app_config.database.host + ':
> 3306/' + app_config.database.schema
>         metadata.bind.echo = True
>         metadata.bind.recycle = 3600
>
> i had thought setting recycle to 3600 in this way would cause the
> connections to be reused every 5 minutes if they had remained open.
> we're only getting a couple hundred unique visitors per day currently
> so i'm surprised that the error happens so quickly.  how can i figure
> this out?

does anyone have any advice on this?  it's a real problem as you might
imagine..  regularly getting "too many connection" errors just because
the app isn't releasing the connections.  the above code is executed
at the start of the application, and at the end, i run

(Continue reading)

headers

Michael Bayer | 6 Sep 18:27

Re: Too many connections


On Sep 6, 2008, at 8:03 AM, gatto wrote:

>
> On Sep 5, 12:19 pm, gatto <thegattoma...@gmail.com> wrote:
>> hi everyone.  just started using sqlalchemy and elixir recently.  i'm
>> not having any issues with coding yet, just this one:
>>
>> OperationalError: (OperationalError) (1040, 'Too many connections')
>>
>> as a workaround for this problem, i created a cron job to restart
>> apache once every hour and that is mostly working.  but i really need
>> to find out what is causing this.  i'm using a custom built mvc
>> framework that runs atop mod_python, and in the base application  
>> class
>> i have the following code, which executes once at the beginning of  
>> the
>> request:
>>
>>        metadata.bind = 'mysql://' + app_config.database.user + ':' +
>> app_config.database.password + '@' + app_config.database.host + ':
>> 3306/' + app_config.database.schema
>>        metadata.bind.echo = True
>>        metadata.bind.recycle = 3600

If this is executed "at the beginning of a request", do you mean  
"each" request ?  If so, this code is creating a new engine on each  
request, since metadata.bind = "somestring" calls create_engine().     
For a mod_python application, create_engine() should be called exactly  
once at the module level, so that one connection pool is created for

(Continue reading)

headers

gatto | 6 Sep 23:07

Re: Too many connections


On Sep 6, 9:27 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 6, 2008, at 8:03 AM, gatto wrote:
> > On Sep 5, 12:19 pm, gatto <thegattoma...@gmail.com> wrote:
> >> hi everyone.  just started using sqlalchemy and elixir recently.  i'm
> >> not having any issues with coding yet, just this one:
>
> >> OperationalError: (OperationalError) (1040, 'Too many connections')
>
> >> as a workaround for this problem, i created a cron job to restart
> >> apache once every hour and that is mostly working.  but i really need
> >> to find out what is causing this.  i'm using a custom built mvc
> >> framework that runs atop mod_python, and in the base application
> >> class
> >> i have the following code, which executes once at the beginning of
> >> the
> >> request:
>
> >>        metadata.bind = 'mysql://' + app_config.database.user + ':' +
> >> app_config.database.password + '@' + app_config.database.host + ':
> >> 3306/' + app_config.database.schema
> >>        metadata.bind.echo = True
> >>        metadata.bind.recycle = 3600
>
> If this is executed "at the beginning of a request", do you mean
> "each" request ?  If so, this code is creating a new engine on each
> request, since metadata.bind = "somestring" calls create_engine().
> For a mod_python application, create_engine() should be called exactly
> once at the module level, so that one connection pool is created for
> the lifespan of the entire process.    Though in theory, if you were

(Continue reading)

headers

Michael Bayer | 6 Sep 23:32

Re: Too many connections


On Sep 6, 2008, at 5:07 PM, gatto wrote:

>
> global phase
>
> try:
> 	if phase == 'start':
> 		phase = 'running'
> except:
> 	phase = 'start'
>
> if phase == 'start':
> 	self.engine = create_engine('mysql://' + app_config.database.user +
> ':' + app_config.database.password + '@' + app_config.database.host +
> ':3306/' + app_config.database.schema)
> 	metadata.bind = self.engine
> 	metadata.bind.recycle = 3600

this greatly complicates what needs to be done.   heres a pseudo  
mod_python application.  "module level" means, "not inside of a def  
which is called within the request".

import sqlalchemy as sa
import sqlalchemy.orm as orm

engine = sa.create_engine(<url>)
meta = sa.MetaData(engine)
# define tables
# define classes

(Continue reading)