zen_68
asked on
How long should these procedures take, in your estimation.
1.
2.
3.
1. We have determined that the database needs to be VAACUMed and ANALYZEd. VACUUM is a postgres routine maintenance command which essentially defragments your database. It needs to be run every 1 billion transactions. You are running at 5 transactions/sec right now, and have processed about 468,000,000 transactions since the last VACUUM or initdb, so my guess is that VACUUM has never been run on this data. While a backup of the database consumes 29mb, the size of the data directory is over 700mb. This lends some support to my vacuum hypothesis. VACUUM is also necessary for ANALYZE to run. ANALYZE updates the query planner for the actual data layout, so if one does not run ANALYZE, one can expect query performance to drop over time. From my research, I expect a performance increase when this routine maintenence has been accomplished.
2.
The database has not been tuned to the size of the system. Almost all settings are at the default, which are not optimal for a 4GB system. With some further research, I can change the tuning parameters to make the database faster. This may not be necessary if #1 makes performance good enough. If necessary, it should be possible to tune the database so the entire data set fits in memory.
3.
Postgres is currently at version 8.0.3. This version was released 2005-05-09. I recommend that this be upgraded, at a minimum to 8.0.26, the last bugfix release in the 8.0 stream. The upgrade may require reindexing of some tables and may be conducted in place, however it may be less troublesome to dump the database, initdb, and restore.
With so much space for not so much data I'm afraid your autovacuum (if you have one) is too low so you are likely to need a vacuum FULL and then a reindexdb.
If no autovacuum then set a cron task to vacuum analyse a least every day
vacuum full will lock the table (exclusive lock) while vacuuming it
bumping version to 9 will be a lot of work
hth
If no autovacuum then set a cron task to vacuum analyse a least every day
vacuum full will lock the table (exclusive lock) while vacuuming it
bumping version to 9 will be a lot of work
hth
ASKER
Im grateful for the advice, but what I'm looking for is estimates in how long each of the three procedures would take. Approximately.
hello, can you describe your hardware ? how many disk ? speed ? fsync or not ? raid ? raid + write-back ?
You need to read 700 MB of data and rewrite a part of it. If you have 4 or 6 15K SAS disks in raid 10 array + BBU + write back this should take less than 2 minutes
You need to read 700 MB of data and rewrite a part of it. If you have 4 or 6 15K SAS disks in raid 10 array + BBU + write back this should take less than 2 minutes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://archives.postgresql.org/pgsql-admin/2006-01/msg00320.php
--rebuild only outdated indexex not ALL on each table
(select * from pg_stat_user_indexes
where idx_tup_fetch > (idx_scan * 100) and idx_scan <> 0 and indexrelname not like '%_pkey' and indexrelname not like '%_uniq%' and indexrelname not like '%_ukey' and indexrelname not like 'pk_%' and schemaname = 'public'
union
select * from pg_stat_user_indexes
where idx_tup_read != idx_tup_fetch and indexrelname not like '%_pkey' and indexrelname not like '%_uniq%' and indexrelname not like '%_ukey' and indexrelname not like 'pk_%' and schemaname = 'public'
)
2. As these are config file changes and some require only re-load but others may require re-start PG you may need a short downtime for these.
3. It all depends on your environment and how you plan to do it.