Weird disk write load caused by PostgreSQL?
От | Alexander Staubo |
---|---|
Тема | Weird disk write load caused by PostgreSQL? |
Дата | |
Msg-id | 2812E5A7-7A65-4781-91CF-CD4D3875710B@purefiction.net обсуждение исходный текст |
Ответы |
Re: Weird disk write load caused by PostgreSQL?
|
Список | pgsql-general |
I have a production PostgreSQL instance (8.1 on Linux 2.6.15) that seems to be writing data to disk at rates that I think are disproportional to the update load imposed on the database. I am looking for ways to determine the cause of this I/O. As an example, here is a typical graph produced by Munin: http://purefiction.net/paste/pg-iostat.png Running an hourly iostat produces this output: Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn sda 43.50 0.21 0.04 427915 72736 sda 43.62 0.21 0.04 428183 88904 sda 43.74 0.21 0.05 428440 104877 sda 43.90 0.21 0.06 428808 124681 sda 44.06 0.21 0.07 429111 145447 sda 44.27 0.21 0.08 429532 170317 sda 44.46 0.21 0.09 429985 193594 In other words, it's reading about 400MB/hour and writing around 15-20GB/hour, or exactly 118GB during the last six hours. To determine how well this correlates to the actual inserts and updates being performed on the database, I ran a loop alongside iostat that executed "select sum(n_tup_upd), sum(n_tup_ins) from pg_stat_all_tables" against PostgreSQL every hour and output the difference. Here are a few samples: | delta_upd | delta_ins | +-----------+-----------+ | 7111 | 2343 | | 7956 | 2302 | | 7876 | 2181 | | 9269 | 2477 | | 8553 | 2205 | For the write numbers to match the tuple numbers, each updated/ inserted tuple would have to average at least 1.5MB (15 GB divided by 10,000 tuples), which is not the case; the total size of the raw tuples updated/inserted during the above session probably does not exceed a couple of megabytes. Even considering overhead, page size, MVCC, etc., this does not compute. I have not narrowed this explicitly down to PostgreSQL, but since the stock Linux kernel we are running does not offer per-process I/O statistics, I cannot determine this for sure. However, except for the PostgreSQL database, everything else on the box should be identical to what we are running on other boxes, which are not exhibiting the same kind of load. Oh, and I have PostgreSQL logging turned off. Note that PostgreSQL's performance in itself seems fine, and according to top/ps it's only very rarely in iowait. Alexander.
В списке pgsql-general по дате отправления: