From: | Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | REINDEX takes half a day (and still not complete!) |
Date: | 2011-03-19 03:07:33 |
Message-ID: | AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2011-03-19 04:58:17 | Re: REINDEX takes half a day (and still not complete!) |
Previous Message | Scott Marlowe | 2011-03-18 20:29:06 | Re: Request for feedback on hardware for a new database server |