Thread

Re: understanding postgres issues/bottlenecks

Luke Lonergan <llonergan@greenplum.com> — 2009-01-11T23:16:21Z

Not to mention the #1 cause of server faults in my experience: OS kernel bug causes a crash.  Battery backup doesn't help you much there.

Fsync of log is necessary IMO.

That said, you could use a replication/backup strategy to get a consistent snapshot in the past if you don't mind losing some data or can recreate it from backup elsewhere.

I think a strategy that puts the WAL on an SLC SSD is a very good one as of Jan/09 and will get much better in short order.

- Luke

----- Original Message -----
From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: Ron <rjpeace@earthlink.net>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Sent: Sun Jan 11 15:35:22 2009
Subject: Re: [PERFORM] understanding postgres issues/bottlenecks

On Sun, 11 Jan 2009, Glyn Astill wrote:

> --- On Sun, 11/1/09, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> They also told me we could never lose power in the hosting
>> center
>> because it was so wonder and redundant and that I was
>> wasting my time.
>
> We'll that's just plain silly, at the very least there's always going to
> be some breakers / fuzes in between the power and the machines.
>
> In fact in our building there's quite a few breaker points between our
> comms room on the 3rd floor and the ups / generator in the basement.
> It's a crappy implementation actually.

the response I get from people is that they give their servers redundant
poewr supplies and put them on seperate circuits so they must be safe from
that.

but as commented, that's not enough in the real world.

David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: understanding postgres issues/bottlenecks

Scott Marlowe <scott.marlowe@gmail.com> — 2009-01-11T23:44:53Z

On Sun, Jan 11, 2009 at 4:16 PM, Luke Lonergan <LLonergan@greenplum.com> wrote:
> Not to mention the #1 cause of server faults in my experience: OS kernel bug causes a crash.  Battery backup doesn't help you much there.

I've been using pgsql since way back, in a lot of projects, and almost
almost of them on some flavor of linux, usually redhat.  I have had
zero kernel crashes on production dbs in that time.  I'd be interested
to know which historic kernels caused you the crashes.

Re: understanding postgres issues/bottlenecks

M. Edward (Ed) Borasky <znmeb@cesmail.net> — 2009-01-12T02:43:42Z

Luke Lonergan wrote:
> Not to mention the #1 cause of server faults in my experience: OS kernel bug causes a crash.  Battery backup doesn't help you much there.

Well now ... that very much depends on where you *got* the server OS and
how you administer it. If you're talking a correctly-maintained Windows
2003 Server installation, or a correctly-maintained Red Hat Enterprise
Linux installation, or any other "branded" OS from Novell, Sun, HP,
etc., I'm guessing such crashes are much rarer than what you've
experienced.

And you're probably in pretty good shape with Debian stable and the RHEL
respins like CentOS. I can't comment on Ubuntu server or any of the BSD
family -- I've never worked with them. But you should be able to keep a
"branded" server up for months, with the exception of applying security
patches that require a reboot. And *those* can be *planned* outages!

Where you *will* have some major OS risk is with testing-level software
or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't
know why people run Fedora servers -- if it's Red Hat compatibility you
want, there's CentOS.


-- 
M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P), WOM

I've never met a happy clam. In fact, most of them were pretty steamed.

Re: understanding postgres issues/bottlenecks

Robert Haas <robertmhaas@gmail.com> — 2009-01-12T03:08:54Z

> Where you *will* have some major OS risk is with testing-level software
> or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't
> know why people run Fedora servers -- if it's Red Hat compatibility you
> want, there's CentOS.

I've had no stability problems with Fedora.  The worst experience I've
had with that distribution is that half the time the CD-burning
utilities seem to be flaky.  As for why that and not CentOS...  I like
having modern versions of all of my packages.  5 years is a long time
to get nothing but bugfixes.

...Robert

Re: understanding postgres issues/bottlenecks

M. Edward (Ed) Borasky <znmeb@cesmail.net> — 2009-01-12T04:28:07Z

Robert Haas wrote:
>> Where you *will* have some major OS risk is with testing-level software
>> or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't
>> know why people run Fedora servers -- if it's Red Hat compatibility you
>> want, there's CentOS.
> 
> I've had no stability problems with Fedora.  The worst experience I've
> had with that distribution is that half the time the CD-burning
> utilities seem to be flaky.  As for why that and not CentOS...  I like
> having modern versions of all of my packages.  5 years is a long time
> to get nothing but bugfixes.
Fedora is not the only distro with "flaky CD-burning utilities". I'm
still hunting down issues with k3b and brasero on my openSUSE 11.1
system. "RW" media are your friends. :)

Five years may be a long time to get nothing but bugfixes, but having to
do extensive upgrade testing and an OS upgrade every six months, which
is where most "community" distros' release schedules are these days,
also seems extreme. I personally think for the money you pay for Windows
2003 Server or RHEL, you ought to be able to go a year or more between
unplanned reboots, and they really should minimize the number of reboots
you have to eat for security fixes too. Otherwise, what's the point of
spending money for an OS? :)
-- 
M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P), WOM

I've never met a happy clam. In fact, most of them were pretty steamed.

Re: understanding postgres issues/bottlenecks

Greg Smith <gsmith@gregsmith.com> — 2009-01-12T04:38:44Z

On Sun, 11 Jan 2009, M. Edward (Ed) Borasky wrote:

> And you're probably in pretty good shape with Debian stable and the RHEL 
> respins like CentOS.

No one is in good shape until they've done production-level load testing 
on the system and have run the sort of "unplug it under load" tests that 
Scott was praising.  Since so many of the kernel bugs you might run into 
are in device drivers, the subset of Debian/RHEL you're using is only as 
stable as the worst supported driver running on your system.

> But you should be able to keep a "branded" server up for months, with 
> the exception of applying security patches that require a reboot.

Right, this is why I only rely on Linux deployments using a name I trust: 
Dell.

Returning to reality, the idea that there are brands you can buy that make 
all your problems go away is rather optimistic.  The number of "branded" 
servers I've seen that are just nearly or completely worthless for 
database use is rather depressing.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: understanding postgres issues/bottlenecks

M. Edward (Ed) Borasky <znmeb@cesmail.net> — 2009-01-12T05:30:55Z

Greg Smith wrote:
> Right, this is why I only rely on Linux deployments using a name I
> trust: Dell.
> 
> Returning to reality, the idea that there are brands you can buy that
> make all your problems go away is rather optimistic.  The number of
> "branded" servers I've seen that are just nearly or completely worthless
> for database use is rather depressing.

Well, of course, they won't make *all* your problems go away. But still,
I'd much rather have an IBM or HP or Dell server running Windows 2003 or
RHEL or SLES than some no-name hardware running Fedora or Ubuntu.
-- 
M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P), WOM

I've never met a happy clam. In fact, most of them were pretty steamed.

Re: understanding postgres issues/bottlenecks

Mark Kirkwood <markir@paradise.net.nz> — 2009-01-12T05:54:45Z

M. Edward (Ed) Borasky wrote:
> Greg Smith wrote:
>   
>> Right, this is why I only rely on Linux deployments using a name I
>> trust: Dell.
>>
>> Returning to reality, the idea that there are brands you can buy that
>> make all your problems go away is rather optimistic.  The number of
>> "branded" servers I've seen that are just nearly or completely worthless
>> for database use is rather depressing.
>>     
>
> Well, of course, they won't make *all* your problems go away. But still,
> I'd much rather have an IBM or HP or Dell server running Windows 2003 or
> RHEL or SLES than some no-name hardware running Fedora or Ubuntu.
>   
If you use no-name hardware it all depends on how reliable your supplier 
is. The no-name white box providers I've had experience with have always 
supplied hardware that was reliable and fast. And as they were small 
companies they would work with you to give you hardware that you 
preferred (e.g raid cards etc).

Conversely I've found big name brand suppliers would often change 
critical parts (network or raid cards) midway through shipment - leaving 
you with the odd-man-out server to debug silly issues with (e.g won't 
get on the network, disk array not recognized by the installation media 
etc). So I'm not entirely convinced by the 'name brand is good' argument.

I'd agree that Fedora is probably not the best choice for a 
deployment(!). My experience of Ubuntu has been better, however using 
the LTS release might be a wise move if one wants to user this distro.

regards

Mark

Re: understanding postgres issues/bottlenecks

Scott Marlowe <scott.marlowe@gmail.com> — 2009-01-12T06:37:29Z

On Sun, Jan 11, 2009 at 8:08 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Where you *will* have some major OS risk is with testing-level software
>> or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't
>> know why people run Fedora servers -- if it's Red Hat compatibility you
>> want, there's CentOS.
>
> I've had no stability problems with Fedora.  The worst experience I've
> had with that distribution is that half the time the CD-burning
> utilities seem to be flaky.  As for why that and not CentOS...  I like
> having modern versions of all of my packages.  5 years is a long time
> to get nothing but bugfixes.

We basically do the same thing with our app tier servers, chasing the
next to latest ubuntus so we aren't running really old bits there.

For the db level it's RHEL with pgsql.  Ubuntu has been pretty stable
on the app tier, but we don't push it in the same ways we push our
databases either.

Re: understanding postgres issues/bottlenecks

Matthew Wakeling <matthew@flymine.org> — 2009-01-12T15:36:56Z

On Sun, 11 Jan 2009, M. Edward (Ed) Borasky wrote:
> Where you *will* have some major OS risk is with testing-level software
> or "bleeding edge" Linux distros like Fedora.

I just ran "uptime" on my home machine, and it said 144 days. Debian 
unstable, on no-name hardware. I guess the last time I rebooted was 
when I changed the graphics drivers. I can't remember the last time it 
actually crashed.

I guess the moral is, you find a combination of hardware and software that 
works well, and you may as well stick with it. The most likely things to 
destabilise the system are drivers for "interesting" pieces of hardware, 
like graphics cards and (unfortunately) some high-performance RAID cards.

Matthew

-- 
 A good programmer is one who looks both ways before crossing a one-way street.
 Considering the quality and quantity of one-way streets in Cambridge, it
 should be no surprise that there are so many good programmers there.

Re: understanding postgres issues/bottlenecks

Jeff Trout <threshar@torgo.978.org> — 2009-01-13T13:40:32Z

On Jan 11, 2009, at 9:43 PM, M. Edward (Ed) Borasky wrote:

> Luke Lonergan wrote:
>> Not to mention the #1 cause of server faults in my experience: OS  
>> kernel bug causes a crash.  Battery backup doesn't help you much  
>> there.
>

Not that long ago (a month or so) we ran into a problem where hpacucli  
(Utility for configuring/inspecting/etc HP smartarray controllers)  
would tickle the cciss driver in such a way that it would  cause a  
kernel panic. KABLAMMO (No data loss! weeeee!).   The box had run for  
a long time without crashes, but it seems that when we added more  
disks and started the array building the new logical drive some  
magical things happened.

Bugs happen.  The [bad word] of it is catching the culprit with its  
fingers in the cookie jar.

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/

Re: understanding postgres issues/bottlenecks

Jean-David Beyer <jeandavid8@verizon.net> — 2009-01-15T19:22:14Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

M. Edward (Ed) Borasky wrote:
| Luke Lonergan wrote:
|> Not to mention the #1 cause of server faults in my experience: OS
|> kernel bug causes a crash.  Battery backup doesn't help you much there.
|>
|
| Well now ... that very much depends on where you *got* the server OS and
| how you administer it. If you're talking a correctly-maintained Windows
| 2003 Server installation, or a correctly-maintained Red Hat Enterprise
| Linux installation, or any other "branded" OS from Novell, Sun, HP, etc.,
| I'm guessing such crashes are much rarer than what you've experienced.
|
| And you're probably in pretty good shape with Debian stable and the RHEL
| respins like CentOS. I can't comment on Ubuntu server or any of the BSD
| family -- I've never worked with them. But you should be able to keep a
| "branded" server up for months, with the exception of applying security
| patches that require a reboot. And *those* can be *planned* outages!
|
| Where you *will* have some major OS risk is with testing-level software
| or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't know
| why people run Fedora servers -- if it's Red Hat compatibility you want,
| there's CentOS.
|
Linux kernels seem to be pretty good these days. I ran Red Hat Linux 7.3
24/7 for over 6 months, and it was discontinued years ago. I recognize that
this is by no means a record. It did not crash after 6 months, but I
upgraded that box to CentOS 4 and it has been running that a long time. That
box has minor hardware problems that do not happen often enough to find the
real cause. But it stays up months at a time. All that box does is run BOINC
and a printer server (CUPS).

This machine does not crash, but it gets rebooted whenever a new kernel
comes out, and has been up almost a month. It run RHEL5.

I would think Fedora's kernel would probably be OK, but the other bleeding
edge stuff I would not risk a serious server on.

- --
~  .~.  Jean-David Beyer          Registered Linux User 85642.
~  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
~ /( )\ Shrewsbury, New Jersey    http://counter.li.org
~ ^^-^^ 14:10:01 up 30 days, 1:55, 3 users, load average: 4.18, 4.26, 4.24
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFJb4zmPtu2XpovyZoRAn9TAKDFoEZ0JtoTi7T0qs9ZlI7rLxs9lACeJjDZ
XL9rGZqzw0LjrszD1DaAhp4=
=LdVq
-----END PGP SIGNATURE-----

Re: understanding postgres issues/bottlenecks

M. Edward (Ed) Borasky <znmeb@cesmail.net> — 2009-01-16T06:03:01Z

Jean-David Beyer wrote:
> M. Edward (Ed) Borasky wrote:
> | Luke Lonergan wrote:
> |> Not to mention the #1 cause of server faults in my experience: OS
> |> kernel bug causes a crash.  Battery backup doesn't help you much there.
> |>
> |
> | Well now ... that very much depends on where you *got* the server OS and
> | how you administer it. If you're talking a correctly-maintained Windows
> | 2003 Server installation, or a correctly-maintained Red Hat Enterprise
> | Linux installation, or any other "branded" OS from Novell, Sun, HP, etc.,
> | I'm guessing such crashes are much rarer than what you've experienced.
> |
> | And you're probably in pretty good shape with Debian stable and the RHEL
> | respins like CentOS. I can't comment on Ubuntu server or any of the BSD
> | family -- I've never worked with them. But you should be able to keep a
> | "branded" server up for months, with the exception of applying security
> | patches that require a reboot. And *those* can be *planned* outages!
> |
> | Where you *will* have some major OS risk is with testing-level software
> | or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't know
> | why people run Fedora servers -- if it's Red Hat compatibility you want,
> | there's CentOS.
> |
> Linux kernels seem to be pretty good these days. I ran Red Hat Linux 7.3
> 24/7 for over 6 months, and it was discontinued years ago. I recognize that
> this is by no means a record. It did not crash after 6 months, but I
> upgraded that box to CentOS 4 and it has been running that a long time.
> That
> box has minor hardware problems that do not happen often enough to find the
> real cause. But it stays up months at a time. All that box does is run
> BOINC
> and a printer server (CUPS).
> 
> This machine does not crash, but it gets rebooted whenever a new kernel
> comes out, and has been up almost a month. It run RHEL5.
> 
> I would think Fedora's kernel would probably be OK, but the other bleeding
> edge stuff I would not risk a serious server on.

I haven't heard much one way or the other about Fedora's kernels.
Because of the way their release cycle is staggered with the other major
community distros, they tend to be a number behind, say, openSUSE.
openSUSE 11.1, for example, just came out with 2.6.27, while Fedora came
out with 2.6.26 only a couple of weeks before that.

The things I care the most about -- kernel-level performance metrics --
pretty much guarantee that I'm going to run a bleeding edge kernel as
soon as it's good enough to live through a couple of days without losing
data on the hard drive. And on my laptop, anything that recognizes my
wireless and sound and leaves some of my 512 MB for applications is OK. :)
> 

-- 
M. Edward (Ed) Borasky

I've never met a happy clam. In fact, most of them were pretty steamed.

Re: understanding postgres issues/bottlenecks

david@lang.hm — 2009-01-16T08:59:53Z

On Thu, 15 Jan 2009, Jean-David Beyer wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> M. Edward (Ed) Borasky wrote:
> | Luke Lonergan wrote:
> |> Not to mention the #1 cause of server faults in my experience: OS
> |> kernel bug causes a crash.  Battery backup doesn't help you much there.
> |>
> |
> | Well now ... that very much depends on where you *got* the server OS and
> | how you administer it. If you're talking a correctly-maintained Windows
> | 2003 Server installation, or a correctly-maintained Red Hat Enterprise
> | Linux installation, or any other "branded" OS from Novell, Sun, HP, etc.,
> | I'm guessing such crashes are much rarer than what you've experienced.
> |
> | And you're probably in pretty good shape with Debian stable and the RHEL
> | respins like CentOS. I can't comment on Ubuntu server or any of the BSD
> | family -- I've never worked with them. But you should be able to keep a
> | "branded" server up for months, with the exception of applying security
> | patches that require a reboot. And *those* can be *planned* outages!
> |
> | Where you *will* have some major OS risk is with testing-level software
> | or "bleeding edge" Linux distros like Fedora. Quite frankly, I don't know
> | why people run Fedora servers -- if it's Red Hat compatibility you want,
> | there's CentOS.
> |
> Linux kernels seem to be pretty good these days. I ran Red Hat Linux 7.3
> 24/7 for over 6 months, and it was discontinued years ago. I recognize that
> this is by no means a record. It did not crash after 6 months, but I
> upgraded that box to CentOS 4 and it has been running that a long time. That
> box has minor hardware problems that do not happen often enough to find the
> real cause. But it stays up months at a time. All that box does is run BOINC
> and a printer server (CUPS).
>
> This machine does not crash, but it gets rebooted whenever a new kernel
> comes out, and has been up almost a month. It run RHEL5.
>
> I would think Fedora's kernel would probably be OK, but the other bleeding
> edge stuff I would not risk a serious server on.

I have been running kernel.org kernels in production for about 12 years 
now (on what has now grown to a couple hundred servers), and I routinely 
run from upgrade to upgrade with no crashes. I tend to upgrade every year 
or so).

that being said, things happen. I have a set of firewalls running the 
Checkpoint Secure Platform linux distribution that locked up solidly a 
couple weeks after putting them in place (the iptables firewalls that they 
replaced had been humming along just fine under much heavier loads for 
months).

the more mainstream your hardware is the safer you are (unfortunantly very 
few RAID cards are mainstream), but I've also found that by compiling a 
minimal kernel that only supports the stuff that I need also contributes 
to reliability.

but even with my experiance, I would never architect anything with the 
expectation that system crashes don't happen. I actually see more crashes 
due to overheating (fans fail, AC units fail, etc) than I do from kernel 
crashes.

not everything needs reliability. I am getting ready to build a pair of 
postgres servers that will have all safety disabled. I will get the 
redundancy I need by replicating between the pair, and if they both go 
down (datacenter outage) it is very appropriate to loose the entire 
contents of the system and reinitialize from scratch (in fact, every boot 
of the system will do this)

but you need to think carefully about what you are doing when you disable 
the protection.

David Lang