Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bl4OY-00035U-Jc for pgsql-hackers@arkaria.postgresql.org; Sat, 17 Sep 2016 01:26:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bl4OX-0001DQ-1Z for pgsql-hackers@arkaria.postgresql.org; Sat, 17 Sep 2016 01:26:17 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bl4N1-0007uW-QA for pgsql-hackers@postgresql.org; Sat, 17 Sep 2016 01:24:44 +0000 Received: from mail-wm0-x22d.google.com ([2a00:1450:400c:c09::22d]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bl4My-0001qL-Et for pgsql-hackers@postgresql.org; Sat, 17 Sep 2016 01:24:42 +0000 Received: by mail-wm0-x22d.google.com with SMTP id 1so69144710wmz.1 for ; Fri, 16 Sep 2016 18:24:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to; bh=0PK5laRsEsF2mGNPkS/n7sONefEmluaC94x9l/lApOo=; b=YIK8flLMLQpANCzbZ/RdsWSErTgupxWeUsoZB/eTQiObMG3SrcLp4kaU+tZ0pxyXn3 Frb8qdvdKE3iXBzzp79AAcBa2Qj39TPdSENujJTDhoxDaUcRqLpVLvBsXaDf/qk52gwU E7s3aX1WT5wqdXpsrw/HPHtCWX9mxNzF3ctcUuAVwwH3EixpFTtuLxred1Xo2RsEZ5DZ GosJmV2aMzpBNWyeIACLxVXQa/B+rQyW+Gzmlso3+qkf2b62rusyAk+zi6iiSB/cAb01 yCdnwnrcFK9tHQ2W1I3twzhM5KR52L5xCJfNcDNDAEe6M6jfC/jXOMrIFSx2hR72ZLfK fhKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=0PK5laRsEsF2mGNPkS/n7sONefEmluaC94x9l/lApOo=; b=bjz/U1TffX1uE0S+TVmxdMYIgB5/1hTzfuBB6Qw++JhwALCzmrZ3O+lRmhsASUx1GE kJ/BhTh6H6d/Y/bAKZf24/yT3AN3o4RVAESiv/R358xmrY41jPtBPLPpdr/eWxHCIzBH dW80rvNE9bcDPFxVVjjWqlZ+O+wusi856VsFpnQg6eHQ3JjqgbNxDKLclM1Yc96XDYik LA2aG6kxaoFR3cdIg5dzFqF6uM1kwblkLOmykQbjUDhU9MH73EE98NrswktY9WFuxqbI xGTDl93pRYV8N0VGXSzz8S44Q+UMnMIQC0jIZ03JuChtBHuQNMizY0XoiVAPneGDJTG6 UzyA== X-Gm-Message-State: AE9vXwOkrGRxhohLR8b7imfBDTEiZHtCvsFTWs1SmAa7K/F5+/ftRvulbiI0NlUCHioVGLiBhv+EB0HAAO2b/zfCEIUguzyFEFgeKoFDv05N5H07TTJFvQXNO9wUjmI+ivGHiD8tUIzOcy8kFCWZi4ZuYlHhHFgTJuvW9QKIWQ6HA9aXKi4/Ua+WNcJxsEGIra+iSvueiSvo X-Received: by 10.194.101.131 with SMTP id fg3mr3102029wjb.33.1474075476816; Fri, 16 Sep 2016 18:24:36 -0700 (PDT) Received: from [10.137.2.17] (ip-89-177-44-58.net.upcbroadband.cz. [89.177.44.58]) by smtp.gmail.com with ESMTPSA id lz5sm10720711wjb.24.2016.09.16.18.24.35 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 16 Sep 2016 18:24:35 -0700 (PDT) Subject: Re: Speed up Clog Access by increasing CLOG buffers To: Dilip Kumar , Robert Haas References: <20150903114137.GE27649@awork2.anarazel.de> <20160330230914.GH13305@awork2.anarazel.de> <20160408153054.hgubabeycowlu3tc@alap3.anarazel.de> <8bab43dd-bc76-0c41-950e-8101269dbf4b@2ndquadrant.com> Cc: Amit Kapila , Andres Freund , pgsql-hackers From: Tomas Vondra Message-ID: <3bb2699f-fe51-7419-b42b-9ad5bfd0d506@2ndquadrant.com> Date: Sat, 17 Sep 2016 03:24:33 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101 Thunderbird/46.0a2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------AACA91839A5A41629B420336" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------AACA91839A5A41629B420336 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 09/14/2016 06:04 PM, Dilip Kumar wrote: > On Wed, Sep 14, 2016 at 8:59 PM, Robert Haas wrote: >> Sure, but you're testing at *really* high client counts here. Almost >> nobody is going to benefit from a 5% improvement at 256 clients. > > I agree with your point, but here we need to consider one more thing, > that on head we are gaining ~30% with both the approaches. > > So for comparing these two patches we can consider.. > > A. Other workloads (one can be as below) > -> Load on CLogControlLock at commit (exclusive mode) + Load on > CLogControlLock at Transaction status (shared mode). > I think we can mix (savepoint + updates) > > B. Simplicity of the patch (if both are performing almost equal in all > practical scenarios). > > C. Bases on algorithm whichever seems winner. > > I will try to test these patches with other workloads... > >> You >> need to test 64 clients and 32 clients and 16 clients and 8 clients >> and see what happens there. Those cases are a lot more likely than >> these stratospheric client counts. > > I tested with 64 clients as well.. > 1. On head we are gaining ~15% with both the patches. > 2. But group lock vs granular lock is almost same. > I've been doing some testing too, but I haven't managed to measure any significant difference between master and any of the patches. Not sure why, I've repeated the test from scratch to make sure I haven't done anything stupid, but I got the same results (which is one of the main reasons why the testing took me so long). Attached is an archive with a script running the benchmark (including SQL scripts generating the data and custom transaction for pgbench), and results in a CSV format. The benchmark is fairly simple - for each case (master + 3 different patches) we do 10 runs, 5 minutes each, for 32, 64, 128 and 192 clients (the machine has 32 physical cores). The transaction is using a single unlogged table initialized like this: create unlogged table t(id int, val int); insert into t select i, i from generate_series(1,100000) s(i); vacuum t; create index on t(id); (I've also ran it with 100M rows, called "large" in the results), and pgbench is running this transaction: \set id random(1, 100000) BEGIN; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s1; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s2; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s3; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s4; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s5; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s6; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s7; UPDATE t SET val = val + 1 WHERE id = :id; SAVEPOINT s8; COMMIT; So 8 simple UPDATEs interleaved by savepoints. The benchmark was running on a machine with 256GB of RAM, 32 cores (4x E5-4620) and a fairly large SSD array. I'd done some basic tuning on the system, most importantly: effective_io_concurrency = 32 work_mem = 512MB maintenance_work_mem = 512MB max_connections = 300 checkpoint_completion_target = 0.9 checkpoint_timeout = 3600 max_wal_size = 128GB min_wal_size = 16GB shared_buffers = 16GB Although most of the changes probably does not matter much for unlogged tables (I planned to see how this affects regular tables, but as I see no difference for unlogged ones, I haven't done that yet). So the question is why Dilip sees +30% improvement, while my results are almost exactly the same. Looking at Dilip's benchmark, I see he only ran the test for 10 seconds, and I'm not sure how many runs he did, warmup etc. Dilip, can you provide additional info? I'll ask someone else to redo the benchmark after the weekend to make sure it's not actually some stupid mistake of mine. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------AACA91839A5A41629B420336 Content-Type: application/x-compressed-tar; name="clog.tgz" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="clog.tgz" H4sIAD+W3FcAA+1cbW8byZH2V/NXzDnC3eZu2er3l/U5gC82EgNZ78L23n05 IJEp2mIskVqS2gT59Xlqupsrz/RMkwK8ueCmAUtasbqnurqqnqe6Rrtd7u6u 9zu22P306EsNjmGtpu/CGX7/O4ayxqlHQjrrBIayj7iQXLlHDf9iGt0bd7v9 xbZp8H25HZcb//yfdOxWf1s2txf7xVWzvVs3i+vVcr3fNfvb3Wx3c3F93dx+ nAdm5zcXu/1y24hGyUYEbTULynKuB6SsbqQyQTKFUx2UEtI3wvkQmFTOejck FvBMaa1jBn7keVFMJs2UYtIrGfyAVKuZ9YEFoZwsP1ImzZQ2jEvIiSEx0kxh JSaMdby8msqaeeaMN6ZsDRU148ExzqGYGZBKmhnBvLFKlrepsmYuMC+CE6Eo pqNmEGCkvixbVreayWACM15iyQGpdJresqCl8OVt6qiZFoIzq7APVRQzUTNp BRYzZmAxEzUzFqcpTTDlYzJJM5wADoAbVd6AOWgWmOIy2LJmNtlMwjW8VdoO SLWnKRxnUsB3y55hs82kZkiQxg89Mp6m55ohToas4ZLNhGBaGKXL23RRM6zD cKIIqAGppJlzDA8MblCs1cxKhQ1IO3CaPp+mY4h258p+5lNswhuFCX7Az3zU DEpx5qULqmxanyPAG+zTaVV+ZoiaIZUx52zgckCq9TM4D/NIGnporeRnHO5o oP+gWDzNNlN5r8uRLngymjFMcwTUQBLlUTeLHSgEnS6fAMTSgXJPuSrI8k5J rtUOMcekQ+rraPdxu7m7nd/dXl7slxkNKM9zpHkvR2Xb03UKEc0NonVUNipr g2KUNw0fF474EDiEheoa6jPhjBJcMCu9GNU4YUUbAhp2G7NERgwhKRV53k1F XeE200it4XW6h1WfCSf0UNqxEKzXblS21Vg7gwymgxpfNyMJYKnlZ+PCUWPA DiwSuonqM+GEKlZQhvSum/s6shH1FGfa63El9MErDLPcqVGzZZyR0MJqC8cf EU5og+Bh3mlnx7wiYo4AjrNgcXZ2VDZqDGhlICYw3Lhw1NhL5gTsNuZCNmdU y4yGX4z5ZsIisAomeT8tdWSzxg78wotudu0KxxxLvgmTjHpFQidjkX9kGA8Q lxgHqAQMYbqMryMbNTbeM41MxMcOOuGV5go2BnrYsdNLqMWNZR4zRpNQwi4H 7EKGM6NekRGMI7tpB8Y3dnj+4MeksdOjKyc002Aw3OJYxmWjjZWJPGzMNTOy EQBKH+woKBzwzSL+rTFdtP88efOckIkOIg2MnV7GOgfktERexvztgHhgHAwU 0clRxMm4BwcijoXI6kpfrO+uL7bz683i02r9MWOfJSIOU+te5urJRzTRwFXt RJddFuSTXzuCNdej8KUJqU4C5wdyd8up3gT5M1/EKZl+huzJRx9HRpUcnLBm oYyJcAIGaojKpj4hMnBNgWEK3KMzQeUdgMtyEMeep/XkI8PEDrRwhVTRk0+o jtIHBw0qVp8Q8wsyF3zOqB6SdCbkCsyD6ITAuzS7IN9GgOeIACVF1aQZMw2S GCcaWfOiQ30GIiM5D92atjchYadQmkkUPLq241SzSZBkF4Lo40BPPscB3Brq dMl3aUKq4+AVQDteO7SEpdx52BTMtPaAhKfEIQUKEF7bQcZUg9TCtdHd65DS hORFHpGJdFc7A5freFC5oGSoPSDVgAY1oIBdq5GcMVYR0SeMrSuUb0Y8WBL8 tKaRzzswDP+EqD0g460keSm6dVtBPmKu9o5xZK8+3PUnRC8CMHFU+d1qtDch YS9HWWpQh4WaVyT89cBf/DO6Lh/PQKOgBQ3XfdbQn5DOQNMTuK89IWExKlLH DCqZaqQlPAaxRTXlQPiPmJBCQXmiPqiUj5gRd4EyRVPmrnjSfL2ZLzbr/XK9 b3+VcRpITczM94uG6vx4kwMromiWfTZTnZ9oCCgnWJbuFxf1BVpcN1LTlZ+2 FZv1FpDpWL1gjoqsUxWQiSk6CRjnRlX8qDA/ZQ+EEiLD92lbfYEYiwoVDPhc 98akukDkCd6CSnEfTPeO5Ij50QJSgllJ369oq/MTE0JkocisJtzSAhERrETp y9XJC+hkAY5aBoXHyV6ceAesBxDWVdQuzG8t4AEFLHiua1SttEBrAQ1EsTiK fsVbWcAkCwTkZ+lFjU4X5rd1P2pG8kFdqw8K81MeAMQBHnq30McsEKPAUQUB SnqqBWyyAN2I8gdkUptuGmEBK3WvQ3HE/HQFaUAVQ1DiVBPaA7MzTGm6iD1x gciTfJCKeXCBGtMrzI95QHPqhJiT81DmUZquuxDHNTZeWiDW1mCGABR7sg9E nuXpnsIbMINT0dDnTOiYtbZaAxbm59uaAAsYq05NxfleH6fHNEc9faoFQkJD 6pIYSognz4+8zcCCAXX/qUcYDreYnqgr4uD0BVoLOE1o6HWNjPcJReJ5ru0s WMtP1SDfw3Akc8e97PZWjlkgwgFHQWGRkk4OhMwLtQShD2D1iEXIf1x2+xnp hgAsG7mb7q3LUpHjoQpHdUNdoLJUVFqACHmPmmBosVYzSlMMPHdILLMy7lCz Sa3tgFTscDqKVxyVG5CK1Y1CYuNUIvMhsVYzqQIzCF5d1iyxJbgmo/rHlK2R OJGltYAFofzIxHzoLpI5oY0VQ2Itx5XUI5d+6AQSi1GWbvRU8GVr6HRDb6mP C7ZZtmxiJJK26RDPAweQeAdyvmRI+DaUbZbYhUFWcJIjPw9ItTZri1COHZR9 NjEFcC26VkeaHXpkzAOCK5gWtLh8AvdQ33kx9EybbOYC3ThIpQekombOUQ0q tS3b7NAXQMljbVuqlsQSGlulYQ2lfdk1IuYK6soHzrkvPzIhK9dWgOAqPuBn 7lBJIPs7VPVlm/lsM+pVOMfLz4xYCE4foL+xrmwznzUTCqQFSbNsjMM9vwS3 QToYWC2hF2oQVMMyDJxTxCiOoETJaZ0r6x+yZp4KO3qjYUgs1h7QzFjpZXm1 jCoahTZqPJSLQ2LRatJin0YM6JYRghvKyZIDLQflWu3oLssaBTj6XK7Ur0Y2 YnSJBewek413G6j5gAjIhqOyCc4MNkUVekW4zcIAEOorY3MjwgklnCKkRfYU o7KpTtfMQgs3ZomMGIJ6UCjNu6m01K8WUrfvJnE5pkVCDy0R1ch01ozKJj5N JZF03TMu9auVposg8Fc+rkTCE0C1gPMYPSKcUMVJwRDM1oydR8KW4AWCGQg6 ZraEMPR2Cb0co8KYxglnFBccdvOim3MK/WpojE8UEsu4bOyZSbIE2JIclU0Z ATWqpuqiItxqLDknmOJqTDihkKPOJKi3G7NbqjM5kRlkcju+btIY6URoUAc1 LpzeCRJM8gD2PiKc0UkAA4ToAUqhXy00vc9hOcBlVDZqLK1kHDlXjAXIAa/a xBKkGYuQhFoGxSWYrh7VOGEXXUoaKiXGLJERDMYFhQD9HV843dPA33RQqstY C/1q5Ai6UXBcjaXNhGkKdA1Fm+JjGmdkEwQK9PrgmMbhcKsAWm/0eORllKPX oww4vhk7vYx1FCFA7DBquAPiIZRQ13Hvx/z+Z9yD44Ni+X6olvvVHCYBhCve C5Ryv1ro9t1Qes2uJp96pZRyqV3dc9Vyv1oQYcDZ9wjDQL/aA9gYdaL6uFns V9MrdgwpkvfxsNyvtloAQGH/0HOEcr+6PV8QBN1PPsV+tScqKEASuvR5oF8N NLBM6n69MNSv1tAEpY91pq5QPAMUtQy+bLrseqBf7cHbGBEYXTuDdG/cvpEC atiti4b61SgBqbkKvtMLmXK/GtwL6C2dEr0cUuxXe9vyE3oztCofz8CCKJFN awolDBVUiQhDjlqf0O4gIO49t7JLwwf61R58gklBsV+VjzuQWD8AGWo7yJja XnHRazw1kyZcbd+Ppfvp6g5cvmGAF4FyidoO0r2raW9UgLL19TNiUXMVGFTb gctVPjggSnxR1ShhraQ7BgOmUntAujcVlroPosDNy/1qDjLBwIuBkPUJ8TYA VYUIup4dM/YidykNElnLviHdEICV0d1L9+5oqF/N21c7Feh9ze0SDoOiEh64 Xp052K9G5YDkxXUdoiIeA6MQmnDsPrMc6FdzelUPx8C7dyzD/Wp8pfsKMNLx GUP9amnodhfMrGK2gX41YhEQoLjpXmsd3a+mdi8VQ7VzG+pXC6MD/e0GSpoT F5CZx2pmfFC1bDLUrzaOGCj9ccvJ81O3VhBtw0IViBjqV6PQ8SwE+mujExfI N7DEoVF59Kul4/rVQB9Kf9XAGOpXaw0nloD5k5048wp6VduBJZxsgXzTCy82 GuzhVB9KvANuTLeeunvLdGy/WklkL+7opYHTF0hvbSCOQRBrEDTQr0bSodfz jXOVfDvQr5bSoiZFFujfVBzXrwYdhriFE1WS5VC/Wmp645VeYzvVAonn8Pbm XVRRf6hfrZ1mwUhVY3pD/WpOr31IpLEa9RvqVyu4IQOGhxomD/WrQVKZk06e HEXpXoLe8UbREfr3ncf1q7kBuTaooLrX5Uf3q42jt7fozb4TF8j9auygfYPp 1DNM9xyW3u3UnvdvZo7rV3N6P13T/fKpUZB5mXfgHChDa2XxUL+a/lbIgIZ0 O2NH9qtBYVD3AxD8qT5w6BJQtxikoH+hc2S/2iCXBxRopxOKfOfCNfNge/7U RJDvYQw3qKO16t+iHdmvBp2iK7AHUILDfQ1oKr38Ato9+0f/6Xc7tnfr+X65 27Pd1Rd7xvjf/4NhcnP4+39k60dcoIZU09///xLjV/9y/n61Pn9/sbuazd4+ /++XL/74/fN3v392Rl9nL56/e/7i1Ztn55cX+4vz3fLTZn35493F5Xm8jFxc bz7OZfvh7M0Pr98+E3z22z+8evn63dtnT1TLw9u4CfLJ7MUPb56/e/Xd62cK rv/2+bff/+HV6989A7sSs9mHzba5bFbrTnOvdPE5VHgdGYhPm8vNbPZ4ubja NE/g+2vIfNOcXT6hX/71drPdN+32z682N8v7Gz67JDt9c/aziTDjV83Nxadl s7vbLpv91XK7/Ldds97sr0ihtPjs8afV9XX7astmt/+4Xe6a3zTnl8ufzqHn dSN/86/Y/+PtTTN/86E5S/bGb24+Xa62UGz2eHFJ32aP/3K1Wlxhn3/Efj6s PmKZ9jcMZ5CX+flDfBp/6n68v27mLw4Pgs1Xe8jSt/uSi9uGsfOs8Y/XjBa7 r15hqTm9vdMuMv9LQ0G1n5EgZjfzRfNkt7xeLvbNvzcftpsb2sZuud/DQLsn 9y2Tf/mZMtslTv7yfUN5Ki9JPzf/SVqS7nNyQUa/j3uJ//3Z1t8v1zAfNIFf zv/cfv1A02PyIyXfYS/JSZv5Oj6hcFbkrFty1j/tlj+i2j0jz/9Tcqz20wV9 epYCIX+QfK45/C82zhbt/3HjbPuk/TgbanG1XHy63azW+1GPeXx/S1gKW6Kv R20paTA/W8zPtgcrPaVVLzfrJf2Qv8P5GCuf926/uZ3NSPD/BpJN4yGjjXz4 ypd8RgX/QQDkAf/BsQn/pbUT/v8SIybX5m6NNPBxednsL95fA8y+WhEe779u frq4ph9+/XS2WsMCe/qPTbNvUjpffd2sYkb/uFwvt1gKeX27Wu6+El+L9nB/ 3ey+WmH6TxeLu7ubZv90lh65Wl8u/9ps1u3DIPCPtsT/z5Gx4ks+oxb/zt6L f/yM+FdCT/H/S4z/BeNqEOzgzZebG0Rtk8J2Nvuvl7979frp7IfvgfgvEfJv X75r08Gz9ut/gPr8z+9fvnlJs58136wun7b1w/ffvXr9rtmJh06UD52oHjpR P3SieehE+9CJ7qET/dPZb7/79ttX76Y8O41pTGMa05jGNKYxjWlMYxrTmMY0 pjGNaUxjGtOYxjSmMY1pTGMa05jGNKYxjWlMYxrTmMY///g7uf4C1QB4AAA= --------------AACA91839A5A41629B420336 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------AACA91839A5A41629B420336--