pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedA Tale of 2 algorithms
3+ messages / 2 participants
[nested] [flat]
* A Tale of 2 algorithms
@ 2012-10-01 21:24 Colin Taylor <colin.taylor@gmail.com>
2012-10-02 04:54 ` Re: A Tale of 2 algorithms Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 3+ messages in thread
From: Colin Taylor @ 2012-10-01 21:24 UTC (permalink / raw)
To: pgsql-performance
Hi, previously I selected categorized data for update then updated counts
or inserted a new record if it was a new category of data.
select all categories
update batches of categories
or insert batches [intermingled as they hit batch size]
Problem was the select was saturating the network (pulling back far more
data than needed too)
So I switched to doing optimistic updates where I checked for 0 row updates
and made inserts out of them.
optimistic update batches
followed by insert batches
New problem massive table bloat. I'm losing gigabytes of disk an hour which
I can only recover by clustering.
Now's the bit where I lose some of my audience by saying I'm having this
bloat problem on 8.3.7 and 8.4.4 but not 9.0. I'd love to upgrade obviously
but that's out of my hands and I've been told not an option in the short
term.
My thoughts are: surely 0-row updates dont cause this or have impact on the
vacuum. I'm still doing the same updates after all why have things
degenerated so badly?
While it made sense to me that the dead tuples are now more in the middle
of the table than the end somehow and since autovacuum starts from the back
that might be the cause, but I've turned on full autovacuum logging and
there is seemingly very little vaccuming going on in either scenario (we
have a nightly scheduled cluster). In desperation I've also doubled the
freespace map settings in 8.3 to the seemingly very large max_fsm_pages =
25000000 and max_fsm_relations = 200000 without improvement.
Any suggestions? These are roughly 0.5 to 1TB databases with 8GB shared
buffers and work mem set appropriately and otherwise running fine.
cheers
Colin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A Tale of 2 algorithms
2012-10-01 21:24 A Tale of 2 algorithms Colin Taylor <colin.taylor@gmail.com>
@ 2012-10-02 04:54 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-05 00:34 ` Re: A Tale of 2 algorithms Colin Taylor <colin.taylor@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Craig Ringer @ 2012-10-02 04:54 UTC (permalink / raw)
To: Colin Taylor <colin.taylor@gmail.com>; +Cc: pgsql-performance
On 10/02/2012 05:24 AM, Colin Taylor wrote:
> My thoughts are: surely 0-row updates dont cause this or have impact on
> the vacuum. I'm still doing the same updates after all why have things
> degenerated so badly?
What exactly is bloating? Have you checked? Is it the table its self?
One of its indexes? Something else?
--
Craig Ringer
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A Tale of 2 algorithms
2012-10-01 21:24 A Tale of 2 algorithms Colin Taylor <colin.taylor@gmail.com>
2012-10-02 04:54 ` Re: A Tale of 2 algorithms Craig Ringer <ringerc@ringerc.id.au>
@ 2012-10-05 00:34 ` Colin Taylor <colin.taylor@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Colin Taylor @ 2012-10-05 00:34 UTC (permalink / raw)
To: pgsql-performance
On Tue, Oct 2, 2012 at 5:54 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/02/2012 05:24 AM, Colin Taylor wrote:
>
>> My thoughts are: surely 0-row updates dont cause this or have impact on
>> the vacuum. I'm still doing the same updates after all why have things
>> degenerated so badly?
>>
>
> What exactly is bloating? Have you checked? Is it the table its self? One
> of its indexes? Something else?
>
> --
> Craig Ringer
>
The table and its indexes, I have them in separate tablespaces so its quite
apparent.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-10-05 00:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-01 21:24 A Tale of 2 algorithms Colin Taylor <colin.taylor@gmail.com>
2012-10-02 04:54 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-05 00:34 ` Colin Taylor <colin.taylor@gmail.com>
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