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: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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:
call function();
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

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,


Linkedin:  /  blog:
jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007


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-2017 The PostgreSQL Global Development Group