From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
Date: Wed, 13 Oct 2010 20:19:26 +1300
Message-ID: <4CB55D7E.8080902@catalyst.net.nz> (raw)
In-Reply-To: <201010122347.20542.neil.whelchel@gmail.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
<4CB2410C.6010603@postnewspapers.com.au>
<201010102115.58729.neil.whelchel@gmail.com>
<201010122347.20542.neil.whelchel@gmail.com>
On 13/10/10 19:47, Neil Whelchel wrote:
>
> Nope...
> So, possible conclusions are:
> 1. Even with VACUUM database table speed degrades as tables are updated.
> 2. Time testing on a freshly INSERTed table gives results that are not real-
> world.
> 3. Filesystem defragmentation helps (some).
> 4. Cache only makes a small difference once a table has been UPDATEd.
>
> I am going to leave this configuration running for the next day or so. This
> way I can try any suggestions and play with any more ideas that I have.
> I will try these same tests on ext4 later, along with any good suggested
> tests.
> I will try MySQL with the dame data with both XFS and ext4.
> -Neil-
>
>
I think that major effect you are seeing here is that the UPDATE has
made the table twice as big on disk (even after VACUUM etc), and it has
gone from fitting in ram to not fitting in ram - so cannot be
effectively cached anymore.
This would not normally happen in real life (assuming UPDATEs only
modify a small part of a table per transaction). However administration
updates (e.g 'oh! - ref 1 should now be ref 2 please update
everything') *will* cause the table size to double.
This is an artifact of Postgres's non overwriting storage manager -
Mysql will update in place and you will not see this.
Try VACUUM FULL on the table and retest.
regards
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz
Subject: Re: Slow count(*) again...
In-Reply-To: <4CB55D7E.8080902@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox