Detecting I/O bottlenecks

The track_io_timing command can give you clues about the database that is causing most of the load. However, there is more; pg_stat_bgwriter contains a lot of information about the system's I/O behavior:

test=# d pg_stat_bgwriter  
              View "pg_catalog.pg_stat_bgwriter"
        Column         |           Type           |
-----------------------+--------------------------+
 checkpoints_timed     | bigint                   | 
 checkpoints_req       | bigint                   | 
 checkpoint_write_time | double precision         | 
 checkpoint_sync_time  | double precision         | 
 buffers_checkpoint    | bigint                   | 
 buffers_clean         | bigint                   | 
 maxwritten_clean      | bigint                   | 
 buffers_backend       | bigint                   | 
 buffers_backend_fsync | bigint                   | 
 buffers_alloc         | bigint                   | 
 stats_reset           | timestamp with time zone | 

Understanding what is going on in the I/O system can be vital. This understanding also includes checkpoints. In PostgreSQL, there are two reasons for a checkpoint: either the system has run out of WAL segments (a requested checkpoint) or the system has exceeded checkpoint_timeout (a timed checkpoint). The pg_stat_bgwriter table will tell you what has caused a checkpoint to take place. If all your checkpoints are timed, it means that there is no point in increasing checkpoint_segments. If all your checkpoints are requested, raising checkpoint_segments and checkpoint_timeout might be a good idea to solve a potential performance bottleneck.

Then there are checkpoint_write_time and checkpoint_sync_time. The checkpoint_write_time field is all about the total amount of time that has been spent in the portion of checkpoint processing where files are written to the disk. The checkpoint_sync_time field represents the amount of time spent on flushing to the disk during a checkpoint.

The buffers_checkpoint field tells people about the number of blocks written during a checkpoint. The buffers_clean field tells people how many blocks are written by the background writer.

When the background writer writes blocks from the shared buffers area to the operating system, it does so in rounds. When it stops because it has already written too many blocks per round, we can see that in maxwritten_clean. If the background writer cannot keep up with the write load, backends (database connections) are also allowed to perform I/O. The number of blocks written by backend is counted in buffers_backend. Sometimes, a backend even has to call fsync to flush to the disk. Those flush calls are counted in buffers_backend_fsync. Finally, there is buffers_alloc, which tells us about the number of buffers allocated.

The key to use pg_stat_bgwriter is to figure out which component is doing I/O. In general, the bgwriter is supposed to do most of the I/O itself. However, under heavy load, it might be necessary for normal database connections to do some of the work on their own. In PostgreSQL 9.2, a checkpointer process has been added to take away some work from the regular background writer process. Both checkpointer and writer processes write to the same files. However, the checkpointer writes all of the data that was dirty at a certain time (the start of the checkpoint), regardless of how often it was used since dirtied. On the other hand, the background writer writes data that hasn't been used recently, regardless of when it was first dirtied. Neither of the two knows or cares whether the data being written was committed or rolled back or is still in progress.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.255.87