From: Robert Treat <xzilla@users.sourceforge.net>
To: shridhar_daithankar@persistent.co.in
Cc: pgsql-hackers@postgresql.org, pgsql-general <pgsql-general@postgresql.org>
Cc: pankaj M. Tolani <pankaj@pspl.co.in>
Subject: Re: Large databases, performance
Date: 03 Oct 2002 11:57:29 -0400
Message-ID: <1033660649.21324.53.camel@camel> (raw)
In-Reply-To: <3D9C8712.9513.9C6521D@localhost>
References: <3D9C8712.9513.9C6521D@localhost>
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
> ----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: xzilla@users.sourceforge.net, shridhar_daithankar@persistent.co.in, pgsql-general@postgresql.org, pankaj@pspl.co.in
Subject: Re: Large databases, performance
In-Reply-To: <1033660649.21324.53.camel@camel>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox