Hello Jason,
On Wed, March 1, 2006 16:20, [EMAIL PROTECTED] wrote:
...
> 1. My anticipated bottleneck under postgres is that the DB-writing app.
> must parse incoming bursts of data and store in the DB. The machine
> sending this data is seeing a delay in processing. Debugging has shown
> that the INSERTS (on the order of a few thousand) is where most of the time
> is wasted.
>
> 2. The other bottleneck is data retrieval. My DB-reading application must
> read the DB record-by-record (opens a cursor and reads one-by-one), build
> the data into a message according to a system ICD, and ship it out.
> postgres (postmaster) CPU usage is hovering around 85 - 90% at this time.
...
though your application seems a good candidate for SQLite use, have you tried
surrounding each burst of inserts and reads in a single transaction? With
PostgreSQL, but also with SQLite, performances might increase dramatically
with proper transaction handling in place. Furthermore having both a reader
and a writer at the same time the MVCC "better than row level locking"
mechanism might provide you better performances than SQLite, but here the
devil's in the detail. A lot depends on how much the read and write operations
overlap each others.
Bye,
--
Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it
