Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 329202B286A for ; Mon, 25 Jun 2012 16:14:38 -0300 (ADT) Received: from nm18-vm0.bullet.mail.ukl.yahoo.com ([217.146.183.95]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SjEk8-0000ay-El for pgsql-performance@postgresql.org; Mon, 25 Jun 2012 19:14:38 +0000 Received: from [217.146.183.184] by nm18.bullet.mail.ukl.yahoo.com with NNFMP; 25 Jun 2012 19:14:22 -0000 Received: from [217.146.183.72] by tm15.bullet.mail.ukl.yahoo.com with NNFMP; 25 Jun 2012 19:14:22 -0000 Received: from [127.0.0.1] by omp1033.mail.ukl.yahoo.com with NNFMP; 25 Jun 2012 19:14:22 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 236125.9038.bm@omp1033.mail.ukl.yahoo.com Received: (qmail 21323 invoked by uid 60001); 25 Jun 2012 19:14:22 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.es; s=s1024; t=1340651662; bh=B1xvA5E+uO8wvIISAmcLaRv8xZWFymhlsP/M8oSDQRs=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=RI4lzT+tKcTDmsScpKIbjVYEmSNOErTTr5Vy7adlZqpWSO6fW9jZ7w/iy6IHlLdfiwdcI9lNoA2qjUmpuGIRosZ8tBD4Hm+eCoyigTwMNuzlYZh1ooL1CUPdGLMaILvbSi+6VAXI1vMO6oFJVgbKnlGk5uUbtAtP30XQ6/zWFgQ= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.es; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=xdR93roIddOf2N38BJiYTg2IzJv4fp1PU2SC/N6VvsbL7z/GKncKm1olLi+GWpt+MtfBs0CWTM5VWvpQzaT5U92LthScSUXgFRnZDpoMoVzD8DBj9yyODwOZfeyzlmXkljwfw8oKcTMQgXayBmh8jbJu96bbfUDEi3JkqNE8SXE=; X-YMail-OSG: xP7D5fcVM1nGiMfASXKYJZGu2kf6z_uzsxB1RpSwkxgPFub BY2bjjcnb Received: from [190.85.40.123] by web171006.mail.ukl.yahoo.com via HTTP; Mon, 25 Jun 2012 20:14:21 BST X-Mailer: YahooMailWebService/0.8.118.349524 References: Message-ID: <1340651661.3160.YahooMailNeo@web171006.mail.ukl.yahoo.com> Date: Mon, 25 Jun 2012 20:14:21 +0100 (BST) From: Alejandro Carrillo Reply-To: Alejandro Carrillo Subject: Re: Postgres delete performance problem To: Frits Jalvingh , "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="2073204126-1640479453-1340651661=:3160" X-Pg-Spam-Score: -1.0 (-) X-Archive-Number: 201206/137 X-Sequence-Number: 47163 --2073204126-1640479453-1340651661=:3160 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable "It does so by removing all constraints, then it compares table contents ro= w by row, inserts missing rows and deletes "extra" rows in the target datab= ase."=0AIf the delete's you do when the constraints and indexes are removed= then you need to create the constraints and indexes before you delete the = rows=0A=0A=0A=0A=0A=0A>________________________________=0A> De: Frits Jalvi= ngh =0A>Para: pgsql-performance@postgresql.org =0A>Enviado: Lun= es 25 de junio de 2012 10:42=0A>Asunto: [PERFORM] Postgres delete performan= ce problem=0A> =0A>=0A>Hi,=0A>=0A>=0A>I have a Java application that tries = to synchronize tables in two databases (remote source to local target). It = does so by removing all constraints, then it compares table contents row by= row, inserts missing rows and deletes "extra" rows in the target database.= Delete performance is incredibly bad: it handles 100 record deletes in abo= ut 16 to 20 seconds(!). Insert and update performance is fine.=0A>=0A>=0A>T= he Java statement to handle the delete uses a prepared statement:=0A>=0A>= =0A>"delete from xxx where xxx_pk=3D?"=0A>=0A>=0A>The delete statement is t= hen executed using addBatch() and executeBatch() (the latter every 100 dele= tes), and committed. Not using executeBatch makes no difference.=0A>=0A>=0A= >An example table where deletes are slow:=0A>=0A>=0A>pzlnew=3D# \d cfs_file= =0A>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Table "public.cfs_file"=0A>=A0 =A0 = =A0 Column =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0Type =A0 =A0 =A0 =A0 =A0 =A0= | Modifiers=A0=0A>------------------+-----------------------------+-------= ----=0A>=A0cfsid =A0 =A0 =A0 =A0 =A0 =A0| bigint =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0| not null=0A>=A0cfs_date_created | timestamp without ti= me zone | not null=0A>=A0cfs_name =A0 =A0 =A0 =A0 | character varying(512) = =A0 =A0 =A0| not null=0A>=A0cfs_cfaid =A0 =A0 =A0 =A0| bigint =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=A0=0A>=A0cfs_cfdid =A0 =A0 =A0 =A0| bigint= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=A0=0A>Indexes:=0A>=A0 =A0 "cf= s_file_pkey" PRIMARY KEY, btree (cfsid)=0A>=0A>=0A>with no FK constraints a= t all, and a table size of 940204 rows.=0A>=0A>=0A>While deleting, postgres= takes 100% CPU all of the time.=0A>=0A>=0A>=0A>=0A>Inserts and updates are= handled in exactly the same way, and these are a few orders of magnitude f= aster than the deletes.=0A>=0A>=0A>I am running the DB on an Ubuntu 12.04 -= 64bits machine with Postgres 9.1, the machine is a fast machine with the d= atabase on ssd, ext4, with 16GB of RAM and a=A0=A0i7-3770 CPU @ 3.40GHz.=0A= >=0A>=0A>Anyone has any idea?=0A>=0A>=0A>Thanks in advance,=0A>=0A>=0A>Frit= s=0A>=0A>=0A>=0A> --2073204126-1640479453-1340651661=:3160 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
"It does so by r= emoving all constraints, then it compares table contents=0A row by row, ins= erts missing rows and deletes "extra" rows in the target=0A database."
If the delete's you do when the constraints and in= dexes are removed then you need to create the constraints and= indexes before you delete the rows

De: Frits Jalvingh <jal@etc.to> Para: pgsql-performance@= postgresql.org
Enviado: Lunes 25 de junio de 2012 10:42
Hi,

I have= a Java application that tries to synchronize tables in two databases (remo= te source to local target). It does so by removing all constraints, then it= compares table contents row by row, inserts missing rows and deletes "extr= a" rows in the target database. Delete performance is incredibly bad: it ha= ndles 100 record deletes in about 16 to 20 seconds(!). Insert and update pe= rformance is fine.
=0A

The Java statement to handle= the delete uses a prepared statement:

"delete fro= m xxx where xxx_pk=3D?"

The delete statement is th= en executed using addBatch() and executeBatch() (the latter every 100 delet= es), and committed. Not using executeBatch makes no difference.
=0A
An example table where deletes are slow:

pzlnew=3D# \d cfs_file
        &= nbsp;         Table "public.cfs_file"
  =     Column      |         &nbs= p;  Type             | Modifiers =0A
------------------+-----------------------------+-----------
 cfsid            | bigint  = ;                    | no= t null
 cfs_date_created | timestamp without time zone | not= null
 cfs_name         | character vary= ing(512)      | not null
=0A
 cfs_cfaid  =      | bigint             &nb= sp;        | 
 cfs_cfdid   &nb= sp;    | bigint               =        | 
Indexes:
  &nbs= p; "cfs_file_pkey" PRIMARY KEY, btree (cfsid)

=0A
=
with no FK constraints at all, and a table size of 940204 rows.
<= div>
While deleting, postgres takes 100% CPU all of the time.=


Inserts and updates are handled in= exactly the same way, and these are a few orders of magnitude faster than = the deletes.
=0A

I am running the DB on an Ubuntu 1= 2.04 - 64bits machine with Postgres 9.1, the machine is a fast machine with= the database on ssd, ext4, with 16GB of RAM and a  i7-3770 CPU @= 3.40GHz.

=0A
Anyone has any idea?

Thanks in advance,

Frits

=0A


--2073204126-1640479453-1340651661=:3160--