背景

场景:

• 高峰期出现慢SQL, 资源打满(cpu、内存、IO、连接数等), 引起雪崩
• 热表的DDL大锁进入队列中, 可能因为其他长事务的锁和这个大锁冲突, 引起等待, 导致连接数打满, 引起雪崩

挑战:

• 雪崩时业务完全受损, 无一幸免

PG解决方案:

1、各种超时参数.

• deadlock_timeout, 解决死锁造成的死等
• idle_in_transaction_session_timeout, 解决堵塞DDL的小锁长事务引起的雪崩问题
• lock_timeout, 解决DDL死等引起雪崩
• statement_timeout, 解决慢SQL堆积导致的雪崩

2、可编程: 钩子, SQL 限流

Executor Hooks

• ExecutorStart_hook — called at the beginning of any execution of any query plan.
• ExecutorRun_hook — called at any plan execution, after ExecutorStart.
• ExecutorFinish_hook — called after the last ExecutorRun call
• ExecutorEnd_hook — called at the end of execution of any query plan.
• ProcessUtility_hook — hook for the ProcessUtility.

参考:

《PostgreSQL hook & callback》