Postgres Professional
en

Re: Sustained inserts per sec ... ?

От: Tom Lane
Тема: Re: Sustained inserts per sec ... ?
Дата: ,
Msg-id: 5217.1112668589@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Sustained inserts per sec ... ?  ("Marc G. Fournier")
Ответы: Re: Sustained inserts per sec ... ?  (Christopher Petrilli)
Re: Sustained inserts per sec ... ?  (Greg Stark)
Re: Sustained inserts per sec ... ?  (Simon Riggs)
Список: pgsql-performance

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

Sustained inserts per sec ... ?  ("Marc G. Fournier", )
 Re: Sustained inserts per sec ... ?  (Mike Nolan, )
  Re: Sustained inserts per sec ... ?  (Tom Lane, )
   Re: Sustained inserts per sec ... ?  (Mike Nolan, )
 Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
  Re: Sustained inserts per sec ... ?  (Tom Lane, )
   Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
    Re: Sustained inserts per sec ... ?  ("Joshua D. Drake", )
     Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
      Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
       Re: Sustained inserts per sec ... ?  (Tom Lane, )
        Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
         Re: Sustained inserts per sec ... ?  (Tom Lane, )
          Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
       Re: Sustained inserts per sec ... ?  (Simon Riggs, )
        Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
         Re: Sustained inserts per sec ... ?  (Simon Riggs, )
          Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
           Re: Sustained inserts per sec ... ?  (PFC, )
            Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
           Re: Sustained inserts per sec ... ?  (Simon Riggs, )
            Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
             Re: Sustained inserts per sec ... ?  (Simon Riggs, )
              Re: Sustained inserts per sec ... ?  (Mike Rylander, )
              Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
    Re: Sustained inserts per sec ... ?  (Tom Lane, )
 Re: Sustained inserts per sec ... ?  (Alex Turner, )
  Re: Sustained inserts per sec ... ?  (Alex Turner, )
   Re: Sustained inserts per sec ... ?  (Tom Lane, )
    Re: Sustained inserts per sec ... ?  (Alex Turner, )
 Re: Sustained inserts per sec ... ?  (Tom Lane, )
  Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
   Re: Sustained inserts per sec ... ?  (Tom Lane, )
  Re: Sustained inserts per sec ... ?  (Greg Stark, )
   Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
   Re: Sustained inserts per sec ... ?  (Tom Lane, )
    Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
     Re: Sustained inserts per sec ... ?  ("Jim C. Nasby", )
     Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
  Re: Sustained inserts per sec ... ?  (Simon Riggs, )
   Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
    Re: Sustained inserts per sec ... ?  (Simon Riggs, )
   Re: Sustained inserts per sec ... ?  (Christopher Petrilli, )
    Re: Sustained inserts per sec ... ?  (Simon Riggs, ) 
Christopher Petrilli <> writes:
> On Apr 4, 2005 12:23 PM, Tom Lane <> wrote:
>> do a test run with *no* indexes on the table, just to see if it behaves
>> any differently?  Basically I was wondering if index overhead might be
>> part of the problem.

> http://www.amber.org/~petrilli/diagrams/pgsql_copy500_pkonly.png

> I appologize, I forgot to kill the PK, but as you can see, the curve
> flattened out a lot.  It still begins to increase in what seems like
> the same place.  You can find the results themselves at:

Yeah, this confirms the thought that the indexes are the source of
the issue.  (Which is what I'd expect, because a bare INSERT ought to be
an approximately constant-time operation.  But it's good to verify.)

Now some amount of slowdown is to be expected as the indexes get larger,
since it ought to take roughly O(log N) time to insert a new entry in an
index of size N.  The weird thing about your curves is the very sudden
jump in the insert times.

What I think might be happening is that the "working set" of pages
touched during index inserts is gradually growing, and at some point it
exceeds shared_buffers, and at that point performance goes in the toilet
because we are suddenly doing lots of reads to pull in index pages that
fell out of the shared buffer area.

It would be interesting to watch the output of iostat or vmstat during
this test run.  If I'm correct about this, the I/O load should be
basically all writes during the initial part of the test, and then
suddenly develop a significant and increasing fraction of reads at the
point where the slowdown occurs.

The indicated fix of course is to increase shared_buffers.

            regards, tom lane

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

Предыдущее:
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Sustained inserts per sec ... ?
Следующее:
От: Christopher Petrilli
Дата:
Сообщение: Re: Sustained inserts per sec ... ?