Skip site navigation (1) Skip section navigation (2)

High load on commit after important schema changes

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: High load on commit after important schema changes
Date: 2009-08-28 16:07:25
Message-ID: 20090828160725.GA14853@depesz.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Responses

pgsql-general by date

Next:From: Dave HuberDate: 2009-08-28 16:07:35
Subject: Update /src/tools/msvc to VC++ 2008
Previous:From: Sébastien LardièreDate: 2009-08-28 15:54:22
Subject: Re: [Skytools-users] WAL Shipping + checkpoint

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group