Howto Increased performace ?

From: "Amrit Angsusingh" <amrit(at)spr(dot)go(dot)th>
To: pgsql-performance(at)postgresql(dot)org
Subject: Howto Increased performace ?
Date: 2004-12-21 09:31:49
Message-ID: 2452.192.168.2.6.1103621509.squirrel@192.168.2.6
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I used postgresql 7.3.2-1 with RH 9 on a mechine of 2 Xeon 3.0 Ghz and ram
of 4 Gb. Since 1 1/2 yr. when I started to use the database server after
optimizing the postgresql.conf everything went fine until a couple of
weeks ago , my database grew up to 3.5 Gb and there were more than 140
concurent connections.
The server seemed to be slower in the rush hour peroid than before . There
is some swap process too. My top and meminfo are shown here below:
14:52:13 up 13 days, 2:50, 2 users, load average: 5.58, 5.97, 6.11
218 processes: 210 sleeping, 1 running, 0 zombie, 7 stopped
CPU0 states: 7.2% user 55.2% system 0.0% nice 0.0% iowait 36.4% idle
CPU1 states: 8.3% user 56.1% system 0.0% nice 0.0% iowait 34.4% idle
CPU2 states: 10.0% user 57.0% system 0.0% nice 0.0% iowait 32.4% idle
CPU3 states: 6.2% user 55.3% system 0.0% nice 0.0% iowait 37.3% idle
Mem: 4124720k av, 4105916k used, 18804k free, 0k shrd, 10152k buff
2900720k actv, 219908k in_d, 167468k in_c
Swap: 20370412k av, 390372k used, 19980040k free 2781256k
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
14 root 18 0 0 0 0 SW 54.5 0.0 766:10 1
kscand/HighMem
13304 postgres 17 0 280M 280M 276M D 52.5 6.9 0:10 2 postmaster
12035 postgres 16 0 175M 174M 169M D 33.0 4.3 0:26 3 postmaster
13193 postgres 16 0 128M 127M 124M S 28.4 3.1 0:05 3 postmaster
12137 postgres 16 0 498M 497M 431M D 27.2 12.3 0:34 1 postmaster
11 root 15 0 0 0 0 SW 13.9 0.0 363:00 2 kswapd
13241 postgres 16 0 318M 318M 314M D 7.3 7.9 0:09 2 postmaster
13 root 16 0 0 0 0 SW 6.9 0.0 82:17 0
kscand/Normal
13367 postgres 15 0 196M 196M 193M D 6.5 4.8 0:02 2 postmaster
11984 postgres 15 0 305M 305M 301M S 4.9 7.5 2:55 1 postmaster
13331 postgres 16 0 970M 970M 966M S 4.9 24.0 0:22 1 postmaster
12388 postgres 15 0 293M 292M 289M S 3.9 7.2 2:42 3 postmaster
13328 postgres 15 0 276M 276M 272M S 2.7 6.8 0:22 0 postmaster
26 root 16 0 0 0 0 SW 2.3 0.0 10:12 1 kjournald
11831 postgres 15 0 634M 634M 630M S 1.5 15.7 1:33 3 postmaster
12127 postgres 15 0 117M 116M 114M S 1.1 2.8 0:20 1 postmaster
12002 postgres 15 0 429M 429M 426M S 0.9 10.6 0:24 1 postmaster
12991 postgres 15 0 143M 143M 139M S 0.7 3.5 0:29 1 postmaster
13234 postgres 15 0 288M 288M 284M S 0.7 7.1 0:17 0 postmaster
13337 postgres 15 0 172M 171M 168M S 0.3 4.2 0:06 0 postmaster
13413 root 15 0 1276 1276 856 R 0.3 0.0 0:00 0 top
11937 postgres 15 0 379M 379M 375M S 0.1 9.4 2:59 2 postmaster

Shared kernel mem:
[root(at)data3 root]# cat < /proc/sys/kernel/shmmax
4000000000
[root(at)data3 root]# cat < /proc/sys/kernel/shmall
300000000

meminfo :
total: used: free: shared: buffers: cached:
Mem: 4223713280 4200480768 23232512 0 11497472 3555827712
Swap: 20859301888 303460352 20555841536
MemTotal: 4124720 kB
MemFree: 22688 kB
MemShared: 0 kB
Buffers: 11228 kB
Cached: 3367688 kB
SwapCached: 104800 kB
Active: 3141224 kB
ActiveAnon: 684960 kB
ActiveCache: 2456264 kB
Inact_dirty: 220504 kB
Inact_laundry: 166844 kB
Inact_clean: 94252 kB
Inact_target: 724564 kB
HighTotal: 3276736 kB
HighFree: 3832 kB
LowTotal: 847984 kB
LowFree: 18856 kB
SwapTotal: 20370412 kB
SwapFree: 20074064 kB

Postgresql.conf :
# Connection Parameters
#
tcpip_socket = true
#ssl = false

#max_connections = 32
max_connections = 180
#superuser_reserved_connections = 2

#port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal

#virtual_host = ''

#krb_server_keyfile = ''

#
# Shared Memory Size
#
#shared_buffers = 64 # min max_connections*2 or 16, 8KB each
shared_buffers = 250000
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4, typically 8KB each

#
# Non-shared Memory Sizes
#
#sort_mem = 1024 # min 64, size in KB
sort_mem = 60000
#vacuum_mem = 8192 # min 1024, size in KB
vacuum_mem = 20072

# Write-ahead log (WAL)
#
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0 # range 0-100000, in microseconds
commit_delay = 10
#commit_siblings = 5 # range 1-1000
#
#fsync = true
fsync = false
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or
open_datasync
#wal_debug = 0 # range 0-16

#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 5000
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

#default_statistics_target = 10 # range 1-1000

#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_effort = 1
#geqo_generations = 0

Please give me any comment about adjustment my mechine.
Amrit Angsusingh
nakornsawan , Thailand

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2004-12-21 11:21:07 Re: Postgres on Linux Cluster!
Previous Message Iain 2004-12-21 07:09:55 Re: [PERFORM] Postgres version change - pg_dump