Re: Minimum hardware requirements for Postgresql db - Mailing list pgsql-performance

Dear all


Sorry for my mistake on the 15000 recs per day.

In fact, this server is planned as a OLTP database server for a retailer.
Our intention is either to setup 1 or 2 Postgresql db in the server.

The proper sizing info for the 1st Postgresql db should be:

No. of item master : 200,000
(This item master grows at 0.5% daily).

No. of transactions from Point-of-Sales machines: 25,000

Plus other tables, the total sizing that I estimated is 590,000 records
daily.

The 2nd Postgresql db will be used by end users on client machines linked
via ODBC, doing manual data entry.
This will house the item master, loyalty card master and other Historical
data to be kept for at least 1.5 years.

Therefore total sizing for this db is around 165,000,000 recs at any time.

In summary, the single machine must be able to take up around 100 users
connections via both socket and ODBC. And house the above number of
records.


Thank you,
REgards.





                         
                      Christopher Browne
                         
                      <cbbrowne@acm.org>                 To:       pgsql-performance@postgresql.org
                         
                      Sent by:                           cc:
                         
                      pgsql-performance-owner@pos        Subject:  Re: [PERFORM] Minimum hardware requirements for
Postgresqldb                  
                      tgresql.org
                         

                         

                         
                      03/12/2003 12:44 PM
                         

                         

                         




After takin a swig o' Arrakan spice grog, CHEWTC@ap.nec.com.sg belched out:
> We would be recommending to our ct. on the use of Postgresql db as
> compared to MS SQL Server. We are targetting to use Redhat Linux ES
> v2.1, Postgresql v7.3.4 and Postgresql ODBC 07.03.0100.
>
> We would like to know the minimum specs required for our below
> target. The minimum specs is referring to no. of CPU, memory,
> harddisk capacity, RAID technology etc. And also the Postgresql
> parameters and configuration to run such a system.
>
> 1) We will be running 2 x Postgresql db  in the machine.
>
> 2) Total number of connections to be around 100. The connections
> from the clients machines will be in ODBC and socket connections.
>
> 3) Estimated number of transactions to be written into the
> Postgresql db is around 15000 records per day.
>
> The growth rate in terms of number of connections is around 10% per
> year and the data retention is kept on average at least for 18
> months for the 2 databases.
>
> Are there any reference books or sites that I can tap on for the
> above requirement?

Perhaps the best reference on detailed performance information is the
"General Bits" documents.

<http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html>

<http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html>

These don't point particularly at minimal hardware requirements, but
rather at how to configure the DBMS to best reflect what hardware you
have.  But there's some degree to which you can work backwards...

If you'll need to support 100 concurrent connections, then minimum
shared_buffers is 200, which implies 1600K of RAM required for shared
buffers.

100 connections probably implies around 100MB of memory for the
backend processes to support the connections.

That all points to the notion that you'd more than probably get
half-decent performance if you had a mere 256MB of RAM, which is about
$50 worth these days.

None of it sounds terribly challenging; 15K records per day is 625
records per hour which represents an INSERT every 6 seconds.  Even if
that has to fit into an 8 hour day, that's still not a high number of
transactions per second.  That _sounds like_ an application that could
work on old, obsolete hardware.  I would imagine that my old Intel
Pentium Pro 200 might cope with the load, in much the way that that
server is more than capable of supporting a web server that would
serve a local workgroup.  (I only have 64MB of RAM on that box, which
would be a mite low, but it's an _ancient_ server...)

The only thing that makes me a little suspicious that there's
something funny about the prescription is your indication of having
100 concurrent users, which is really rather heavyweight in comparison
with the comparatively tiny number of transactions.  Is this for some
sort of "departmental application"?  Where there's a lot of manual
data entry, so that each user would generate a transaction every 3-4
minutes?  That actually sounds about right...

Let me suggest that the "cost driver" in this will _not_ be the cost
of the hardware to support the database itself; it will instead be in
having redundant hardware and backup hardware to ensure reliability.

It would seem likely that just about any sort of modern hardware would
be pretty adequate to the task.  You can hardly _buy_ a system with
less than Gigahertz-speed CPUs, 40GB of disk, and 256MB of RAM.
Upgrade to have 2 SCSI disks, 512MB (or more, which is better) of RAM,
and the cost of a suitable system still won't be outrageous.

Double it, buying a standby server, and the cost still oughtn't be
real scary.  And if the application is important, you _should_ have a
standby server, irrespective of what software you might be running.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/x.html
Rules of the Evil Overlord #199. "I will not make alliances with those
more powerful than myself. Such a person would only double-cross me in
my moment  of glory.  I will make  alliances with those  less powerful
than myself. I will then  double-cross them in their moment of glory."
<http://www.eviloverlord.com/>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match