Re: Cpu usage 100% on slave. s_lock problem.

Hello.

We have not been able to reproduce this problem on a test servers. Use this
patch to production servers do not dare.

In the course of studying the problems we have identified that many queries
are executed on the slave several times slower. On master function
heap_hot_search_buffer execute 100 cycles, on the slave the same query with
the same plan function heap_hot_search_buffer execute 2000 cycles.
Also, we were able to reproduce the problem on the master and detect that
there s_lock of slow queries.

We have solved this problem. A large number of queries used 4 frequently
changing index. In these indexes, 99% of the dead tuples. Autovacuum and
even VACUUM FULL these tuples can not be removed because of
autovacuum_freeze_max_age.

We've added cron that 2-3 times a day, performs CREATE INDEX CONCURRENTLY
idx_name_new; DROP INDEX CONCURRENTLY idx_name; ALTER INDEX idx_name_new
RENAME TO idx_name; for this 4 indexes.

As a result s_lock not exists in listed perf top.

2013/8/29 Merlin Moncure <mmoncure(at)gmail(dot)com>

>
> so -- are you in a position where you might be able to test this patch?
>
> merlin