От: Arjen van der Meijden
Тема: Re: Tuning PostgreSQL
Дата: ,
Msg-id: 000001c3535f$4af7acd0$3ac15e91@acm
(см: обсуждение, исходный текст)
Ответ на: Re: Tuning PostgreSQL  (Andrew McMillan)
Ответы: Re: Tuning PostgreSQL  (Tom Lane)
Re: Tuning PostgreSQL  ("Balazs Wellisch")
Список: pgsql-performance

Скрыть дерево обсуждения

Tuning PostgreSQL  ("Alexander Priem", )
 Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
 Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
  Re: Tuning PostgreSQL  (Ang Chin Han, )
   Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
    Re: Tuning PostgreSQL  (Ang Chin Han, )
     Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
    Re: Tuning PostgreSQL  ("Alexander Priem", )
  Re: Tuning PostgreSQL  (Ron Johnson, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Ron Johnson, )
   Re: Tuning PostgreSQL  (Andrew McMillan, )
    Re: Tuning PostgreSQL  ("Arjen van der Meijden", )
     Re: Tuning PostgreSQL  (Tom Lane, )
     Re: Tuning PostgreSQL  ("Balazs Wellisch", )
      Re: Tuning PostgreSQL  (Josh Berkus, )
 Re: Tuning PostgreSQL  ("Roman Fail", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Josh Berkus, )
    Re: Tuning PostgreSQL  (Vincent van Leeuwen, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Vincent van Leeuwen, )
    Re: Tuning PostgreSQL  (Bruno Wolff III, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Andrew Sullivan, )
   Re: Tuning PostgreSQL  ("Jim C. Nasby", )
    Re: Tuning PostgreSQL  ("scott.marlowe", )
     Re: Tuning PostgreSQL  (Greg Stark, )
      Re: Tuning PostgreSQL  (Ron Johnson, )
     Re: Tuning PostgreSQL  (Vivek Khera, )
      Re: Tuning PostgreSQL  (Ron Johnson, )
       Re: Tuning PostgreSQL  ("scott.marlowe", )
        Re: Tuning PostgreSQL  (Ron Johnson, )
         Re: Tuning PostgreSQL  ("scott.marlowe", )
          Re: Tuning PostgreSQL  (Ron Johnson, )
           Re: Tuning PostgreSQL  ("scott.marlowe", )
            Re: Tuning PostgreSQL  (Ron Johnson, )
             Re: Tuning PostgreSQL, pt 2  (Ron Johnson, )
          Re: Tuning PostgreSQL  (Vivek Khera, )
      Re: Tuning PostgreSQL  (Will LaShell, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Ron Johnson, )
  Re: Tuning PostgreSQL  (Vivek Khera, )
 'View'-performance  ("Alexander Priem", )
  Re: 'View'-performance  (Tom Lane, )

> Andrew McMillan wrote:
>
> The general heuristic is that RAID-5 is not the way to deal
> with databases.  Now surely someone will disagree with me,
> but as I understand it RAID-5 has a bottleneck on a single
> disk for the
> (checksum) information.  Bottleneck is not the word you want
> to hear in the context of "database server".
That's indeed incorrect. There is no single disk "special" in a Raid-5,
you might be mistaking it for Raid-3/4 (where a single disk holds the
checksum). In raid-5 the checksums are scattered around on all the
harddisks.
Raid-5's problem is the write-performance, but with a decent
raid-controller it outperforms a less-decent raid-controller (with the
same harddisks) on both read- and writeperformance which is running a
raid-10.
With a decent raid-controller you end up with "about the same" write
performance as with raid-1, but slightly lower read performance.

At least, that's what I was able to gather from some tests of a
colleague of mine with different raid-setups.

> RAID-1 (mirroring) or RAID-10 (sort-of-mirrored-RAID-5) is
> the best choice.
Raid-10 is _not_ similar to raid-5, it is raid1+0 i.e. a mirroring set
of stripes (raid-0 is more-or-less a stripe).

For databases, raid-10 is supposed to be the fastest, since you have the
advantage of the striping for both reading and writing. While you also
have the advantage of the mirroring for reading.

The main disadvantage of raid-1 (and also of raid-10) is the heavy waste
of harddisk space. Another advantage of raid-5 over raid-10 is that when
you don't care about space, raid-5 is more save with four harddrives
than raid-10 (i.e. set it up with a 3-disk+1spare).

> As far as FS performance goes, a year or two ago I remember
> someone doing an evaluation of FS performance for PostgreSQL
> and they found that the best performance was...
>
> FAT
>
> Yep: FAT
FAT has a few disadvantages afaik, I wouldn't use it for my database at
least.

> Personally I don't plan to reboot my DB server more than once
> a year (if that (even my_laptop currently has 37 days uptime,
> not including suspend).  On our DB servers I use ext2 (rather
> than ext3) mounted with noatime, and I bite the 15 minutes to
> fsck (once a year) rather than screw general performance with
> journalling database on top of journalling FS.  I split
> pg_xlog onto a separate physical disk, if performance
> requirements are extreme.
Well, reboting is not a problem with ext2, but crashing might be... And
normally you don't plan a systemcrash ;)
Ext3 and xfs handle that much better.

Regards,

Arjen





В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Tuning PostgreSQL
От: "Balazs Wellisch"
Дата:
Сообщение: Re: Tuning PostgreSQL