Буферный кэш PostgreSQL: автоматизация анализа с помощью bash-скрипта

Эффективное управление буферным кэшем — важный аспект оптимизации производительности базы данных PostgreSQL. Он играет важную роль в удержании страниц данных в памяти, сокращая операции дискового ввода-вывода и улучшая время выполнения запросов. Понимание того, как буферный кэш распределяется между различными базами данных, может дать ценную информацию об оптимизации производительности и обеспечении эффективного использования ресурсов.

В этой статье рассмотрим bash-скрипт, который автоматизирует процесс проверки распределения буферного кэша для всех баз данных на экземпляре PostgreSQL. Скрипт упрощает анализ и помогает администраторам баз данных определить, какие таблицы потребляют больше всего буферного кэша. Это может быть особенно полезно для настройки производительности и оптимизации ресурсов.

Обзор сценария

Предоставленный bash-скрипт выполняет следующие задачи:

1. Извлекает список баз данных: подключается к экземпляру PostgreSQL и извлекает список баз данных, используемых в данный момент.
2. Собирает информацию о буферном кэше: для каждой базы данных скрипт запрашивает информацию о буферном кэше, чтобы определить, какой объем кэша выделен различным таблицам.
3. Форматы и отчеты: скрипт форматирует выходные данные для удобства чтения и отображает отчет об использовании буферного кэша.

Как работает скрипт

А сейчас разберем сценарий на его ключевые компоненты:

1. Настройка и инициализация:

#!/bin/bash

# Скрипт для проверки выделения буферного кэша для всех баз данных на экземпляре базы данных

nPort=$1

current_date_time="$(date +'%Y%m%d_%H%M%S')" 

fileOne="./temp_output/check_buffer_cache_${current_date_time}.tmp"

fileTwo="./temp_output/check_buffer_cache_${current_date_time}_formatted.tmp"

echo " "
echo "Started..."
echo " "

Скрипт начинается с настройки необходимых переменных, включая номер порта (nPort) и пути к файлам для хранения временных выходных файлов. Он также фиксирует текущую дату и время для временной отметки выходных файлов.

2. Итерация по базам данных :

for i in `psql -h localhost -p ${nPort} -U postgres -t -c "select datname from pg_database where datname in (select datname from pg_stat_activity) order by 1"`; do 

echo "Collecting buffercache info about database: $i"

Скрипт запрашивает список баз данных, которые в данный момент активны, и выполняет итерацию по каждой базе данных. Для каждой базы он собирает информацию о буферном кэше.

3. Сбор информации о буферном кэше :

psql -h localhost -p ${nPort} -U $i -d $i -qt <<SQL >> ${fileOne}

\\c ${i};

SELECT '${i}' as db_name, n.nspname, c.relname, count(*) AS buffers
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND
                b.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             JOIN pg_namespace n ON n.oid = c.relnamespace and n.nspname ='public'
             GROUP BY n.nspname, c.relname
             ORDER BY 4 DESC
             LIMIT 10;

SQL

Для каждой базы данных скрипт выполняет SQL-запрос для получения использования буферного кэша. Он объединяет несколько таблиц системного каталога PostgreSQL, чтобы получить сведения о распределении буфера для каждой таблицы, фильтруя для включения только тех, которые находятся в схеме «public».

4. Форматирование и отображение отчета :

cat ${fileOne} | awk -F"|" ' { if (NF==4) {  printf("%-40s %-20s %-50s %s\n",$1, $2, $3, $4); } } ' | sort -nrk4 > ${fileTwo}

echo " "
echo "Report:"
echo " "

cat ${fileTwo}

echo " "
echo "Ended..."
echo " "

После сбора данных скрипт форматирует их при помощи awk,чтобы обеспечить читаемость. Он сортирует вывод по количеству буферов в порядке убывания и сохраняет результат в другом временном файле. Наконец, он отображает отформатированный отчет.

Запуск скрипта

Для выполнения этого скрипта необходимо передать номер порта экземпляра PostgreSQL в качестве аргумента:

./check_buffer_cache.sh 5432

Замените 5432на номер порта экземпляра PostgreSQL. Скрипт сгенерирует и отобразит отчет об использовании буферного кэша для всех активных баз данных.

Заключение

Этот скрипт — удобный инструмент для администраторов PostgreSQL, желающих получить представление о распределении кэша буфера. Для достижения наилучших результатов попробуйте позапускать этот скрипт в разные периоды времени, чтобы отслеживать изменения в использовании кэша и принимать обоснованные решения о настройке базы данных.

Ниже представлен полный скрипт:

#!/bin/bash

# Скрипт для проверки выделения буферного кэша для всех баз данных на экземпляре базы данных

nPort=$1

current_date_time="$(date +'%Y%m%d_%H%M%S')" 

fileOne="./temp_output/check_buffer_cache_${current_date_time}.tmp"

fileTwo="./temp_output/check_buffer_cache_${current_date_time}_formatted.tmp"

echo " "
echo "Started..."
echo " "

for i in `psql -h localhost -p ${nPort} -U postgres -t -c "select datname from pg_database where datname in (select datname from pg_stat_activity) order by 1"`; do 

echo "Collecting buffercache info about database: $i"
#echo " "

psql -h localhost -p ${nPort} -U $i -d $i -qt <<SQL >> ${fileOne}

\\c ${i};

SELECT '${i}' as db_name, n.nspname, c.relname, count(*) AS buffers
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND
                b.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             JOIN pg_namespace n ON n.oid = c.relnamespace and n.nspname ='public'
             GROUP BY n.nspname, c.relname
             ORDER BY 4 DESC
             LIMIT 10;

SQL

done

cat ${fileOne} | awk -F"|" ' { if (NF==4) {  printf("%-40s %-20s %-50s %s\n",$1, $2, $3, $4); } } ' | sort -nrk4 > ${fileTwo}

echo " "
echo "Report:"
echo " "

cat ${fileTwo}

echo " "
echo "Ended..."
echo " "

На этом все! Спасибо за внимание! Если статья была интересна, подпишитесь на телеграм-канал usr_bin, где будет еще больше полезной информации.