1

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
Improve this question
7
  • Trying to use ZFS on EC2 is a bit unusual. What's going on there? Commented Jul 19, 2016 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. Commented Jul 19, 2016 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? Commented Jul 20, 2016 at 8: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... Commented Jul 20, 2016 at 8:02
  • What is in your zfs.conf file? Commented Jul 20, 2016 at 11:37

1 Answer 1

Reset to default
4

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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.