Re: Deleting bytea, autovacuum, and 8.2/8.4 differences

Поиск
Список
Период
Сортировка
От VJK
Тема Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Дата
Msg-id 600ad6df1003150828u5540ac3cq5bcf599dc0975469@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Deleting bytea, autovacuum, and 8.2/8.4 differences  (Greg Smith <greg@2ndquadrant.com>)
Список pgsql-performance
Inline:

On Mon, Mar 15, 2010 at 10:42 AM, Greg Smith <greg@2ndquadrant.com> wrote:
VJK wrote:
Since Pg does not use the concept of rollback segments,  it is unclear why deletion produces so much disk IO (4GB).

With PostgreSQL's write-ahead log, MVCC and related commit log, and transactional DDL features, there's actually even more overhead that can be involved than a simple rollback segment design when you delete things:

There does not appear to be much WAL activity.  Here's the insertion of 100 rows as seen by iotop:
   4.39 G  0.00 %  9.78 % postgres: writer process          
   5.34 G  0.00 %  5.93 % postgres: postgr~0.5.93(1212) idle
  27.84 M  0.00 %  1.77 % postgres: wal writer process      
 144.00 K  0.00 %  0.00 % postgres: stats collector process 
   0.00 B  0.00 %  0.00 % postgres: autova~ launcher process
   0.00 B  0.00 %  0.00 % postgres: postgr~0.5.93(4632) idle
                                                            


.. and the deletion:
288.18 M  0.00 % 37.80 % postgres: writer process          
  3.41 G  0.00 % 19.76 % postgres: postgr~0.5.93(1212) DELETE
 27.27 M  0.00 %  3.18 % postgres: wal writer process      
 72.00 K  0.00 %  0.03 % postgres: stats collector process 
  0.00 B  0.00 %  0.00 % postgres: autova~ launcher process
  0.00 B  0.00 %  0.00 % postgres: postgr~0.5.93(4632) idle

So, the original 1.9 GB of useful data generate about 10GB of IO, 5 of which end up being written to the disk  The deletion generates about 3.8 GB of IO all of which results in disk IO.  WAL activity is about 27MB in both cases.
 

http://www.postgresql.org/docs/current/static/wal.html
http://www.postgresql.org/docs/current/static/mvcc-intro.html
http://wiki.postgresql.org/wiki/Hint_Bits
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis


I read all of the above, but it does not really clarify why deletion generates so much IO.
 
One fun thing to try here is to increase shared_buffers and checkpoint_segments, then see if the total number of writes go down.  The defaults for both are really low, which makes buffer page writes that might otherwise get combined as local memory changes instead get pushed constantly to disk.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Следующее
От: Matthew Wakeling
Дата:
Сообщение: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences