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@xxxxxxxx] Sent: Sunday, October 29, 2006 11:01 AM To: Schwenker, Stephen Cc: pgsql-general@xxxxxxxxxxxxxx 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
