Postgres Pro
Downloads
Home   >   mailing lists

Thread: Good performance?

Good performance?

From
Rafal Kedziorski
Date:
Hi,

I have following tables:

with id as number(20,0):
CREATE TABLE public.firm (
   firm_id numeric(20, 0) NOT NULL,
   name varchar(40) NOT NULL,
   CONSTRAINT firm_pkey PRIMARY KEY (firm_id)
)

with id as int8:

CREATE TABLE public.firmint8 (
   firmint8_id int8 NOT NULL,
   name varchar(40) NOT NULL,
   CONSTRAINT firmint8_pkey PRIMARY KEY (firmint8_id)
)

my system:
- dual PIII 800 MHz with 640 MB RAM
- cygwin
- PostgreSQL 7.3.1 (default configuration after install thru cygwin)
- J2SE 1.4.1_01
- JDBC driver for J2SE 1.4.1_01 and J2SE 1.3.1_06

I get very bad performance inserting 1000 simple values in the tables
defined above. I'm using PreparedStatement without Batch.

with J2SE 1.4.1_01 it need:

java db.InsertFirmSQLNumber
InsertFirmSQLNumber() needed 74438 for creating 1000 entries
InsertFirmSQLNumber() needed 53140 for creating 1000 entries

java db.InsertFirmSQLInt8
InsertFirmSQLInt8() needed 44531 for creating 1000 entries
InsertFirmSQLInt8() needed 63500 for creating 1000 entries
InsertFirmSQLInt8() needed 70578 for creating 1000 entries
InsertFirmSQLInt8() needed 68375 for creating 1000 entries
InsertFirmSQLInt8() needed 80234 for creating 1000 entries


with J2SE 1.3.1_06 it need:

java db.InsertFirmSQLNumber
InsertFirmSQLNumber() needed 40093 for creating 1000 entries
InsertFirmSQLNumber() needed 39016 for creating 1000 entries
InsertFirmSQLNumber() needed 39579 for creating 1000 entries

java db.InsertFirmSQLInt8
InsertFirmSQLInt8() needed 75437 for creating 1000 entries
InsertFirmSQLInt8() needed 39156 for creating 1000 entries
InsertFirmSQLInt8() needed 41421 for creating 1000 entries
InsertFirmSQLInt8() needed 41156 for creating 1000 entries


and there is the Java code:

         DriverManager.registerDriver(new org.postgresql.Driver());
         Connection conn = DriverManager.getConnection(db, dbuser, dbpassword);
         PreparedStatement pstmt = null;
         ResultSet rs = null;

         if (conn != null) {
             String query = "insert into firm values(?,?)";
             pstmt = conn.prepareStatement(query);

             long start = System.currentTimeMillis();
             for (int i = 0; i < N; i++) {
                 pstmt.setLong(1, getUniquelongID());
                 pstmt.setString(2, "" + i);
                 pstmt.executeUpdate();
             }
             long end = System.currentTimeMillis() - start;

             System.out.println("InsertFirmSQLInt8() needed " + end + " for
creating " + N + " entries");
         }

         closeConnections(conn, pstmt, rs);
     }

Is this a JDBC driver or PostgreSQL configuration problem? Or is the
performance normal?


Best Regards,
Rafal

Re: Good performance?

From
"John Cavacas"
Date:
> ....
>                  pstmt.setLong(1, getUniquelongID());
>....

What is getUniquelongID()? Can you post the code for that? I would
suspect that might be your problem.

Your results point to something being wrong somewhere. Just yesterday I
was doing some benchmarking of my own, and using code similar to yours I
was inserting 10000 records in about 23 seconds.

john

Re: Good performance?

From
Rafal Kedziorski
Date:
At 18:23 16.02.2003 -0500, John Cavacas wrote:
> > ....
> >                  pstmt.setLong(1, getUniquelongID());
> >....
>
>What is getUniquelongID()? Can you post the code for that? I would
>suspect that might be your problem.

here is the code.

private final static long getUniquelongID() {
     return (System.currentTimeMillis() * 1000 + (long) (100000 *
Math.random()));
}

but this routine is very fast. for computing 100.000 values she need 6-7
seconds.

>Your results point to something being wrong somewhere. Just yesterday I
>was doing some benchmarking of my own, and using code similar to yours I
>was inserting 10000 records in about 23 seconds.
>
>john
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly

Re: Good performance?

From
Barry Lind
Date:
Rafal,

Performance of postgres running under cygwin isn't great.  Can you try
the same test on a different platform?  It also looks like you are
running in autocommit mode.  You should see a significant performance
improvement if you batch your commits in say groups of 1000 inserts per
commit.

thanks,
--Barry

Rafal Kedziorski wrote:
> Hi,
>
> I have following tables:
>
> with id as number(20,0):
> CREATE TABLE public.firm (
>   firm_id numeric(20, 0) NOT NULL,
>   name varchar(40) NOT NULL,
>   CONSTRAINT firm_pkey PRIMARY KEY (firm_id)
> )
>
> with id as int8:
>
> CREATE TABLE public.firmint8 (
>   firmint8_id int8 NOT NULL,
>   name varchar(40) NOT NULL,
>   CONSTRAINT firmint8_pkey PRIMARY KEY (firmint8_id)
> )
>
> my system:
> - dual PIII 800 MHz with 640 MB RAM
> - cygwin
> - PostgreSQL 7.3.1 (default configuration after install thru cygwin)
> - J2SE 1.4.1_01
> - JDBC driver for J2SE 1.4.1_01 and J2SE 1.3.1_06
>
> I get very bad performance inserting 1000 simple values in the tables
> defined above. I'm using PreparedStatement without Batch.
>
> with J2SE 1.4.1_01 it need:
>
> java db.InsertFirmSQLNumber
> InsertFirmSQLNumber() needed 74438 for creating 1000 entries
> InsertFirmSQLNumber() needed 53140 for creating 1000 entries
>
> java db.InsertFirmSQLInt8
> InsertFirmSQLInt8() needed 44531 for creating 1000 entries
> InsertFirmSQLInt8() needed 63500 for creating 1000 entries
> InsertFirmSQLInt8() needed 70578 for creating 1000 entries
> InsertFirmSQLInt8() needed 68375 for creating 1000 entries
> InsertFirmSQLInt8() needed 80234 for creating 1000 entries
>
>
> with J2SE 1.3.1_06 it need:
>
> java db.InsertFirmSQLNumber
> InsertFirmSQLNumber() needed 40093 for creating 1000 entries
> InsertFirmSQLNumber() needed 39016 for creating 1000 entries
> InsertFirmSQLNumber() needed 39579 for creating 1000 entries
>
> java db.InsertFirmSQLInt8
> InsertFirmSQLInt8() needed 75437 for creating 1000 entries
> InsertFirmSQLInt8() needed 39156 for creating 1000 entries
> InsertFirmSQLInt8() needed 41421 for creating 1000 entries
> InsertFirmSQLInt8() needed 41156 for creating 1000 entries
>
>
> and there is the Java code:
>
>         DriverManager.registerDriver(new org.postgresql.Driver());
>         Connection conn = DriverManager.getConnection(db, dbuser,
> dbpassword);
>         PreparedStatement pstmt = null;
>         ResultSet rs = null;
>
>         if (conn != null) {
>             String query = "insert into firm values(?,?)";
>             pstmt = conn.prepareStatement(query);
>
>             long start = System.currentTimeMillis();
>             for (int i = 0; i < N; i++) {
>                 pstmt.setLong(1, getUniquelongID());
>                 pstmt.setString(2, "" + i);
>                 pstmt.executeUpdate();
>             }
>             long end = System.currentTimeMillis() - start;
>
>             System.out.println("InsertFirmSQLInt8() needed " + end + "
> for creating " + N + " entries");
>         }
>
>         closeConnections(conn, pstmt, rs);
>     }
>
> Is this a JDBC driver or PostgreSQL configuration problem? Or is the
> performance normal?
>
>
> Best Regards,
> Rafal
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: Good performance?

From
Rafal Kedziorski
Date:
hi,

Barry Lind wrote:

> Rafal,
>
> Performance of postgres running under cygwin isn't great.  Can you try
> the same test on a different platform?  It also looks like you are
> running in autocommit mode.  You should see a significant performance
> improvement if you batch your commits in say groups of 1000 inserts
> per commit.

after set autocommit false, I need 0,9 - 1,04 seconds for insert 1000
new entries into my table. is this normal, that autocommit false is
40-50 times slower?


Rafal

> thanks,
> --Barry

Re: [PERFORM] Good performance?

From
Rafal Kedziorski
Date:
Tomasz Myrta wrote:

> Rafal Kedziorski wrote:
> <cut>
>
>> after set autocommit false, I need 0,9 - 1,04 seconds for insert 1000
>> new entries into my table. is this normal, that autocommit false is
>> 40-50 times slower?
>>
>>
>> Rafal
>
> It is possible when you have "fsync=false" in your postgresql.conf.
> (don't change it if you don't have to).

fsync is:
#fsync = true

but there are my new start options: postmaster -i -o -F -D ...

after set fsync false I get this Performance for creating new entries
with entity beans:

needed 9223 for creating 1000 entries

instead of about 50.000 milliseconds. it's possible to make it faster?


Rafal

> Regards,

> Tomasz Myrta

Re: [PERFORM] Good performance?

From
Rafal Kedziorski
Date:
Justin Clift wrote:

> Rafal Kedziorski wrote:
> <snip>
>
>> instead of about 50.000 milliseconds. it's possible to make it faster?
>
>
> Hi Rafal,
>
> Have you tuned the memory settings of PostgreSQL yet?

I'm working on it.


Rafal

> Regards and best wishes,
>
> Justin Clift

Re: Good performance?

From
Barry Lind
Date:
Rafal,

I would expect things to be slower with a commit after each insert,
since it is the commit that forces the data to be written to disk.
However 50x seems a bit much and I think is due to cygwin performance.

I ran your code on my laptop running RH7.3 and get the following results:

Running with autocommit on:
InsertFirmSQLInt8() needed 5129 for creating 1000 entries
InsertFirmSQLInt8() needed 5417 for creating 1000 entries
InsertFirmSQLInt8() needed 4976 for creating 1000 entries
InsertFirmSQLInt8() needed 4162 for creating 1000 entries

Running with autocommit off:
InsertFirmSQLInt8() needed 1250 for creating 1000 entries
InsertFirmSQLInt8() needed 932 for creating 1000 entries
InsertFirmSQLInt8() needed 1000 for creating 1000 entries
InsertFirmSQLInt8() needed 1321 for creating 1000 entries
InsertFirmSQLInt8() needed 1248 for creating 1000 entries

On linux I see about a 5x slowdown which is more in line with what I
would expect.

thanks,
--Barry


Rafal Kedziorski wrote:
> hi,
>
> Barry Lind wrote:
>
>> Rafal,
>>
>> Performance of postgres running under cygwin isn't great.  Can you try
>> the same test on a different platform?  It also looks like you are
>> running in autocommit mode.  You should see a significant performance
>> improvement if you batch your commits in say groups of 1000 inserts
>> per commit.
>
>
> after set autocommit false, I need 0,9 - 1,04 seconds for insert 1000
> new entries into my table. is this normal, that autocommit false is
> 40-50 times slower?
>
>
> Rafal
>
>> thanks,
>> --Barry
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: [PERFORM] Good performance?

From
Tomasz Myrta
Date:
Rafal Kedziorski wrote:
<cut>
> after set autocommit false, I need 0,9 - 1,04 seconds for insert 1000
> new entries into my table. is this normal, that autocommit false is
> 40-50 times slower?
>
>
> Rafal
It is possible when you have "fsync=false" in your postgresql.conf.
(don't change it if you don't have to).
Regards,
Tomasz Myrta

Re: [PERFORM] Good performance?

From
Josh Berkus
Date:
Rafal,  Tomasz,

> It is possible when you have "fsync=false" in your postgresql.conf.
> (don't change it if you don't have to).

You should NOT turn off fsync unless you know what you are doing.  With fsync
off, your database can be unrecoverably corrupted after an unexpected
power-out, and you will be forced to restore from your last backup.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco