Re: Large databases, performance - Mailing list pgsql-general

NOTE: Setting follow up to the performance list

Funny that the status quo seems to be if you need fast selects on data
that has few inserts to pick mysql, otherwise if you have a lot of
inserts and don't need super fast selects go with PostgreSQL; yet your
data seems to cut directly against this.

I'm curious, did you happen to run the select tests while also running
the insert tests? IIRC the older mysql versions have to lock the table
when doing the insert, so select performance goes in the dumper in that
scenario, perhaps that's not an issue with 3.23.52?

It also seems like the vacuum after each insert is unnecessary, unless
your also deleting/updating data behind it. Perhaps just running an
ANALYZE on the table would suffice while reducing overhead.

Robert Treat

On Thu, 2002-10-03 at 08:36, Shridhar Daithankar wrote:
> Machine
> Compaq Proliant Server ML 530
> "Intel Xeon 2.4 Ghz Processor x 4, "
> "4 GB RAM, 5 x 72.8 GB SCSI HDD "
> "RAID 0 (Striping) Hardware Setup, Mandrake Linux 9.0"
> "Cost - $13,500 ($1,350 for each additional 72GB HDD)"
>
> Performance Parameter                MySQL 3.23.52          MySQL 3.23.52          PostgreSQL 7.2.2
>                         WITHOUT InnoDB         WITH InnoDB for     with built-in support
>                         for transactional     transactional support    for transactions
>                         support
> Complete Data
>
> Inserts + building a composite index
> "40 GB data, 432,000,000 tuples"        3738 secs        18720 secs        20628 secs
> "about 100 bytes each, schema on
> 'schema' sheet"
> "composite index on 3 fields
> (esn, min, datetime)"
>
> Load Speed                    115570 tuples/second    23076 tuples/second    20942 tuples/second
>
> Database Size on Disk                48 GB            87 GB            111 GB
>
> Average per partition
>
> Inserts + building a composite index
> "300MB data, 3,000,000 tuples,"            28 secs            130 secs        150 secs
> "about 100 bytes each, schema on
> 'schema' sheet"
> "composite index on 3 fields
> (esn, min, datetime)"
>
> Select Query                      7 secs            7 secs            6 secs
> based on equality match of 2 fields
> (esn and min) - 4 concurrent queries
> running
>
> Database Size on Disk                341 MB            619 MB            788 MB
> ----