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

Список
Период
Сортировка
От Kevin Brown
Тема Re: How to improve db performance with $7K?
Дата
Msg-id 20050414083608.GC19518@filer
обсуждение исходный текст
Ответ на Re: How to improve db performance with $7K?  (Greg Stark)
Ответы Re: How to improve db performance with $7K?  (Tom Lane)
Re: How to improve db performance with $7K?  (Rosser Schwarz)
Re: How to improve db performance with $7K?  (Greg Stark)
Re: How to improve db performance with $7K?  (Bruce Momjian)
Список 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", )
Greg Stark wrote:


> I think you're being misled by analyzing the write case.
>
> Consider the read case. When a user process requests a block and
> that read makes its way down to the driver level, the driver can't
> just put it aside and wait until it's convenient. It has to go ahead
> and issue the read right away.

Well, strictly speaking it doesn't *have* to.  It could delay for a
couple of milliseconds to see if other requests come in, and then
issue the read if none do.  If there are already other requests being
fulfilled, then it'll schedule the request in question just like the
rest.

> In the 10ms or so that it takes to seek to perform that read
> *nothing* gets done. If the driver receives more read or write
> requests it just has to sit on them and wait. 10ms is a lifetime for
> a computer. In that time dozens of other processes could have been
> scheduled and issued reads of their own.

This is true, but now you're talking about a situation where the
system goes from an essentially idle state to one of furious
activity.  In other words, it's a corner case that I strongly suspect
isn't typical in situations where SCSI has historically made a big
difference.

Once the first request has been fulfilled, the driver can now schedule
the rest of the queued-up requests in disk-layout order.


I really don't see how this is any different between a system that has
tagged queueing to the disks and one that doesn't.  The only
difference is where the queueing happens.  In the case of SCSI, the
queueing happens on the disks (or at least on the controller).  In the
case of SATA, the queueing happens in the kernel.

I suppose the tagged queueing setup could begin the head movement and,
if another request comes in that requests a block on a cylinder
between where the head currently is and where it's going, go ahead and
read the block in question.  But is that *really* what happens in a
tagged queueing system?  It's the only major advantage I can see it
having.


> The same thing would happen if you had lots of processes issuing
> lots of small fsynced writes all over the place. Postgres doesn't
> really do that though. It sort of does with the WAL logs, but that
> shouldn't cause a lot of seeking.  Perhaps it would mean that having
> your WAL share a spindle with other parts of the OS would have a
> bigger penalty on IDE drives than on SCSI drives though?

Perhaps.

But I rather doubt that has to be a huge penalty, if any.  When a
process issues an fsync (or even a sync), the kernel doesn't *have* to
drop everything it's doing and get to work on it immediately.  It
could easily gather a few more requests, bundle them up, and then
issue them.  If there's a lot of disk activity, it's probably smart to
do just that.  All fsync and sync require is that the caller block
until the data hits the disk (from the point of view of the kernel).
The specification doesn't require that the kernel act on the calls
immediately or write only the blocks referred to by the call in
question.


--
Kevin Brown                          

В списке pgsql-performance по дате отправления:

Предыдущее
От: Christopher Kings-Lynne
Дата:
Сообщение: Re: Foreign key slows down copy/insert
Следующее
От: Tom Lane
Дата:
Сообщение: Re: How to improve db performance with $7K?