MySQL going to sleep vs Postgresql
iain duncan
with postgresql? Or if there are any similar issues for pg?
I need to deploy a new site on a very tight schedule in the next two
weeks and the client has a trade show, so it would be A Very Bad Thing
to have problems keeping the site up. If I cut out some variables by
using postgresql, I could do the switch, but right now I know mysql a
lot better.
I got the cronjob hitting a page technique working with mysql on another
site, but it rubs me the wrong way. Is it really reliable? Thoughts on
this issue?
Thanks
iain
Bob Ippolito
I've never seen any issue like that with PostgreSQL and SQLAlchemy and
TG or Pylons. I've also never used SQLObject and/or MySQL in
production with TG though, so I can't really directly compare them.
-bob
Jorge Godoy
> I've never seen any issue like that with PostgreSQL and SQLAlchemy and
> TG or Pylons. I've also never used SQLObject and/or MySQL in
> production with TG though, so I can't really directly compare them.
I have used SO in production. And I also never saw a problem with PostgreSQL
(but I confess I don't like MySQL at all...).
--
Jorge Godoy <jgo...@gmail.com>
Glenn Davy
> Can anyone tell me whether the mysql going to sleep issue also happens
> with postgresql? Or if there are any similar issues for pg?
>
postgres over years for a variety of projects (all non TG :-( ) - never
had any issues of it becoming non-responsive in anyway(or of any other
sort of issue either really)
glenn
iain duncan
> On Sat, 2007-02-03 at 13:33 -0800, iain duncan wrote:
> > Can anyone tell me whether the mysql going to sleep issue also happens
> > with postgresql? Or if there are any similar issues for pg?
> >
> to be fair, i dont know what 'the sleep' issue is, but ive mainly used
> postgres over years for a variety of projects (all non TG :-( ) - never
> had any issues of it becoming non-responsive in anyway(or of any other
> sort of issue either really)
The sleep issue is that when tg runs with cherrypy, it's in a
long-running process that keeps a mysql connection alive. But if mysql
doesn't receive a hit on that connection in a long time, it goes to
sleep and then the first hit fails. Which is Really Annoying. So the
workaround is to have something make a hit every hour. Which is Really
Ugly. ; )
Apparently this was brought to the attention of the mysql folks but I
don't know the status on that these days ...
Iain
Glenn Davy
> The sleep issue is that when tg runs with cherrypy, it's in a
> long-running process that keeps a mysql connection alive. But if mysql
> doesn't receive a hit on that connection in a long time, it goes to
> sleep and then the first hit fails. Which is Really Annoying. So the
> workaround is to have something make a hit every hour. Which is Really
> Ugly. ; )
>
due to unicode issues - (no great loss as postgres is by far my
'sentimental' favourite) but 80% of my paid work atm is ruby on rails +
mysql, so while ive experienced no issues I thought id better check -
apparently mongrel has a timeout value, which if lower than that of the
mysql connection will 'revive' the connection before it 'goes stale' -
perhaps cherrypy can be extended to have something similar to this
added, given lack of response from mysql people?
anyhow good luck - and thanks for the explanation
glenn
iain duncan
>
> > The sleep issue is that when tg runs with cherrypy, it's in a
> > long-running process that keeps a mysql connection alive. But if mysql
> > doesn't receive a hit on that connection in a long time, it goes to
> > sleep and then the first hit fails. Which is Really Annoying. So the
> > workaround is to have something make a hit every hour. Which is Really
> > Ugly. ; )
> >
> yes - that is very ugly - I gave up on mysql with TG, at least for SO
> due to unicode issues - (no great loss as postgres is by far my
> 'sentimental' favourite) but 80% of my paid work atm is ruby on rails +
> mysql, so while ive experienced no issues I thought id better check -
> apparently mongrel has a timeout value, which if lower than that of the
> mysql connection will 'revive' the connection before it 'goes stale' -
> perhaps cherrypy can be extended to have something similar to this
> added, given lack of response from mysql people?
Maybe it does and I don't know about it? CherryPy gurus?
I have every intention of learning Postgresql, but I also have a bad
habit of trying to pick up too many new things at once and digging
myself into a whole of stress and caffeine ...
Iain
Glenn Davy
> habit of trying to pick up too many new things at once and digging
> myself into a whole of stress and caffeine ...
>
I remember hearing Jacob Kaplin-moss (?) of Django fame say you
couldn't pay him not to work on postgres - tend to feels some way
(though in truth, im a DB slut, have been known to work on any platform
for $$)
> Iain
>
>
> >
Mark Ramm
lookup every few min. to keep the database alive.
On 2/3/07, iain duncan <iaind...@telus.net> wrote:
>
--
Mark Ramm-Christensen
email: mark at compoundthinking dot com
blog: www.compoundthinking.com/blog
Mark Ramm
on your other new thread, but I think the best answer was to use the
TurboGears scheduler to hit the DB regularly.
http://docs.turbogears.org/1.0/Scheduler
--Mark
On 2/3/07, iain duncan <iaind...@telus.net> wrote:
>
iain duncan
> This has come up on the list once before, and I answered the same way
> on your other new thread, but I think the best answer was to use the
> TurboGears scheduler to hit the DB regularly.
>
> http://docs.turbogears.org/1.0/Scheduler
Yeah cool, I was wondering whether the mysql guys had made this
unecessary yet. The catch with the schedular method that I don't like is
that it depends on CherryPy staying up. So if you're using the
mod_proxy/mod_rewrite of a 500 error message that wakes up CherryPy in
case of a problem, then a cron job hit should wake up CP and then hit
mysql. If CP goes down and you're using the schedular, then it won't get
woken up until a user hits your site, correct? So mysql could go to
sleep still.
I think the cron job is kind of ugly, but at least it makes sure they
both stay up relying only on cron and wget.
Iain
Karl Guertin
> Yeah cool, I was wondering whether the mysql guys had made this
> unecessary yet.
I haven't looked into it, but I recall discussions about setting a
pool timeout in SA that will disconnect from the server after a
certain amount of inactivity. This allows SA to maintain state rather
than erroring.
Jorge Vargas
yes indeed it was track to a bug on the python mysql driver and not inside mysql itself or SO. I recall reading a thread about it but SF forums are such a mess that I can't locate it now.
As a side note
Sadly the biggest problem mysqldb has is that it's sole driver for python is lacking someone that runs windows to test most drivers it it seems the most users are windows so they bug him a lot to get them :)
Iain
lasizoillo
connections has pool, it is difficult to refresh them all from
scheluder.
Perhaps the only solution is to review pool of connections of
SQLObject and to verify that the returned connections are alive. I
can't estimate this work.
Excuse me poor english.
2007/2/5, Mark Ramm <mark.mch...@gmail.com>:
fumanchu
> On Sun, 2007-04-02 at 12:21 +1100, Glenn Davy wrote:
>
> > > The sleep issue is that when tg runs with cherrypy, it's in a
> > > long-running process that keeps a mysql connection alive. But if mysql
> > > doesn't receive a hit on that connection in a long time, it goes to
> > > sleep and then the first hit fails. Which is Really Annoying. So the
> > > workaround is to have something make a hit every hour. Which is Really
> > > Ugly. ; )
>
> > yes - that is very ugly - I gave up on mysql with TG, at least for SO
> > due to unicode issues - (no great loss as postgres is by far my
> > 'sentimental' favourite) but 80% of my paid work atm is ruby on rails +
> > mysql, so while ive experienced no issues I thought id better check -
> > apparently mongrel has a timeout value, which if lower than that of the
> > mysql connection will 'revive' the connection before it 'goes stale' -
> > perhaps cherrypy can be extended to have something similar to this
> > added, given lack of response from mysql people?
>
> Maybe it does and I don't know about it? CherryPy gurus?
That's a bit out of scope for CherryPy (which is only concerned with
HTTP). You could certainly write an on_start_resource filter/tool to
ping the connection for you, but such a tool isn't (and shouldn't be)
included in CP.
Robert Brewer
System Architect
Amor Ministries
fuma...@amor.org
Jorge Godoy
> That's a bit out of scope for CherryPy (which is only concerned with
> HTTP). You could certainly write an on_start_resource filter/tool to
> ping the connection for you, but such a tool isn't (and shouldn't be)
> included in CP.
IMVHO this belongs to the db connector. In this case the mysqldb connector.
That's not a problem for the ORM (since it isn't caused by it) and that isn't
generic enough to put this burden on servers that work perfectly. Either
MySQL people fix that or the connector people make the workaround there or
you'll end up with this ugly hack on every installation for every project...
--
Jorge Godoy <jgo...@gmail.com>
lasizoillo
The connection URI of mysql accept a connect_timeout (or
connect_time)* parameter. This value is the time in seconds of
timeout. Can any test if a value of -1 or 0 don't close? Another
posibility is set a very very big number.
* My dude:
http://www.sqlobject.org/sqlobject/mysql/mysqlconnection.py.html?f=5&l=210#5
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
http://sourceforge.net/tracker/index.php?func=detail&aid=1523883&group_id=22307&atid=374932
Is a bug in SO? Where is the docs?
2007/2/5, Jorge Godoy <jgo...@gmail.com>: