0

I am using Ubuntu Xenial, zfs and PostgreSQL 9.5 with all updates available atm. All testing is done on EC2 m4.xlarge (8GB RAM) and table size ~4GB. PostgreSQL effective_cache_size=6GB.

First scenarion: primarycache=metadata, arc_max_size=1GB (tested 4 and 6GB too), arc_size=~160mb, but Ubuntu does not use buff/cache for sequential scans (free -m says buff/cache=200MB and does not grow) and instead reads data from the disk every time query is executed. So queries are very slow.

Second scenario: primarycache=all, arc_max_size=4GB. Sequential reads are cached, but cache becomes invalidated (I see that Postgres reads data from the disk) whenever database is changed (Postgres receives updates from the master).

What I am doing wrong?

PS

cat /etc/modprobe.d/zfs.conf
options zfs zfs_prefetch_disable=1
options zfs zfs_nocacheflush=1
options zfs zfs_arc_max=7073741824
Vladimir
  • 145
  • 10
  • Trying to use ZFS on EC2 is a bit unusual. What's going on there? – Michael Hampton Jul 19 '16 at 19:20
  • I would allocate little space to ZFS cache and allocate most of the memory to PostgreSQL cache. PostgreSQL knows much better what to do with the memory than ZFS. – Tero Kilkanen Jul 19 '16 at 20:49
  • @MichaelHampton I want to compress data in database and ZFS looks like the easiest/only option. Why ZFS on EC2 is a bad choice? – Vladimir Jul 20 '16 at 08:01
  • @TeroKilkanen AFAIK that is the 1st scenario I described. The problem is that in this case Linux/UbuntuXenial does not uses buffer cache for disk IO at all... – Vladimir Jul 20 '16 at 08:02
  • What is in your zfs.conf file? – ewwhite Jul 20 '16 at 11:37
  • @ewwhite cat /etc/modprobe.d/zfs.conf options zfs zfs_prefetch_disable=1 options zfs zfs_nocacheflush=1 options zfs zfs_arc_max=7073741824 – Vladimir Jul 20 '16 at 11:57
  • Something is wrong with you PostgreSQL setup, it should use its own buffer cache to store data so that there is no disk I/O with sequential scans. – Tero Kilkanen Jul 20 '16 at 15:14

1 Answers1

3

What I've learned on zfs-discuss and during my experiments:

  • zfs_prefetch_disable=1 - significantly degrades performance for seq scans - don't disable it
  • zfs_nocacheflush=1 - leads to data corruption - don't enable it

ZFS config that works for me and is even faster than ext4 for seq reads:

  • recordsize=8K pg_data_95
  • recordsize=8K pg_wal_95
  • atime=off pg_data_95
  • atime=off pg_wal_95
  • relatime=on pg_data_95
  • relatime=on pg_wal_95
  • logbias=throughput pg_data_95
  • compression=lz4 pg_data_95
  • compression=lz4 pg_wal_95
  • xattr=sa pg_data_95
  • xattr=sa pg_wal_95
  • primarycache=metadata pg_wal_95

I also set ashift=9 to get better compression, but it is default on EC2 EBS anyway.

Vladimir
  • 145
  • 10