Re: DELETE performance issues

Hey,

The api uses hibernate to do all the work. Here is a bit of the log of
the sql queries it does. The JBPM_bytelock queries seem to execute
quite fast but when the api starts switching back and forth from
JBPM_BYTEARRAY and JBPM_VARIABLEINSTANCE it seems to run slow. Like
probably about 1 second for each set of 2 deletes. Which means that
they are taking approximately 500ms to execute each one which seems slow
because the application can create records faster than then can be
deleted. I'm working on a way to bulk purge the database but I haven't
got there yet so I'm just trying to tune the current process.

Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_BYTEBLOCK where PROCESSFILE_=?
Hibernate: delete from JBPM_MODULEINSTANCE where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_VARIABLEINSTANCE where ID_=?
Hibernate: delete from JBPM_BYTEARRAY where ID_=?
Hibernate: delete from JBPM_TOKENVARIABLEMAP where ID_=?
Hibernate: delete from JBPM_MODULEINSTANCE where ID_=?
Hibernate: delete from JBPM_PROCESSINSTANCE where ID_=? and VERSION_=?
Hibernate: delete from JBPM_TOKEN where ID_=? and VERSION_=?

Now, to answer your questions,

All the tables have primary keys and when I explain the queries, they
use the primary key index. I have increased the shared memory which
only seems to help when the same table is accessed synchronously but
this switching back and forth between tables seems to be slow. There
are approximately 600,000 rows in the tables. And these tables do not
have any references point to them but they do reference other tables.
The autovacuum is on.

We're using Postgresql 8.1.4 and the server is a dual 3.2ghz cpu linux
system.

I'm guessing that the problem lies in loading the indexes off the disk
each time the table switches. Is there a way to view what Postgres is
doing in the back end. Ie, loading index from disk or using it in
memory.... Is there a way to see what memory is being used and what's
in it?

Here is my current config changes...
shared_buffers = 24000 # 192MB
work_mem = 50240
max_fsm_pages = 40000
max_fsm_relations = 2000

bgwriter_lru_percent = 50.0
bgwriter_lru_maxpages = 50

wal_buffers = 64

I hope you can all help.

Thank you,

Steve.

-----Original Message-----
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
Sent: Sunday, October 29, 2006 11:01 AM
To: Schwenker, Stephen
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] DELETE performance issues

On Fri, Oct 27, 2006 at 03:40:51PM -0400, Schwenker, Stephen wrote:
> I'm having a performance issue with postgresql. I'm using it with
> JBoss BPM api and when I use the api to delete a process instance from

> the database, it seems like the delete requests are taking quite a
> long time to delete.

How long is "quite a long time"? How long are you expecting the delete
to take? Do you get better performance if you don't use the API, like
when executing the same statement in psql? How many rows are you
deleting? How many rows are in the table? Is the delete able to use an
index? Do you have unindexed foreign key references to the table you're
deleting from? What does EXPLAIN ANALYZE show?
Are you regularly vacuuming and analyzing the table? What version of
PostgreSQL are you using? What OS and hardware?

> Can someone tell me what logging I can turn on so I can figure out
> what is causing the high load and slow deletes? I can then use that
> info to tune the runtime parameters.

What non-default settings are you currently using?

--
Michael Fuhr