On 28.4.2014 07:50, Elanchezhiyan Elango wrote:
>
> So how much data in total are we talking about?
> OK, so there are multiple tables, and you're updating 50k rows in all
> tables in total?
>
> Every 5 minutes: 50K rows are updated in 4 tables. 2K rows are updated
> in 39 tables.
> Every 1 hour (on top of the hour): 50K rows are updated in 8 tables. 2K
> rows are updated in 78 tables.
> If every update will take up space equivalent to 1 row, then there are
> 278K rows updated across all tables every 5 minutes. And 556K (278 * 2)
> rows updated across all tables every 1 hour. All tables follow the same
> schema except some tables don't have the 'port' field. And the data[]
> column on each row could have maximum 48 values.
I wasn't really asking about the amount of updates (that's reasonably
well seen in the checkpoint logs), but about the size of the database.
> Can you post \dt+ and \di+ so that we get an idea of table/index sizes?
>
> \dt+: http://pastebin.com/Dvg2vSeb
> \di+: http://pastebin.com/586MGn0U
According to the output, it seems you're dealing with ~20GB of data and
~30GB of indexes. Is that about right?
> Thanks for your input on ext3 filesystem and having WAL on a
> different disk. I'll see if these can be changed. I cannot change
> these in the short term.
What kernel version is this, actually?
Also, have you done some basic performance tests, to see how the disk
array behaves? I mean something like
dd if=/dev/zero of=/mnt/raid/test.file bs=1M count=16000
dd if=/mnt/raid/test.file of=/dev/null bs=1M count=16000
to test sequential performance, pgbench to test something more random
etc. Because trying to solve this from the "it's checkpoint issue" when
in reality it might be something completely different.
Also, are you sure there's no other source of significant I/O activity?
Try to run iotop to watch what's happening there.
regards
Tomas