Machine specs & traffic for Postgres

Anon

Hey,
Could somebody give me the specs of the machines that you are running with Postgres and what type of traffic you are receiving ?

thx

Anon

Well we have two servers running postgres. Each one is about 700 Mhz. Each has 20MB of shared memory allocated to postgres and 2MB of sort mem (I think).

We're getting a million hits a month on each machine (hits, not visitors), but I can't give you much more than that.

We've never had Postgres crash on us. We love it - much more than MySQL that we used to run.

You need to know how to optimise it tho.

Chris

smarlowe

We run a web server / database server with Dual PIII 750s and 512 Megs ram. This machine also acts as the LDAP server. All use is internal only, not on the internet, and we average only a few thousand reads a day on the company phonebook. We run lots of other databases, but they are way more bursty in terms of how they get used. I.e. no access for hours, then we deliver 35,000 rows at once to an excel spread sheet five or ten times.

Under load testing, we can handle about 10,000 to 30,000 hits an hour with no real slow down in delivery, so we're way overpowered for what we need. but the Dual PIII was coming back from a customer and we still had a year or two on the lease, so we upgraded from our old box (an AMD K6-300 with 256 Megs ram) which was handling the load just fine. That machine could have handled probably 1,000 to 5,000 hits an hour with good response time.

My testing on the box leads me to believe we are pretty much I/O bound, since I have to use apache bench (ab) at something like 200 simos to generate more than 50% CPU load on both CPUs. We use a pair of 10K RPM 18 Gig Ultra SCSI drives in a RAID1.

I've tried using RAID0, which was no faster, but offers no redundancy, and RAID5 with 4 USCSI drives, but it was no faster than the RAID1. All RAID provided by the Linux Kernel, not hardware. It's a common misnomer that hardware RAID is faster than software, but many hardware RAID cards are actually slower than software RAID under linux. I can read data off our RAID1 SCSI at ~50 Megs a second with bonnie, and at ~28Megs a second off a UDMA-66 RAID1 set.

All our response times are sub second (usually <1/10th of a second).
The most important step is to configure your linux kernel to give postgresql LOTS of memory to play with, and to reconfigure the kernel to have lots more file handles than a stock install.

look up sysctl and sysctl.conf, they are a darned near idiot proof way of tweaking your kernel without editing /etc/rc.d/rc.local.

Anon

what kind of site are you running?
do you have examples of big sites running postgres?

almost everyone I have spoken to has told me Postgres is awesome, but I am having so much trouble with it. I really need a database solution quickly. The server load on the server I have running MySql has reached its limit. And from what I 've heard the size of my tables is not that big compared to other sites. approx 500,000 users with profiles.
we redesinged our site and decided to use postgres as our db, everytime we have tried to launch it just keeps locking up.

Anon

We are on a dual P3 with 1.5 GB of ram and really having trouble. Is it worth buying a Quad Xeon with 16 GB RAM ??? thats what someone suggested to me . What are the limitations of Postgres? I want a solution that will last me for at least a couple of years.

smarlowe

First off, you need to figure out what's getting used up by the server, ram, CPU, file handles etc... If you're running out of file handles then more memory won't help, etc...

Anon

Well Postgres should be able to handle that easily enough - as long as you understand how to use Postgres efficiently, and optimise it. For this, I suggest mailing one of the postgres mailing lists - you'll get plenty of help. If you are getting actual crashes or lockups, then we on the pgsql-hackers list would be VERY interested in hearing about it.

Have you increased shared mem on the system?
Have you upped simultaneous users to, say, 128?
Have you increased postgres's shared buffers up to, say, 2048? (20MB shared mem reqd)
Have you increased sort mem up to, say, 2048?
Have you created proper indices, and explained all your queries to make sure their being used and you don't have inefficiencies?
Do you fully understand functional and partial indices?

Just to give you some idea of how to optimise postgres - it won't hold your hand!

Chris

rycamor

As Scott says, what about filehandles, etc... Have you examined all the relevant system logs? Such as:

/var/log/messages
/var/log/httpd/error_log (or wherever Apache's error log is)
/path/to/pgsql/data/serverlog (Postgres' error log)

Also, it's worth examining your database design itself. PostgreSQL is optimized for a relational, normalized design, (and MySQL is not) and you might be handing PostgreSQL the wrong kinds of tasks.

For example, data retrieval that might take many small queries, looping through arrays, etc... with PHP/MySQL, can often be accomplished with a single View in PostgreSQL. Experience in MySQL does not really prepare you to deal with a real database like PostgreSQL. Are you reading any PostgreSQL books at the moment?