mermet: zfs: limite ARC

[sourcephile-nix.git] / hosts / losurdo / postgresql.nix

diff --git a/hosts/losurdo/postgresql.nix b/hosts/losurdo/postgresql.nix
index eeae6da019e9966f68834ecba14f9c7084efa04d..f6c7d9e15f9030c907da5b2da89c281d0f88c013 100644 (file)
--- a/hosts/losurdo/postgresql.nix
+++ b/hosts/losurdo/postgresql.nix
@@ -1,4 +1,4 @@
-{ pkgs, config, hostName, ... }:
+{ pkgs, config, hostName, host, ... }:
 let
   inherit (config.networking) domain;
   inherit (config.services) postgresql;
@@ -37,20 +37,52 @@ in
     package = pkgs.postgresql_12;
     #enableTCPIP = true;
     settings = {
-      # ZFS is Copy on Write (CoW). As a result, it’s not possible
-      # to have a torn page because a page can’t be partially written
-      # without reverting to the previous copy.
-      full_page_writes = false;
       log_connections = true;
       log_disconnections = true;
       log_hostname = false;
-      max_connections = 25;
       max_locks_per_transaction = 1024;
       password_encryption = "scram-sha-256"; # requires postfix >= 11
       ssl = false;
       ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
       ssl_key_file = "/var/lib/acme/${domain}/key.pem";
       unix_socket_permissions = "0770";
+
+      # ZFS is Copy on Write (CoW). As a result, it’s not possible
+      # to have a torn page because a page can’t be partially written
+      # without reverting to the previous copy.
+      full_page_writes = false;
+      # Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
+      # The reasoning here is that since ZFS overwrites these anyway,
+      # you might as well get yourself a new inode.
+      wal_init_zero = false;
+      wal_recycle = false;
+
+      max_connections = 50;
+      # The proper size for the POSTGRESQL shared buffer cache is the largest
+      # useful size that does not adversely affect other activity.
+      # Ideally we should limit ZFS's ARC's max size
+      # to a percentage of physical memory
+      # minus the shared_buffer cache for PostgreSQL
+      # minus the kernel's memory overhead.
+      shared_buffers = "1GB";
+      # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
+      effective_cache_size = "3GB";
+      # Setting a large value helps in tasks like
+      # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
+      maintenance_work_mem = "256MB";
+      # Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
+      wal_buffers = "16MB";
+      min_wal_size = "1GB";
+      max_wal_size = "4GB";
+      default_statistics_target = 100;
+      random_page_cost = "1.1";
+      effective_io_concurrency = 200;
+      work_mem = "20971kB";
+      huge_pages = false;
+      max_worker_processes = host.CPUs;
+      max_parallel_workers_per_gather = host.CPUs / 2;
+      max_parallel_workers = host.CPUs;
+      max_parallel_maintenance_workers = host.CPUs / 2;
     };
     # If one record is chosen and the auth fails,
     # subsequent records are not considered.
@@ -69,12 +101,6 @@ in
       #user       /^(.*)$          \1
     '';
   };
-  fileSystems."/var/lib/postgresql" = {
-    device = "${hostName}/var/postgresql";
-    fsType = "zfs"; # with sync=always,
-    # though loading OpenConcerto's initial SQL
-    # takes 1m40s instead of 40s :\
-  };
   /*
     services.syncoid.commands = {
     "${hostName}/var/postgresql" = {
@@ -84,9 +110,11 @@ in
     };
   */
   services.sanoid.datasets = {
-    "${hostName}/var/postgresql" = {
+    "${hostName}/var/lib/postgresql" = {
       use_template = [ "snap" ];
       daily = 31;
+      hourly = 0;
+      recursive = true;
     };
   };
   systemd.services.postgresql = {
@@ -123,7 +151,7 @@ in
         local template=''${template:-template1}
         $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
           $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-            CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN PASSWORD '$pass'};
+            CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
             CREATE DATABASE "$db" WITH OWNER="$owner"
              ''${encoding:+ENCODING='$encoding'}
              ''${lc_collate:+LC_COLLATE='$lc_collate'}
@@ -146,7 +174,7 @@ in
         local user=$2
         $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
         $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
-          CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ''${pass:+LOGIN ENCRYPTED PASSWORD '$pass'};
+          CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
           GRANT USAGE ON SCHEMA public TO "$user";
           GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
       EOF