Thread: Case Studies for PostgresSQL

Hello People,

I want to evaluate some of the open source database to use in my billing
system of Telecommunication Company. Can somebody point me a Case Studies in
PostgreSQL?.
Can PostgreSQL really handle this kind of sytem? Im using Linux Box in my
server. There will be 500,000 average inserts in a month and 1,000,000 query every month . My tentative
choice are Interbase 6.0 or PostgreSQL 7. Please give me insight with this
one.


Ian

and there are  2 592 000
seconds in a month.. that's a little over

that's 1.728 seconds per query..

short answer .. postgres can do it, it depends
on the machine

jeff


On Mon, 25 Sep 2000, ian wrote:

> Hello People,
>
> I want to evaluate some of the open source database to use in my billing
> system of Telecommunication Company. Can somebody point me a Case Studies in
> PostgreSQL?.
> Can PostgreSQL really handle this kind of sytem? Im using Linux Box in my
> server. There will be 500,000 average inserts in a month and 1,000,000 query every month . My tentative
> choice are Interbase 6.0 or PostgreSQL 7. Please give me insight with this
> one.
>
>
> Ian
>
>

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick  : bignose

At 22:42 24/09/00 -0300, Jeff MacDonald wrote:
>and there are  2 592 000
>seconds in a month.. that's a little over
>
>that's 1.728 seconds per query..
>
>short answer .. postgres can do it, it depends
>on the machine

You need to be a little carefull here; I'd guess the peak will be at least
4x the average, and may be 10x or even 100x. It may still come down to the
machine power, but the most likely TPS would be 2.5, 5 and 50, which are
still probably quite doable.

I'd also question the original numbers: an enquiry load that is only twice
the update load would be quite unusual. But I've never had anything to do
with a high volume billing system like a phone system. I am more used to a
10:1 ratio, which starts making the upper estimate TPS look a little harder
to achieve. I don't know what PG is really capable of - the recent GB
benchmarks suggest it should work, but it will start to depend on the
complexity of the queries, I think.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

> Hello People,
>
> I want to evaluate some of the open source database to use in my billing
> system of Telecommunication Company. Can somebody point me a Case Studies
in
> PostgreSQL?.
> Can PostgreSQL really handle this kind of sytem? Im using Linux Box in my
> server. There will be 500,000 average inserts in a month and 1,000,000
query every month . My tentative
> choice are Interbase 6.0 or PostgreSQL 7. Please give me insight with this
> one.
>
> Ian

  In short, yes, it can.

  I recently had the opportunity to test a 4 x Xeon machine that we built
(around $6,000) as our database server to an Alpha machine ($25,000).  Both
of them were pretty close to what you're looking for.

   To start out, let me say that this Xeon machine only had 400 Mhz
processors, and the secondary cache on them was disabled - so with any
relatively modern processor, the performance would be much, much higher.  It
does have a fairly decent RAID array (5 disks in RAID 5 across 2 channels).

  To test it, we logged our real-world queries for a night, and I selected
1,000 distinct queries from that list.  I launched 20 simultaneous
processes, each of which executed the 1,000 queries, enough to drive the
system load out the roof. : )

  The Xeon machine finished the test in 90 minutes, giving us 3.7
transactions per second, or 0.27 seconds per query.

  On average, your machine needs to handle 0.38 selects per second, about
1/10th of what our machine did.  However, our processors are slow, and the
secondary cache is completely disabled (a weird technicality, soon to be
remedied).  With any relatively modern processors, and maybe a few more
disks on the machine, you should be sitting pretty.

   If you don't feel like building the machine yourself, the Alpha that I
tested was about 50% faster, but at three times the cost.

steve