pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimization postgresql 8.1.4 FC 6 X64 ?
3+ messages / 3 participants
[nested] [flat]
* Optimization postgresql 8.1.4 FC 6 X64 ?
@ 2007-03-24 06:15 amrit angsusingh <amritspr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: amrit angsusingh @ 2007-03-24 06:15 UTC (permalink / raw)
To: pgsql-performance
I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32
bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one
with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try
to use rather the same parameter from the previous postgresql.conf :-
the older server config --
shared_buffers = 31744
#sort_mem = 1024 # min 64, size in KB
sort_mem = 8192
#vacuum_mem = 8064 # min 1024, size in KB
vacuum_mem = 32768
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or
open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_segments = 8
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
commit_delay = 20
#commit_siblings = 5 # range 1-1000
#effective_cache_size = 153600
effective_cache_size = 307800
I use pgbench to test the speed of my older database server and the result
is
bash-3.00$ pgbench test -t 20 -c 30 -s 50
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 30
number of transactions per client: 20
number of transactions actually processed: 600/600
tps = 337.196481 (including connections establishing)
tps = 375.478735 (excluding connections establishing)
But my newer database server configuration is somewhat like this;-
max_connections = 200
#shared_buffers = 2000 # min 16 or max_connections*2, 8KB each
shared_buffers = 31744
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1024 # min 64, size in KB
work_mem = 8192
#maintenance_work_mem = 16384 # min 1024, size in KB
maintenance_work_mem = 131078
#max_stack_depth = 2048 # min 100, size in KB
#commit_delay = 0 # range 0-100000, in microseconds
commit_delay = 20
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_segments = 8
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # in seconds, 0 is off
#effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 307800
#autovacuum = off # enable autovacuum subprocess?
#autovacuum_naptime = 60 # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
and the result of pgbench from my new server is only
-- pgbench test -t 20 -c 30 -s 50
tps = 197 (including connections establishing)
tps = 212
1. How should I adjust my new configuration to improve the performance ?
2. And should I set autovaccum = on and if it is on what is the other proper
parameter to be set?
Thank a lot for your help.....
Amrit Angsusingh
Thailand
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimization postgresql 8.1.4 FC 6 X64 ?
@ 2007-03-24 10:44 Heikki Linnakangas <heikki@enterprisedb.com>
parent: amrit angsusingh <amritspr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2007-03-24 10:44 UTC (permalink / raw)
To: amrit angsusingh <amritspr@gmail.com>; +Cc: pgsql-performance
amrit angsusingh wrote:
> I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32
> bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one
> with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try
> to use rather the same parameter from the previous postgresql.conf :-
> ...
> I use pgbench to test the speed of my older database server and the result
> is
>
> bash-3.00$ pgbench test -t 20 -c 30 -s 50
> ...
-t 20 is not enough to give repeatable results. Try something like -t 1000.
The speed of pgbench in that configuration (scaling factor 50, fsync
enabled) is limited by the speed you can fsync the WAL. There isn't much
you can do in postgresql.conf for that. If you get similar results with
higher -t setting, it may be because your new RAID and drives have
slightly higher latency.
You're better off testing with real queries with your real database.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Optimization postgresql 8.1.4 FC 6 X64 ?
@ 2007-03-24 13:31 Dave Cramer <pg@fastcrypt.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dave Cramer @ 2007-03-24 13:31 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: amrit angsusingh <amritspr@gmail.com>; pgsql-performance
I also think there have been changes in pgbench itself.
Make sure you run the same pgbench on both servers.
Dave
On 24-Mar-07, at 6:44 AM, Heikki Linnakangas wrote:
> amrit angsusingh wrote:
>> I try to change my database server from the older one ie. 2Cpu
>> Xeon 2.4 32
>> bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the
>> newer one
>> with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG
>> 8.14 and try
>> to use rather the same parameter from the previous
>> postgresql.conf :-
>> ...
>> I use pgbench to test the speed of my older database server and
>> the result
>> is
>> bash-3.00$ pgbench test -t 20 -c 30 -s 50
>> ...
>
> -t 20 is not enough to give repeatable results. Try something like -
> t 1000.
>
> The speed of pgbench in that configuration (scaling factor 50,
> fsync enabled) is limited by the speed you can fsync the WAL. There
> isn't much you can do in postgresql.conf for that. If you get
> similar results with higher -t setting, it may be because your new
> RAID and drives have slightly higher latency.
>
> You're better off testing with real queries with your real database.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-24 13:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-24 06:15 Optimization postgresql 8.1.4 FC 6 X64 ? amrit angsusingh <amritspr@gmail.com>
2007-03-24 10:44 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-03-24 13:31 ` Dave Cramer <pg@fastcrypt.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