Update performance degrades over time

["Subbiah Stalin-XCGF84" <SSubbiah@xxxxxxxxxxxx>]

Hi All,

 

We are doing some load tests with our application running postgres 8.2.4. At times we see updates on a table taking longer (around 11-16secs) than expected sub-second response time. The table in question is getting updated constantly through the load tests. In checking the table size including indexes, they seem to be bloated got it confirmed after recreating it (stats below). We have autovacuum enabled with default parameters. I thought autovaccum would avoid bloating issues but looks like its not aggressive enough. Wondering if table/index bloating is causing update slowness in over a period of time. Any ideas how to troubleshoot this further.

 

No IO waits seen during load tests and cpu usage on the server seem to be 85% idle. This is a v445 sol10 with 4 cpu box attached to SAN storage.

 

Here is the update statement and table/index/instance stats.

 

shared_buffers=4000MB

max_fsm_pages = 2048000

maintenance_work_mem = 512MB

checkpoint_segments = 128

effective_cache_size = 4000MB

 

update tablexy set col2=$1,col9=$2, col10=$3,col11=$4,col3=$5 WHERE ID=$6;

 

Bloated

            relname            | relowner | relpages | reltuples
 ------------------------------+----------+----------+-----------
  tablexy                      |       10 |   207423 |    502627
  ix_tablexy_col1_col2         |       10 |    38043 |    502627
  ix_tablexy_col3              |       10 |    13944 |    502627
  ix_tablexy_col4              |       10 |    17841 |    502627
  ix_tablexy_col5              |       10 |    19669 |    502627
  ix_tablexy_col6              |       10 |     3865 |    502627
  ix_tablexy_col7              |       10 |    12359 |    502627
  ix_tablexy_col8_col7         |       10 |    26965 |    502627
  ct_tablexy_id_u1             |       10 |     6090 |    502627

 

Recreating tablexy (compact),

 

           relname            | relowner | relpages | reltuples
------------------------------+----------+----------+-----------
 tablexy                      |       10 |    41777 |    501233
 ix_tablexy_col3              |       10 |     2137 |    501233
 ix_tablexy_col8_col7         |       10 |     4157 |    501233
 ix_tablexy_col6              |       10 |     1932 |    501233
 ix_tablexy_col7              |       10 |     1935 |    501233
 ix_tablexy_col1_col2         |       10 |     1933 |    501233
 ix_tablexy_col5              |       10 |     2415 |    501233
 ix_tablexy_col6              |       10 |     1377 |    501233
 ct_tablexy_id_u1             |       10 |     3046 |    501233

 

Thanks,

Stalin