Howto Increased performace ?

Поиск
Список
Период
Сортировка
От Amrit Angsusingh
Тема Howto Increased performace ?
Дата
Msg-id 2452.192.168.2.6.1103621509.squirrel@192.168.2.6
обсуждение исходный текст
Ответы Re: Howto Increased performace ?  (Ragnar Hafstað <gnari@simnet.is>)
Список 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@data3 root]# cat < /proc/sys/kernel/shmmax
4000000000
[root@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



В списке pgsql-performance по дате отправления:

Предыдущее
От: Pierre-Frédéric Caillaud
Дата:
Сообщение: Re: Caching of Queries
Следующее
От: "Thomas Wegner"
Дата:
Сообщение: Re: Speed in V8.0