PostgreSQL 同步流复制锁瓶颈分析

背景

PostgreSQL 的同步流复制机制通过 walreceiver 接收到 WAL 的 LSN 位点，来唤醒等待 WAL 已被备库接收的事务的进程。对于同步事务，用户提交事务后，生成的 RECORD LSN 必须小于或等于 walreceiver 反馈的 LSN 位点。这个过程通过 WAIT 队列实现，walsender 负责释放队列中的事务。

在使用 PostgreSQL 的同步流复制时，进行 insert 压测时，发现主节点的 CPU、网络和块设备资源都处于空闲状态，但写性能被局限在一个范围内，无法提升。进一步分析发现，主节点存在大量 mutex 导致性能瓶颈。

问题复现

环境

3 台数据库主机

1 台测试机器

千兆位互联

部署

postgres.conf 配置：

listen_addresses = '0.0.0.0'max_connections = 300shared_buffers = 24GBmaintenance_work_mem = 512MBdynamic_shared_memory_type = posixvacuum_cost_delay = 0bgwriter_delay = 10msbgwriter_lru_maxpages = 1000bgwriter_lru_multiplier = 10.0bgwriter_flush_after = 0max_parallel_workers_per_gather = 0old_snapshot_threshold = -1checkpoint_timeout = 45minmax_wal_size = 48GBcheckpoint_completion_target = 0.05checkpoint_flush_after = 0max_wal_senders = 5random_page_cost = 1.0parallel_tuple_cost = 0parallel_setup_cost = 0effective_cache_size = 48GBforce_parallel_mode = offlog_destination = 'csvlog'logging_collector = onlog_truncate_on_rotation = onlog_timezone = 'PRC'update_process_title = offautovacuum = onlog_autovacuum_min_duration = -1autovacuum_max_workers = 16autovacuum_naptime = 15sautovacuum_vacuum_scale_factor = 0.02autovacuum_analyze_scale_factor = 0.01vacuum_freeze_table_age = 1500000000vacuum_multixact_freeze_table_age = 1500000000datestyle = 'iso, mdy'timezone = 'PRC'lc_messages = 'C'lc_monetary = 'C'lc_numeric = 'C'lc_time = 'C'default_text_search_config = 'pg_catalog.english'

启动命令：

pg_ctl -o "-p 1921 -c synchronous_standby_names='1(b,c)'" start -D /u01/digoal/pg_root

备库配置：

recovery.confrecovery_target_timeline = 'latest'standby_mode = onprimary_conninfo = 'user=postgres host= port= application_name=b'

状态查看：

-[ RECORD 1 ]----+----------------------------pid              | 42754usesysid         | 10usename          | postgresapplication_name | bclient_addr      | xxx.xxx.xxx.xxxclient_hostname  | client_port      | 52834backend_start    | 2016-11-07 16:07:26.353563+08backend_xmin     | statesent_location    | 2/36798458write_location   | 2/36798458flush_location   | 2/36798458replay_location  | 2/36798458sync_priority    | 1sync_state       | sync-[ RECORD 2 ]----+----------------------------pid              | 42755usesysid         | 10usename          | postgresapplication_name | cclient_addr      | xxx.xxx.xxx.xxxclient_hostname  | client_port      | 60064backend_start    | 2016-11-07 16:07:26.353765+08backend_xmin     | statesent_location    | 2/36798458write_location   | 2/36798458flush_location   | 2/36798458replay_location  | 2/36798458sync_priority    | 2sync_state       | potential

压测

测试过程

创建测试表：

psql -c "create table test(id serial primary key, info text, crt_time timestamp);"

执行压测：

pgbench -M prepared -n -r -P 1 -f ./test3.sql -h xxx.xxx.xxx.xxx -p 1921 -U postgres -c 64 -j 64 -T 120

测试结果

top 输出显示系统资源空闲，但压测只能达到约 7 万 QPS。

perf top 输出显示 CPU 占比很高，主要由 LOCK 相关函数占用。

代码分析

SetLatch 函数

void SetLatch(volatile Latch *latch) {    pg_memory_barrier();    if (latch->is_set) return;    latch->is_set = true;    #ifndef WIN32    if (owner_pid == MyProcPid) {        if (waiting) sendSelfPipeByte();    } else {        kill(owner_pid, SIGUSR1);    }    #endif}

SyncRepWakeQueue 函数

static int SyncRepWakeQueue(bool all, int mode) {    volatile WalSndCtlData *walsndctl = WalSndCtl;    PGPROC *proc = NULL;    PGPROC *thisproc = NULL;    int numprocs = 0;    Assert(mode >= 0 && mode < NUM_SYNC_REP_WAIT_MODE);    Assert(SyncRepQueueIsOrderedByLSN(mode));    proc = (PGPROC *) SHMQueueNext(&walsndctl->SyncRepQueue[mode],                                     &walsndctl->SyncRepQueue[mode],                                     offsetof(PGPROC, syncRepLinks));    while (proc) {        if (!all && walsndctl->lsn[mode] < proc->waitLSN) return numprocs;        thisproc = proc;        proc = (PGPROC *) SHMQueueNext(&walsndctl->SyncRepQueue[mode],                                       &proc->syncRepLinks,                                       offsetof(PGPROC, syncRepLinks));        thisproc->syncRepState = SYNC_REP_WAIT_COMPLETE;        SHMQueueDelete(&thisproc->syncRepLinks);        SetLatch(&thisproc->procLatch);        numprocs++;    }    return numprocs;}

SyncRepReleaseWaiters 函数

void SyncRepReleaseWaiters(void) {    volatile WalSndCtlData *walsndctl = WalSndCtl;    XLogRecPtr writePtr;    XLogRecPtr flushPtr;    XLogRecPtr applyPtr;    bool got_oldest;    bool am_sync;    int numwrite = 0, numflush = 0, numapply = 0;    if (MyWalSnd->sync_standby_priority == 0 ||        MyWalSnd->state < WALSNDSTATE_STREAMING ||        XLogRecPtrIsInvalid(MyWalSnd->flush)) {        announce_next_takeover = true;        return;    }    LWLockAcquire(SyncRepLock, LW_EXCLUSIVE);    got_oldest = SyncRepGetOldestSyncRecPtr(&writePtr, &flushPtr, &applyPtr, &am_sync);    if (announce_next_takeover && am_sync) {        announce_next_takeover = false;        ereport(LOG, (errmsg("standby \"%s\" is now a synchronous standby with priority %u",                             application_name, MyWalSnd->sync_standby_priority)));    }    if (!got_oldest || !am_sync) {        LWLockRelease(SyncRepLock);        announce_next_takeover = !am_sync;        return;    }    if (walsndctl->lsn[SYNC_REP_WAIT_WRITE] < writePtr) {        walsndctl->lsn[SYNC_REP_WAIT_WRITE] = writePtr;        numwrite = SyncRepWakeQueue(false, SYNC_REP_WAIT_WRITE);    }    if (walsndctl->lsn[SYNC_REP_WAIT_FLUSH] < flushPtr) {        walsndctl->lsn[SYNC_REP_WAIT_FLUSH] = flushPtr;        numflush = SyncRepWakeQueue(false, SYNC_REP_WAIT_FLUSH);    }    if (walsndctl->lsn[SYNC_REP_WAIT_APPLY] < applyPtr) {        walsndctl->lsn[SYNC_REP_WAIT_APPLY] = applyPtr;        numapply = SyncRepWakeQueue(false, SYNC_REP_WAIT_APPLY);    }    LWLockRelease(SyncRepLock);    elog(DEBUG3, "released %d procs up to write %X/%X, %d procs up to flush %X/%X, %d procs up to apply %X/%X",         numwrite, (uint32)(writePtr >> 32), (uint32)writePtr,         numflush, (uint32)(flushPtr >> 32), (uint32)flushPtr,         numapply, (uint32)(applyPtr >> 32), (uint32)applyPtr);}

cpuset 隔离后的性能

使用 cgroup cpuset 隔离 CPU 核数，将其限制在 15 个左右，性能从约 7.6 万提升到 10 万 TPS。测试表明，当参与 LOCK 的 CPU 核数超过 15 时，性能会下降。这是因为 mutex 在多个 CPU 核之间开销增加，导致瓶颈。

测试方法

创建 cgroup：

mkdir -p /cgroup/cpusetmount -t cgroup -o cpuset /cgroup/cpusetecho "1-15" > /cgroup/cpuset/cpuset.cpusetecho 0 > /cgroup/cpuset/cpuset.memecho 1 > /cgroup/cpuset/cpuset.cpu_exclusiveecho 1 > /cgroup/cpuset/cpuset.memory_migrate

部署多实例：

mkdir r1 && cd r1 && echo "1-15" > cpuset.cpusecho 0 > cpuset.mem && echo 1 > cpuset.cpu_exclusive && echo 1 > cpuset.memory_migratemkdir r2 && cd r2 && echo "1-15" > cpuset.cpusecho 0 > cpuset.mem && echo 1 > cpuset.cpu_exclusive && echo 1 > cpuset.memory_migrate

结果

当参与 LOCK 的 CPU 核数为 30 时，性能下降至约 7.6 万 QPS。

当限制在 15 个核时，性能保持在约 10 万 QPS，且无法通过 cgroup 多实例进一步提升。

总结

通过分析 SyncRepWakeQueue 和 SetLatch 函数的性能瓶颈，发现同步互斥锁 (mutex) 是主要问题。在使用 cgroup cpuset 将 CPU 核数限制在 15 个以下时，性能得以提升。进一步测试表明，多实例部署无法消除 mutex 的影响，性能提升有限。因此，优化 SyncRepWakeQueue 和 SetLatch 函数的性能至关重要。

转载地址：http://uixfk.baihongyu.com/

你可能感兴趣的文章