Thread: Kudos on the 64 bit PostgreSQL for Windows
I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it seems to perform wonderfully. I moved 1.5 million records that are 3K wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in less than 6 minutes. I was thinking of using SQLite for the project, but I calculate the same data movement would have taken about one month! The memory footprint is also very nice for the amount of performance delivered.
On Thursday 30 September 2010, Dann Corbit elucidated thus: > I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it > seems to perform wonderfully. I moved 1.5 million records that are 3K > wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in > less than 6 minutes. > I was thinking of using SQLite for the project, > but I calculate the same data movement would have taken about one > month! This is a bit OT, but were you using transactions in SQLite? SQLite can deliver several thousand inserts per second if inside of a transaction. If you were only getting a few inserts per second, then you were not using transactions, thus SQLite was on "autocommit" mode, and thus committing after every insert, thus the performance penalty. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com - Fairbanks, AK PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A
> -----Original Message----- > From: Joshua J. Kugler [mailto:joshua@eeinternet.com] > Sent: Friday, October 01, 2010 10:36 AM > To: pgsql-general@postgresql.org > Cc: Dann Corbit > Subject: Re: Kudos on the 64 bit PostgreSQL for Windows > > On Thursday 30 September 2010, Dann Corbit elucidated thus: > > I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it > > seems to perform wonderfully. I moved 1.5 million records that are 3K > > wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in > > less than 6 minutes. > > > I was thinking of using SQLite for the project, > > but I calculate the same data movement would have taken about one > > month! > > This is a bit OT, but were you using transactions in SQLite? I was using a data movement tool with the SQLite ODBC driver. I also tried a script of SQL inserts. Efficiency was the same. The actual operation is performed using an INSERT/SELECT. In many respects, the comparison is not fair, because we have written a custom ODBC driver for PostgreSQL that can use bulk mode (COPY) on insert and SQLite does not have a bulk mode. We can only do bulk mode operations with database systems that have some mechanism for this. > SQLite > can > deliver several thousand inserts per second if inside of a transaction. > If you were only getting a few inserts per second, then you were not > using transactions, thus SQLite was on "autocommit" mode, and thus > committing after every insert, thus the performance penalty. For this application, SQLite has no chance to compete. I would not want to rewrite applications unless there were a dire need. I am not sure that it would make sense to do 400 MB in one big transaction either, so some kind of fiddling would be needed. PostgreSQL flies like an arrow right out of the box. Problem solved.
On 2/10/2010 2:06 AM, Dann Corbit wrote: >> SQLite can >> deliver several thousand inserts per second if inside of a transaction. >> If you were only getting a few inserts per second, then you were not >> using transactions, thus SQLite was on "autocommit" mode, and thus >> committing after every insert, thus the performance penalty. > > For this application, SQLite has no chance to compete. I would not want > to rewrite applications unless there were a dire need. I am not sure > that it would make sense to do 400 MB in one big transaction either, > so some kind of fiddling would be needed. PostgreSQL flies like an > arrow right out of the box. Problem solved. If you're inserting one row per transaction, PostgreSQL shouldn't be all that fast either, because it has to wait for data to fsync() to disk. If you're using a storage controller with write-back caching (usually battery backed cache) this doesn't apply, but otherwise postgresql usually lands up waiting a disk rotation or two, so you should be seeing insert rates below 100/s on most storage systems if it's working properly. Are you using synchronous_commit=off and/or a commit delay? Do you have fsync=off set in your postgresql.conf? If so, I hope you're aware of the serious data integrity risks. http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html If neither of these are true and you're not using battery-backed cache, check to make sure your storage subsystem is honouring fsync requests. Some SSDs are known to ignore fsync, which *will* cause data corruption if you have an OS crash or power loss. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/
> -----Original Message-----
> From: Dann Corbit
> Sent: Friday, October 01, 2010 11:06 AM
> To: 'Joshua J. Kugler'; pgsql-general@postgresql.org
> Subject: RE: Kudos on the 64 bit PostgreSQL for Windows
>
> > -----Original Message-----
> > From: Joshua J. Kugler [mailto:joshua@eeinternet.com]
> > Sent: Friday, October 01, 2010 10:36 AM
> > To: pgsql-general@postgresql.org
> > Cc: Dann Corbit
> > Subject: Re: Kudos on the 64 bit PostgreSQL for Windows
> >
> > On Thursday 30 September 2010, Dann Corbit elucidated thus:
> > > I installed the 64 bit PostgreSQL 9.0 on Windows Server 2008 and it
> > > seems to perform wonderfully. I moved 1.5 million records that are
> 3K
> > > wide from SQL*Server into PostgreSQL and rebuilt 6 indexes all in
> > > less than 6 minutes.
> >
> > > I was thinking of using SQLite for the project,
> > > but I calculate the same data movement would have taken about one
> > > month!
> >
> > This is a bit OT, but were you using transactions in SQLite?
>
> I was using a data movement tool with the SQLite ODBC driver.
> I also tried a script of SQL inserts. Efficiency was the same.
> The actual operation is performed using an INSERT/SELECT.
> In many respects, the comparison is not fair, because we have
> written a custom ODBC driver for PostgreSQL that can use bulk
> mode (COPY) on insert and SQLite does not have a bulk mode.
> We can only do bulk mode operations with database systems that
> have some mechanism for this.
>
> > SQLite
> > can
> > deliver several thousand inserts per second if inside of a
> transaction.
> > If you were only getting a few inserts per second, then you were not
> > using transactions, thus SQLite was on "autocommit" mode, and thus
> > committing after every insert, thus the performance penalty.
>
> For this application, SQLite has no chance to compete. I would not
> want
> to rewrite applications unless there were a dire need. I am not sure
> that it would make sense to do 400 MB in one big transaction either,
> so some kind of fiddling would be needed. PostgreSQL flies like an
> arrow right out of the box. Problem solved.
>
To see what SQLite could do, I made a script that tried some performance options of SQLite. The runtime went down to 2
hoursto populate the data, and 2 hours to build the indexes. It was several orders of magnitude faster than the
originalscript and the ODBC procedure, but still a far cry from what PostgreSQL could do {under 6 minutes total}. Note
thatthe table was originally without indexes (they are built afterwards) so that the data loading would go faster.
PRAGMA journal_mode = OFF ;
PRAGMA locking_mode = EXCLUSIVE ;
PRAGMA synchronous = OFF ;
DELETE FROM "Epd";
BEGIN TRANSACTION ;
INSERT INTO "Epd" VALUES ('rn1q1rk1/p4pbp/b2p1np1/1P1pp3/4P3/P1NBBP2/1P4PP/R2QK1NR w KQ -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{482233C0-4B86-4184-B333-000000D0A8BC}', NULL, 7, 1,
3);
INSERT INTO "Epd" VALUES ('r1b2rk1/pp1n2p1/2nbpq1p/3p4/3P4/P2B1N2/1P2NPPP/R1BQ1RK1 w - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Bc2 {8} Nc3 {4} Be3 {1} Ng3 {2}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{E9991746-78FE-4372-A3C4-000036CECF6F}',NULL, 5, 5, 5);
INSERT INTO "Epd" VALUES ('r1b1k2r/1pqnbpp1/p2ppn1p/6B1/4PP2/1NN3Q1/PPP3PP/2KR1B1R w kq -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Bh4 {11} Bxf6 {12}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{DE066550-2C7B-49EA-9599-0000498A1037}',NULL, 5, 10, 6);
INSERT INTO "Epd" VALUES ('rn1q1rk1/p2pbppp/Bp2pn2/8/3PP3/5N2/PP1N1PPP/R1BQ1RK1 b - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Nxa6 {9}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{05009024-BA8E-4E33-AA6A-00004C93EB9C}', NULL,
1,1, 6);
INSERT INTO "Epd" VALUES ('rnb2rk1/ppp1qppp/3b1n2/3p4/3P4/2PBBN2/PP3PPP/RN1Q1RK1 w - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Nbd2 {2} Re1 {3}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{AD5D8DCC-44EF-4768-8E8C-00004F630788}',NULL, 3, 0, 1);
INSERT INTO "Epd" VALUES ('r1b1qrk1/ppp3bp/2np2p1/4Pp2/2PN4/1PB3P1/P3PPBP/R2Q1RK1 b - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Nxd4 {3} Nxe5 {2}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{BC29DABE-FABE-488D-A5A7-00005451C1A4}',NULL, 1, 1, 3);
INSERT INTO "Epd" VALUES ('r1b1k2r/pp1pnp1p/1q5b/3Pp3/4P2p/1PPQ4/P5PP/RN2KBNR w KQkq -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Na3 {3} Nd2 {4}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{860F528A-12A1-4707-9594-00005A34FF25}',
NULL,1, 4, 0);
INSERT INTO "Epd" VALUES ('2rq1rk1/pb2bppp/1pn1pn2/3p4/2PP4/PP1B1N2/1B1NQPPP/2R2RK1 b - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{9AFCE43B-55B4-410E-8EC8-000060FE1ABC}', NULL, 8, 7,
6);
INSERT INTO "Epd" VALUES ('r1b2rk1/ppqnbppp/3ppn2/8/4PB2/N1PB1N2/PP2QPPP/R2R2K1 b - -', 28, 562792157, 16, 'e5 Bg5 h6
Bh4g5 Bg3 Nc5 Bc2 Be6 Nd2 Rac8 Nb3 a6 Nxc5 Qxc5 Re1 Rfe8 Bb3 Qb6 Qc2 Kg7 Rad1 Red8 Rd3 Rf8 Qd1 Rh8 h3 Rhg8 Bh2 Rgf8
Rf3',NULL, 'e5', NULL, 'Ne5 {1} e5 {63}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 141,
'{3A2F8CFA-41EC-46E7-8044-0000657146C6}',NULL, 16, 15, 33);
INSERT INTO "Epd" VALUES ('r1bqk2r/pp1pbppp/2n2n2/2p1p1B1/2B1P3/2PP1N2/PP3PPP/RN1QK2R b KQkq -', 0, NULL, NULL, NULL,
NULL,NULL, NULL, 'O-O {50} d6 {1}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{F2488973-50BA-46DE-A391-000069FAE0E6}',NULL, 17, 15, 19);
INSERT INTO "Epd" VALUES ('r4rk1/1bq1bppp/p1nppn2/1p6/4PP2/PNNBBQ2/1PP3PP/R4R1K b - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Rac8 {12} b4 {12} Rab8 {8} Rfe8 {6} Rae8 {3} h5 {3} Rfd8 {2} Nb8 {1} Nd7 {1} Rfb8 {1} Rfc8 {1} g6 {2}',
NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, '{CC164BB2-343D-40DB-AD9B-00006FDE5FA5}', NULL, 24, 12, 16);
INSERT INTO "Epd" VALUES ('r1bq1rk1/pppp1ppp/8/2b1n3/2P1N3/4PB2/PP3PPP/R1BQ1RK1 b - -', 0, NULL, NULL, NULL, NULL,
NULL,NULL, 'Be7 {2} d6 {2} Nxf3+ {1} Bb6 {1}', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'{9A323C3F-283A-40CB-A17D-0000724BEF83}',NULL, 1, 0, 2);
One and a half million similar lines follow, and then a commit.
After that, a script build the indexes is run.
In case anyone is wondering, this is a database of chess positions and their analysis.
> -----Original Message----- > From: Craig Ringer [mailto:craig@postnewspapers.com.au] > Sent: Friday, October 01, 2010 6:08 PM > To: Dann Corbit > Cc: 'Joshua J. Kugler'; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Kudos on the 64 bit PostgreSQL for Windows > > On 2/10/2010 2:06 AM, Dann Corbit wrote: > >> SQLite can > >> deliver several thousand inserts per second if inside of a > transaction. > >> If you were only getting a few inserts per second, then you were not > >> using transactions, thus SQLite was on "autocommit" mode, and thus > >> committing after every insert, thus the performance penalty. > > > > For this application, SQLite has no chance to compete. I would not > want > > to rewrite applications unless there were a dire need. I am not sure > > that it would make sense to do 400 MB in one big transaction either, > > so some kind of fiddling would be needed. PostgreSQL flies like an > > arrow right out of the box. Problem solved. > > If you're inserting one row per transaction, PostgreSQL shouldn't be > all > that fast either, because it has to wait for data to fsync() to disk. > If > you're using a storage controller with write-back caching (usually > battery backed cache) this doesn't apply, but otherwise postgresql > usually lands up waiting a disk rotation or two, so you should be > seeing > insert rates below 100/s on most storage systems if it's working > properly. The inserts are processed via an INSERT/SELECT statement. A custom driver has been written that performs this operation using the COPY API. I get many thousands of rows per second. The operations all take place in binary mode also (any necessary conversions are performed by the client). > Are you using synchronous_commit=off and/or a commit delay? I do not use synchronous_commit=off > Do you have fsync=off set in your postgresql.conf? No. > If so, I hope you're > aware of the serious data integrity risks. > http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html > > If neither of these are true and you're not using battery-backed cache, > check to make sure your storage subsystem is honouring fsync requests. > Some SSDs are known to ignore fsync, which *will* cause data corruption > if you have an OS crash or power loss.
On 2/10/2010 9:41 AM, Dann Corbit wrote: > The inserts are processed via an INSERT/SELECT statement. > A custom driver has been written that performs this operation > using the COPY API. Aaah, so it's not really sending individual INSERT statements to the database at all, you're using COPY behind the scenes. That actually means that all your rows are inserted in one transaction after all. > I get many thousands of rows per second. Yep. A COPY happens inside one transaction. >> Are you using synchronous_commit=off and/or a commit delay? > > I do not use synchronous_commit=off > >> Do you have fsync=off set in your postgresql.conf? > > No. Good! Given the performance you were reporting, I thought I'd check. Your use of COPY explains it, though, because you're actually doing all the work inside one transaction so the database doesn't have to wait for the disk after each row insert. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/
On 2/10/2010 9:41 AM, Dann Corbit wrote: > The inserts are processed via an INSERT/SELECT statement. > A custom driver has been written that performs this operation > using the COPY API. BTW, is there any chance you can push that (ODBC, you said?) driver somewhere public? It might interest the psqlODBC folks. I know you might not have the right to do so, in which case no worries, but if you can it could be handy. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/