Re: Cpu usage 100% on slave. s_lock problem. - Mailing list pgsql-performance

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.