В кластерах Galera, интегрированных с MySQL или MariaDB, метрики, такие как buffer_pool_dirty_pages (количество "грязных" страниц в буферном пуле InnoDB) и wsrep_local_cert_failures (количество ошибок сертификации транзакций), часто сигнализируют о скрытых проблемах, даже при низкой общей нагрузке. Эти метрики взаимосвязаны: рост "грязных" страниц может указывать на задержки в записи данных на диск, а ошибки сертификации — на конфликты транзакций, что в итоге влияет на производительность и целостность данных. Ниже я консолидирую ключевые insights из нашего обсуждения в практические советы по реагированию. Подходите к анализу систематически: сначала мониторьте, затем диагностируйте и оптимизируйте, чтобы избежать простоев или потерь данных.

1. Понимание метрик и их взаимосвязи

Что отражает buffer_pool_dirty_pages? Это количество страниц в буферном пуле InnoDB, измененных в памяти, но не записанных на диск. Нормальное значение — низкое (менее 10–20% от общего пула), но рост может указывать на интенсивные записи, ограничения I/O или задержки в очистке. В Galera это усугубляется репликацией: изменения (write-sets) распространяются по нодам, и если одна нода не справляется, "грязные" страницы накапливаются.
Что отражает wsrep_local_cert_failures? Это счетчик транзакций, откатанных на локальной ноде из-за конфликтов сертификации (когда несколько нод конкурируют за изменения одних и тех же данных). Рост этой метрики часто коррелирует с "горячими точками" (hotspots) в данных или фоновыми задачами.
Взаимосвязь: Если wsrep_local_cert_failures растет (например, в 00:00 из-за cron-задач), это может привести к откатам транзакций, что временно снижает приток новых изменений в буферный пул, помогая стабилизировать buffer_pool_dirty_pages. Обратно, накопление "грязных" страниц на соседних нодах при низкой нагрузке может быть вызвано несбалансированной репликацией, где конфликты сертификации замедляют обработку.

Совет по реагированию: Всегда проверяйте эти метрики в паре. Используйте команды вроде SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; и SHOW GLOBAL STATUS LIKE 'wsrep_local_cert_failures'; для быстрой оценки. Если одна метрика растет, а другая стабилизируется (как в сценарии с 00:00), это может указывать на завершение кратковременной задачи — не игнорируйте, а анализируйте корреляцию через графики в Grafana.

2. Мониторинг: Настройте proactive отслеживание

Установите мониторинг с помощью mysqld_exporter в Prometheus/Grafana для метрик mysql_global_status_buffer_pool_pages{state="dirty"}, mysql_global_status_wsrep_local_cert_failures, а также связанных: Innodb_buffer_pool_pages_flushed (сброс страниц), wsrep_local_recv_queue (очередь входящих write-sets), wsrep_local_send_queue (исходящих) и wsrep_flow_control_paused (паузы из-за перегрузки).
Настройте алерты:
Для buffer_pool_dirty_pages — если >70% от Innodb_buffer_pool_pages_total или рост на 20% за 5 минут.
Для wsrep_local_cert_failures — если rate >0 за 5 минут (используйте PromQL: rate(mysql_global_status_wsrep_local_cert_failures[5m]) > 0).
Отслеживайте временные паттерны: Рост в фиксированное время (например, 00:00) часто связан с cron-заданиями, бэкапами или ETL-процессами.

Совет по реагированию: При срабатывании алерта немедленно проверьте логи (error_log, slow_query_log) и системные метрики (iostat, vmstat) на всех нодах. Если нагрузка низкая, но метрики растут на отдельных нодах, сравните их конфигурации (my.cnf) и оборудование — несоответствия (например, HDD vs SSD) могут быть причиной.

3. Диагностика причин роста метрик

При низкой нагрузке и росте buffer_pool_dirty_pages на соседних нодах:
Несбалансированная нагрузка: Проверьте распределение запросов через балансировщик (HAProxy/ProxySQL) и метрики wsrep_received_bytes/wsrep_replicated_bytes. Если ноды получают неравномерно записи, это приводит к локальному накоплению "грязных" страниц.
Проблемы I/O: Оцените innodb_io_capacity (должен соответствовать диску: 2000+ для SSD) и проверьте дисковую загрузку. Медленный I/O не позволяет сбрасывать страницы timely.
Проблемы репликации: Рост очередей (wsrep_local_recv_queue) указывает на задержки; конфликты сертификации могут усугублять.
Разные конфигурации или пики: Сравните innodb_buffer_pool_size, innodb_page_cleaners на нодах; проверьте на фоновые пики (например, ALTER TABLE).
При росте wsrep_local_cert_failures (например, в 00:00) и стабилизации buffer_pool_dirty_pages:
Фоновые задачи: Проверьте cron (crontab -l) на обновления, оптимизации или бэкапы — они могут вызывать конфликты в "горячих точках".
Hotspots: Анализируйте запросы на частые обновления одних строк; используйте pt-query-digest для выявления.
Освобождение ресурсов: Стабилизация dirty pages может означать завершение задачи или рост Innodb_buffer_pool_pages_flushed — проверьте I/O после пика.
Сеть/репликация: Проверьте задержки (ping, iperf) и wsrep_local_bf_aborts для brute force abort.

Совет по реагированию: Начните с логов и метрик за проблемный период (например, 00:00). Если рост cert failures приводит к откатам, это временно стабилизирует dirty pages — используйте это как сигнал для оптимизации. Соберите данные с всех нод для сравнения, чтобы выявить "проблемную" ноду.

4. Оптимизация и предотвращение

Для buffer_pool_dirty_pages:
Увеличьте innodb_io_capacity и innodb_page_cleaners (равно innodb_buffer_pool_instances, обычно 4–8).
Перейдите на SSD для ускорения I/O; оптимизируйте запросы (пакетные операции, индексы).
Балансируйте нагрузку: Равномерно распределяйте записи через ProxySQL.
Для wsrep_local_cert_failures:
Разбейте длинные транзакции; избегайте hotspots (используйте очереди для счетчиков).
Увеличьте wsrep_slave_threads (4–8) для ускорения применения write-sets.
Настройте wsrep_certification_rules осторожно (если не strict, но рискуя согласованностью).
Общие меры:
Унифицируйте конфигурации на всех нодах; тестируйте задачи в off-peak время.
Используйте ProxySQL для маршрутизации фоновых операций на одну ноду.
Регулярно анализируйте: Еженедельно проверяйте метрики на паттерны, чтобы предотвратить эскалацию.

5. Когда эскалировать проблему

Если метрики не стабилизируются после оптимизации (например, dirty pages >90% от innodb_max_dirty_pages_pct), это риск сбоя — подготовьте failover или добавьте ноды.
Вовлекайте DBA или экспертов, если конфликты приводят к частым откатам, влияя на приложение.
Помните: В Galera синхронность обеспечивает целостность, но требует баланса — игнорирование метрик может привести к flow control паузам или отставанию нод.

Следуя этим советам, вы сможете оперативно реагировать на подобные явления, минимизируя риски. Если метрики продолжают расти, соберите детальные логи/метрики и уточните для более targeted анализа.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Для того чтобы спровоцировать проблему с ростом метрики wsrep_local_cert_failures (ошибки сертификации транзакций) в кластере Galera, нужно создать условия, при которых несколько нод одновременно пытаются изменить одни и те же данные, вызывая конфликты сертификации. В Galera Cluster сертификация основана на принципе "первый фиксирует" (first-committer-wins), и конфликт возникает, когда write-sets (наборы изменений) от разных транзакций на разных нодах не могут быть согласованы. Ниже приведены практические способы, как можно искусственно спровоцировать такую проблему, а также объяснение, почему это приводит к ошибкам сертификации. Эти методы могут быть полезны для тестирования поведения кластера или отладки, но будьте осторожны, так как они могут нарушить нормальную работу системы.

---

Способы спровоцировать рост `wsrep_local_cert_failures`

1. Одновременное обновление одной и той же строки на разных нодах

Как сделать:

1. Выберите таблицу с небольшой таблицей (например, таблицу с одной строкой, содержащей счетчик).
2. На двух или более нодах одновременно запустите SQL-запросы, обновляющие одну и ту же строку. Например:

        UPDATE test_table SET counter = counter + 1 WHERE id = 1;

Выполняйте этот запрос параллельно на разных нодах (например, через скрипт или несколько клиентов MySQL).
3. Для автоматизации используйте скрипт (например, на Python с библиотекой mysql-connector или pymysql), который одновременно отправляет запросы на разные ноды:

        import mysql.connector
        import threading

        def update_counter(host):
            conn = mysql.connector.connect(host=host, user='user', password='pass', database='test_db')
            cursor = conn.cursor()
            for _ in range(100):
                cursor.execute("UPDATE test_table SET counter = counter + 1 WHERE id = 1")
                conn.commit()
            cursor.close()
            conn.close()

        nodes = ['node1', 'node2', 'node3']
        threads = [threading.Thread(target=update_counter, args=(node,)) for node in nodes]
        for t in threads:
            t.start()
        for t in threads:
            t.join()

Почему это вызывает проблему:
Каждая нода отправляет write-set с изменением строки id=1 в момент фиксации (COMMIT). Galera проверяет эти write-sets на конфликты. Если две ноды одновременно изменяют одну строку, только одна транзакция пройдет сертификацию, а другая будет откатана, увеличивая wsrep_local_cert_failures на ноде, где она была инициирована.
Ожидаемый результат:
Метрика wsrep_local_cert_failures начнет расти на нодах, где транзакции откатываются.
Вы можете проверить это с помощью:

       SHOW GLOBAL STATUS LIKE 'wsrep_local_cert_failures';

2. Массовые обновления одной таблицы с разных нод

Как сделать:

1. Создайте таблицу с несколькими строками (например, 1000 строк).
2. Запустите массовые обновления (например, UPDATE test_table SET value = value + 1;) без условия WHERE или с пересекающимися наборами строк на разных нодах.
3. Используйте несколько клиентов, чтобы одновременно отправлять запросы на разные ноды. Например:

        UPDATE test_table SET value = value + 1 WHERE id BETWEEN 1 AND 500; -- на node1
        UPDATE test_table SET value = value + 1 WHERE id BETWEEN 200 AND 700; -- на node2

4. Для большей интенсивности используйте цикл в скрипте или утилиту вроде sysbench:

        sysbench oltp_update_non_index --mysql-host=node1 --mysql-user=user --mysql-password=pass --db-driver=mysql --tables=1 --table-size=1000 run

Повторите на нескольких нодах одновременно.

Почему это вызывает проблему:
Обновления пересекающихся наборов строк создают конфликты в write-sets, так как Galera проверяет каждую измененную строку. Если хотя бы одна строка изменяется на двух нодах одновременно, одна из транзакций откатывается.
Ожидаемый результат:
Частые откаты транзакций из-за конфликтов увеличат wsrep_local_cert_failures. Это особенно заметно при отсутствии индексов или при больших транзакциях.

3. Создание длинных транзакций с пересекающимися данными

Как сделать:

1. На одной ноде начните длинную транзакцию, которая обновляет множество строк, но не фиксируйте её сразу:

        START TRANSACTION;
        UPDATE test_table SET value = value + 1 WHERE id < 1000;
        -- Задержка перед COMMIT, например, SLEEP(10)
        SELECT SLEEP(10);
        COMMIT;

2. На другой ноде одновременно выполните другую транзакцию, затрагивающую те же строки:

        UPDATE test_table SET value = value + 2 WHERE id < 500;
        COMMIT;

3. Для автоматизации используйте скрипт, который запускает длинные транзакции с задержкой на одной ноде и быстрые обновления на другой.

Почему это вызывает проблему:
Длинные транзакции увеличивают окно времени, в течение которого могут возникнуть конфликты. Пока первая транзакция не зафиксирована, изменения на других нодах могут конфликтовать с ней, вызывая откаты.
Ожидаемый результат:
Транзакции на второй ноде будут откатываться, увеличивая wsrep_local_cert_failures на этой ноде.

4. Имитация сетевых задержек

Как сделать:

1. Искусственно увеличьте сетевую задержку между нодами с помощью утилит вроде tc (Traffic Control) на Linux:

        tc qdisc add dev eth0 root netem delay 100ms

Это добавит задержку в 100 мс на сетевой интерфейс eth0. Выполните на одной или нескольких нодах.
2. Запустите обновления одной и той же таблицы на разных нодах (например, как в пункте 1 или 2).
3. После теста уберите задержку:

        tc qdisc del dev eth0 root

Почему это вызывает проблему:
Сетевые задержки замедляют доставку write-sets между нодами, увеличивая вероятность конфликтов, так как транзакции на разных нодах могут фиксироваться с небольшой разницей во времени, но затрагивать одни и те же данные.
Ожидаемый результат:
Увеличение wsrep_local_cert_failures из-за задержек в сертификации, а также возможный рост wsrep_local_recv_queue и wsrep_flow_control_paused.

5. Запуск ресурсоемких операций (например, DDL)

Как сделать:

1. На одной ноде выполните DDL-операцию (например, ALTER TABLE или OPTIMIZE TABLE):

        ALTER TABLE test_table ADD COLUMN new_col INT;

2. На другой ноде одновременно обновляйте ту же таблицу:

        UPDATE test_table SET value = value + 1;

3. Для большей интенсивности запустите OPTIMIZE TABLE или CREATE INDEX на одной ноде и параллельные DML-запросы (INSERT/UPDATE) на других.

Почему это вызывает проблему:
DDL-операции (особенно в старых версиях MySQL/MariaDB) блокируют таблицу или создают большие write-sets, что увеличивает вероятность конфликтов с другими транзакциями. Даже в Total Order Isolation (TOI) для DDL Galera может откатывать конкурирующие DML-транзакции.
Ожидаемый результат:
Рост wsrep_local_cert_failures на нодах, где DML-транзакции откатываются из-за конфликтов с DDL.

6. Нагрузка через утилиты тестирования

Как сделать:

1. Используйте sysbench для создания высокой нагрузки на запись с разных нод:

        sysbench oltp_write_only --mysql-host=node1 --mysql-user=user --mysql-password=pass --db-driver=mysql --tables=10 --table-size=10000 --threads=10 run

Повторите на нескольких нодах одновременно, чтобы создать конкуренцию.
2. Настройте тест так, чтобы запросы затрагивали пересекающиеся строки (например, уменьшите размер таблицы или используйте одинаковый диапазон id).

Почему это вызывает проблему:
Высокая частота операций записи на пересекающихся данных создает множество конфликтов сертификации, особенно если таблицы небольшие или запросы не используют индексы.
Ожидаемый результат:
Значительный рост wsrep_local_cert_failures, особенно на нодах с высокой интенсивностью запросов.

---

Связь с `buffer_pool_dirty_pages`

Спровоцированные ошибки сертификации могут косвенно влиять на buffer_pool_dirty_pages. Например:

Рост "грязных" страниц: Если транзакции создают изменения в буферном пуле, но откатываются из-за конфликтов, это может временно замедлить приток новых "грязных" страниц, что объясняет их стабилизацию (как в вашем сценарии в 00:00).
Стабилизация: Если конфликты приводят к откатам, а I/O-подсистема успевает сбрасывать накопленные страницы (рост Innodb_buffer_pool_pages_flushed), количество "грязных" страниц может выровняться.

Для проверки влияния на buffer_pool_dirty_pages параллельно отслеживайте:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';

---

Меры предосторожности

1. Тестируйте в песочнице: Проводите такие эксперименты на тестовой среде, чтобы избежать влияния на продакшен.
2. Мониторинг: Настройте мониторинг метрик (wsrep_local_cert_failures, buffer_pool_dirty_pages, wsrep_local_recv_queue) через Prometheus/Grafana, чтобы видеть эффект в реальном времени.
3. Логирование: Включите slow_query_log и general_log для анализа запросов, вызывающих конфликты.
4. Ограничьте масштаб: Начинайте с небольших тестов (например, одна таблица, 100 строк), чтобы минимизировать нагрузку на кластер.
5. Резервное копирование: Убедитесь, что у вас есть актуальные бэкапы перед тестированием DDL или массовых операций.

---

Как проверить результат

После выполнения любого из вышеуказанных действий:
1. Проверьте метрику:

   SHOW GLOBAL STATUS LIKE 'wsrep_local_cert_failures';

2. Сравните с другими метриками:

   SHOW GLOBAL STATUS LIKE 'wsrep_local_bf_aborts';
   SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue';

3. Используйте Prometheus для анализа трендов:

   rate(mysql_global_status_wsrep_local_cert_failures[5m])

4. Проверьте логи (error_log) на наличие сообщений о конфликтах или откатах.

---

Итог

Чтобы спровоцировать рост wsrep_local_cert_failures, создайте конкуренцию за данные: обновляйте одну строку или таблицу с разных нод, используйте длинные транзакции, имитируйте сетевые задержки или запускайте DDL параллельно с DML. Эти действия вызовут конфликты сертификации, увеличивая метрику. Одновременно это может повлиять на buffer_pool_dirty_pages, особенно если откаты транзакций сокращают приток изменений в буферный пул. Для точного анализа результатов используйте мониторинг и логи.

============

Как избежать проблемы в мультимастере?
Чтобы минимизировать рост wsrep_local_cert_failures в мультимастер-конфигурации:

Используйте балансировщик нагрузки:

Настройте ProxySQL или HAProxy, чтобы направлять запросы на запись к одной ноде для определенных таблиц или операций. Например:
sql-- Настройка ProxySQL: перенаправлять все UPDATE в таблице test_table на node1
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^UPDATE test_table', 10, 1);

==========================

import MySQLdb
import threading
import time
import os

Конфигурация нод

nodes = [
{"host": os.getenv("NODE1_HOST", "localhost"), "port": 3306, "user": "root", "password": os.getenv("NODE1_PASSWORD", "your_root_password")},
{"host": os.getenv("NODE2_HOST", "localhost"), "port": 3306, "user": "root", "password": os.getenv("NODE2_PASSWORD", "your_root_password")},
{"host": os.getenv("NODE3_HOST", "localhost"), "port": 3306, "user": "root", "password": os.getenv("NODE3_PASSWORD", "your_root_password")},
]

db_name = 'provocation_db'
table_name = 'provocation_table'
barrier = threading.Barrier(len(nodes))

def create_db_and_table(cursor):
cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
cursor.execute(f"USE {db_name};")
cursor.execute(f"""CREATE TABLE IF NOT EXISTS {table_name} (
id INT PRIMARY KEY AUTO_INCREMENT,
node1_val INT DEFAULT 0,
node2_val INT DEFAULT 0,
node3_val INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;""")

def insert_initial_record(cursor):
cursor.execute(
f"""INSERT INTO {table_name} (node1_val, node2_val, node3_val)
VALUES (0, 0, 0)
ON DUPLICATE KEY UPDATE node1_val = node1_val;"""
)

def get_record_id(cursor):
cursor.execute(f"SELECT id FROM {table_name} LIMIT 1;")
return cursor.fetchone()[0]

def update_record(conn, record_id, node_index):
cursor = conn.cursor()
for i in range(100):
try:
val = node_index * 1000 + i
cursor.execute("START TRANSACTION;")
cursor.execute(
f"""UPDATE {table_name} SET
node1_val = %s,
node2_val = %s,
node3_val = %s
WHERE id = %s;""",
(val, val, val, record_id)
)
cursor.execute("COMMIT;")
time.sleep(0.01) # Задержка для снижения нагрузки
except MySQLdb.Error as e:
print(f"Error on node {node_index}, iteration {i}: {e}")
cursor.execute("ROLLBACK;")
cursor.close()

def get_metrics(node):
try:
with MySQLdb.connect(
host=node['host'], port=node['port'], user=node['user'], passwd=node['password']
) as conn:
with conn.cursor() as cursor:
cursor.execute("SHOW GLOBAL STATUS LIKE 'wsrep_local_cert_failures';")
cert_failures = cursor.fetchone()[1]
cursor.execute("SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';")
dirty_pages = cursor.fetchone()[1]
print(f"Node {node['host']}: wsrep_local_cert_failures={cert_failures}, buffer_pool_dirty_pages={dirty_pages}")
except MySQLdb.Error as e:
print(f"Error fetching metrics from {node['host']}: {e}")

def process_node(node, node_index):
try:
with MySQLdb.connect(
host=node['host'],
port=node['port'],
user=node['user'],
passwd=node['password'],
autocommit=True
) as conn:
with conn.cursor() as cursor:
create_db_and_table(cursor)
insert_initial_record(cursor)
record_id = get_record_id(cursor)
print(f"Node {node['host']} ready, waiting for others...")
barrier.wait()
print(f"Starting updates on {node['host']}...")
update_record(conn, record_id, node_index)
print(f"Finished processing {node['host']}")
except MySQLdb.Error as e:
print(f"Error on node {node['host']}: {e}")

if __name__ == "__main__":

Инициализация базы данных на первой ноде

with MySQLdb.connect(
host=nodes[0]['host'], port=nodes[0]['port'], user=nodes[0]['user'], passwd=nodes[0]['password'], autocommit=True
) as conn:
with conn.cursor() as cursor:
create_db_and_table(cursor)
insert_initial_record(cursor)

Запуск потоков для параллельных обновлений

threads = []
for idx, node in enumerate(nodes):
thread = threading.Thread(target=process_node, args=(node, idx+1))
threads.append(thread)
thread.start()

for thread in threads:
thread.join()

Сбор метрик

print("\nCollecting metrics...")
for node in nodes:
get_metrics(node)

print("All nodes updated in parallel.")

[file-name 000185_2025-08-28_22-12-27.txt]

### Советы по мониторингу и реагированию на рост метрик `buffer_pool_dirty_pages` и `wsrep_local_cert_failures` в кластере Galera