On 2013-09-17 08:18:54 -0500, Merlin Moncure wrote: > On Tue, Sep 17, 2013 at 6:59 AM, Andres Freund <andres@xxxxxxxxxxxxxxx> wrote: > > Hi, > > > > On 2013-09-17 17:55:01 +0600, Дмитрий Дегтярёв wrote: > >> 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. > > > > What you describe is normally an indication that you have too many > > longrunning transactions around preventing hot pruning from working. > > Do you think it's worth submitting the lock avoidance patch for formal review? You mean the bufmgr.c thing? Generally I think that that code needs a good of scalability work - there's a whole thread about it somewhere. But TBH the theories you've voiced about the issues you've seen haven't convinced me so far. If you can manage to prove it has a benefit in some case that's reproducable - why not go ahead? Quick question: Do you happen to have pg_locks output from back then around? We've recently found servers going into somewhat similar slowdowns because they exhausted the fastpath locks which made lwlocks far more expensive and made s_lock go up very high in the profle. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
