CPU utilization vs. IO wait, shared buffers?

Hi everybody,

I am running a bake/load test and I am seeing sudden, daily shifts
from CPU utilization to IO wait.  The load harness has been running
for 3 weeks and should be putting a uniform load on the application.
The application processes data on a daily basis and a sawtooth CPU
pattern on the database is expected as more values are added
throughout the day and processing resets with the next day.  Each day,
I see the CPU utilization climb as expected until a shift occurs and
it spends the rest of the day primarily in IO wait.

Looking at pg_statio_user_tables, I can see that during the CPU
intense timeframe, most of the results come from the buffer cache
(hits).  During the IO wait, most of the results are being read in
(misses).  Examples from each timeframe (CPU/IO) are included below.
For each sample, I issued pg_stat_reset(), waited briefly, and then
queried pg_statio_user_tables.

*during CPU Intense timeframe*
db=# select * from pg_statio_user_tables;
 relid | schemaname |              relname              |
heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit |
toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit

-------+------------+-----------------------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16612 | public     | tablea                            |
1 |       1346782 |             1 |        55956 |               0 |
           0 |              0 |             0
 16619 | public     | tableb                            |
0 |           579 |             0 |         1158 |                 |
             |                |

*during IO WAIT timeframe*
db=# select * from pg_statio_user_tables;
 relid | schemaname |              relname              |
heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit |
toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit

-------+------------+-----------------------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 16612 | public     | tablea                            |
244146 |           594 |          4885 |         3703 |
0 |              0 |              0 |             0
 16619 | public     | tableb                            |
418 |           589 |           432 |         1613 |                 |
               |                |



Another thing to note, we have VACUUM ANALYZE running on an hourly
interval and the switch from CPU to IO wait appears to always coincide
with a vacuum.

What might cause this shift?

I have tried adjusting buffer_cache from 512 MB to 1024 MB, but this
did not appear to have an impact.

I also tried upping the work_mem from 1MB to 10MB, and this did not
appear to have an impact either.

Any ideas?  Thanks for your help!

Oliver


We're running Postgresql 8.2.9