High load on commit after important schema changes - Mailing list pgsql-general

From hubert depesz lubaczewski
Subject High load on commit after important schema changes
Date
Msg-id 20090828160725.GA14853@depesz.com
Whole thread Raw
Responses Re: High load on commit after important schema changes  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

pgsql-general by date:

Previous
From: Sébastien Lardière
Date:
Subject: Re: [Skytools-users] WAL Shipping + checkpoint
Next
From: Dave Huber
Date:
Subject: Update /src/tools/msvc to VC++ 2008