pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Gagnon <dgagnon@siunik.com>
To: pg@fastcrypt.com
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem on delete from for 10k rows.  May
Date: Wed, 16 Mar 2005 09:26:26 -0500
Message-ID: <42384212.8070000@siunik.com> (raw)
In-Reply-To: <42383D60.7080900@fastcrypt.com>
References: <42375281.9090102@siunik.com>
	<20050315233834.GA68209@winnie.fuhr.org>
	<4238322F.10302@siunik.com>
	<42383D60.7080900@fastcrypt.com>

Hi,

I'm using ibatis. But in this particular case the sql statement come 
from a plain ascii file and it's run by the Ibatis ScriptRunner class.  
Beside the fact this class come from ibatis framework it's just plain 
sql connection (I'm I wrong???).  Just to be sure, here is the code from 
the class.  I must say that i run script that contains create table, 
alter table, insert   statements with the same runner. 

If I wrong please tell me .. I like to be wrong when the result is 
eliminating a misunderstanding from my part :-)

Thanks for your help!

/David



 public void runScript(Connection conn, Reader reader)
            throws IOException, SQLException {
        StringBuffer command = null;
        try {
            LineNumberReader lineReader = new LineNumberReader(reader);
            String line = null;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuffer();
                }
                String trimmedLine = line.trim();
                if (trimmedLine.startsWith("--")) {
                    println(trimmedLine);
                    if (log.isDebugEnabled()) {
                        log.debug(trimmedLine);
                    }
                } else if (trimmedLine.length() < 1 || 
trimmedLine.startsWith("//")) {
                    //Do nothing
                } else if (trimmedLine.endsWith(";")) {
                    command.append(line.substring(0, 
line.lastIndexOf(";")));
                    command.append(" ");
                    Statement statement = conn.createStatement();

                    println(command);
                    if (log.isDebugEnabled()) {
                        log.debug(command);
                    }

                    boolean hasResults = false;
                    if (stopOnError) {
                        hasResults = statement.execute(command.toString());
                    } else {
                        try {
                            statement.execute(command.toString());
                        } catch (SQLException e) {
                            e.fillInStackTrace();
                            printlnError("Error executing: " + command);
                            printlnError(e);
                        }
                    }

                    if (autoCommit && !conn.getAutoCommit()) {
                        conn.commit();
                    }

                    ResultSet rs = statement.getResultSet();
                    if (hasResults && rs != null) {
                        ResultSetMetaData md = rs.getMetaData();
                        int cols = md.getColumnCount();
                        for (int i = 0; i < cols; i++) {
                            String name = md.getColumnName(i);
                            print(name + "\t");
                        }
                        println("");
                        while (rs.next()) {
                            for (int i = 0; i < cols; i++) {
                                String value = rs.getString(i);
                                print(value + "\t");
                            }
                            println("");
                        }
                    }

                    command = null;
                    try {
                        statement.close();
                    } catch (Exception e) {
                        // Ignore to workaround a bug in Jakarta DBCP
//                        e.printStackTrace();
                    }
                    Thread.yield();
                } else {
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!autoCommit) {
                conn.commit();
            }
        } catch (SQLException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            log.error("Error executing: " + command, e);
            throw e;
        } catch (IOException e) {
            e.fillInStackTrace();
            printlnError("Error executing: " + command);
            printlnError(e);
            log.error("Error executing: " + command, e);
            throw e;
        } finally {
            conn.rollback();
            flush();
        }
    }


Dave Cramer wrote:

>
>
> David Gagnon wrote:
>
>> Hi All,
>>
>> I rerun the example with the debug info turned on in postgresl. As 
>> you can see all dependent tables (that as foreign key on table IC) 
>> are emptied before the DELETE FROM IC statement is issued.  For what 
>> I understand the performance problem seem to came from those selects 
>> that point back to IC ( LOG:  statement: SELECT 1 FROM ONLY 
>> "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of 
>> them.  I don't know where they are comming from.  But if I want to 
>> delete the content of the table (~10k) it may be long to those 6 
>> selects for each deleted rows.  Why are those selects are there ?  
>> Are those select really run on each row deleted?
>
>
> You are using hibernate. Hibernate is generating them to lock the tables.
>
>>
>>
>> I'm running version 7.4.5 on cygwin.  I ran the same delete from 
>> pgAdminIII and I got 945562ms for all the deletes within the same 
>> transaction  .. (so I was wrong saying it took less time in 
>> PgAdminIII... sorry about this).
>>
>> Do you have any idea why those 6 selects are there?
>
>
> Hibernate
>
>>
>> Maybe I can drop indexes before deleting the content of the table.  I 
>> didn't planned to because tables are quite small and it's more 
>> complicated in my environment.  And tell me if I'm wrong but if I 
>> drop indexed do I have to reload all my stored procedure (to reset 
>> the planner related info)??? Remember having read that somewhere.. 
>> (was it in the Postgresql General Bit newletter ...anyway)
>>
>> Thanks for your help I really appréciate it :-)
>>
>> /David
>>
>> LOG:  duration: 144.000 ms
>> LOG:  statement: DELETE FROM YN
>> LOG:  duration: 30.000 ms
>> LOG:  statement: DELETE FROM YO
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" 
>> = $1 AND "ynyonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = 
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" 
>> = $1 AND "yryonum" = $2 FOR UPDATE OF x
>> LOG:  duration: 83.000 ms
>> LOG:  connection received: host=127.0.0.1 port=2196
>> LOG:  connection authorized: user=admin database=webCatalog
>> LOG:  statement: set datestyle to 'ISO'; select version(), case when 
>> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else 
>> getdatabaseencoding() end;
>> LOG:  duration: 2.000 ms
>> LOG:  statement: set client_encoding = 'UNICODE'
>> LOG:  duration: 0.000 ms
>> LOG:  statement: DELETE FROM IY
>> LOG:  duration: 71.000 ms
>> LOG:  statement: DELETE FROM IA
>> LOG:  duration: 17.000 ms
>> LOG:  statement: DELETE FROM IQ
>> LOG:  duration: 384.000 ms
>> LOG:  statement: DELETE FROM IC
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" 
>> = $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" 
>> = $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = 
>> $1 FOR UPDATE OF x
>> LOG:  duration: 656807.000 msMichael Fuhr wrote:
>>
>>
>>
>>
>>
>> -----------------------
>> DELETE FROM BM;
>> DELETE FROM BD;
>> DELETE FROM BO;
>> DELETE FROM IL;
>> DELETE FROM YR;
>> DELETE FROM YN;
>> DELETE FROM YO;
>> DELETE FROM IY;
>> DELETE FROM IA;
>> DELETE FROM IQ;
>> DELETE FROM IC;
>>
>> Michael Fuhr wrote:
>>
>>> On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
>>>
>>>  
>>>
>>>> Il get this strange problem when deleting rows from a Java 
>>>> program.  Sometime (For what I noticed it's not all the time) the 
>>>> server take almost forever to delete rows from table.
>>>>   
>>>
>>>
>>>
>>> Do other tables have foreign key references to the table you're
>>> deleting from?  If so, are there indexes on the foreign key columns?
>>>
>>> Do you have triggers or rules on the table?
>>>
>>> Have you queried pg_locks during the long-lasting deletes to see
>>> if the deleting transaction is waiting for a lock on something?
>>>
>>>  
>>>
>>>> I rememeber having tried to delete the content of my table (IC) from
>>>> PgAdminIII and I took couples of seconds!!! Not minutes.
>>>>   
>>>
>>>
>>>
>>> How many records did you delete in this case?  If there are foreign
>>> key references, how many records were in the referencing tables?
>>> How repeatable is the disparity in delete time?  A single test case
>>> might have been done under different conditions, so it might not
>>> mean much.  No offense intended, but "I remember" doesn't carry as
>>> much weight as a documented example.
>>>
>>>  
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>




view thread (12+ messages)  latest in thread

Message-ID: <42384212.8070000@siunik.com>
Permalink:  ../42384212.8070000@siunik.com/
Also on:    postgresql.org/message-id/42384212.8070000@siunik.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dgagnon@siunik.com, pg@fastcrypt.com, mike@fuhr.org
  Subject: Re: Performance problem on delete from for 10k rows.  May
  In-Reply-To: <42384212.8070000@siunik.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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