What exactly is postgres doing during INSERT/UPDATE ?

От: Joseph S
Тема: What exactly is postgres doing during INSERT/UPDATE ?
Дата: ,
Msg-id: h77v2l$bkf$1@news.hub.org
(см: обсуждение, исходный текст)
Ответы: Re: What exactly is postgres doing during INSERT/UPDATE ?  (Pierre Frédéric Caillaud<>)
Re: What exactly is postgres doing during INSERT/UPDATE ?  (Greg Smith)
Список: pgsql-performance

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

What exactly is postgres doing during INSERT/UPDATE ?  (Joseph S, )
 Re: What exactly is postgres doing during INSERT/UPDATE ?  (Pierre Frédéric Caillaud<>, )
 Re: What exactly is postgres doing during INSERT/UPDATE ?  (Greg Smith, )
  Re: What exactly is postgres doing during INSERT/UPDATE ?  (Scott Marlowe, )
   Re: What exactly is postgres doing during INSERT/UPDATE ?  (Joseph S, )
    Re: What exactly is postgres doing during INSERT/UPDATE ?  (Luke Koops, )
     Re: What exactly is postgres doing during INSERT/UPDATE ?  (Greg Stark, )
      Re: What exactly is postgres doing during INSERT/UPDATE ?  (Scott Marlowe, )
       Re: What exactly is postgres doing during INSERT/UPDATE ?  (Merlin Moncure, )
        Re: What exactly is postgres doing during INSERT/UPDATE ?  (Greg Stark, )
        Re: What exactly is postgres doing during INSERT/UPDATE ?  (Mark Mielke, )
         Re: What exactly is postgres doing during INSERT/UPDATE ?  (Merlin Moncure, )
          Re: What exactly is postgres doing during INSERT/UPDATE ?  (Greg Stark, )
           Re: What exactly is postgres doing during INSERT/UPDATE ?  (Merlin Moncure, )
            Re: What exactly is postgres doing during INSERT/UPDATE ?  (Aidan Van Dyk, )
       Re: What exactly is postgres doing during INSERT/UPDATE ?  (Joseph S, )
        Re: What exactly is postgres doing during INSERT/UPDATE ?  (Jeff Janes, )
         Re: What exactly is postgres doing during INSERT/UPDATE ?  ("Kevin Grittner", )
          Re: What exactly is postgres doing during INSERT/UPDATE ?  (Scott Marlowe, )
      Re: What exactly is postgres doing during INSERT/UPDATE ?  (David Rees, )
  Re: What exactly is postgres doing during INSERT/UPDATE ?  (Joseph S, )
 Re: What exactly is postgres doing during INSERT/UPDATE ?  (Jeff Janes, )
  Re: What exactly is postgres doing during INSERT/UPDATE ?  (Merlin Moncure, )
   Re: What exactly is postgres doing during INSERT/UPDATE ?  (Jeff Janes, )

If I run " dd if=/dev/zero bs=1024k of=file count=1000 " iostat shows me:

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             671.50        88.00    113496.00        176     226992


However postgres 8.3.7 doing a bulk data write (a slony slave, doing
inserts and updates) doesn't go nearly as fast:

  Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             418.41       648.76      7052.74       1304      14176

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             237.50        44.00      3668.00         88       7336

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             221.50       444.00      3832.00        888       7664

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             166.00       248.00      3360.00        496       6720

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             163.00       480.00      3184.00        960       6368

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             102.50       724.00      1736.00       1448       3472

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             295.50       712.00      6004.00       1424      12008

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             109.45       433.83      2260.70        872       4544

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda             180.00       640.00      3512.00       1280       7024

top shows the cpu usage of the pg process ranges from zero to never more
than ten percent of a cpu, and that one cpu is always ninety some odd
percent in iowait.  So what is postgres doing (with fsync off) that
causes the cpu to spend so much time in iowait?

This is a 64 bit amd linux system with ext3 filesystem. free shows:

              total       used       free     shared    buffers     cached
Mem:       8116992    8085848      31144          0     103016    3098568
-/+ buffers/cache:    4884264    3232728
Swap:      6697296    2035508    4661788


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

От: Scott Marlowe
Дата:
Сообщение: Re: What exactly is postgres doing during INSERT/UPDATE ?
От: Joseph S
Дата:
Сообщение: Re: What exactly is postgres doing during INSERT/UPDATE ?