From: Phoenix Kiula <phoenix.kiula@gmail.com>
To: pgsql-performance@postgresql.org
Subject: REINDEX takes half a day (and still not complete!)
Date: Sat, 19 Mar 2011 11:07:33 +0800
Message-ID: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com> (raw)
I have a large table but not as large as the kind of numbers that get
discussed on this list. It has 125 million rows.
REINDEXing the table takes half a day, and it's still not finished.
To write this post I did "SELECT COUNT(*)", and here's the output -- so long!
select count(*) from links;
count
-----------
125418191
(1 row)
Time: 1270405.373 ms
That's 1270 seconds!
I suppose the vaccuum analyze is not doing its job? As you can see
from settings below, I have autovacuum set to ON, and there's also a
cronjob every 10 hours to do a manual vacuum analyze on this table,
which is largest.
PG is version 8.2.9.
Any thoughts on what I can do to improve performance!?
Below are my settings.
max_connections = 300
shared_buffers = 500MB
effective_cache_size = 1GB
max_fsm_relations = 1500
max_fsm_pages = 950000
work_mem = 100MB
temp_buffers = 4096
authentication_timeout = 10s
ssl = off
checkpoint_warning = 3600
random_page_cost = 1
autovacuum = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay = 20
vacuum_cost_limit = 600
autovacuum_naptime = 10
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_scale_factor = 0.01
wal_buffers = 64
checkpoint_segments = 128
checkpoint_timeout = 900
fsync = on
maintenance_work_mem = 512MB
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: phoenix.kiula@gmail.com
Subject: Re: REINDEX takes half a day (and still not complete!)
In-Reply-To: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com>
* 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