Thread: High load on commit after important schema changes
Hi, I have following situation: - PostgreSQL 8.2.6 - ~ 1000 schemata - ~ 31k tables - ~600GB database - Linux (2.6.22, suse) - 32GB ram - disk system unknown (some raid with 3ware controllers) One of operations that happens on the database is: begin; call function(); commit; where function is plpgsql function which does: - drop several (n) views/tables - rename ~2n views and tables (and related objects like indexes and constraints) - including ones that are very often used eveyrting is fine up until commit; when commit is called load jumps from ~2 to ~40, despite the fact that there are not much activity on the system (it happens in the morning). couple of "kickers": - io - doesn't show any increase - there is next to none iowait - when entering commit all cores (8 cores from intel xeon E5345) got hammered with *user* calculations. We tested the situation on 2nd system - it has ~ 75% of original data/tables/schemata, we run http siege with standard queries and then ran several times this critical transaction, and the problem never happened. Now, I know the usual is to upgrade pg, and there is plan to do it, but perhaps you have any idea on what might be going on in here? Any chance I could fix it without spending big$ on new hardware, upgrade to 8.4 and total rewrite of system? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
hubert depesz lubaczewski <depesz@depesz.com> writes:
> One of operations that happens on the database is:
> begin;
> call function();
> commit;
> where function is plpgsql function which does:
> - drop several (n) views/tables
> - rename ~2n views and tables (and related objects like indexes and
> constraints) - including ones that are very often used
> eveyrting is fine up until commit;
> when commit is called load jumps from ~2 to ~40, despite the fact that
> there are not much activity on the system (it happens in the morning).
Hm, do you have forty or so idle backends hanging around while this
happens? The only thing I can think of that might be causing this is
shared cache invalidation messages being broadcast to all the other
sessions.
8.4 has some improvements in SI messaging that might or might not
solve it for you. In 8.2 I think the only thing you could do is
not have so many open sessions while you change the schema.
regards, tom lane
On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: > Hm, do you have forty or so idle backends hanging around while this > happens? The only thing I can think of that might be causing this is > shared cache invalidation messages being broadcast to all the other > sessions. I have about 1000 backends running. more of them (like 998) are usually idling. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote:
>> Hm, do you have forty or so idle backends hanging around while this
>> happens? The only thing I can think of that might be causing this is
>> shared cache invalidation messages being broadcast to all the other
>> sessions.
> I have about 1000 backends running.
Ouch. You need to update to 8.4 --- the SI messaging stuff will
definitely be hurting you with that many backends. Or consider
using connection pooling or something to cut the number of backends.
regards, tom lane
On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: > > On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: > >> Hm, do you have forty or so idle backends hanging around while this > >> happens? The only thing I can think of that might be causing this is > >> shared cache invalidation messages being broadcast to all the other > >> sessions. > > I have about 1000 backends running. > Ouch. You need to update to 8.4 --- the SI messaging stuff will > definitely be hurting you with that many backends. Or consider > using connection pooling or something to cut the number of backends. thanks. it looks like i will not be spared this fun :) Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: > > On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: > >> Hm, do you have forty or so idle backends hanging around while this > >> happens? The only thing I can think of that might be causing this is > >> shared cache invalidation messages being broadcast to all the other > >> sessions. > > I have about 1000 backends running. > Ouch. You need to update to 8.4 --- the SI messaging stuff will > definitely be hurting you with that many backends. Or consider > using connection pooling or something to cut the number of backends. Do you have any idea on how (easily) to test if this is the cause of the situation? Will running 1000 connections to db, making each of them run query on some table, keep the connection open, and then in another connection doing the rename thing on it - work? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote:
>> Ouch. You need to update to 8.4 --- the SI messaging stuff will
>> definitely be hurting you with that many backends. Or consider
>> using connection pooling or something to cut the number of backends.
> Do you have any idea on how (easily) to test if this is the cause of the
> situation?
Well, if you have something like oprofile it would probably prove or
disprove the theory that sinvaladt.c is taking all the time.
regards, tom lane