Re: [PERFORM] Good performance?

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

> ....
> 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

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(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly

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
>

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

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

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

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?

Regards and best wishes,

Justin Clift

> Rafal

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

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

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(at)postgresql(dot)org
>

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