Guy,
Did you tune postgresql ? How much memory does the box have? Have you
tuned postgresql ?
Dave
On 28-Dec-06, at 12:46 AM, Guy Rouillier wrote:
I don't want to violate any license agreement by discussing
performance, so I'll refer to a large, commercial PostgreSQL-
compatible DBMS only as BigDBMS here.
I'm trying to convince my employer to replace BigDBMS with
PostgreSQL for at least some of our Java applications. As a proof
of concept, I started with a high-volume (but conceptually simple)
network data collection application. This application collects
files of 5-minute usage statistics from our network devices, and
stores a raw form of these stats into one table and a normalized
form into a second table. We are currently storing about 12 million
rows a day in the normalized table, and each month we start new
tables. For the normalized data, the app inserts rows initialized
to zero for the entire current day first thing in the morning, then
throughout the day as stats are received, executes updates against
existing rows. So the app has very high update activity.
In my test environment, I have a dual-x86 Linux platform running
the application, and an old 4-CPU Sun Enterprise 4500 running
BigDBMS and PostgreSQL 8.2.0 (only one at a time.) The Sun box has
4 disk arrays attached, each with 12 SCSI hard disks (a D1000 and 3
A1000, for those familiar with these devices.) The arrays are set
up with RAID5. So I'm working with a consistent hardware platform
for this comparison. I'm only processing a small subset of files
(144.)
BigDBMS processed this set of data in 20000 seconds, with all
foreign keys in place. With all foreign keys in place, PG took
54000 seconds to complete the same job. I've tried various
approaches to autovacuum (none, 30-seconds) and it doesn't seem to
make much difference. What does seem to make a difference is
eliminating all the foreign keys; in that configuration, PG takes
about 30000 seconds. Better, but BigDBMS still has it beat
significantly.
I've got PG configured so that that the system database is on disk
array 2, as are the transaction log files. The default table space
for the test database is disk array 3. I've got all the reference
tables (the tables to which the foreign keys in the stats tables
refer) on this array. I also store the stats tables on this
array. Finally, I put the indexes for the stats tables on disk
array 4. I don't use disk array 1 because I believe it is a
software array.
I'm out of ideas how to improve this picture any further. I'd
appreciate some suggestions. Thanks.
--
Guy Rouillier
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
