wal_level=archive gives better performance than minimal - why?

Поиск
Список
Период
Сортировка
От Tomas Vondra
Тема wal_level=archive gives better performance than minimal - why?
Дата
Msg-id 4F0F6A02.3000403@fuzzy.cz
обсуждение исходный текст
Ответы Re: wal_level=archive gives better performance than minimal - why?  (Greg Smith <greg@2ndQuadrant.com>)
Список pgsql-performance
Hi all,

I've run a series fo pgbench benchmarks with the aim to see the effect
of moving the WAL logs to a separate drive, and one thing that really
surprised me is that the archive log level seems to give much better
performance than minimal log level.

On spinning drives this is not noticeable, but on SSDs it's quite clear.
See for example this:

  http://www.fuzzy.cz/tmp/tps-rw-minimal.png
  http://www.fuzzy.cz/tmp/tps-rw-archive.png

That minimal log level gives about 1600 tps all the time, while archive
log level gives about the same performance at the start but then it
continuously increases up to about 2000 tps.

This seems very suspicious, because AFAIK the wal level should not
really matter for pgbench and if it does I'd expect exactly the opposite
behaviour (i.e. 'archive' performing worse than 'minimal').

This was run on 9.1.2 with two SSDs (Intel 320) and EXT4, but I do see
exactly the same behaviour with a single SSD drive.

The config files are here (the only difference is the wal_level line at
the very end)

  http://www.fuzzy.cz/tmp/postgresql-minimal.conf
  http://www.fuzzy.cz/tmp/postgresql-archive.conf

pgbench results and logs are here:

  http://www.fuzzy.cz/tmp/pgbench.minimal.log.gz
  http://www.fuzzy.cz/tmp/pgbench.archive.log.gz

  http://www.fuzzy.cz/tmp/results.minimal.log
  http://www.fuzzy.cz/tmp/results.archive.log

I do plan to rerun the whole benchmark, but is there any reasonable
explanation or something that might cause such behaviour?

kind regards
Tomas

В списке pgsql-performance по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Partitioning by status?
Следующее
От: alexandre - aldeia digital
Дата:
Сообщение: Re: Partitioning by status?