Re: Slow Postgresql server - Mailing list pgsql-performance

1= RAID 1improves data =intregrity=, not IO performance.
Your HD IO performance is essentially that of 1 160GB HD of whatever
performance one of those HDs have.
(what kind of HDs are they anyway?  For instance 7200rpm 160GB HDs
are not particularly "high performance")
BEST case is streaming IO involving no seeks => ~50 MBps.
You can't get even that as the back end of a website.

2= 1GB of RAM is -small- for a DB server.

You need to buy RAM and HD.

Boost the RAM to 4GB, change pg config parameters appropriately and
see how much it helps.
Non ECC RAM is currently running ~$60-$75 per GB for 1 or 2 GB sticks
ECC RAM prices will be ~ 1.5x - 2x that, $120 - $150 per GB for 1 or
2 GB sticks.
(do !not! buy 4GB sticks unless you have a large budget.  Their price
pr GB is still too high)

If adding RAM helps as much as I suspect it will, find out how big
the "hot" section of your DB is and see if you can buy enough RAM to
make it RAM resident.
If you can do this, it will result in the lowest term DB maintenance.

If you can't do that for whatever reason, the next step is to improve
your HD subsystem.
Cheap RAID cards with enough BB cache to allow writes to be coalesced
into larger streams (reducing seeks) will help, but you fundamentally
you will need more HDs.

RAID 5 is an reasonable option for most website DBs workloads.
To hit the 300MBps speeds attainable by the cheap RAID cards, you are
going to at least 7 HDs (6 HDs * 50MBps ASTR = 300MBps ASTR + the
equivalent of 1 HD gets used for the "R" in RAID).  A minimum of 8
HDs are need for this performance if you want to use RAID 6.
Most tower case (not mini-tower, tower) cases can hold this internally.
Price per MBps of HD is all over the map.  The simplest (but not
necessarily best) option is to buy more of the 160GB HDs you already have.
Optimizing the money spent when buying HDs for a RAID set is a bit
more complicated than doing so for RAM.  Lot's of context dependent
things affect the final decision.

I see you are mailing from Brandeis.  I'm local.  Drop me some
private email at the address I'm posting from if you want and I'll
send you further contact info so we can talk in more detail.

Cheers,
Ron Peacetree


At 06:02 PM 4/11/2007, Jason Lustig wrote:
>Hello all,
>
>My website has been having issues with our new Linux/PostgreSQL
>server being somewhat slow. I have done tests using Apache Benchmark
>and for pages that do not connect to Postgres, the speeds are much
>faster (334 requests/second v. 1-2 requests/second), so it seems that
>Postgres is what's causing the problem and not Apache. I did some
>reserach, and it seems that the bottleneck is in fact the hard
>drives! Here's an excerpt from vmstat:
>
>procs -----------memory---------- ---swap-- -----io---- --system--
>-----cpu------
>r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us
>sy id wa st
>1  1    140  24780 166636 575144    0    0     0  3900 1462  3299  1
>4 49 48  0
>0  1    140  24780 166636 575144    0    0     0  3828 1455  3391  0
>4 48 48  0
>1  1    140  24780 166636 575144    0    0     0  2440  960  2033  0
>3 48 48  0
>0  1    140  24780 166636 575144    0    0     0  2552 1001  2131  0
>2 50 49  0
>0  1    140  24780 166636 575144    0    0     0  3188 1233  2755  0
>3 49 48  0
>0  1    140  24780 166636 575144    0    0     0  2048  868  1812  0
>2 49 49  0
>0  1    140  24780 166636 575144    0    0     0  2720 1094  2386  0
>3 49 49  0
>
>As you can see, almost 50% of the CPU is waiting on I/O. This doesn't
>seem like it should be happening, however, since we are using a RAID
>1 setup (160+160). We have 1GB ram, and have upped shared_buffers to
>13000 and work_mem to 8096. What would cause the computer to only use
>such a small percentage of the CPU, with more than half of it waiting
>on I/O requests?
>
>Thanks a lot
>Jason
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly