pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres delete performance problem
5+ messages / 4 participants
[nested] [flat]
* Postgres delete performance problem
@ 2012-06-25 15:42 Frits Jalvingh <jal@etc.to>
0 siblings, 2 replies; 5+ messages in thread
From: Frits Jalvingh @ 2012-06-25 15:42 UTC (permalink / raw)
To: pgsql-performance
Hi,
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 about 16 to 20 seconds(!).
Insert and update performance is fine.
The Java statement to handle the delete uses a prepared statement:
"delete from xxx where xxx_pk=?"
The delete statement is then executed using addBatch() and executeBatch()
(the latter every 100 deletes), and committed. Not using executeBatch makes
no difference.
An example table where deletes are slow:
pzlnew=# \d cfs_file
Table "public.cfs_file"
Column | Type | Modifiers
------------------+-----------------------------+-----------
cfsid | bigint | not null
cfs_date_created | timestamp without time zone | not null
cfs_name | character varying(512) | not null
cfs_cfaid | bigint |
cfs_cfdid | bigint |
Indexes:
"cfs_file_pkey" PRIMARY KEY, btree (cfsid)
with no FK constraints at all, and a table size of 940204 rows.
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.
I am running the DB on an Ubuntu 12.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.
Anyone has any idea?
Thanks in advance,
Frits
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres delete performance problem
@ 2012-06-25 15:52 Vitalii Tymchyshyn <tivv00@gmail.com>
parent: Frits Jalvingh <jal@etc.to>
1 sibling, 0 replies; 5+ messages in thread
From: Vitalii Tymchyshyn @ 2012-06-25 15:52 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; +Cc: pgsql-performance
Hello.
This may be wrong type for parameter, like using setObject(param, value)
instead of setObject(param, value, type). Especially if value passed is
string object. AFAIR index may be skipped in this case. You can check by
changing statement to "delete from xxx where xxx_pk=?::bigint". If it
works, check how parameter is set in java code.
25.06.12 18:42, Frits Jalvingh написав(ла):
> Hi,
>
> 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 about
> 16 to 20 seconds(!). Insert and update performance is fine.
>
> The Java statement to handle the delete uses a prepared statement:
>
> "delete from xxx where xxx_pk=?"
>
> The delete statement is then executed using addBatch() and
> executeBatch() (the latter every 100 deletes), and committed. Not
> using executeBatch makes no difference.
>
> An example table where deletes are slow:
>
> pzlnew=# \d cfs_file
> Table "public.cfs_file"
> Column | Type | Modifiers
> ------------------+-----------------------------+-----------
> cfsid | bigint | not null
> cfs_date_created | timestamp without time zone | not null
> cfs_name | character varying(512) | not null
> cfs_cfaid | bigint |
> cfs_cfdid | bigint |
> Indexes:
> "cfs_file_pkey" PRIMARY KEY, btree (cfsid)
>
> with no FK constraints at all, and a table size of 940204 rows.
>
> 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.
>
> I am running the DB on an Ubuntu 12.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.
>
> Anyone has any idea?
>
> Thanks in advance,
>
> Frits
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres delete performance problem
@ 2012-06-25 19:14 Alejandro Carrillo <fasterzip@yahoo.es>
parent: Frits Jalvingh <jal@etc.to>
1 sibling, 1 reply; 5+ messages in thread
From: Alejandro Carrillo @ 2012-06-25 19:14 UTC (permalink / raw)
To: Frits Jalvingh <jal@etc.to>; pgsql-performance
"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."
If 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
>________________________________
> De: Frits Jalvingh <jal@etc.to>
>Para: pgsql-performance@postgresql.org
>Enviado: Lunes 25 de junio de 2012 10:42
>Asunto: [PERFORM] Postgres delete performance problem
>
>
>Hi,
>
>
>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 about 16 to 20 seconds(!). Insert and update performance is fine.
>
>
>The Java statement to handle the delete uses a prepared statement:
>
>
>"delete from xxx where xxx_pk=?"
>
>
>The delete statement is then executed using addBatch() and executeBatch() (the latter every 100 deletes), and committed. Not using executeBatch makes no difference.
>
>
>An example table where deletes are slow:
>
>
>pzlnew=# \d cfs_file
> Table "public.cfs_file"
> Column | Type | Modifiers
>------------------+-----------------------------+-----------
> cfsid | bigint | not null
> cfs_date_created | timestamp without time zone | not null
> cfs_name | character varying(512) | not null
> cfs_cfaid | bigint |
> cfs_cfdid | bigint |
>Indexes:
> "cfs_file_pkey" PRIMARY KEY, btree (cfsid)
>
>
>with no FK constraints at all, and a table size of 940204 rows.
>
>
>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.
>
>
>I am running the DB on an Ubuntu 12.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.
>
>
>Anyone has any idea?
>
>
>Thanks in advance,
>
>
>Frits
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres delete performance problem
@ 2013-01-04 17:49 dankogan <dan@iqtell.com>
parent: Alejandro Carrillo <fasterzip@yahoo.es>
0 siblings, 1 reply; 5+ messages in thread
From: dankogan @ 2013-01-04 17:49 UTC (permalink / raw)
To: pgsql-performance
Hello,
Just wondering whether you were able to resolve this issue.
We are experiencing a very similar issue with deletes using Postgrs 9.0.5 on
Ubuntu 12.04.
Dan
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-delete-performance-problem-tp5714153p5738765.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres delete performance problem
@ 2013-01-04 18:05 Vitalii Tymchyshyn <tivv00@gmail.com>
parent: dankogan <dan@iqtell.com>
0 siblings, 0 replies; 5+ messages in thread
From: Vitalii Tymchyshyn @ 2013-01-04 18:05 UTC (permalink / raw)
To: dankogan <dan@iqtell.com>; +Cc: pgsql-performance
Yes, the issue was resolved by the method I proposed. You need to specify
correct type either on java-side or server-side (query text).
See my explanation (it seems it got out of the list):
The driver does not parse your query, so it simply passes everything to
server.
Server use widening conversion, so "bigint=number" becomes
"bigint::number=number", not "bigint=number::bigint" and index can't be
used when any function is applied to indexed field.
Note, that server can't do "bigint=number::bigint" because it does not know
the numbers you will pass.
Consider examples:
1) 0 = 123456789012345678901234567890
2) 0 = 0.4
Correct value is false, but "bigint=number::bigint" will give you
"overflow" error for the first example and true for the second, which is
incorrect.
2013/1/4 dankogan <dan@iqtell.com>
> Hello,
>
> Just wondering whether you were able to resolve this issue.
> We are experiencing a very similar issue with deletes using Postgrs 9.0.5
> on
> Ubuntu 12.04.
>
> Dan
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Postgres-delete-performance-problem-tp5714153p5738765.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-01-04 18:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-25 15:42 Postgres delete performance problem Frits Jalvingh <jal@etc.to>
2012-06-25 15:52 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2012-06-25 19:14 ` Alejandro Carrillo <fasterzip@yahoo.es>
2013-01-04 17:49 ` dankogan <dan@iqtell.com>
2013-01-04 18:05 ` Vitalii Tymchyshyn <tivv00@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox