Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Kevin Brown <kevin@sysexperts.com>,
	pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Mon, 21 Apr 2003 09:14:43 -0700
User-Agent: KMail/1.4.3
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
	<26961.1050852092@sss.pgh.pa.us> <20030421004630.GL1847@filer>
In-Reply-To: <20030421004630.GL1847@filer>
MIME-Version: 1.0
Message-Id: <200304210914.43552.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable

Kevin,

> > Josh already mentioned this as a problem with user-trigger-based
> > counting.
>
> Right, but the trigger based mechanism probably magnifies the issue by
> orders of magnitude, and thus can't necessarily be used as an argument
> against an internally-implemented method.

I'm not sure about that, Kevin.   The production trigger test was written i=
n C=20
(by Joe Conway), using some of the best memory/efficiency management he cou=
ld=20
devise.   I could buy that the trigger mechanism adds a certain fixed=20
overhead to the process, but not the contention that we were seeing ...=20
especially not the geometric progression of inefficiency as the transaction=
=20
count went up.   We'll talk about this offlist; I may be able to get the=20
client to authorize letting you examine the database.

For further detail, our setup was sort of a "destruction test"; including:
1) a slightly underpowered server running too many processes;
2) a very high disk contention environment, with multiple applications=20
fighting for I/O.
3) running COUNT(*), GROUP BY x on a table with 1.4 million rows, which was=
=20
being updated in batches of 10,000 rows to 40,000 rows every few minutes.

As I said before, the overhead for c-trigger based accounting, within the M=
VCC=20
framework, was quite tolerable with small update batches, only 9-11% penalt=
y=20
to the updates overall for batches of 100-300 updates.   However, as we=20
increased the application activity, the update penalty increased, up to=20
40-45% with the full production load.

It's not hard to figure out why; like most user's servers, the aggregate=20
caching table was on the same disk as the table(s) being updated.    The=20
resut was a huge amount of disk-head-skipping between the updated table and=
=20
the aggregate caching table every time a commit hit the database, with rand=
om=20
seek times increasing the longer the time since the last VACUUM.

Now, on a better server with these tables on fast RAID or on different=20
spindles, I expect the result would be somewhat better.   However, I also=
=20
suspect that many of the users who complain the loudest about slow count(*)=
=20
are operating in single-spindle environments.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco