advise on performance issues please
Hi
this is the first time I'm working with Posgresql other than a defualt install.
I have a CentOS 5.5 virtual machine with -
4 virtual cpus , 8 Gig RAM , resource pool set to High (8000)
running on a vmware ESXi 4.1 host -
4 x 2.4 GHz cpus AMD 6 cores each, 96 Gig ram, storage is provided by
HP Left hand SAN iSCSI.
I'm running post Postgresql 8.4.4 rpm from the postgresql repo.
What I need to clarify is wether the performance issue the website
that uses database is experiencing is related to postgresql miss
configuration or bad code in the site.
Customisations in the postgresql.conf
max_connections = 1000
shared_buffers = 4096MB
temp_buffers = 512MB
work_mem = 10MB # min 64kB
maintenance_work_mem = 160MB # min 1MB
fsync = off
synchronous_commit = on
wal_sync_method = open_sync
full_page_writes = off
effective_cache_size = 32MB
join_collapse_limit = 1
autovacuum_vacuum_cost_delay = 100ms
checkpoint_segments = 60
this configuration gives the following pgbench results
[macgre@topnz15209-linux ~]$ pgbench -h drvppgs01 -U postgres -c 40 -T 120 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 40
duration: 120 s
number of transactions actually processed: 47070
tps = 391.660580 (including connections establishing)
tps = 392.523468 (excluding connections establishing)
pgbench was setup with pgbench -h drvppgs01 -U postgres -c 40 -T 120
-i test -F 100 -S 15
mpstat every 5 seconds gives the following:
11:17:15 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:15 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1154.93
11:17:15 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.52 989.97
11:17:15 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.42
11:17:15 PM 2 7.30 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:15 PM 3 8.98 0.00 1.70 0.13 0.12 0.41
0.00 88.66 87.54
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7226
-/+ buffers/cache: 363 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:20 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:20 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.01
11:17:20 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:20 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.42
11:17:20 PM 2 7.30 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:20 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.66 87.63
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7226
-/+ buffers/cache: 364 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:25 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:25 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.09
11:17:25 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:25 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.42
11:17:25 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:25 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.66 87.71
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7226
-/+ buffers/cache: 363 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:30 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:30 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.18
11:17:30 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:30 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.41
11:17:30 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:30 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.65 87.80
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7226
-/+ buffers/cache: 363 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:35 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:35 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.23
11:17:35 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:35 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.41
11:17:35 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:35 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.65 87.86
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7227
-/+ buffers/cache: 363 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:40 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:40 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.28
11:17:40 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:40 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.41
11:17:40 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:40 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.65 87.91
total used free shared buffers cached
Mem: 7983 7748 235 0 157 7227
-/+ buffers/cache: 363 7620
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:45 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:45 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.67 1155.33
11:17:45 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:45 PM 1 11.77 0.00 2.09 0.07 0.09 0.33
0.00 85.65 75.40
11:17:45 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:45 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.65 87.96
total used free shared buffers cached
Mem: 7983 7749 234 0 157 7227
-/+ buffers/cache: 364 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:50 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:50 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.38
11:17:50 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:50 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.65 75.40
11:17:50 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:50 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.65 88.02
total used free shared buffers cached
Mem: 7983 7749 234 0 157 7227
-/+ buffers/cache: 364 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:17:55 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:17:55 PM all 9.04 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.43
11:17:55 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:17:55 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.40
11:17:55 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:17:55 PM 3 8.99 0.00 1.70 0.13 0.12 0.42
0.00 88.64 88.07
total used free shared buffers cached
Mem: 7983 7749 234 0 157 7227
-/+ buffers/cache: 364 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:00 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:00 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.48
11:18:00 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:18:00 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.40
11:18:00 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:18:00 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.64 88.13
total used free shared buffers cached
Mem: 7983 7749 234 0 157 7227
-/+ buffers/cache: 364 7619
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:05 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:05 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.53
11:18:05 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:18:05 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.39
11:18:05 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:18:05 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.64 88.18
total used free shared buffers cached
Mem: 7983 7734 248 0 157 7211
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:10 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:10 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.58
11:18:10 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:18:10 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.39
11:18:10 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.86 2.00
11:18:10 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.64 88.23
total used free shared buffers cached
Mem: 7983 7735 248 0 157 7212
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:15 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:15 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.63
11:18:15 PM 0 8.10 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:18:15 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.39
11:18:15 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:15 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.28
total used free shared buffers cached
Mem: 7983 7736 247 0 157 7213
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:20 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:20 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.68
11:18:20 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.97
11:18:20 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.38
11:18:20 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:20 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.34
total used free shared buffers cached
Mem: 7983 7737 246 0 157 7214
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:25 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:25 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.73
11:18:25 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.51 989.96
11:18:25 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.38
11:18:25 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:25 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.39
total used free shared buffers cached
Mem: 7983 7738 244 0 157 7215
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:30 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:30 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.78
11:18:30 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.50 989.96
11:18:30 PM 1 11.77 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.38
11:18:30 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:30 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.44
total used free shared buffers cached
Mem: 7983 7739 243 0 157 7216
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:35 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:35 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.83
11:18:35 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.50 989.96
11:18:35 PM 1 11.78 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.38
11:18:35 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:35 PM 3 9.00 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.50
total used free shared buffers cached
Mem: 7983 7740 243 0 157 7217
-/+ buffers/cache: 365 7618
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:40 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:40 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.88
11:18:40 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.50 989.96
11:18:40 PM 1 11.78 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.37
11:18:40 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:40 PM 3 9.01 0.00 1.70 0.13 0.12 0.42
0.00 88.62 88.55
total used free shared buffers cached
Mem: 7983 7678 305 0 157 7218
-/+ buffers/cache: 302 7681
Swap: 1725 0 1725
Linux 2.6.18-194.26.1.el5 (drvppgs01) 11/30/2010
11:18:45 PM CPU %user %nice %sys %iowait %irq %soft
%steal %idle intr/s
11:18:45 PM all 9.05 0.00 1.77 0.22 0.06 0.24
0.00 88.66 1155.87
11:18:45 PM 0 8.11 0.00 1.80 0.44 0.02 0.13
0.00 89.50 989.96
11:18:45 PM 1 11.78 0.00 2.10 0.07 0.09 0.33
0.00 85.64 75.37
11:18:45 PM 2 7.31 0.00 1.50 0.25 0.00 0.09
0.00 90.85 2.00
11:18:45 PM 3 9.01 0.00 1.70 0.13 0.12 0.42
0.00 88.63 88.54
total used free shared buffers cached
Mem: 7983 7674 308 0 157 7218
-/+ buffers/cache: 298 7685
Swap: 1725 0 1725
Are these stats realisitic ? Is the server performing correctly ? Can
I configure it better ?
Thanks
G
On Tue, 30 Nov 2010 04:34:32 -0600, Gregory Machin <gdm@linuxpro.co.za>
wrote:
running on a vmware ESXi 4.1 host -
4 x 2.4 GHz cpus AMD 6 cores each, 96 Gig ram, storage is provided by
HP Left hand SAN iSCSI.
Does the VM do iSCSI itself to get access to the filesystem on the SAN, or
is this just a generic setup where ESX's datastores are on the iSCSI SAN?
Mark
On 11/30/2010 4:34 AM, Gregory Machin wrote:
Hi
this is the first time I'm working with Posgresql other than a defualt install.I have a CentOS 5.5 virtual machine with -
4 virtual cpus , 8 Gig RAM , resource pool set to High (8000)running on a vmware ESXi 4.1 host -
4 x 2.4 GHz cpus AMD 6 cores each, 96 Gig ram, storage is provided by
HP Left hand SAN iSCSI.I'm running post Postgresql 8.4.4 rpm from the postgresql repo.
What I need to clarify is wether the performance issue the website
that uses database is experiencing is related to postgresql miss
configuration or bad code in the site.Customisations in the postgresql.conf
max_connections = 1000
shared_buffers = 4096MB
temp_buffers = 512MB
work_mem = 10MB # min 64kB
maintenance_work_mem = 160MB # min 1MB
fsync = off
synchronous_commit = on
wal_sync_method = open_sync
full_page_writes = off
effective_cache_size = 32MB
join_collapse_limit = 1
autovacuum_vacuum_cost_delay = 100ms
checkpoint_segments = 60this configuration gives the following pgbench results
[macgre@topnz15209-linux ~]$ pgbench -h drvppgs01 -U postgres -c 40 -T 120 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 40
duration: 120 s
number of transactions actually processed: 47070
tps = 391.660580 (including connections establishing)
tps = 392.523468 (excluding connections establishing)pgbench was setup with pgbench -h drvppgs01 -U postgres -c 40 -T 120
-i test -F 100 -S 15mpstat every 5 seconds gives the following:
pgbench needs its scale (-s) and #connections (-c) to be close. You
have a scale of 1 and 40 connections, so this is a bad test. Init with
-s 40, then run with -c at 40 or less.
Your mpstat output is hard to read because of the word wrapping. Not
sure if iostat would be more readable (its usually the one I look at
(I'd never even heard of mpstat before now)).
fsync = off
Thats a bad idea if you care about your data.
effective_cache_size = 32MB
That looks wrong, you better read up on that one in the docs.
-Andy