Re: How to improve db performance with $7K?

От: William Yu
Тема: Re: How to improve db performance with $7K?
Дата: ,
Msg-id: d31f71$2epm$1@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: Re: How to improve db performance with $7K?  (Alex Turner)
Ответы: Re: How to improve db performance with $7K?  (Alex Turner)
Список: pgsql-performance

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

How to improve db performance with $7K?  (Steve Poe, )
 Re: How to improve db performance with $7K?  (Tom Lane, )
  Re: How to improve db performance with $7K?  (Steve Poe, )
 Re: How to improve db performance with $7K?  (Will LaShell, )
 Re: How to improve db performance with $7K?  ("Joshua D. Drake", )
  Re: How to improve db performance with $7K?  (Steve Poe, )
   Re: How to improve db performance with $7K?  (Bjoern Metzdorf, )
    Re: How to improve db performance with $7K?  (Josh Berkus, )
     Re: How to improve db performance with $7K?  (Steve Poe, )
      Re: How to improve db performance with $7K?  (Cott Lang, )
       Re: How to improve db performance with $7K?  (Steve Poe, )
        Re: How to improve db performance with $7K?  (Cott Lang, )
         Follow-Up: How to improve db performance with $7K?  (Steve Poe, )
          Re: Follow-Up: How to improve db performance with $7K?  (Thomas F.O'Connell, )
           Re: Follow-Up: How to improve db performance with $7K?  (Kevin Brown, )
            Re: Follow-Up: How to improve db performance with $7K?  ("Steinar H. Gunderson", )
            Re: Follow-Up: How to improve db performance with $7K?  (Thomas F.O'Connell, )
          Re: Follow-Up: How to improve db performance with $7K?  (Vivek Khera, )
           Re: Follow-Up: How to improve db performance with $7K?  (Will LaShell, )
     Re: How to improve db performance with $7K?  (Steve Poe, )
    Re: Follow-Up: How to improve db performance with $7K?  (PFC, )
     Re: Follow-Up: How to improve db performance with $7K?  ("Steinar H. Gunderson", )
   Re: How to improve db performance with $7K?  (PFC, )
    Re: How to improve db performance with $7K?  (Dave Cramer, )
     Re: How to improve db performance with $7K?  (Greg Stark, )
     Re: How to improve db performance with $7K?  (Alex Turner, )
      Re: How to improve db performance with $7K?  (Steve Poe, )
       Re: How to improve db performance with $7K?  (Alex Turner, )
        Re: How to improve db performance with $7K?  (Vivek Khera, )
         Re: How to improve db performance with $7K?  (Alex Turner, )
        Re: How to improve db performance with $7K?  (William Yu, )
         Re: How to improve db performance with $7K?  (Alex Turner, )
          Re: How to improve db performance with $7K?  (William Yu, )
           Re: How to improve db performance with $7K?  (Alex Turner, )
            Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
             Re: How to improve db performance with $7K?  (Alex Turner, )
              Re: How to improve db performance with $7K?  (Alex Turner, )
               Re: How to improve db performance with $7K?  (Alex Turner, )
              Re: How to improve db performance with $7K?  (Greg Stark, )
               Re: How to improve db performance with $7K?  (Alex Turner, )
               Re: How to improve db performance with $7K?  (Tom Lane, )
                Re: How to improve db performance with $7K?  (Kevin Brown, )
                 Re: How to improve db performance with $7K?  (Greg Stark, )
                  Re: How to improve db performance with $7K?  (Kevin Brown, )
                   Re: How to improve db performance with $7K?  (Tom Lane, )
                    Re: How to improve db performance with $7K?  (Kevin Brown, )
                     Re: How to improve db performance with $7K?  (Alex Turner, )
                      Re: How to improve db performance with $7K?  (PFC, )
                     Re: How to improve db performance with $7K?  (Tom Lane, )
                      Re: How to improve db performance with $7K?  (Kevin Brown, )
                       Re: How to improve db performance with $7K?  (Tom Lane, )
                        Re: How to improve db performance with $7K?  (Kevin Brown, )
                      Re: How to improve db performance with $7K?  (Greg Stark, )
                     Re: How to improve db performance with $7K?  (Vivek Khera, )
                      Re: How to improve db performance with $7K?  ("Joshua D. Drake", )
                       Re: How to improve db performance with $7K?  (Vivek Khera, )
                      Re: How to improve db performance with $7K?  (Kevin Brown, )
                    Re: How to improve db performance with $7K?  (Matthew Nuzum, )
                     Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
                   Re: How to improve db performance with $7K?  (Rosser Schwarz, )
                    Re: How to improve db performance with $7K?  (Kevin Brown, )
                   Re: How to improve db performance with $7K?  (Greg Stark, )
                   Re: How to improve db performance with $7K?  (Bruce Momjian, )
                    Re: How to improve db performance with $7K?  (Alex Turner, )
                     Re: How to improve db performance with $7K?  (Alvaro Herrera, )
                     Re: How to improve db performance with $7K?  (Bruce Momjian, )
              Re: How to improve db performance with $7K?  ("Douglas J. Trainor", )
               Re: How to improve db performance with $7K?  (, )
                Re: How to improve db performance with $7K?  (Alex Turner, )
                 Re: How to improve db performance with $7K?  (, )
            Re: How to improve db performance with $7K?  ("Douglas J. Trainor", )
            Re: How to improve db performance with $7K?  (PFC, )
             Re: How to improve db performance with $7K?  (Alan Stange, )
    Re: How to improve db performance with $7K?  (Dave Cramer, )
 Re: How to improve db performance with $7K?  ("Alexander Kirpa", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
 Re: How to improve db performance with $7K?  ("Matthew Nuzum", )
  Re: How to improve db performance with $7K?  (Greg Stark, )
   Re: How to improve db performance with $7K?  (PFC, )
  Re: How to improve db performance with $7K?  (Tom Lane, )
 Re: How to improve db performance with $7K?  ("Dave Held", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
  Re: How to improve db performance with $7K?  (Steve Poe, )
   Re: How to improve db performance with $7K?  ("Joshua D. Drake", )
   Re: How to improve db performance with $7K?  (William Yu, )
    Re: How to improve db performance with $7K?  (Greg Stark, )
     Re: How to improve db performance with $7K?  (Alex Turner, )
      Re: How to improve db performance with $7K?  (Greg Stark, )
       Re: How to improve db performance with $7K?  (Alex Turner, )
        Re: How to improve db performance with $7K?  (Jacques Caron, )
         Re: How to improve db performance with $7K?  (Alex Turner, )
          Re: How to improve db performance with $7K?  (Alex Turner, )
           Re: How to improve db performance with $7K?  (Jacques Caron, )
            Re: How to improve db performance with $7K?  (Alex Turner, )
          Re: How to improve db performance with $7K?  (Jacques Caron, )
         Re: How to improve db performance with $7K?  (Greg Stark, )
        Re: How to improve db performance with $7K?  (Alan Stange, )
        Re: How to improve db performance with $7K?  (John A Meinel, )
         Re: How to improve db performance with $7K?  (Alex Turner, )
          Re: How to improve db performance with $7K?  ("Joshua D. Drake", )
           Re: How to improve db performance with $7K?  (Alex Turner, )
        Re: How to improve db performance with $7K?  (Steve Poe, )
         Re: How to improve db performance with $7K?  (Alex Turner, )
     Re: How to improve db performance with $7K?  (Jacques Caron, )
      Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
     Re: How to improve db performance with $7K?  (William Yu, )
 Re: How to improve db performance with $7K?  ("Dave Held", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
 Re: How to improve db performance with $7K?  ("Dave Held", )
  Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
  Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
   Re: How to improve db performance with $7K?  (Alex Turner, )
 Re: How to improve db performance with $7K?  ("Dave Held", )
  Re: How to improve db performance with $7K?  (Alex Turner, )
   Re: How to improve db performance with $7K?  (William Yu, )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
  Re: How to improve db performance with $7K?  (Bruce Momjian, )
   Re: How to improve db performance with $7K?  (, )
    Re: How to improve db performance with $7K?  ("Jim C. Nasby", )
     What to do with 6 disks?  (Jeff Frost, )
      Re: What to do with 6 disks?  ("Jim C. Nasby", )
      Re: What to do with 6 disks?  (Josh Berkus, )
       Re: Opteron vs Xeon (Was: What to do with 6 disks?)  (Jeff Frost, )
        Re: Opteron vs Xeon (Was: What to do with 6 disks?)  ("J. Andrew Rogers", )
         Re: Opteron vs Xeon (Was: What to do with 6 disks?)  (Jeff Frost, )
          Re: Opteron vs Xeon (Was: What to do with 6 disks?)  (William Yu, )
        Re: Opteron vs Xeon (Was: What to do with 6 disks?)  (Vivek Khera, )
       Re: What to do with 6 disks?  (Vivek Khera, )
      Re: What to do with 6 disks?  (William Yu, )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
  Re: How to improve db performance with $7K?  (Bruce Momjian, )
  Re: How to improve db performance with $7K?  (Dawid Kuroczko, )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )
 Re: How to improve db performance with $7K?  ("Dave Held", )
 Re: How to improve db performance with $7K?  ("Mohan, Ross", )

It's the same money if you factor in the 3ware controller. Even without
a caching controller, SCSI works good in multi-threaded IO (not
withstanding crappy shit from Dell or Compaq). You can get such cards
from LSI for $75. And of course, many server MBs come with LSI
controllers built-in. Our older 32-bit production servers all use Linux
software RAID w/ SCSI and there's no issues when multiple
users/processes hit the DB.

*Maybe* a 3ware controller w/ onboard cache + battery backup might do
much better for multi-threaded IO than just plain-jane SATA.
Unfortunately, I have not been able to find anything online that can
confirm or deny this. Hence, the choice is spend $$$ on the 3ware
controller and hope it meets your needs -- or spend $$$ on SCSI drives
and be sure.

Now if you want to run such tests, we'd all be delighted with to see the
results so we have another option for building servers.


Alex Turner wrote:
> It's hardly the same money, the drives are twice as much.
>
> It's all about the controller baby with any kind of dive.  A bad SCSI
> controller will give sucky performance too, believe me.  We had a
> Compaq Smart Array 5304, and it's performance was _very_ sub par.
>
> If someone has a simple benchmark test database to run, I would be
> happy to run it on our hardware here.
>
> Alex Turner
>
> On Apr 6, 2005 3:30 AM, William Yu <> wrote:
>
>>Alex Turner wrote:
>>
>>>I'm no drive expert, but it seems to me that our write performance is
>>>excellent.  I think what most are concerned about is OLTP where you
>>>are doing heavy write _and_ heavy read performance at the same time.
>>>
>>>Our system is mostly read during the day, but we do a full system
>>>update everynight that is all writes, and it's very fast compared to
>>>the smaller SCSI system we moved off of.  Nearly a 6x spead
>>>improvement, as fast as 900 rows/sec with a 48 byte record, one row
>>>per transaction.
>>
>>I've started with SATA in a multi-read/multi-write environment. While it
>>ran pretty good with 1 thread writing, the addition of a 2nd thread
>>(whether reading or writing) would cause exponential slowdowns.
>>
>>I suffered through this for a week and then switched to SCSI. Single
>>threaded performance was pretty similar but with the advanced command
>>queueing SCSI has, I was able to do multiple reads/writes simultaneously
>>with only a small performance hit for each thread.
>>
>>Perhaps having a SATA caching raid controller might help this situation.
>>I don't know. It's pretty hard justifying buying a $$$ 3ware controller
>>just to test it when you could spend the same money on SCSI and have a
>>guarantee it'll work good under multi-IO scenarios.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to )
>


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

От: Alex Turner
Дата:
Сообщение: Re: How to improve db performance with $7K?
От: Christopher Kings-Lynne
Дата:
Сообщение: Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this