Thread: Minimum hardware requirements for Postgresql db
Dear all 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? Thank you, REgards.
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/>
On Wed, 3 Dec 2003 CHEWTC@ap.nec.com.sg wrote: > Dear all > > 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? Like another poster pointed out, this is a walk in the park for postgresql. My workstation (1.1GHz celeron, 40 gig IDE drive, 512 Meg memory) could handle this load while still being my workstation. :-)
"scott.marlowe" <scott.marlowe@ihs.com> writes: > > 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. > Like another poster pointed out, this is a walk in the park for > postgresql. My workstation (1.1GHz celeron, 40 gig IDE drive, 512 Meg > memory) could handle this load while still being my workstation. Well there's some info missing. Like what would you actually be _doing_ with these data? 15,000 inserts per day is nothing. But after 18 months that's over 5M records not including the 10% growth rate. 5M records isn't really all that much but it's enough that it's possible to write slow queries against it. If you're doing big batch updates or complex reports against the data that will be more interesting than the inserts. -- greg
> 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. Assuming this server will be dedicated to PostgreSQL only, the needs outlined above are modest. As was pointed out in other posts, a simple sub-ghz machine with 512mb of ram is more than enough, but I'd slap on a gig only because RAM is cheaper now. If the database on this server is crucial, I'd look at setting up a UPS, RAID (at this level, even software-based RAID will do fine, RAID 5 preferably) and investing in a backup/replicator solution. -- Best, Al Hulaton | Sr. Account Engineer | Command Prompt, Inc. 503.667.4564 | ahulaton@commandprompt.com Home of Mammoth Replicator for PostgreSQL Managed PostgreSQL, Linux services and consulting Read and Search O'Reilly's 'Practical PostgreSQL' at http://www.commandprompt.com
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
CHEWTC@ap.nec.com.sg wrote:
> Sorry for my mistake on the 15000 recs per day.
It was useful for us to pick at that a bit; it was certainly looking a
mite suspicious.
> 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.
So that's more like 7 TPS, with, more than likely, a peak load several
times that.
> 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.
FYI, it is useful to plan for purging the old data from the very
beginning; if you don't, things can get ugly :-(.
> 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.
Based on multiplying the load by 40, we certainly move from
"pedestrian hardware where anything will do" to something requiring
more exotic hardware.
- You _definitely_ want a disk array, with a bunch of SCSI disks.
- You _definitely_ will want some form of RAID controller with
battery-backed cache.
- You probably want multiple CPUs.
- You almost certainly will want a second (and maybe third) complete
redundant system that you replicate data to.
- The thing that will have _wild_ effects on whether this is enough,
or whether you need to go for something even _more_ exotic
(e.g. - moving to big iron UNIX(tm), whether that be Solaris,
AIX, or HP/UX) is the issue of how heavily the main database gets
hit by queries.
If "all" it is doing is consolidating transactions, and there is
little query load from the POS systems, that is a very different
level of load from what happens if it is also servicing pricing
queries.
Performance will get _destroyed_, regardless of how heavy the iron
is, if you hit the OLTP system with a lot of transaction reports.
You'll want a secondary replicated system to draw that load off.
Evaluating whether it needs to be "big" hardware or "really enormous"
hardware is not realistic based on what you have said. There are
_big_ variations possible based notably on:
1. What kind of query load does the OLTP server have to serve up?
If the answer is "lots," then everything gets more expensive.
2. How was the database schema and the usage of the clients designed?
How well it is done will have a _large_ impact on how many TPS the
system can cope with.
You'll surely need to do some prototyping, and be open to
possibilities such as that you'll need to consider alternative OSes.
On Intel/AMD hardware, it may be worth considering FreeBSD; it may
also be needful to consider "official UNIX(tm)" hardware. It would be
unrealistic to pretend more certainty...
--
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"Being really good at C++ is like being really good at using rocks to
sharpen sticks." -- Thant Tessman