Desempenho de bloqueio do PostgreSQL horrível

Question

Recentemente, notei uma queda muito grande no desempenho do bloqueio do PostgreSQL após a execução do servidor de banco de dados por um par de meses. A carga do site aumentou muito essas semanas, mas a velocidade está ficando mais lenta.

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

O servidor de banco de dados está executando o 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

A memória total no servidor é de 4 GB

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

Totalmente 3GB de memória compartilhada tem direito na configuração do kernel

# 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

Estas são as principais configurações do 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

De acordo com os usuários, o site está ficando mais lento e lento atualmente. Eu ajustei muito os serviços da Web em outro servidor, mas sem uma melhoria de desempenho eficaz, então estou pensando que talvez o problema esteja no servidor de banco de dados. Então eu registrei consultas lentas e descobri que a maioria delas está com mecanismo de bloqueio, e parte do tempo consumido é horrível.

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)

Qualquer ajuda e sugestão são muito apreciadas.

    

Answer 1

Não sou especialista em banco de dados, mas parece ser um uso inválido de comunicado Bloqueia em algum lugar da sua aplicação.

PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.

e

Like all locks in PostgreSQL, a complete list of advisory locks currently held by any session can be found in the pg_locks system view.

Editar:

Olhando para o código-fonte do moodle , /moodle/lib/dml/pgsql_native_moodle_database.php Acabei de encontrar algo que pode ser interessante:

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)";
    ...
}