Обсуждение: kernel version impact on PostgreSQL performance

Поиск
Список
Период
Сортировка

kernel version impact on PostgreSQL performance

От
Cyril Scetbon
Дата:
Does anyone know what can be the differences between linux kernels
2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !)
http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2

Regards
--
Cyril SCETBON

Re: kernel version impact on PostgreSQL performance

От
Scott Marlowe
Дата:
On Fri, Mar 5, 2010 at 7:09 AM, Cyril Scetbon
<cscetbon.ext@orange-ftgroup.com> wrote:
> Does anyone know what can be the differences between linux kernels 2.6.29
> and 2.6.30 that can cause this big difference (TPS x 7 !)
> http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2

I'm gonna guess those ones with huge TPC B throughput weren't properly
fsyncing their data to the disks.  to get that high of a number
requires a disk subsystem that can commit 1300 or so changes a second,
which a single disk can't really do.

Re: kernel version impact on PostgreSQL performance

От
John R Pierce
Дата:
Scott Marlowe wrote:
> On Fri, Mar 5, 2010 at 7:09 AM, Cyril Scetbon
> <cscetbon.ext@orange-ftgroup.com> wrote:
>
>> Does anyone know what can be the differences between linux kernels 2.6.29
>> and 2.6.30 that can cause this big difference (TPS x 7 !)
>> http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2
>>
>
> I'm gonna guess those ones with huge TPC B throughput weren't properly
> fsyncing their data to the disks.  to get that high of a number
> requires a disk subsystem that can commit 1300 or so changes a second,
> which a single disk can't really do.
>
>


indeed, that was my first thought too.   being on the trailing edge of
kernel builds (I mostly run CentOS4 and 5, and most our more important
servers are Solaris based), I can only view this with some amusement.



Re: kernel version impact on PostgreSQL performance

От
Greg Smith
Дата:
Cyril Scetbon wrote:
> Does anyone know what can be the differences between linux kernels
> 2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !)
> http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2

Discussed in detail at
http://archives.postgresql.org/message-id/4B512D0D.4030909@2ndquadrant.com

You might need the background information at
http://developer.postgresql.org/pgdocs/postgres/wal-reliability.html to
be able to follow what that's talking about.

The short version is that ext3 combined with regular hard drives has
never been safe for database use by default, it was cheating on writes
in a way that left it possible for corruption to occur after a crash.
The change in ext4 that caused the performance drop is from the kernel
developers finally eliminating the source for that cheat.  The result
should be reliable operation by default, which unfortunately happens to
be much slower operation by default too.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


Filesysstems

От
Adrian von Bidder
Дата:
Heyho!

On Friday 05 March 2010 20.18:46 Greg Smith wrote:
> The short version is that ext3 combined with regular hard drives has
> never been safe for database use by default,  [...]
> The change in ext4 [...] eliminating the source for that cheat.

Tangentially related: how is the behaviour [1] of pg when the db is located
on an extent based cow filesysstem like btrfs?  I'm not too sure about the
internals, but my understanding is that the mechanism is a bit similar to
what pg does with its mvcc: new content is written into empty space and then
later pointers are updated to point to the new content.

[1] I'd expect that there are no issues with reliability, assuming the fs is
bug-free.  I'm more worried about performance.  Wouldn't the table files get
fragmented more and more with updates?

cheers
-- vbi

--
featured product: vim - http://vim.org

Вложения

Re: kernel version impact on PostgreSQL performance

От
"Albe Laurenz"
Дата:
Greg Smith wrote:
> Cyril Scetbon wrote:
> > Does anyone know what can be the differences between linux kernels
> > 2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !)
> >
> http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2
>
> Discussed in detail at
> http://archives.postgresql.org/message-id/4B512D0D.4030909@2nd
> quadrant.com
>
> The short version is that ext3 combined with regular hard drives has
> never been safe for database use by default, it was cheating on writes
> in a way that left it possible for corruption to occur after a crash.
> The change in ext4 that caused the performance drop is from the kernel
> developers finally eliminating the source for that cheat.  The result
> should be reliable operation by default, which unfortunately happens to
> be much slower operation by default too.

Maybe that question is dumb, but why should a change in ext4 have an
impact on a figure that was generated with ext3? To quote the link:

"the PostgreSQL performance atop the EXT3 file-system has fallen off a cliff"

Yours,
Laurenz Albe

Re: kernel version impact on PostgreSQL performance

От
Greg Smith
Дата:
Albe Laurenz wrote:
> Maybe that question is dumb, but why should a change in ext4 have an
> impact on a figure that was generated with ext3? To quote the link:
>
> "the PostgreSQL performance atop the EXT3 file-system has fallen off a cliff"
>

What I'm guessing is that after finding the root problem because it's
more obvious when combined with ext4, someone has now put the fix for it
in the right place for 2.6.33 so that it also impacts ext3 writes.  That
would be more good news from a reliability perspective, and bad news
from an advocacy one because it looks really bad on benchmarks if the
writer doesn't understand how database commits to disk work--which seems
to the case at Phoronix.  They really are not running the right sort of
PostgreSQL benchmarks at all over there.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


Re: kernel version impact on PostgreSQL performance

От
John R Pierce
Дата:
Albe Laurenz wrote:
> "the PostgreSQL performance atop the EXT3 file-system has fallen off a
> cliff"

really a pretty stupid statement from them, as the performance went
right back to where it was a few versions earlier.