Re: Massive table (500M rows) update nightmare

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




crank it up more and delay the checkpoints as much as possible during
these updates.  64 segments is already 1024M.

We have 425M rows, total table size is 78GB, so we can imagine a worst case UPDATE write is less than 200 bytes * number of rows specified in the update (is that logic correct?).

There is also the WAL : all these updates need to be logged, which doubles the UPDATE write throughput. Perhaps you're WAL-bound (every 16MB segment needs fsyncing), and tuning of fsync= and wal_buffers, or a faster WAL disk could help ? (I don't remember your config).

Inerestingly, the total index size is 148GB, twice that of the table, which may be an indication of where the performance bottleneck is.

Index updates can create random I/O (suppose you have a btree on a rather random column)...


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux