Горячее
Лучшее
Свежее
Подписки
Сообщества
Блоги
Эксперты

Postgres + Тестирование

С этим тегом используют

Postgresql IT SQL Тест Технологии Программа Инновации Программирование Чат-бот Все
6 постов сначала свежее
0

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA

Очередная иллюстрация -ЛЮБОЙ ответ нейросетей необходимо экспериментально проверять.

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL Нейронные сети, Субд, Postgres, Тестирование, Настройка, Длиннопост, DeepSeek

В случае использования советов нейросетей - древняя мудрость особенно актуальна.

Вопрос DeepSeek

Как изменится производительность СУБД PostgreSQL при нагрузке создаваемой pgbench и количестве подключений от 5 до 100 для СУБД размером 300MB при ресурсах CPU = 2 и RAM = 2GB при изменении настроек автовакуума с настроек по умолчанию на агрессивные настройки ?

Ответ DeepSeek

Исходные параметры:

  • Размер БД: 300 MB

  • Ресурсы: 2 CPU, 2 GB RAM

  • Количество подключений: 5–100

  • Нагрузка: pgbench в режиме TPC-B (смесь SELECT/UPDATE).

  • Сравнение:
    Настройки по умолчанию:

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 50

autovacuum_vacuum_cost_delay = 20ms

autovacuum_vacuum_cost_limit = 200

Агрессивные настройки:

autovacuum_vacuum_scale_factor = 0.05 # Запуск при 5% изменений вместо 20%

autovacuum_vacuum_threshold = 25 # Нижний порог срабатывания

autovacuum_vacuum_cost_delay = 5ms # Меньшая задержка между операциями

autovacuum_vacuum_cost_limit = 1000 # Больше операций за один проход

Прогнозируемые результаты:

1. Низкая нагрузка (5–20 подключений):

  • По умолчанию:
    Автовакуум запускается редко. Мертвые кортежи накапливаются, но не критично. Производительность стабильна.
    TPS: ~600–800.

  • Агрессивные настройки:
    Автовакуум срабатывает чаще, но нагрузка от него незначительна. Мертвые кортежи удаляются своевременно.
    TPS: ~650–850 (+5–10%).

2. Средняя нагрузка (30–60 подключений):

  • По умолчанию:
    Накопление мертвых кортежей приводит к:
    Увеличению размера таблиц (из-за "раздувания").
    Замедлению SELECT (сканирование большего объема данных).
    Блокировкам при UPDATE из-за долгих вакуумных операций.
    TPS: падение до 400–500.

  • Агрессивные настройки:
    Частые, но короткие вакуум-сессии. Меньше "раздувания", быстрее доступ к данным.
    TPS: ~550–700 (+20–40%).

3. Высокая нагрузка (80–100 подключений):

  • По умолчанию:
    Критическое накопление мертвых кортежей. Автовакуум не успевает очищать данные, что вызывает:
    Резкое падение TPS (до 200–300).
    Рост времени отклика (блокировки, конкуренция за I/O).

  • Агрессивные настройки:
    Плюсы: Минимизация "раздувания", стабильный TPS (~450–600).
    Минусы: Конкуренция за ресурсы CPU между фоновым вакуумом и рабочими процессами. При 2 CPU возможны просадки из-за нехватки вычислительной мощности.

Ключевые метрики для сравнения:

  1. TPS (Transactions Per Second):
    Агрессивные настройки дадут прирост на 20–40% при 30–60 подключениях.
    При 80–100 подключениях рост TPS будет менее значительным (+10–15%) из-за ограничений CPU.

  2. Задержки (Latency):
    В агрессивном режиме задержки будут стабильнее, особенно в диапазоне 30–60 подключений.

  3. Размер таблиц (Table Bloat):
    Агрессивный автовакуум уменьшит "раздувание" на 40–60% по сравнению с настройками по умолчанию.

  4. Использование CPU:
    При агрессивных настройках нагрузка на CPU вырастет на 15–25% из-за частого выполнения вакуума.

Рекомендации:

  1. Для тестовой конфигурации (2 CPU, 2 GB RAM):
    Агрессивные настройки целесообразны при 20–60 подключениях.
    При 80–100 подключениях требуется мониторинг CPU: если загрузка достигнет 90–100%, производительность может ухудшиться.

  2. Оптимизация под высокую нагрузку:
    Увеличьте autovacuum_max_workers (если нужно параллелить вакуум).
    Настройте maintenance_work_mem (выделите 256–512 MB для операций вакуума).

  3. Дисковый I/O:
    Если диск медленный (HDD), агрессивный автовакуум может вызвать contention I/O. Используйте SSD для минимизации рисков.

Заключение:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц. Однако при экстремальной нагрузке (80–100 подключений) и ограниченных ресурсах CPU (2 ядра) возможна конкуренция за процессорное время, что снизит эффективность. Тестирование на реальной инфраструктуре с мониторингом CPU/I/O обязательно.

Результаты сравнительных экспериментов

Конфигурация виртуальной машины

CPU = 2RAM = 2GB

Версия СУБД:Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Сценарий нагрузки "Mix Without median time"

Эксперимент-1 : Настройки автовакуума по умолчанию

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 50

autovacuum_analyze_scale_factor = 0.1

autovacuum_analyze_threshold = 50

autovacuum_vacuum_cost_limit = -1

vacuum_cost_limit = 2000

autovacuum_vacuum_cost_delay = 2ms

Эксперимент-2 : "агрессивные" настройки автовакуума

autovacuum_vacuum_scale_factor = 0.01

autovacuum_vacuum_threshold = 25

autovacuum_analyze_scale_factor = 0.005

autovacuum_analyze_threshold = 10

autovacuum_vacuum_cost_limit = 2000

vacuum_cost_limit = 2000

autovacuum_vacuum_cost_delay = 2ms

maintenance_work_mem = 128MB

autovacuum_naptime = 5s

Результаты сравнительных экспериментов

Операционная скорость

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL Нейронные сети, Субд, Postgres, Тестирование, Настройка, Длиннопост, DeepSeek

Ось X - нагрузка на СУБД . Ось Y - операционная скорость

Ожидания СУБД

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL Нейронные сети, Субд, Postgres, Тестирование, Настройка, Длиннопост, DeepSeek

Итоги и проверка гипотез DeepSeek

  1. Операционная скорость для данной СУБД и данных ресурсах ВМ - не увеличилась и даже уменьшилась до -5% при оптимальной нагрузке.

  2. Характерные ожидания - не изменились .

  3. Гипотеза нейросети о влиянии "агрессивной" настройки автовакуума на производительность СУБД - не подтвердилась:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц.

Показать полностью 3
[моё] Нейронные сети Субд Postgres Тестирование Настройка Длиннопост DeepSeek
3
0

Задача на оптимизацию SQL запроса⁠⁠

Предлагаю ещё одну задачу по базам данных со своего Телеграм-канала по SQL и базам данных. Итак, среди прочих, имеется таблица выставленных счетов INVOICES:

Задача на оптимизацию SQL запроса Тестирование, IT, Программирование, Собеседование, SQL, База данных, Аналитик, Аналитика, Программист, Postgresql, Postgres, Oracle, Mysql, Telegram (ссылка)

В некотором отчёте АБС количество счетов, выставленных за текущий день формируется SQL запросом:

SELECT COUNT(*) COUNT_INV FROM INVOICES WHERE DATE(DATE_CREATE) = CURRENT_DATE

Задача на оптимизацию SQL запроса Тестирование, IT, Программирование, Собеседование, SQL, База данных, Аналитик, Аналитика, Программист, Postgresql, Postgres, Oracle, Mysql, Telegram (ссылка)
Тестирование IT Программирование Собеседование SQL База данных Аналитик Аналитика Программист Postgresql Postgres Oracle Mysql Telegram (ссылка)
3
23

Задача с собеседования по SQL с заложенной в неё ошибкой⁠⁠

Задача с собеседования по SQL с заложенной в неё ошибкой Программирование, IT, Собеседование, SQL, Задача, Oracle, Postgresql, Postgres, Mysql, Ms SQL, База данных, Программист, Тестирование

Продолжаю тему разборов задач с собеседований по SQL. В этот раз выложу полное содержание, чтобы пост не удалили.

Итак, в базе данных есть таблица CLIENTS, содержащая три строки:

Задача с собеседования по SQL с заложенной в неё ошибкой Программирование, IT, Собеседование, SQL, Задача, Oracle, Postgresql, Postgres, Mysql, Ms SQL, База данных, Программист, Тестирование

И есть таблица INVOICES, в которой две строки:

Задача с собеседования по SQL с заложенной в неё ошибкой Программирование, IT, Собеседование, SQL, Задача, Oracle, Postgresql, Postgres, Mysql, Ms SQL, База данных, Программист, Тестирование

И сама задача звучит так: какое количество строк вернёт указанный ниже запрос:

SELECT * FROM CLIENTS WHERE EXISTS (SELECT COUNT(*) FROM INVOICES WHERE ID_CLIENT = CLIENTS.ID)

Задача с собеседования по SQL с заложенной в неё ошибкой Программирование, IT, Собеседование, SQL, Задача, Oracle, Postgresql, Postgres, Mysql, Ms SQL, База данных, Программист, Тестирование

Какой бы вариант Вы выбрали?

Здесь нужно было заметить неправильное использование EXISTS.

Оператор EXISTS используют для проверки того, возвращается ли что-то подзапросом, указанным в скобках. Как правило, в скобках для оператора EXISTS/NOT EXISTS пишут:

SELECT *

или:

SELECT 1

В задаче выше нарочно допущена ошибка (в EXISTS нарочно написано SELECT COUNT).

SELECT COUNT всегда будет возвращать данные. Написанный в скобочках подзапрос всегда будет возвращать количество, хоть и иногда 0. Но данные-то есть. Ноль - это тоже данные. Поэтому EXISTS будет давать TRUE для каждой из трех строчек основного запроса.

Ещё больше интересных задач с собеседований я публикую в моем Телеграм-канале по SQL!

Показать полностью 4
[моё] Программирование IT Собеседование SQL Задача Oracle Postgresql Postgres Mysql Ms SQL База данных Программист Тестирование
2
Партнёрский материал

Сколько нужно времени, чтобы уложить теплый пол?⁠⁠

Точно не скажем, но в нашем проекте с этим можно справиться буквально за минуту одной левой!

Попробовать

Ремонт Теплый пол Текст

Ответ на пост «Задачи с собеседования по SQL»⁠⁠1

Example Answer Structure

Question: What value will the following query return?

SELECT COUNT(*) FROM TMP_TABLE;

Answer:

To determine the result of the query SELECT COUNT(*) FROM TMP_TABLE;, we need to understand what the COUNT(*) function does in SQL. The COUNT(*) function counts the number of rows in a table, regardless of whether they contain NULL values or not.

Let's look at the data in the table TMP_TABLE:

column1column21212NULL131214NULLNULL1315

There are 5 rows in the table. The COUNT(*) function will count all these rows, including those with NULL values.

Therefore, the query:

SELECT COUNT(*) FROM TMP_TABLE;

will return the value 5.

This value is the total number of rows in the table TMP_TABLE, without any consideration of the content of the columns or whether they contain NULL values.
______
ChatGPT4o. Простите.

Программирование Собеседование IT SQL Задача Аналитика База данных Ms SQL Oracle Postgresql Postgres Mysql Тестирование Telegram (ссылка) Ответ на пост Текст
1
14

Задача по SQL с неочевидным решением⁠⁠

Задача по SQL с неочевидным решением IT, Программирование, Собеседование, Тестирование, Фриланс, SQL, Oracle, Программист, Ms SQL, Mysql, Postgresql, Postgres, База данных, Длиннопост

Здравствуй, уважаемый читатель! Спасибо, что заходишь ко мне на канал и изучаешь SQL со мной!

Недавно нашёл в интернете достаточно простую задачу с собеседования по SQL, в которой нужно было составить запрос к таблицам. Ссылку на источник размещу ниже. В своём Телеграм канале, где мы решаем разные задачи с собеседований и разбираем практические ситуации, я предложил всем поучаствовать в её решении. Надеюсь, тебе будет тоже интересно попробовать её решить. Ниже описание задачи.

В базе данных есть таблица анализов Analysis, имеющая следующие столбцы: an_id — ID анализа; an_name — название анализа; an_price — цена анализа; an_group — группа анализов. Есть, также, таблица заказов Orders: ord_id — ID заказа; ord_datetime — дата и время заказа; ord_an — ID анализа. Необходимо вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.

Тут сделай паузу и попробуй сначала сам решить задачу.

Итак, надеюсь, ты делал паузу и составил SQL-запрос. Далее будем решать вместе.

Напомню, что нужно было вывести список анализов и их цену, которые продавались 05.02.2022 и всю следующую неделю.

Первым решением напрашивается соединение таблицы анализов и таблицы продаж с применением условия на период. Кстати, именно это и указано в качестве ответа на задачу.

На самом деле, из-за того, что один анализ за выбираемый интервал времени мог быть продан сколько угодно раз (один и тот же анализ мог быть продан хоть тысячу раз и больше) заместо соединения таблицы анализов с таблицей заказов, я бы предложил использование EXISTS:

select  a.an_name, a.an_cost from  analysis a where  exists (select 1 from orders  where ord_an = a.an_id  and ord_datetime between to_date('05.02.2022', 'dd.mm.yyyy') and to_date('05.02.2022', 'dd.mm.yyyy') + 7)

Благодаря тому, что мы не джоинили анализы с их заказами, в результирующей таблице анализы не замножатся по количеству присоединившихся к ним заказов. То есть в результате мы получим:

Задача по SQL с неочевидным решением IT, Программирование, Собеседование, Тестирование, Фриланс, SQL, Oracle, Программист, Ms SQL, Mysql, Postgresql, Postgres, База данных, Длиннопост

а не:

Задача по SQL с неочевидным решением IT, Программирование, Собеседование, Тестирование, Фриланс, SQL, Oracle, Программист, Ms SQL, Mysql, Postgresql, Postgres, База данных, Длиннопост

В примере, что я нашёл в интернете (ссылка ниже), помимо названий анализов и их стоимостей, выведен ещё и столбец с датами заказов. По условиям задачи вывод этого столбца не запрашивался. Не требовался вывод никакой информации о заказах/продажах. Поэтому и надобности в соединении я не усмотрел. Наоборот, это и потенциально замножит вывод анализов, которые покупали в запрашиваемый период, и прибавит лишнюю работу СУБД.

Ссылка на источник: https://tproger.ru/articles/5-zadanij-po-sql-s-realnyh-sobesedovanij

Поддержи статью лайком или подпиской!

Ещё больше интересных практических задач по SQL и задач с собеседований в нашем Телеграмм-канале и в интернете :)

Показать полностью 3
[моё] IT Программирование Собеседование Тестирование Фриланс SQL Oracle Программист Ms SQL Mysql Postgresql Postgres База данных Длиннопост
8
2

Задачи с собеседования по SQL⁠⁠1

Всем привет!

Продолжаем разбирать SQL задачи с собеседований с нашего Телеграм-канала!

В базе данных есть таблица TMP_TABLE, имеющая всего два столбца и содержащая следующие данные:

TMP_TABLE

column1 column2

-----------------------------------------

12 12

NULL 13

12 14

NULL NULL

13 15

Какое значение вернёт следующий запрос?

SELECT COUNT(*) FROM TMP_TABLE

Задачи с собеседования по SQL Программирование, Собеседование, IT, SQL, Задача, Аналитика, База данных, Ms SQL, Oracle, Postgresql, Postgres, Mysql, Тестирование, Telegram (ссылка)
Показать полностью 1
[моё] Программирование Собеседование IT SQL Задача Аналитика База данных Ms SQL Oracle Postgresql Postgres Mysql Тестирование Telegram (ссылка)
68
Посты не найдены