From: | Chris Ruprecht <chrup999(at)yahoo(dot)com> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Performance issues |
Date: | 2001-07-16 00:11:25 |
Message-ID: | B7779B5C.23CF%chrup999@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi all,
I have a table with +/- 5.5 million records. There is a column (field) which
has the default value of a sequence. The records get dumped to postgresql
from another database, about 50'000 records at a time, once a day. I read
these records in using the 'load' feature.
Today, I decided to re-sequence all the records, I did this with:
DROP SEQUENCE Seq_PhoneLog;
CREATE SEQUENCE Seq_PhoneLog START 1;
update phonelog set recno = nextval('seq_phonelog') + 1000000000;
update phonelog set recno = recno - 1000000000;
Each of the update statements is taking about 4 hours. I do this in two
steps so there is no problem with records with duplicate numbers which might
already exist. The recno field has a unique index on it.
I checked disk space and it went down from about 25 GB available to 9 GB
available, with a huge number of files getting created in $PGDATA/pg_xlog,
ache 16 MB in size.
Surely, there is something I can do to make this faster. Here are some of
the values I have set in postgres.conf (the rest is not set)
(the machine is a 850 MHz AMD K7 with 768 MB RAM and 2 x 36 GB Ultra
SCSI-160 for data and tmp storage and 1 x 9 GB Ultra SCSI-160 as boot
drive):
#
# Performance
#
#sort_mem = 512
shared_buffers = 1024
#fsync = true
#
# Optimizer Parameters
#
enable_seqscan = true
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
#ksqo = false
#geqo = true
effective_cache_size = 5000 # default in 8k pages
#random_page_cost = 4
ql_inheritance = true
#
# Deadlock
#
deadlock_timeout = 100
#
# Expression Depth Limitation
#
max_expr_depth = 10000 # min 10
#
# Write-ahead log (WAL)
#
wal_buffers = 32 # min 4
wal_files = 64 # range 0-64
wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync
# Note: default wal_sync_method varies across platforms
wal_debug = 0 # range 0-16
commit_delay = 0 # range 0-100000
commit_siblings = 5 # range 1-1000
checkpoint_segments = 3 # in logfile segments (16MB each), min 1
checkpoint_timeout = 300 # in seconds, range 30-3600
#
# Debug display
#
#silent_mode = false
log_connections = true
log_timestamp = true
log_pid = false
#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Rashid N. Achilov | 2001-07-16 03:47:15 | Re: crypt autorization |
Previous Message | SeungKyu Lee | 2001-07-15 05:09:59 | Is there other benchmark tool than pgbench or xpgbench. |