> -----Original Message----- > From: Craig Ringer [mailto:craig@xxxxxxxxxxxxxxxxxxxxx] > Sent: Friday, October 01, 2010 6:08 PM > To: Dann Corbit > Cc: 'Joshua J. Kugler'; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
