☰

糟糕的 PostgreSQL 锁定性能

tag-icon • tag-icon performance postgresql freebsd
糟糕的 PostgreSQL 锁定性能

最近，我注意到运行数据库服务器几个月后，PostgreSQL 锁定性能下降非常严重。这几周网站的负载增加了很多，但速度却越来越慢。

$ psql webspace2_db
psql (9.0.1)
Type "help" for help.
webspace2_db=#

数据库服务器运行的是 FreeBSD 8.1 + PostgreSQL 9.0.1

FreeBSD Moncalvo 8.1-RELEASE-p2 FreeBSD 8.1-RELEASE-p2 #1: Mon Jan 10 13:02:48 MYT 2011     hailang@Moncalve:/usr/obj/usr/src/sys/Moncalve  amd64

服务器总内存为 4GB

Moncalvo# cat /var/run/dmesg.boot | grep memory
real memory  = 4294967296 (4096 MB)
avail memory = 4101955584 (3911 MB)

内核配置中总共有 3GB 的共享内存

# Shared Memory
options          SEMMNI=256
options          SEMMSL=128
options          SEMMNS=32768
options          SEMMAP=512
options          SEMMNU=256
options      SEMOPM=128
options          SHMMNI=512
options          SHMSEG=256
options          SHMMAX=3221225472
options      SHMALL=3221225472
options          SHMMAXPGS=786432

这些是关键的 postgresql.conf 设置

Moncalvo# cat postgresql.conf | grep shared_buffers
shared_buffers = 512MB          # min 128kB

Moncalvo# cat postgresql.conf | grep effective_cache_size
effective_cache_size = 3276MB

Moncalvo# cat postgresql.conf | grep work_mem
work_mem = 256MB                # min 64kB
maintenance_work_mem = 128MB        # min 1MB

听用户反映最近网站越来越慢，我在另一台服务器上对网站服务做了大量调优，但并没有取得有效的性能提升，所以我想问题可能出在数据库服务器上。于是我记录了慢查询，发现大多数查询都带有锁定机制，有些查询耗时非常可怕。

LOG:  duration: 4768697.255 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4739020.976 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4709376.119 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4679438.894 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4649714.811 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4619931.184 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4590323.188 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4560627.214 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4530796.297 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4501178.286 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4471515.579 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4441832.934 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4410774.012 ms  statement: SELECT pg_advisory_lock(93690)
LOG:  duration: 4382435.595 ms  statement: SELECT pg_advisory_lock(93690)

非常感谢任何帮助和建议。

答案1

我不是数据库专家，但看起来咨询锁在您的应用程序中的某个地方。

PostgreSQL 提供了一种创建具有应用程序定义含义的锁的方法。这些锁被称为咨询锁，因为系统并不强制使用它们 — 是否正确使用取决于应用程序。

和

与 PostgreSQL 中的所有锁一样，任何会话当前持有的咨询锁的完整列表可以在锁系统视图。

编辑：

调查 Moodle源代码我/moodle/lib/dml/pgsql_native_moodle_database.php刚刚发现了一些东西可能有趣：

public function get_session_lock($rowid) {
    // NOTE: there is a potential locking problem for database running
    // multiple instances of moodle, we could try to use
    // pg_advisory_lock(int, int), luckily there is not a big chance
    // that they would collide
    if (!$this->session_lock_supported()) {
        return;
    }

    parent::get_session_lock($rowid);
    $sql = "SELECT pg_advisory_lock($rowid)";
    ...
}

相关内容