X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DFDCCD1B536 for ; Mon, 13 Oct 2003 19:35:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16265-06 for ; Mon, 13 Oct 2003 16:35:11 -0300 (ADT) Received: from boats_exch01.boats.com (h209-17-182-106.gtconnect.net [209.17.182.106]) by svr1.postgresql.org (Postfix) with ESMTP id B07AAD1B50E for ; Mon, 13 Oct 2003 16:35:11 -0300 (ADT) Received: by BOATS_EXCH01 with Internet Mail Service (5.5.2653.19) id <4S92KRKY>; Mon, 13 Oct 2003 12:33:26 -0700 Received: from GRIFFITHS2 ([192.168.12.192]) by boats_exch01.boats.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id 4S92KRKV; Mon, 13 Oct 2003 12:33:12 -0700 From: David Griffiths To: pgsql-performance@postgresql.org Message-ID: <039801c391c2$4b83d600$6501a8c0@griffiths2> Subject: Any issues with my tuning... Date: Mon, 13 Oct 2003 12:43:32 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0395_01C39187.9C4B7830" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/430 X-Sequence-Number: 4178 This is a multi-part message in MIME format. ------=_NextPart_000_0395_01C39187.9C4B7830 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've been having performance issues with Postgres (sequential scans vs index scans in an update statement). I've read that optimizer will change it's plan based on the resources it thinks are available. In addition, I've read alot of conflicting info on various parameters, so I'd like to sort those out as well. =20 Here's the query I've been having problems with: =20 UPDATE user_account SET last_name=3D'abc' FROM commercial_entity ce, commercial_service cs WHERE user_account.user_account_id =3D ce.user_account_id AND ce.commercial_entity_id=3Dcs.commercial_entity_id; =20 or=20 =20 UPDATE user_account SET last_name =3D 'abc' WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs WHERE user_account.user_account_id =3D ce.user_account_id AND ce.commercial_entity_id =3D cs.commercial_entity_id); =20 Both are about the same. All columns are indexed; all column-types are the same (numeric(10,0)). A vacuum analyze was run just before the last attempt at running the above statement. =20 =20 MACHINE STATS --------------------------- The machine is a dual-Pentium 3 933mhz, 2 gig of RAM, RAID 5 (3xWestern Digital 80 gig drives with 8-meg buffers on 3Ware), Red Hat 9.0 =20 =20 POSTGRES TUNING INFO --------------------------------------- =20 Here are part of the contents of my sysctl.conf file (note that I've played with values as low as 600000 with no difference) kernel.shmmax=3D1400000000 kernel.shmall=3D1400000000 Here's the uncommented-lines from the postgresql.conf file (not the default one in the /usr/local/pgsql directory - I've initialzed the database on a different mount point with more space): =20 tcpip_socket =3D true max_connections =3D 500 shared_buffers =3D 96000 # min max_connections*2 or 16, 8KB each wal_buffers =3D 64 # min 4, typically 8KB each sort_mem =3D 2048 # min 64, size in KB effective_cache_size =3D 6000 # typically 8KB each LC_MESSAGES =3D 'en_US.UTF-8' LC_MONETARY =3D 'en_US.UTF-8' LC_NUMERIC =3D 'en_US.UTF-8' LC_TIME =3D 'en_US.UTF-8' Note that I've played with all these values; shared_buffers has been as low as 5000, and effective_cache_size has been as high as 50000. Sort mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been between 16 and 128. =20 =20 INFO FROM THE MACHINE ----------------------------------------- Here are the vmstat numbers while running the query. =20 procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 0 1 2 261940 11624 110072 1334896 12 0 12 748 177 101 2 4 95 0 1 1 261940 11628 110124 1334836 0 0 0 1103 170 59 2 1 97 0 3 1 261928 11616 110180 1334808 3 0 6 1156 169 67 2 2 96 0 2 1 261892 11628 110212 1334636 7 0 7 1035 186 100 2 2 96 0 1 1 261796 11616 110272 1334688 18 0 18 932 169 79 2 1 97 0 1 1 261780 11560 110356 1334964 3 0 3 4155 192 118 2 7 92 0 1 1 261772 11620 110400 1334956 2 0 2 939 162 63 3 0 97 0 1 3 261744 11636 110440 1334872 6 0 9 1871 171 104 3 2 95 0 0 0 261744 13488 110472 1332244 0 0 0 922 195 1271 3 2 94 0 0 0 261744 13436 110492 1332244 0 0 0 24 115 47 0 1 99 0 0 0 261744 13436 110492 1332244 0 0 0 6 109 36 0 5 95 0 0 0 261744 13436 110492 1332244 0 0 0 6 123 63 0 0 100 0 0 0 261744 13436 110492 1332244 0 0 0 6 109 38 0 0 100 0 0 0 261744 13436 110492 1332244 0 0 0 6 112 39 0 1 99 I'm not overly familiar with Linux, but the swap in-out seems low, as does the io in-out. Have I allocated too much memory? Regardless, it doesn't explain why the optimizer would decide to do a sequential scan.=20 =20 Here's the explain-analyze: =20 Merge Join (cost=3D11819.21..15258.55 rows=3D12007 width=3D752) (actual time=3D4107.64..5587.81 rows=3D20880 loops=3D1) Merge Cond: ("outer".commercial_entity_id =3D "inner".commercial_entity_id) -> Index Scan using comm_serv_comm_ent_id_i on commercial_service cs (cost=3D0.00..3015.53 rows=3D88038 width=3D12) (actual time=3D0.05..487.23 rows=3D88038 loops=3D1) -> Sort (cost=3D11819.21..11846.08 rows=3D10752 width=3D740) (actual time=3D3509.07..3955.15 rows=3D25098 loops=3D1) Sort Key: ce.commercial_entity_id -> Merge Join (cost=3D0.00..9065.23 rows=3D10752 width=3D740) (actual time=3D0.18..2762.13 rows=3D7990 loops=3D1) Merge Cond: ("outer".user_account_id =3D "inner".user_account_id) -> Index Scan using user_account_pkey on user_account (cost=3D0.00..8010.39 rows=3D72483 width=3D716) (actual time=3D0.05..2220.86 rows=3D72483 loops=3D1) -> Index Scan using comm_ent_usr_acc_id_i on commercial_entity ce (cost=3D0.00..4787.69 rows=3D78834 width=3D24) (actual time=3D0.02..55.64 rows=3D7991 loops=3D1) Total runtime: 226239.77 msec (10 rows) =20 ------------------------------------------------------------ =20 Tied up in all this is my inability to grasp what shared_buffers do =20 =46rom " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ": =20 "shbufShared buffers defines a block of memory that PostgreSQL will use to hold requests that are awaiting attention from the kernel buffer and CPU." and "The shared buffers parameter assumes that OS is going to cache a lot of files and hence it is generally very low compared with system RAM." =20 =46rom " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html " =20 "Increase the buffer size. Postgres uses a shared memory segment among its subthreads to buffer data in memory. The default is 512k, which is inadequate. On many of our installs, we've bumped it to ~16M, which is still small. If you can spare enough memory to fit your whole database in memory, do so." =20 Our database (in Oracle) is just over 4 gig in size; obviously, this won't comfortably fit in memory (though we do have an Opteron machine inbound for next week with 4-gig of RAM and SCSI hard-drives). The more of it we can fit in memory the better. =20 What about changing these costs - the doc at http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html doesn't go into a lot of detail. I was thinking that maybe the optimizer decided it was faster to do a sequential scan rather than an index scan based on an analysis of the cost using these values. =20 #random_page_cost =3D 4 # units are one sequential page fetch cost #cpu_tuple_cost =3D 0.01 # (same) #cpu_index_tuple_cost =3D 0.001 # (same) #cpu_operator_cost =3D 0.0025 # (same) =20 David ------=_NextPart_000_0395_01C39187.9C4B7830 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I've been having performance issues with P= ostgres=20 (sequential scans vs index scans in an update statement). I've read that=20 optimizer will change it's plan based on the resources it thinks are availa= ble.=20 In addition, I've read alot of conflicting info on various parameters, so I= 'd=20 like to sort those out as well.
 
Here's the query I've been having problems= =20 with:
 
UPDATE user_account SET last_name=3D'abc'<= BR>FROM=20 commercial_entity ce, commercial_service cs
WHERE=20 user_account.user_account_id =3D ce.user_account_id=20 AND
ce.commercial_entity_id=3Dcs.commercial_entity_id;
 
or
 
UPDATE user_account SET last_name =3D=20 'abc'
 WHERE EXISTS (SELECT 1 FROM commercial_entity ce,=20 commercial_service cs
 WHERE user_account.user_account_id =3D=20 ce.user_account_id AND
 ce.commercial_entity_id =3D=20 cs.commercial_entity_id);
 
Both are about the same.
All columns are indexed; all column-types = are the=20 same (numeric(10,0)). A vacuum analyze was run just before the last attempt= at=20 running the above statement.
 
 
MACHINE STATS
---------------------------
The machine is a dual-Pentium 3 933mhz, 2 = gig of=20 RAM, RAID 5 (3xWestern Digital 80 gig drives with 8-meg buffers on 3Ware), = Red=20 Hat 9.0
 
 
POSTGRES TUNING INFO
---------------------------------------
 
Here are part of the contents of my sysctl= .conf=20 file (note that I've played with values as low as 600000 with no=20 difference)
kernel.shmmax=3D1400000000
kernel.shmall=3D1400000000
Here's the uncommented-lines from the=20 postgresql.conf file (not the default one in the /usr/local/pgsql directory= -=20 I've initialzed the database on a different mount point with more=20 space):
 
tcpip_socket =3D true
max_connections = =3D=20 500
shared_buffers =3D=20 96000          # min=20 max_connections*2 or 16, 8KB each
wal_buffers =3D=20 64            &= nbsp;  =20 # min 4, typically 8KB each
sort_mem =3D=20 2048            = ;    =20 # min 64, size in KB
effective_cache_= size =3D=20 6000     # typically 8KB each
LC_MESSAGES =3D=20 'en_US.UTF-8'
LC_MONETARY =3D 'en_US.UTF-8'
LC_NUMERIC =3D=20 'en_US.UTF-8'
LC_TIME =3D 'en_US.UTF-8'
Note that I've played with all these value= s;=20 shared_buffers has been as low as 5000, and effective_cache_size has been a= s=20 high as 50000. Sort mem has varied between 1024 bytes and 4096 bytes.=20 wal_buffers have been between 16 and 128.
 
 
INFO FROM THE MACHINE
-----------------------------------------
Here are the vmstat numbers while running = the=20 query.
 
  =20 procs           &nbs= p;         =20 memory     =20 swap         =20 io     system     =20 cpu
 r  b  w   swpd   free  = ;=20 buff  cache   si   so   =20 bi    bo   in    cs us sy=20 id
 0  1  2 261940  11624 110072 1334896  = =20 12    0    12   748 =20 177   101  2  4 95
 0  1  1 261940&nb= sp;=20 11628 110124 1334836    0   =20 0     0  1103  170    59 = =20 2  1 97
 0  3  1 261928  11616 110180=20 1334808    3    0     6&n= bsp;=20 1156  169    67  2  2 96
 0  2&n= bsp;=20 1 261892  11628 110212 1334636    7   = =20 0     7  1035  186   100  2&nb= sp; 2=20 96
 0  1  1 261796  11616 110272 1334688  = =20 18    0    18   932 =20 169    79  2  1 97
 0  1  1=20 261780  11560 110356 1334964    3   =20 0     3  4155  192   118  2&nb= sp; 7=20 92
 0  1  1 261772  11620 110400=20 1334956    2    0    =20 2   939  162    63  3  0=20 97
 0  1  3 261744  11636 110440=20 1334872    6    0     9&n= bsp;=20 1871  171   104  3  2 95
 0  0  = 0=20 261744  13488 110472 1332244    0   =20 0     0   922  195  1271  3&nb= sp; 2=20 94
 0  0  0 261744  13436 110492=20 1332244    0    0    =20 0    24  115    47  0  1=20 99
 0  0  0 261744  13436 110492=20 1332244    0    0    =20 0     6  109    36  0  5= =20 95
 0  0  0 261744  13436 110492=20 1332244    0    0    =20 0     6  123    63  0  0= =20 100
 0  0  0 261744  13436 110492=20 1332244    0    0    =20 0     6  109    38  0  0= =20 100
 0  0  0 261744  13436 110492=20 1332244    0    0    =20 0     6  112    39  0  1= =20 99
I'm not overly familiar with Linux, but th= e swap=20 in-out seems low, as does the io in-out. Have I allocated too much memory?= =20 Regardless, it doesn't explain why the optimizer would decide to do a seque= ntial=20 scan.
 
Here's the explain-analyze:
 
 Merge Join  (cost=3D11819.21..1= 5258.55=20 rows=3D12007 width=3D752) (actual time=3D4107.64..5587.81 rows=3D20880=20 loops=3D1)
   Merge Cond: ("outer".commercial_entity_id =3D=20 "inner".commercial_entity_id)
   ->  Index Scan using= =20 comm_serv_comm_ent_id_i on commercial_service cs (cost=3D0.00..3015.53 rows= =3D88038=20 width=3D12) (actual time=3D0.05..487.23 rows=3D88038 loops=3D1)
 &n= bsp;=20 ->  Sort  (cost=3D11819.21..11846.08 rows=3D10752 width=3D740)= (actual=20 time=3D3509.07..3955.15 rows=3D25098=20 loops=3D1)
         Sort Key:=20 ce.commercial_entity_id
        = =20 ->  Merge Join  (cost=3D0.00..9065.23 rows=3D10752 width=3D740= ) (actual=20 time=3D0.18..2762.13 rows=3D7990=20 loops=3D1)
          &= nbsp;   =20 Merge Cond: ("outer".user_account_id =3D=20 "inner".user_account_id)
        = ;      =20 ->  Index Scan using user_account_pkey on user_account=20 (cost=3D0.00..8010.39 rows=3D72483 width=3D716) (actual time=3D0.05..2220.8= 6 rows=3D72483=20 loops=3D1)
          &= nbsp;   =20 ->  Index Scan using comm_ent_usr_acc_id_i on commercial_entity ce&= nbsp;=20 (cost=3D0.00..4787.69 rows=3D78834 width=3D24) (actual time=3D0.02..55.64 r= ows=3D7991=20 loops=3D1)
 Total runtime: 226239.77 msec
(10 rows)
 
------------------------------------------------------------
 
Tied up in all this is my inability to gra= sp what=20 shared_buffers do
 
From "http:= //www.varlena.com/varlena/GeneralBits/Tidbits/perf.html":
 
"Shared buffers defines a = block of=20 memory that PostgreSQL will use to hold requests that are awaiting attentio= n=20 from the kernel buffer and CPU." and "The shared buffers parameter assu= mes=20 that OS is going to cache a lot of files and hence it is generally very low= =20 compared with system RAM."
 
From "http://www= .lyris.com/lm_help/6.0/tuning_postgresql.html"
 
"Increase the buffer size. Postgres uses a= shared=20 memory segment among its subthreads to buffer data in memory. The default i= s=20 512k, which is inadequate. On many of our installs, we've bumped it to ~16M= ,=20 which is still small. If you can spare enough memory to fit your whole data= base=20 in memory, do so."
 
Our database (in Oracle) is just over 4 gi= g in=20 size; obviously, this won't comfortably fit in memory (though we do have an= =20 Opteron machine inbound for next week with 4-gig of RAM and SCSI hard-drive= s).=20 The more of it we can fit in memory the better.
 
What about changing these costs - the doc = at http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.= html doesn't=20 go into a lot of detail. I was thinking that maybe the optimizer decided it= was=20 faster to do a sequential scan rather than an index scan based on an analys= is of=20 the cost using these values.
 
#random_page_cost =3D=20 4           # units are o= ne=20 sequential page fetch cost
#cpu_tuple_cost =3D=20 0.01          #=20 (same)
#cpu_index_tuple_cost =3D 0.001   #=20 (same)
#cpu_operator_cost =3D 0.0025     #=20 (same)
 
David
------=_NextPart_000_0395_01C39187.9C4B7830--