Re: Speed up Clog Access by increasing CLOG buffers - Mailing list pgsql-hackers
From | Amit Kapila |
---|---|
Subject | Re: Speed up Clog Access by increasing CLOG buffers |
Date | |
Msg-id | CAA4eK1LyR2A+m=RBSZ6rcPEwJ=rVi1ADPSndXHZdjn56yqO6Vg@mail.gmail.com Whole thread Raw |
In response to | Re: Speed up Clog Access by increasing CLOG buffers (Dilip Kumar <dilipbalaut@gmail.com>) |
Responses |
Re: Speed up Clog Access by increasing CLOG buffers
|
List | pgsql-hackers |
On Mon, Oct 24, 2016 at 2:48 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote: > On Fri, Oct 21, 2016 at 7:57 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote: >> On Thu, Oct 20, 2016 at 9:03 PM, Tomas Vondra >> <tomas.vondra@2ndquadrant.com> wrote: >> >>> In the results you've posted on 10/12, you've mentioned a regression with 32 >>> clients, where you got 52k tps on master but only 48k tps with the patch (so >>> ~10% difference). I have no idea what scale was used for those tests, >> >> That test was with scale factor 300 on POWER 4 socket machine. I think >> I need to repeat this test with multiple reading to confirm it was >> regression or run to run variation. I will do that soon and post the >> results. > > As promised, I have rerun my test (3 times), and I did not see any regression. > Thanks Tomas and Dilip for doing detailed performance tests for this patch. I would like to summarise the performance testing results. 1. With update intensive workload, we are seeing gains from 23%~192% at client count >=64 with group_update patch [1]. 2. With tpc-b pgbench workload (at 1000 scale factor), we are seeing gains from 12% to ~70% at client count >=64 [2]. Tests are done on 8-socket intel m/c. 3. With pgbench workload (both simple-update and tpc-b at 300 scale factor), we are seeing gain 10% to > 50% at client count >=64 [3]. Tests are done on 8-socket intel m/c. 4. To see why the patch only helps at higher client count, we have done wait event testing for various workloads [4], [5] and the results indicate that at lower clients, the waits are mostly due to transactionid or clientread. At client-counts where contention due to CLOGControlLock is significant, this patch helps a lot to reduce that contention. These tests are done on on 8-socket intel m/c and 4-socket power m/c 5. With pgbench workload (unlogged tables), we are seeing gains from 15% to > 300% at client count >=72 [6]. There are many more tests done for the proposed patches where gains are either or similar lines as above or are neutral. We do see regression in some cases. 1. When data doesn't fit in shared buffers, there is regression at some client counts [7], but on analysis it has been found that it is mainly due to the shift in contention from CLOGControlLock to WALWriteLock and or other locks. 2. We do see in some cases that granular_locking and no_content_lock patches has shown significant increase in contention on CLOGControlLock. I have already shared my analysis for same upthread [8]. Attached is the latest group update clog patch. In last commit fest, the patch was returned with feedback to evaluate the cases where it can show win and I think above results indicates that the patch has significant benefit on various workloads. What I think is pending at this stage is the either one of the committer or the reviewers of this patch needs to provide feedback on my analysis [8] for the cases where patches are not showing win. Thoughts? [1] - https://www.postgresql.org/message-id/CAFiTN-u-XEzhd%3DhNGW586fmQwdTy6Qy6_SXe09tNB%3DgBcVzZ_A%40mail.gmail.com [2] - https://www.postgresql.org/message-id/CAFiTN-tr_%3D25EQUFezKNRk%3D4N-V%2BD6WMxo7HWs9BMaNx7S3y6w%40mail.gmail.com [3] - https://www.postgresql.org/message-id/CAFiTN-v5hm1EO4cLXYmpppYdNQk%2Bn4N-O1m%2B%2B3U9f0Ga1gBzRQ%40mail.gmail.com [4] - https://www.postgresql.org/message-id/CAFiTN-taV4iVkPHrxg%3DYCicKjBS6%3DQZm_cM4hbS_2q2ryLhUUw%40mail.gmail.com [5] - https://www.postgresql.org/message-id/CAFiTN-uQ%2BJbd31cXvRbj48Ba6TqDUDpLKSPnsUCCYRju0Y0U8Q%40mail.gmail.com [6] - http://tvondra.bitbucket.org/#pgbench-300-unlogged-sync-skip [7] - http://tvondra.bitbucket.org/#pgbench-3000-unlogged-sync-skip [8] - https://www.postgresql.org/message-id/CAA4eK1J9VxJUnpOiQDf0O%3DZ87QUMbw%3DuGcQr4EaGbHSCibx9yA%40mail.gmail.com -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Attachment
pgsql-hackers by date: