X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8CC232A20D for ; Fri, 1 Oct 2004 20:49:03 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26646-08 for ; Fri, 1 Oct 2004 19:49:00 +0000 (GMT) Received: from hotmail.com (bay18-dav5.bay18.hotmail.com [65.54.187.185]) by svr1.postgresql.org (Postfix) with ESMTP id 06F0532A208 for ; Fri, 1 Oct 2004 20:49:01 +0100 (BST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 1 Oct 2004 12:49:00 -0700 Received: from 67.81.98.198 by BAY18-DAV5.phx.gbl with DAV; Fri, 01 Oct 2004 19:48:53 +0000 X-Originating-IP: [67.81.98.198] X-Originating-Email: [awerman2@hotmail.com] X-Sender: awerman2@hotmail.com From: "Aaron Werman" To: , "Patrick Hatcher" References: Subject: Re: Slow update/insert process Date: Fri, 1 Oct 2004 15:48:50 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004E_01C4A7CE.2553D890" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Message-ID: X-OriginalArrivalTime: 01 Oct 2004 19:49:00.0526 (UTC) FILETIME=[B265E0E0:01C4A7EF] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE, LINES_OF_YELLING X-Spam-Level: X-Archive-Number: 200410/13 X-Sequence-Number: 8489 This is a multi-part message in MIME format. ------=_NextPart_000_004E_01C4A7CE.2553D890 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Some quick notes: - Using a side effect of a function to update the database feels bad to me - how long does the SELECT into varQueryRecord md5(upc.keyp.... function take / what does it's explain look like? - There are a lot of non-indexed columns on that delta master table, such a= s keyf_upc.=20 I'm guessing you're doing 90,000 x {a lot of slow scans} - My temptation would be to rewrite the processing to do a pass of updates,= a pass of inserts,=20 and then the SELECT ----- Original Message -----=20 From: Patrick Hatcher=20 To: pgsql-performance@postgresql.org=20 Sent: Friday, October 01, 2004 2:14 PM Subject: [PERFORM] Slow update/insert process Pg: 7.4.5=20 RH 7.3=20 8g Ram=20 200 g drive space=20 RAID0+1=20 Tables vacuum on a nightly basis=20 The following process below takes 8 hours to run on 90k records and I'm n= ot sure where to being to look for the bottleneck. This isn't the only upd= ating on this database that seems to take a long time to complete. Is ther= e something I should be looking for in my conf settings?=20=20=20 TIA=20 Patrick=20 SQL:=20 ---Bring back only selected records to run through the update process.=20 --Without the function the SQL takes < 10secs to return 90,000 records=20 SELECT count(pm.pm_delta_function_amazon(upc.keyp_upc,'amazon'))=20 FROM mdc_upc upc=20 JOIN public.mdc_products prod ON upc.keyf_products =3D prod.keyp_products= =20 JOIN public.mdc_price_post_inc price ON prod.keyp_products =3D price.keyf= _product=20 JOIN public.mdc_attribute_product ap on ap.keyf_products =3D prod.keyp_pr= oducts and keyf_attribute=3D22=20 WHERE=20 upper(trim(ap.attributevalue)) NOT IN ('ESTEE LAUDER', 'CLINIQUE','ORGINS= ','PRESCRIPTIVES','LANC?ME','CHANEL','ARAMIS','M.A.C','TAG HEUER')=20 AND keyf_producttype<>222=20 AND prod.action_publish =3D 1;=20 Function:=20 CREATE OR REPLACE FUNCTION pm.pm_delta_function_amazon(int4, "varchar") RETURNS bool AS 'DECLARE varkeyf_upc ALIAS FOR $1; varPassword ALIAS FOR $2; varRealMD5 varchar; varDeltaMD5 varchar; varLastTouchDate date; varQuery text; varQuery1 text; varQueryMD5 text; varQueryRecord record; varFuncStatus boolean :=3D false; =20=20=20=20=20 BEGIN -- Check the password IF varPassword <> \'amazon\' THEN Return false; END IF; -- Get the md5 hash for this product SELECT into varQueryRecord md5(upc.keyp_upc || prod.description || pm.pm= _price_post_inc(prod.keyp_products)) AS md5 FROM public.mdc_upc upc JOIN public.mdc_products prod ON upc.keyf_products =3D prod.keyp_produ= cts JOIN public.mdc_price_post_inc price ON price.keyf_product =3D prod.ke= yp_products WHERE upc.keyp_upc =3D varkeyf_upc LIMIT 1 ; =20=20=20 IF NOT FOUND THEN RAISE EXCEPTION \'varRealMD5 is NULL. UPC ID is %\', varkeyf_upc; ELSE varRealMD5:=3DvarQueryRecord.md5; END IF; -- Check that the product is in the delta table and return its hash for = comparison=20 SELECT into varQueryRecord md5_hash,last_touch_date=20 FROM pm.pm_delta_master_amazon WHERE keyf_upc =3D varkeyf_upc LIMIT 1; IF NOT FOUND THEN -- ADD and exit INSERT INTO pm.pm_delta_master_amazon (keyf_upc,status,md5_hash,last_t= ouch_date) values (varkeyf_upc,\'add\',varRealMD5,CURRENT_DATE); varFuncStatus:=3Dtrue; RETURN varFuncStatus; ELSE --Update the record=20 --- If the hash matches then set the record to HOLD IF varRealMD5 =3D varQueryRecord.md5_hash THEN UPDATE pm.pm_delta_master_amazon SET status=3D \'hold\', last_touch_date =3D CURRENT_DATE WHERE keyf_upc =3D varkeyf_upc AND last_touch_date <> CURRENT_DAT= E;=20 varFuncStatus:=3Dtrue; ELSE -- ELSE mark the item as ADD UPDATE pm.pm_delta_master_amazon SET status=3D \'add\', last_touch_date =3D CURRENT_DATE WHERE keyf_upc =3D varkeyf_upc; varFuncStatus:=3Dtrue; END IF; END IF; RETURN varFuncStatus; END;' LANGUAGE 'plpgsql' IMMUTABLE; TableDef=20 CREATE TABLE pm.pm_delta_master_amazon (=20 keyf_upc int4 ,=20 status varchar(6) ,=20 md5_hash varchar(40) ,=20 last_touch_date date=20 )=20 GO=20 CREATE INDEX status_idx=20 ON pm.pm_delta_master_amazon(status)=20 GO=20 CONF=20 --------=20 # WRITE AHEAD LOG=20 #------------------------------------------------------------------------= ---=20 # - Settings -=20 #fsync =3D true # turns forced synchronization on or of= f=20 #wal_sync_method =3D fsync # the default varies across platforms:= =20 # fsync, fdatasync, open_sync, or open_da= tasync=20 wal_buffers =3D 32 # min 4, 8KB each=20 # - Checkpoints -=20 checkpoint_segments =3D 50 # in logfile segments, min 1, 16MB each= =20 checkpoint_timeout =3D 600 # range 30-3600, in seconds=20 #checkpoint_warning =3D 30 # 0 is off, in seconds=20 #commit_delay =3D 0 # range 0-100000, in microseconds=20 #commit_siblings =3D 5 # range 1-1000=20 Patrick Hatcher Macys.Com ------=_NextPart_000_004E_01C4A7CE.2553D890 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Some quick notes:
 
- Using a side effect of a function to upd= ate the=20 database feels bad to me
- how long does the  SELECT into varQueryRecord=20 md5(upc.keyp....
  function take / what does it's expl= ain look=20 like?
- There are a lot of non-indexed columns o= n that=20 delta master table, such as keyf_upc.=20
   I'm guessing you'= re doing=20 90,000 x {a lot of slow scans}
- My temptation would be to rewrite the pr= ocessing=20 to do a pass of updates, a pass of inserts,
  and then the SELECT
----- Original Message -----
Fro= m:=20 Patrick= =20 Hatcher
To: pgsql-performance@postgr= esql.org=20
Sent: Friday, October 01, 2004 2:1= 4=20 PM
Subject: [PERFORM] Slow update/ins= ert=20 process


Pg: 7.4.5
RH=20 7.3
8g Ram
200 g drive space
RAID0+1
Tables vacuu= m on a=20 nightly basis

The followi= ng=20 process below takes 8 hours to run on 90k records and I'm not sure where = to=20 being to look for the bottleneck.  This isn't the only updating on t= his=20 database that seems to take a long time to complete.  Is there somet= hing=20 I should be looking for in my conf settings?  

TIA
Patrick


SQL:= =20
---Bring back only selected records = to run=20 through the update process.
-= -Without=20 the function the SQL takes < 10secs to return 90,000 records=20
SELECT=20 count(pm.pm_delta_function_amazon(upc.keyp_upc,'amazon'))
FROM mdc_upc upc
JOIN public.mdc_products prod ON upc.keyf_products =3D=20 prod.keyp_products
JOIN=20 public.mdc_price_post_inc price ON prod.keyp_products =3D=20 price.keyf_product
JOIN=20 public.mdc_attribute_product ap on ap.keyf_products =3D prod.keyp_product= s and=20 keyf_attribute=3D22
WHERE=20
upper(trim(ap.attributevalue)= ) NOT IN=20 ('ESTEE LAUDER',=20 'CLINIQUE','ORGINS','PRESCRIPTIVES','LANC?ME','CHANEL','ARAMIS','M.A.C','= TAG=20 HEUER')
AND=20 keyf_producttype<>222
A= ND=20 prod.action_publish =3D 1;


Function:

C= REATE OR=20 REPLACE FUNCTION pm.pm_delta_function_amazon(int4, "varchar")
 RE= TURNS=20 bool AS
'DECLARE
   varkeyf_upc       &nbs= p;=20        ALIAS FOR $1;
   varPassword &nbs= p;=20              ALIAS FOR $2;
 = =20  varRealMD5              =20  varchar;
   varDeltaMD5         &n= bsp;=20      varchar;
   varLastTouchDate   &nbs= p;=20    date;
   varQuery         &= nbsp;=20       text;
   varQuery1      = =20           text;
   varQueryMD5 &nbs= p;=20              text;
 =20  varQueryRecord        record;
 =20  varFuncStatus        boolean :=3D false;
&nb= sp;=20  
BEGIN

-- Check the password
 IF varPassword <= >=20 \'amazon\' THEN
   Return false;
 END IF;

--=20  Get the md5 hash for this product
 SELECT into varQueryReco= rd=20 md5(upc.keyp_upc || prod.description ||=20 pm.pm_price_post_inc(prod.keyp_products)) AS md5
   FROM=20 public.mdc_upc upc
   JOIN public.mdc_products prod ON=20 upc.keyf_products =3D prod.keyp_products
   JOIN=20 public.mdc_price_post_inc price ON price.keyf_product =3D=20 prod.keyp_products
   WHERE upc.keyp_upc =3D varkeyf_upc LIM= IT 1=20 ;
 

 IF NOT FOUND THEN
   RAISE EXCEPTIO= N=20 \'varRealMD5 is NULL. UPC ID is %\', varkeyf_upc;
 ELSE
 = =20  varRealMD5:=3DvarQueryRecord.md5;
 END IF;

--  = Check=20 that the product is in the delta table and return its hash for comparison= =20
 SELECT into varQueryRecord md5_hash,last_touch_date
 = =20  FROM pm.pm_delta_master_amazon
   WHERE keyf_upc =3D= =20 varkeyf_upc LIMIT 1;

 IF NOT FOUND THEN
   -- AD= D and=20 exit
   INSERT INTO pm.pm_delta_master_amazon=20 (keyf_upc,status,md5_hash,last_touch_date)
   values=20 (varkeyf_upc,\'add\',varRealMD5,CURRENT_DATE);
 =20  varFuncStatus:=3Dtrue;
   RETURN=20 varFuncStatus;
 ELSE
   --Update the record
&nbs= p;=20    --- If the hash matches then set the record to HOLD
 = ;=20  IF varRealMD5 =3D varQueryRecord.md5_hash THEN
    &nb= sp;=20  UPDATE pm.pm_delta_master_amazon
       =20  SET status=3D \'hold\',
       =20  last_touch_date =3D  CURRENT_DATE
      &nbs= p;WHERE=20 keyf_upc =3D  varkeyf_upc AND last_touch_date <> CURRENT_DATE;= =20
       varFuncStatus:=3Dtrue;
 =20  ELSE
       --  ELSE mark the item as= =20 ADD
       UPDATE pm.pm_delta_master_amazon
&nb= sp;=20        SET status=3D \'add\',
     =  =20  last_touch_date =3D  CURRENT_DATE
      &nbs= p;WHERE=20 keyf_upc =3D  varkeyf_upc;
     =20  varFuncStatus:=3Dtrue;
   END IF;
  END=20 IF;

 RETURN varFuncStatus;
END;'
 LANGUAGE 'plpgsq= l'=20 IMMUTABLE;



TableDe= f=20
CREATE TABLE pm.pm_delta_master_amaz= on (=20
    keyf_upc  =  =20           int4 ,
    status             &= nbsp;=20   varchar(6) ,
  &n= bsp;=20 md5_hash               varchar(40) ,=20
    last_touch_date  =  =20    date
  &nbs= p;=20 )
GO

CREATE INDEX status_idx

    ON=20 pm.pm_delta_master_amazon(status)
GO



CONF
--------
# WRITE AHE= AD=20 LOG
#---------------------------------------------------------------= ------------=20

# - Settings -

#fsync =3D true         &n= bsp;  =20       # turns forced synchronization on or off
= #wal_sync_method =3D fsync      = ;  #=20 the default varies across platforms:
                  &= nbsp;=20             # fsync, fdatasync, open_sync, = or=20 open_datasync
wal_buffers =3D= 32  =20              # min 4, 8KB each= =20

# - Checkpoints -
checkpoint_segments =3D 50      = ;  #=20 in logfile segments, min 1, 16MB each
checkpoint_timeout =3D 600        # range 30= -3600, in=20 seconds
#checkpoint_warning = =3D 30=20        # 0 is off, in seconds
#commit_delay =3D 0        = ;  =20     # range 0-100000, in microseconds
#commit_siblings =3D 5       &n= bsp;  =20  # range 1-1000


= Patrick=20 Hatcher
Macys.Com
------=_NextPart_000_004E_01C4A7CE.2553D890--