Re: Large databases, performance

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
> ----