4 ms·

I'd love to hear more about your tuning around both PG and ZFS. I assume pg_xlog is on its own fs with 8K records, but beyond that, anything interesting, or any

I'd love to hear more about your tuning around both PG and ZFS. I assume pg_xlog is on its own fs with 8K records, but beyond that, anything interesting, or any guides that you found especially useful?

olavgg 9y ago
pg_xlog has 8k records with lz4 compression. You can disable full-page writes, though I've never had any issues with it on either. For tablespaces the recordsize can be anything from 8k to dynamic / 128k. They all have pro/cons. Greater record size improve compression and sequential read performance but small random updates becomes a lot more expensive. I feel a recordsize of 16k or 32k with lz4 compression is a safe setting for optimal performance with good compression. Other useful topics related to performance are: Use multi-column/functional/partial indexes, as this will help you design a database where most of your 'hot' indexes can fit in RAM. If you have too many indexes, you will do index scan on disk, which is painfully slow. I many cases a full table scan (sequential read) would be faster instead. BRIN indexes are great for large tables that can be sorted on for example on timestamp. Common Table Expressions, for recursive queries. The query planner sometimes needs help as it will start doing full table scan on a table when it could just have used an index. This is related to table statistics which you need to alter. You can read more about this here: https://www.postgresql.org/docs/9.5/static/planner-stats.html https://www.postgresql.org/docs/9.5/static/planner-stats.htm... Adjusting the seq_page_cost and random_page_cost can also help the query plannner. There is probably a lot more, but you learn as you go :)
- aeorgnoieang 9y ago
  Unrelated, but the link in your profile to your site Boost AI is HTTPs but the site itself only seems to support HTTP.
  - olavgg 9y ago
    Thanks!
- aeorgnoieang 9y ago
  No ZFS-specific tuning?
  - olavgg 9y ago
    There is no need, default settings works great. We run PostgreSQL on FreeBSD though.

olavgg 9y ago

pg_xlog has 8k records with lz4 compression. You can disable full-page writes, though I've never had any issues with it on either. For tablespaces the recordsize can be anything from 8k to dynamic / 128k. They all have pro/cons. Greater record size improve compression and sequential read performance but small random updates becomes a lot more expensive. I feel a recordsize of 16k or 32k with lz4 compression is a safe setting for optimal performance with good compression. Other useful topics related to performance are: Use multi-column/functional/partial indexes, as this will help you design a database where most of your 'hot' indexes can fit in RAM. If you have too many indexes, you will do index scan on disk, which is painfully slow. I many cases a full table scan (sequential read) would be faster instead. BRIN indexes are great for large tables that can be sorted on for example on timestamp. Common Table Expressions, for recursive queries. The query planner sometimes needs help as it will start doing full table scan on a table when it could just have used an index. This is related to table statistics which you need to alter. You can read more about this here: https://www.postgresql.org/docs/9.5/static/planner-stats.html https://www.postgresql.org/docs/9.5/static/planner-stats.htm... Adjusting the seq_page_cost and random_page_cost can also help the query plannner. There is probably a lot more, but you learn as you go :)

aeorgnoieang 9y ago
Unrelated, but the link in your profile to your site Boost AI is HTTPs but the site itself only seems to support HTTP.
- olavgg 9y ago
  Thanks!
aeorgnoieang 9y ago
No ZFS-specific tuning?
- olavgg 9y ago
  There is no need, default settings works great. We run PostgreSQL on FreeBSD though.