Кеш бывает разным02.02.2021 12:17

include = 'shared_buffers.conf'
# при max_parallel_workers_per_gather>6 ошибка выполнения при размере кеша PostgreSQL 128 Кб
max_parallel_workers_per_gather=6

shared_buffers=128kB

-- Размер таблицы, которую нужно создать. Подбирается методом проб и ошибок так, чтобы таблица имела размер, равный ОЗУ.
\set table_size  75000000
-- Создаю таблицу с именем random, там лежат случайные данные. :)
drop table if exists random;
create table random(random real, data float[]);
insert into random select random,array_fill(random,ARRAY[20]) from (select random() as random, generate_series(1,:table_size)) as subselect;
-- И индекс к ней.
create index on random(random);
-- Дальнейшие операции требуют роль суперпользователя, мой пользователь имеет эту роль, поэтому переключиться несложно.
set role postgres;
-- Модуль для функции pg_prewarm
create extension if not exists pg_prewarm;
-- Модуль, в котором можно наблюдать структуру кеша PostgreSQL
create extension if not exists pg_buffercache;
-- View на базе предыдущего модуля для наблюдением за кешем. Для эксперимента не используется, и во время эксперимента лучше не использовать, т.к. pg_buffercache вел себя нестабильно и иногда рушил процесс PostgreSQL.
create or replace view cache as SELECT n.nspname AS schema,
    c.relname,
    pg_size_pretty(count(*) * 8192) AS buffered,
    count(*) * 8 AS buffered_KiB,
    round(100.0 * count(*)::numeric / ((( SELECT pg_settings.setting
           FROM pg_settings
          WHERE pg_settings.name = 'shared_buffers'::text))::integer)::numeric, 1) AS buffer_percent,
    round(100.0 * count(*)::numeric * 8192::numeric / pg_table_size(c.oid::regclass)::numeric, 1) AS percent_of_relation
   FROM pg_class c
     JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
     JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database()
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  GROUP BY c.oid, n.nspname, c.relname
  ORDER BY (round(100.0 * count(*)::numeric / ((( SELECT pg_settings.setting
           FROM pg_settings
          WHERE pg_settings.name = 'shared_buffers'::text))::integer)::numeric, 1)) DESC
 LIMIT 5;
-- View, в котором можно посмотреть размер таблицы на диске, использовался для подгона размера таблицы под ОЗУ.
create or replace view disk as SELECT n.nspname AS schema,
    c.relname,
    pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size,
    pg_relation_size(c.oid::regclass)/1024 AS size_KiB
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  ORDER BY (pg_relation_size(c.oid::regclass)) DESC
 LIMIT 5;
vacuum full freeze analyze;

select random from random where random=(select random()::real);

select count(data) from random;

# database connection parameters
export PGDATABASE='pgcache'
# Добавляю путь к бинарникам PostgreSQL, это было нужно на CentOS и, возможно, других редхатоподобных дистрибутивах.
PATH=$PATH:/usr/pgsql-13/bin
# Через пробел указаны значения для конфигурационной переменной PostgreSQL shared_buffers, т.е. собственно размеры кеша на которых будет производится тестирование. Порядок по уменьшению величин важен. Дело в том, что HugePages уменьшить можно всегда, а вот увеличить — не всегда, видимо, из-за фрагментации памяти. Поэтому тестирование ведется при уменьшении размера кеша.
readonly shared_buffers='14GB 13GB 12GB 11GB 10GB 9GB 8GB 7GB 6GB 5GB 4GB 3GB 2GB 1GB 512MB 256MB 128MB 64MB 32MB 16MB 8MB 4MB 2MB 1MB 512kB 256kB 128kB';

#!/bin/bash
. config.sh
readonly postgresql_service='postgresql-13' postgresql_pid='/var/lib/pgsql/13/data/postmaster.pid'
readonly huge_pages_sh='huge_pages.sh' shared_buffers_conf='shared_buffers.conf'
sudo systemctl start "$postgresql_service"
echo 'declare -r -A huge_pages_size=( \' >"$huge_pages_sh"
for s_b in $shared_buffers
do
   echo "shared_buffers=$s_b" >"$shared_buffers_conf"
   sleep 5
   sudo systemctl restart "$postgresql_service"
   pid=$(sudo head -1 "$postgresql_pid")
   echo -n '   [' >>"$huge_pages_sh"
   psql --expanded --quiet --tuples-only -c "show shared_buffers" | awk '/^shared_buffers \|/{printf "%s", $3}' >>"$huge_pages_sh"
   echo -n ']=' >>"$huge_pages_sh"
   sudo awk '/^VmPeak:/ {printf "%i", $2/2048+1}' /proc/"$pid"/status >>"$huge_pages_sh"
   echo ' \' >>"$huge_pages_sh"
done
echo ')' >>"$huge_pages_sh"
sudo systemctl stop "$postgresql_service"

declare -r -A huge_pages_size=( \
   [14GB]=7416 \
   [13GB]=6893 \
   [12GB]=6370 \
   [11GB]=5847 \
   [10GB]=5323 \
   [9GB]=4800 \
   [8GB]=4277 \
   [7GB]=3750 \
   [6GB]=3226 \
   [5GB]=2703 \
   [4GB]=2180 \
   [3GB]=1655 \
   [2GB]=1131 \
   [1GB]=607 \
   [512MB]=345 \
   [256MB]=209 \
   [128MB]=142 \
   [64MB]=108 \
   [32MB]=91 \
   [16MB]=82 \
   [8MB]=78 \
   [4MB]=76 \
   [2MB]=75 \
   [1MB]=74 \
   [512kB]=74 \
   [256kB]=74 \
   [128kB]=74 \
)

#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.
sudo -u myuser /home/myuser/experiment/pgcache/test &>/home/myuser/experiment/pgcache/output.log &
touch /var/lock/subsys/local

#!/bin/bash
set -eCu -o pipefail
postgresql_service="postgresql-13"
# перехожу в директорию, где лежит этот скрипт
cd "$( dirname "${BASH_SOURCE[0]}" )" || exit $?
# настроечные опции
. config.sh
# ассоциативный массив, связывающий размер кеша PostgreSQL и количество нужных ему hugepages
. huge_pages.sh
# заголовок для CSV-файлов
readonly header='huge_pages(2MiB),pg_cache,pg_cache(KiB),idx1(ms),seq(ms),idx2(ms),heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,finish'
date --iso-8601=seconds
# цикл по включенным и отключенным hugepages
for hp in 'HP' 'NHP'
do
# Цикл по всем "ступенькам" размера кеша, по которым надо пройтись 
   for s_b in $shared_buffers
   do
      data_csv="data${hp}.csv"
      if [ ! -s "$data_csv" ]
      then
         echo "$header" >|"$data_csv"
      fi
      echo "shared_buffers=$s_b" >|'shared_buffers.conf'
      if [ "$hp" = 'HP' ]
      then
         set_huge_pages=${huge_pages_size["$s_b"]}
      else
         set_huge_pages=0
      fi
      huge_pages=$(awk '/^HugePages_Total:/ {print $2}' /proc/meminfo)
      if [ $huge_pages -ne $set_huge_pages ]
      then
         sudo sysctl --quiet --write vm.nr_hugepages="$set_huge_pages"
         while [ $(awk '/^HugePages_Total:/ {print $2}' /proc/meminfo) -gt $set_huge_pages ]
         do
            sleep 1
         done
      fi
      huge_pages=$(awk '/^HugePages_Total:/ {print $2}' /proc/meminfo)
      if [ $set_huge_pages -ne $huge_pages ]
      then
         echo -n 'hugepage_error,' >>"$data_csv"
         echo "$(date --iso-8601=seconds)" >>"$data_csv"
         continue
      fi
      echo -n "$huge_pages," >>"$data_csv"
      sudo systemctl start "$postgresql_service"
      psql --expanded --quiet --tuples-only -c "show shared_buffers" | awk '/^shared_buffers \|/ {printf "%s,", $3}' >>"$data_csv"
      psql --expanded --quiet --tuples-only -c "select setting::int*8 as shared_buffers from pg_settings where name='shared_buffers'" | awk '/^shared_buffers \|/ {printf "%s,", $3}' >>"$data_csv"
      # prewarm to the cache
      psql --quiet -c "select pg_prewarm('random')" -c "select pg_prewarm('random_random_idx')" >/dev/null
      # more prewarm
      pgbench --no-vacuum --time 100 --file test_idx.sql >/dev/null
      # reset stats
      psql --quiet -c "select pg_stat_reset_single_table_counters('random'::regclass),pg_stat_reset_single_table_counters('random_random_idx'::regclass)" >/dev/null
      # first test index search
      pgbench --no-vacuum --transaction 100 --report-latencies --file test_idx.sql | awk '/select random from random where random=\(select random\(\)::real\);$/ {printf "%s,", $1 >>"'"$data_csv"'";}'
      # test sequence scan
      pgbench --no-vacuum --transaction 1 --report-latencies --file test_seq.sql | awk '/select count\(data\) from random;$/ {printf "%s,", $1 >>"'"$data_csv"'"}'
      # second test index search
      pgbench --no-vacuum --transaction 100 --report-latencies --file test_idx.sql | awk '/select random from random where random=\(select random\(\)::real\);$/ {printf "%s,", $1 >>"'"$data_csv"'";}'
      # get stats
      sleep 10
      psql --quiet --tuples-only --field-separator=' ' --no-align -c "select heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit from pg_statio_user_tables where relname='random'"| head -1 | awk '{printf "%s,", $1 >>"'"$data_csv"'";printf "%s,", $2 >>"'"$data_csv"'";printf "%s,", $3 >>"'"$data_csv"'";printf "%s,", $4 >>"'"$data_csv"'";}'
      sudo systemctl stop "$postgresql_service"
      echo "$(date --iso-8601=seconds)" >>"$data_csv"
   done
done
sudo systemctl reboot