Re: New system recommendations - Mailing list pgsql-admin

On Wed, 2006-04-26 at 20:30, Benjamin Krajmalnik wrote:
> I am about to take a system from testing into production.
>
> The system is a combination oltp/bi (network monitoring platform).
>
> We are currently inserting about 1 million rows per day, and will
> increase to probably 5 million once it goes into full deployment.

Assuming most of that is during the nominal 8 hour workday, that's about
35 inserts per second, up to 173 inserts per second, on average.

Anytime you're looking at a high write load on a database (not just
PostgreSQL) you should be looking at a hardware RAID controller with
battery backed cache and RAID 1 or RAID 1+0.

> The current test production server is running Windows Server 2003,
> PostgreSQL 8.1.3.
>
> Record insertion is done via an ODBC call to a stored procedure from a
> Windows based host monitor.

Windows support is still pretty new, but I'm guessing that much of the
performance problems we've seen have been as much tuning as OS related.

> In addition to PostgreSQL, the server also runs Apache/PHP for the GUI
> which displays our dashboards with the querying and drill-down front
> end..

You might be able to get better performance if you make the postgresql
box a single purpose machine.

> It is currently running on a 3GHz Xeon HT, 2GB RAM, dual 72GB disks
> running RAID 1.  This server is a 1U without only 2 drive bays, so I
> have a potential issue with drive space.
>
> As a result, I will be moving the db server to a Dell 1650 with 3
> 146GB SCSI drives running RAID 0.  System is a dual processor, 1.2GHz,
> with 4GB RAM.

Don't run important servers on RAID 0.  RAID 1 is a better choice.  RAID
1+0 is a betterer choice.

I do NOT recommend the Dell 16xx/26xx series, from experience.  Last job
I had I had a 2600, fairly early model, and it was stable.  We had the
AMI based RAID controller in it (Perc4/DC), and ran the latest 2.xx
version of the megaraid driver.  All the other 2600s where I worked (and
we had lots of them) had occasional, unexplainable lockups.  They were
never corrected.  They all had the adaptec controllers (Perc4/DI) in
them.  Started a new job last year, and our servers were occasionally
locking up for no good reason.  go and look in the server room and guess
what we're running.  yep.  2650s with Perc4/DI controllers.

We have 2850s now, and they've been quite solid.  Performance-wise
neither the 26xx or 28xx is a stellar performer, but at least the 28xx
series seems to be stable.

> Due to the amount of record insertions being performed, record
> insertion speed is paramount – also because excessive execution time
> will have a side effect of causing the monitoring agent to go stale.

Then definitely get the right tool for the job: a battery backed caching
RAID controller.

>  Some of the users have mentioned that I will get better performance
> running under a *nix OS.  We are mostly a Microsoft OS house, but also
> run FreeBSD.  I am considering deploying with FreeBSD 6.0.  I was
> wondering if anyone has benchmarks showing speed of execution of
> PostgreSQL 8.1.3 under Win2003 and FreeBSD 6.0.  Also, are there any
> caveats or items I should be aware of if running under FreeBSD?  Any
> issues when running under a multi-processor kernel?  Anything in
> specific which I should include in the kernel build to give me optimum
> performance for running PostgreSQL?

If you've got basic experience with FreeBSD, then running PostgreSQL on
it should be a no brainer.