Re: Large databases, performance

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: shridhar_daithankar(at)persistent(dot)co(dot)in
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-general <pgsql-general(at)postgresql(dot)org>, "pankaj M(dot) Tolani" <pankaj(at)pspl(dot)co(dot)in>
Subject: Re: Large databases, performance
Date: 2002-10-03 15:57:29
Message-ID: 1033660649.21324.53.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers pgsql-performance pgsql-sql

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas F.O'Connell 2002-10-03 15:57:45 Re: Anyone want to assist with the translation of the Advocacy site?
Previous Message Justin Clift 2002-10-03 15:57:23 Re: Anyone want to assist with the translation of the

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas F.O'Connell 2002-10-03 15:57:45 Re: Anyone want to assist with the translation of the Advocacy site?
Previous Message Justin Clift 2002-10-03 15:57:23 Re: Anyone want to assist with the translation of the

Browse pgsql-performance by date

  From Date Subject
Next Message Shridhar Daithankar 2002-10-03 16:07:55 Re: Large databases, performance
Previous Message Shridhar Daithankar 2002-10-03 15:56:43 Re: Large databases, performance

Browse pgsql-sql by date

  From Date Subject
Next Message Marie G. Tuite 2002-10-03 16:06:14 drop constraint primary key
Previous Message Shridhar Daithankar 2002-10-03 15:56:43 Re: Large databases, performance