From hkocam@googlemail.com Mon Oct 4 15:16:02 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B259A1337BB0 for ; Mon, 4 Oct 2010 12:16:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 77823-02; Mon, 4 Oct 2010 15:15:51 +0000 (UTC) X-Greylist: delayed 00:23:57.505887 by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f174.google.com (mail-gx0-f174.google.com [209.85.161.174]) by mail.postgresql.org (Postfix) with ESMTP id 0A80E1337BAC; Mon, 4 Oct 2010 12:15:50 -0300 (ADT) Received: by gxk9 with SMTP id 9so1668989gxk.19 for ; Mon, 04 Oct 2010 08:15:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:mime-version:received:from:date :message-id:subject:to:content-type; bh=C/z9bhl9bn/Nn25bheHEesHE0O0ac6CusskxOHG2Rrs=; b=B4aWYBITA4xzdJRhGMOJgVZo6UbDyEDsyPKmTik3BcYEBzsT60018xjD2sSlKw+mBq LCz5M+HDkEKD8UwQuNsADSCKS0UTUpWDQaVHIUAhsaZYb6u4m7mNmmfSLA4nPE3sRoJy lhP93yVqVrpAg8kpwyA4SBK3haiDRXNJbOTA4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:from:date:message-id:subject:to:content-type; b=D52HT4sRDg0wH8FoD3zE12sshSYPRtvA5I/bS0YLf5IU84YVq0SZi5VfPB0QTBMwBF sJ0/pPWzzwNrcmE4AK1H2eEhwkVQKHUoswItjB6xgsOsbMtWiGCyULCsJ9Rr6OBfzrzP BmiDuC+0t3OCUKHFIfK47HhaKivMDpE2OtrXE= Received: by 10.90.79.20 with SMTP id c20mr4835545agb.36.1286203483899; Mon, 04 Oct 2010 07:44:43 -0700 (PDT) MIME-Version: 1.0 Received: by 10.90.35.15 with HTTP; Mon, 4 Oct 2010 07:44:23 -0700 (PDT) From: Hakan Kocaman Date: Mon, 4 Oct 2010 16:44:23 +0200 Message-ID: Subject: MIT benchmarks pgsql multicore (up to 48)performance To: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6509bc8c447d40491cb9589 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/161 X-Sequence-Number: 171201 --0016e6509bc8c447d40491cb9589 Content-Type: text/plain; charset=ISO-8859-1 Hi, for whom it may concern: http://pdos.csail.mit.edu/mosbench/ They tested with 8.3.9, i wonder what results 9.0 would give. Best regards and keep up the good work Hakan --0016e6509bc8c447d40491cb9589 Content-Type: text/html; charset=ISO-8859-1
Hi,

for whom it may concern:
http://pdos.csail.mit.edu/mosbench/

They tested with 8.3.9, i wonder what results 9.0 would give.

Best regards and keep up the good work

Hakan

--0016e6509bc8c447d40491cb9589-- From robertmhaas@gmail.com Mon Oct 4 17:13:45 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id F03741337B82 for ; Mon, 4 Oct 2010 14:13:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 25037-06 for ; Mon, 4 Oct 2010 17:13:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id BC85A1337B8B for ; Mon, 4 Oct 2010 14:13:38 -0300 (ADT) Received: by iwn5 with SMTP id 5so7146124iwn.19 for ; Mon, 04 Oct 2010 10:13:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=vI9BWMCWGU21Kf2eD33NL3dxY4PAo3ZgbiKnCBXsvMA=; b=iRsZQScB/BarFF8M/eUrzDYiH5Mh4R6R+Qvg+qi5sf+U3UFQzr6einf+bZFoS0Weq8 8PIAuRez21QuwnR02IBX+f89B2JcxzKB3A7aijQVu0VL8rsTeA3ki1XaigxJMWnFjZDZ lvptbUWwpb3y5MgOtCTleNDLOPyVfHtNt0J+w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=Kuq6vju9+DjQoUXpQxCHEfGwII6EOoaogtvI46HxU0P2Sg4k/Qeku4q1NM/RlNeF/Y J4/GhQqrjSCFeAe8oiW+c0tgooGb8wl3eSZH/2ZqNQiCU4ObR3hgqng3JbEcM0eWJVdP yP+uEMNRJHePJiOjWvb70SW5o8SysIQQhJ9cM= MIME-Version: 1.0 Received: by 10.231.191.74 with SMTP id dl10mr10479598ibb.157.1286212417179; Mon, 04 Oct 2010 10:13:37 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Mon, 4 Oct 2010 10:13:36 -0700 (PDT) In-Reply-To: References: Date: Mon, 4 Oct 2010 13:13:36 -0400 Message-ID: Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Hakan Kocaman Cc: pgsql-hackers@postgresql.org Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/167 X-Sequence-Number: 171207 On Mon, Oct 4, 2010 at 10:44 AM, Hakan Kocaman wrot= e: > for whom it may concern: > http://pdos.csail.mit.edu/mosbench/ > They tested with 8.3.9, i wonder what results 9.0 would give. > Best regards and keep up the good work > Hakan Here's the most relevant bit to us: -- The =93Stock=94 line in Figures 7 and 8 shows that Post- greSQL has poor scalability on the stock kernel. The first bottleneck we encountered, which caused the read/write workload=92s total throughput to peak at only 28 cores, was due to PostgreSQL=92s design. PostgreSQL implements row- and table-level locks atop user-level mutexes; as a result, even a non-conflicting row- or table-level lock acquisition requires exclusively locking one of only 16 global mutexes. This leads to unnecessary contention for non-conflicting acquisitions of the same lock=97as seen in the read/write workload=97and to false contention between unrelated locks that hash to the same exclusive mutex. We address this problem by rewriting PostgreSQL=92s row- and table-level lock manager and its mutexes to be lock-free in the uncontended case, and by increasing the number of mutexes from 16 to 1024. -- I believe the "one of only 16 global mutexes" comment is referring to NUM_LOCK_PARTITIONS (there's also NUM_BUFFER_PARTITIONS, but that wouldn't be relevant for row and table-level locks). Increasing that from 16 to 1024 wouldn't be free and it's not clear to me that they've done anything to work around the downsides of such a change. Perhaps it's worthwhile anyway on a 48-core machine! The use of lock-free techniques seems quite interesting; unfortunately, I know next to nothing about the topic and this paper doesn't provide much of an introduction. Anyone have a reference to a good introductory paper on the topic? The other sort of interesting thing that they mention is that apparently I/O between shared buffers and the underlying data files causes a lot of kernel contention due to inode locks induced by lseek(). There's nothing much we can do about that within PG but surely it would be nice if it got fixed upstream. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company From grzm@seespotcode.net Mon Oct 4 17:39:05 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 295DC1337B88 for ; Mon, 4 Oct 2010 14:39:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 69786-04 for ; Mon, 4 Oct 2010 17:38:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from proton.planetargon.com (proton.planetargon.com [198.145.115.11]) by mail.postgresql.org (Postfix) with ESMTP id BC80D1337B77 for ; Mon, 4 Oct 2010 14:38:57 -0300 (ADT) Received: by proton.planetargon.com (Postfix, from userid 502) id 1BAD182DDBA; Mon, 4 Oct 2010 10:38:54 -0700 (PDT) Received: from [192.168.1.150] (unknown [68.64.144.221]) by proton.planetargon.com (Postfix) with ESMTP id F135382DDB2; Mon, 4 Oct 2010 10:38:47 -0700 (PDT) Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance Mime-Version: 1.0 (Apple Message framework v1081) Content-Type: text/plain; charset=us-ascii From: Michael Glaesemann In-Reply-To: Date: Mon, 4 Oct 2010 13:38:33 -0400 Cc: Hakan Kocaman , pgsql-hackers@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <1D0057B0-7151-425C-8096-56836F9AB4BD@seespotcode.net> References: To: Robert Haas X-Mailer: Apple Mail (2.1081) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/171 X-Sequence-Number: 171211 On Oct 4, 2010, at 13:13 , Robert Haas wrote: > On Mon, Oct 4, 2010 at 10:44 AM, Hakan Kocaman = wrote: >> for whom it may concern: >> http://pdos.csail.mit.edu/mosbench/ >> They tested with 8.3.9, i wonder what results 9.0 would give. >> Best regards and keep up the good work >> Hakan >=20 > Here's the most relevant bit to us: > The use of lock-free > techniques seems quite interesting; unfortunately, I know next to > nothing about the topic and this paper doesn't provide much of an > introduction. Anyone have a reference to a good introductory paper on > the topic? The README in the postgres section of the git repo leads me to think the = code that includes the fixes it there, if someone wants to look into it = (wrt to the Postgres lock manager changes). Didn't check the licensing. Michael Glaesemann grzm seespotcode net From robertmhaas@gmail.com Mon Oct 4 17:47:56 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 7E9D61337B82 for ; Mon, 4 Oct 2010 14:47:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 25799-06 for ; Mon, 4 Oct 2010 17:47:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 292901337B77 for ; Mon, 4 Oct 2010 14:47:49 -0300 (ADT) Received: by iwn5 with SMTP id 5so7179662iwn.19 for ; Mon, 04 Oct 2010 10:47:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=33yGujonvufrUNEOrXHXwceALU3SHIJqBw1B+eaeruw=; b=gwKhGJlDnNEdlz9R4ZD99t6gDworyGzVx074mCLRp/Xm+0cQjC6+OY5b7RVNGAuppZ ZzQJu+jIqeOqc0U46mf61es6KMuG5iWqOnOdVsbc1lFfYnbduhutIjr/gGatbcQAa4Fw LPe+m25TGyxh7I09Qt6cku2l5sC8WhIX3GqUI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=TMQWdkbq9hGVWN+VqW/E6ocTS91bk/fKEUfWmAenOHVeWEKyJTDJdHXLCX3Yv/l5qq b8OvulchK+u7LZ84oN17sHPq6ufgRV4GCXYl5iVHTVJgbTXOvO0OR4XiFnYdxWFvxF2t CLchlhI9mV3DUBO13vgKbDCkKl7E4PbXRwJVg= MIME-Version: 1.0 Received: by 10.231.149.3 with SMTP id r3mr10598150ibv.109.1286214468432; Mon, 04 Oct 2010 10:47:48 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Mon, 4 Oct 2010 10:47:48 -0700 (PDT) In-Reply-To: <1D0057B0-7151-425C-8096-56836F9AB4BD@seespotcode.net> References: <1D0057B0-7151-425C-8096-56836F9AB4BD@seespotcode.net> Date: Mon, 4 Oct 2010 13:47:48 -0400 Message-ID: Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Michael Glaesemann Cc: Hakan Kocaman , pgsql-hackers@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/172 X-Sequence-Number: 171212 On Mon, Oct 4, 2010 at 1:38 PM, Michael Glaesemann w= rote: > > On Oct 4, 2010, at 13:13 , Robert Haas wrote: > >> On Mon, Oct 4, 2010 at 10:44 AM, Hakan Kocaman w= rote: >>> for whom it may concern: >>> http://pdos.csail.mit.edu/mosbench/ >>> They tested with 8.3.9, i wonder what results 9.0 would give. >>> Best regards and keep up the good work >>> Hakan >> >> Here's the most relevant bit to us: > > > >> The use of lock-free >> techniques seems quite interesting; unfortunately, I know next to >> nothing about the topic and this paper doesn't provide much of an >> introduction. =A0Anyone have a reference to a good introductory paper on >> the topic? > > The README in the postgres section of the git repo leads me to think the = code that includes the fixes it there, if someone wants to look into it (wr= t to the Postgres lock manager changes). Didn't check the licensing. It does, but it's a bunch of x86-specific hacks that breaks various important features and include comments like "use usual technique for lock-free thingamabob". So even if the licensing is/were suitable, the code's not usable. I think the paper is neat from the point of view of providing us with some information about where the scalability bottlenecks might be on hardware to which most of us don't have easy access, but as far as the implementation goes I think we're on our own. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company From drkp@csail.mit.edu Mon Oct 4 18:01:11 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D05761337BD5 for ; Mon, 4 Oct 2010 15:01:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 18644-03; Mon, 4 Oct 2010 18:01:03 +0000 (UTC) X-Greylist: delayed 00:05:08.910183 by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from midnight-anchovy.ambulatoryclam.net (unknown [18.141.0.210]) by mail.postgresql.org (Postfix) with ESMTP id 2BFD91337BB5; Mon, 4 Oct 2010 15:00:55 -0300 (ADT) Received: by midnight-anchovy.ambulatoryclam.net (Postfix, from userid 1001) id 893C24AC2D; Mon, 4 Oct 2010 13:55:45 -0400 (EDT) Date: Mon, 4 Oct 2010 13:55:45 -0400 From: Dan Ports To: Hakan Kocaman Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance Message-ID: <20101004175545.GA2690@csail.mit.edu> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201010/175 X-Sequence-Number: 171215 I wasn't involved in this work but I do know a bit about it. Sadly, the work on Postgres performance was cut down to under a page, complete with the amazing offhand mention of "rewriting PostgreSQL's lock manager". Here are a few more details... The benchmarks in this paper are all about stressing the kernel. The database is entirely in memory -- it's stored on tmpfs rather than on disk, and it fits within shared_buffers. The workload consists of index lookups and inserts on a single table. You can fill in all the caveats about what conclusions can and cannot be drawn from this workload. The big takeaway for -hackers, I think, is that lock manager performance is going to be an issue for large multicore systems, and the uncontended cases need to be lock-free. That includes cases where multiple threads are trying to acquire the same lock in compatible modes. Currently even acquiring a shared heavyweight lock requires taking out an exclusive LWLock on the partition, and acquiring shared LWLocks requires acquiring a spinlock. All of this gets more expensive on multicores, where even acquiring spinlocks can take longer than the work being done in the critical section. Their modifications to Postgres should be available in the code that was published last night. As I understand it, the approach is to implement LWLocks with atomic operations on a counter that contains both the exclusive and shared lock count. Heavyweight locks do something similar but with counters for each lock mode packed into a word. Note that their implementation of the lock manager omits some features for simplicity, like deadlock detection, 2PC, and probably any semblance of portability. (These are the sort of things we're allowed to do in the research world! :-) The other major bottleneck they ran into was a kernel one: reading from the heap file requires a couple lseek operations, and Linux acquires a mutex on the inode to do that. The proper place to fix this is certainly in the kernel but it may be possible to work around in Postgres. Dan -- Dan R. K. Ports MIT CSAIL http://drkp.net/ From gsstark@mit.edu Mon Oct 4 18:08:16 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 0BC2D1337C4F for ; Mon, 4 Oct 2010 15:08:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 77766-03-8 for ; Mon, 4 Oct 2010 18:08:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 30AB31337BD3 for ; Mon, 4 Oct 2010 15:06:48 -0300 (ADT) Received: by bwz11 with SMTP id 11so3827297bwz.19 for ; Mon, 04 Oct 2010 11:06:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:sender:received :in-reply-to:references:from:date:x-google-sender-auth:message-id :subject:to:cc:content-type; bh=w+T41pwK4f5UG+UtyP/a+Gh0Hil2W6kdn2r4LKDOcIc=; b=YF7Bs1h+EcBavv2FM6KNYrYr7z9nMsQSBplwkORn1ujtNfgWiJY2lxvouAQuX3qGi0 /OkbGB9sCH9Hr2D1SpQG/j9kki2Qg7eYjxKYyQOzts0RfPM/OqDMCmsDYAFwe5elV19C M9+w4CqW8ogIOdvUPbwKRMN6Y9XUSC9+oeEes= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type; b=gwwvn9awCFzOkpxDx4xkHfsreVIqyASUtlYekZo3ektl+ZFZH5Gy92LFGHBHJ7NhIg t/ojFwp+wijC9GmQJz1kiNFxFRN4wD00Ezd/QVjP0xpHbRrlMMen9F9EA83GucVApTe7 FlQ+lOC6nSPWf+N9g78r+bcQI8UtAmzu7yLxE= Received: by 10.204.122.206 with SMTP id m14mr7338877bkr.61.1286215607888; Mon, 04 Oct 2010 11:06:47 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.68.82 with HTTP; Mon, 4 Oct 2010 11:06:27 -0700 (PDT) In-Reply-To: References: From: Greg Stark Date: Mon, 4 Oct 2010 11:06:27 -0700 X-Google-Sender-Auth: L2Vtl6o9C1g6HqLDIpoLuDkZ_2E Message-ID: Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance To: Robert Haas Cc: Hakan Kocaman , pgsql-hackers@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/176 X-Sequence-Number: 171216 Here's a video on lock-free hashing for example: http://video.google.com/videoplay?docid=2139967204534450862# I guess by "lock-free in the uncontended case" they mean the buffer cache manager is lock-free unless you're actually contending on the same buffer? From drkp@csail.mit.edu Mon Oct 4 18:35:50 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3F06C133655C for ; Mon, 4 Oct 2010 15:35:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 84638-04 for ; Mon, 4 Oct 2010 18:35:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from midnight-anchovy.ambulatoryclam.net (unknown [18.141.0.210]) by mail.postgresql.org (Postfix) with ESMTP id EF72E133616B for ; Mon, 4 Oct 2010 15:35:42 -0300 (ADT) Received: by midnight-anchovy.ambulatoryclam.net (Postfix, from userid 1001) id BE5BB4AC2D; Mon, 4 Oct 2010 14:35:42 -0400 (EDT) Date: Mon, 4 Oct 2010 14:35:42 -0400 From: Dan Ports To: Robert Haas Cc: Hakan Kocaman , pgsql-hackers@postgresql.org Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance Message-ID: <20101004183542.GB2690@csail.mit.edu> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201010/177 X-Sequence-Number: 171217 On Mon, Oct 04, 2010 at 01:13:36PM -0400, Robert Haas wrote: > I believe the "one of only 16 global mutexes" comment is referring to > NUM_LOCK_PARTITIONS (there's also NUM_BUFFER_PARTITIONS, but that > wouldn't be relevant for row and table-level locks). Yes -- my understanding is that they hit two lock-related problems: 1) LWLock contention caused by acquiring the same lock in compatible modes (e.g. multiple shared locks) 2) false contention caused by acquiring two locks that hashed to the same partition and the first was the worse problem. The lock-free structures helpe with both, so the impact of changing NUM_LOCK_PARTITIONS was less interesting. Dan -- Dan R. K. Ports MIT CSAIL http://drkp.net/ From josh@agliodbs.com Mon Oct 4 18:49:56 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B3812133655C for ; Mon, 4 Oct 2010 15:49:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 84042-05 for ; Mon, 4 Oct 2010 18:49:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com [69.36.227.170]) by mail.postgresql.org (Postfix) with ESMTP id E2A87133616B for ; Mon, 4 Oct 2010 15:49:48 -0300 (ADT) Received: from dsl081-245-111.sfo1.dsl.speakeasy.net ([64.81.245.111] helo=Sidney-Stratton.local) by glacier.frostconsultingllc.com with esmtpsa (TLSv1:CAMELLIA256-SHA:256) (Exim 4.69) (envelope-from ) id 1P2q6a-0004K5-3W; Mon, 04 Oct 2010 11:49:47 -0700 Message-ID: <4CAA21C7.1030701@agliodbs.com> Date: Mon, 04 Oct 2010 11:49:43 -0700 From: Josh Berkus User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 MIME-Version: 1.0 To: Dan Ports CC: pgsql-performance@postgresql.org Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance References: <20101004175545.GA2690@csail.mit.edu> In-Reply-To: <20101004175545.GA2690@csail.mit.edu> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/27 X-Sequence-Number: 40446 Dan, (btw, OpenSQL Confererence is going to be at MIT in 2 weeks. Think anyone from the MOSBENCH team could attend? http://www.opensqlcamp.org/Main_Page) > The big takeaway for -hackers, I think, is that lock manager > performance is going to be an issue for large multicore systems, and > the uncontended cases need to be lock-free. That includes cases where > multiple threads are trying to acquire the same lock in compatible > modes. Yes; we were aware of this due to work Jignesh did at Sun on TPC-E. > Currently even acquiring a shared heavyweight lock requires taking out > an exclusive LWLock on the partition, and acquiring shared LWLocks > requires acquiring a spinlock. All of this gets more expensive on > multicores, where even acquiring spinlocks can take longer than the > work being done in the critical section. Certainly, the question has always been how to fix it without breaking major features and endangering data integrity. > Note that their implementation of the lock manager omits some features > for simplicity, like deadlock detection, 2PC, and probably any > semblance of portability. (These are the sort of things we're allowed > to do in the research world! :-) Well, nice that you did! We'd never have that much time to experiment with non-production stuff as a group in the project. So, now we have a theoretical solution which we can look at maybe implementing parts of in some watered-down form. > The other major bottleneck they ran into was a kernel one: reading from > the heap file requires a couple lseek operations, and Linux acquires a > mutex on the inode to do that. The proper place to fix this is > certainly in the kernel but it may be possible to work around in > Postgres. Or we could complain to Kernel.org. They've been fairly responsive in the past. Too bad this didn't get posted earlier; I just got back from LinuxCon. So you know someone who can speak technically to this issue? I can put them in touch with the Linux geeks in charge of that part of the kernel code. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com From drkp@csail.mit.edu Mon Oct 4 19:22:54 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 22182133655C for ; Mon, 4 Oct 2010 16:22:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 90095-06 for ; Mon, 4 Oct 2010 19:22:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from midnight-anchovy.ambulatoryclam.net (unknown [18.141.0.210]) by mail.postgresql.org (Postfix) with ESMTP id B5FDA1337B2F for ; Mon, 4 Oct 2010 16:22:46 -0300 (ADT) Received: from [131.106.60.147] (unknown [142.131.16.70]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) (Authenticated sender: dan) by midnight-anchovy.ambulatoryclam.net (Postfix) with ESMTPSA id 9589E4AC27; Mon, 4 Oct 2010 15:22:45 -0400 (EDT) References: In-Reply-To: Mime-Version: 1.0 (iPhone Mail 8A306) Content-Type: text/plain; charset=us-ascii Message-Id: Content-Transfer-Encoding: quoted-printable Cc: Robert Haas , Hakan Kocaman , "pgsql-hackers@postgresql.org" X-Mailer: iPhone Mail (8A306) From: Dan Ports Subject: Re: MIT benchmarks pgsql multicore (up to 48)performance Date: Mon, 4 Oct 2010 12:22:32 -0700 To: Greg Stark X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.106 tagged_above=-5 required=5 tests=BAYES_00=-1.9, MIME_QP_LONG_LINE=0.001, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201010/187 X-Sequence-Number: 171227 On Oct 4, 2010, at 11:06, Greg Stark wrote: > I guess by "lock-free in the uncontended case" they mean the buffer > cache manager is lock-free unless you're actually contending on the > same buffer? That refers to being able to acquire non-conflicting row/table locks without= needing an exclusive LWLock, and acquiring shared LWLocks without spinlocks= if possible. I think the buffer cache manager is the next bottleneck after the row/table l= ock manager. Seems like it would also be a good candidate for similar techni= ques, but that's totally uninformed speculation on my part. Dan= From scott.marlowe@gmail.com Mon Oct 4 19:35:53 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 117421336971 for ; Mon, 4 Oct 2010 16:35:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50672-04; Mon, 4 Oct 2010 19:35:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com [74.125.82.50]) by mail.postgresql.org (Postfix) with ESMTP id 222D0133616B; Mon, 4 Oct 2010 16:35:44 -0300 (ADT) Received: by wwb17 with SMTP id 17so6756025wwb.19 for ; Mon, 04 Oct 2010 12:35:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=dKWAX0TUzdRBpuTL0YSQ2oLI4Sv4ETqMc0ecEUGzTSo=; b=xpxMm2kFrBbEh5I1MqTScTmZgcQY7Ymo7Cg0neB1oGHw0423qlQMLfLVHKkD3ZDPuu rTynD5eujilxxEJBYDO0yqe33XaTmXx3ag1ymcebMt7w2tEW9+I/BnFMTmkhdTRQ9XOX hdykUpTGC6V+PwWJzGChmh1Abw1SNuXLTfFRc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=OJRucVn+ZfYKpmdWFze3ecw24EjlzElAx7orO0uYT/AeByEAqNcPKvCJ1TL3t7aFbh TKnRr+7AQLOUAoIgl3DUxbzTqlsancntwrh4cDPBSEp1ytOEwIR/t6tXO6CKiLO6dM+4 WVczhuI2poET8wn7OdIEWY6jCDIrWRguQOwps= MIME-Version: 1.0 Received: by 10.227.138.76 with SMTP id z12mr8556067wbt.60.1286220944387; Mon, 04 Oct 2010 12:35:44 -0700 (PDT) Received: by 10.227.136.213 with HTTP; Mon, 4 Oct 2010 12:35:44 -0700 (PDT) In-Reply-To: References: Date: Mon, 4 Oct 2010 13:35:44 -0600 Message-ID: Subject: Re: [PERFORM] MIT benchmarks pgsql multicore (up to 48)performance From: Scott Marlowe To: Hakan Kocaman Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/190 X-Sequence-Number: 171230 On Mon, Oct 4, 2010 at 8:44 AM, Hakan Kocaman wrote: > Hi, > for whom it may concern: > http://pdos.csail.mit.edu/mosbench/ > They tested with 8.3.9, i wonder what results 9.0 would give. > Best regards and keep up the good work They mention that these tests were run on the older 8xxx series opterons which has much slower memory speed and HT speed as well. I wonder how much better the newer 6xxx series magny cours would have done on it... When I tested some simple benchmarks like pgbench, I got scalability right to 48 processes on our 48 core magny cours machines. Still, lots of room for improvement in kernel and pgsql. -- To understand recursion, one must first understand recursion. From ivoras@freebsd.org Wed Oct 6 22:31:37 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C4E231337BFC for ; Wed, 6 Oct 2010 19:31:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 06866-03 for ; Wed, 6 Oct 2010 22:31:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 3CEE91337B54 for ; Wed, 6 Oct 2010 19:31:29 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1P3cWG-0007Bd-If for pgsql-performance@postgresql.org; Thu, 07 Oct 2010 00:31:28 +0200 Received: from 93-142-63-132.adsl.net.t-com.hr ([93.142.63.132]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 07 Oct 2010 00:31:28 +0200 Received: from ivoras by 93-142-63-132.adsl.net.t-com.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 07 Oct 2010 00:31:28 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Ivan Voras Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Date: Thu, 07 Oct 2010 00:31:19 +0200 Lines: 21 Message-ID: References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: 93-142-63-132.adsl.net.t-com.hr User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.9.1.9) Gecko/20100620 Thunderbird/3.0.4 In-Reply-To: <4CAA21C7.1030701@agliodbs.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/42 X-Sequence-Number: 40461 On 10/04/10 20:49, Josh Berkus wrote: >> The other major bottleneck they ran into was a kernel one: reading from >> the heap file requires a couple lseek operations, and Linux acquires a >> mutex on the inode to do that. The proper place to fix this is >> certainly in the kernel but it may be possible to work around in >> Postgres. > > Or we could complain to Kernel.org. They've been fairly responsive in > the past. Too bad this didn't get posted earlier; I just got back from > LinuxCon. > > So you know someone who can speak technically to this issue? I can put > them in touch with the Linux geeks in charge of that part of the kernel > code. Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK cannot be fixed since you're modifying the global "strean position" variable and something has got to lock that. OTOH, pread() / pwrite() don't have to do that. From jnelson+pgsql@jamponi.net Wed Oct 6 22:42:05 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 865511337BCB for ; Wed, 6 Oct 2010 19:42:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 27191-09 for ; Wed, 6 Oct 2010 22:41:59 +0000 (UTC) X-Greylist: delayed 00:07:15.727256 by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id A8E601337BC1 for ; Wed, 6 Oct 2010 19:41:57 -0300 (ADT) Received: by bwz11 with SMTP id 11so69821bwz.19 for ; Wed, 06 Oct 2010 15:41:57 -0700 (PDT) Received: by 10.204.55.211 with SMTP id v19mr10167014bkg.153.1286404480943; Wed, 06 Oct 2010 15:34:40 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.102.20 with HTTP; Wed, 6 Oct 2010 15:34:20 -0700 (PDT) In-Reply-To: References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> From: Jon Nelson Date: Wed, 6 Oct 2010 17:34:20 -0500 X-Google-Sender-Auth: q0g8oHC42AW5BYQtIrMvzOJ5c48 Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.879 tagged_above=-10 required=5 tests=BAYES_00=-1.9, MISSING_HEADERS=1.021, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/43 X-Sequence-Number: 40462 On Wed, Oct 6, 2010 at 5:31 PM, Ivan Voras wrote: > On 10/04/10 20:49, Josh Berkus wrote: > >>> The other major bottleneck they ran into was a kernel one: reading from >>> the heap file requires a couple lseek operations, and Linux acquires a >>> mutex on the inode to do that. The proper place to fix this is >>> certainly in the kernel but it may be possible to work around in >>> Postgres. >> >> Or we could complain to Kernel.org. =C2=A0They've been fairly responsive= in >> the past. =C2=A0Too bad this didn't get posted earlier; I just got back = from >> LinuxCon. >> >> So you know someone who can speak technically to this issue? I can put >> them in touch with the Linux geeks in charge of that part of the kernel >> code. > > Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK > cannot be fixed since you're modifying the global "strean position" > variable and something has got to lock that. > > OTOH, pread() / pwrite() don't have to do that. While lseek is very "cheap" it is like any other system call in that when you multiple "cheap" times "a jillion" you end up with "notable" or even "lots". I've personally seen notable performance improvements by switching to pread/pwrite instead of lseek+{read,write}. For platforms that don't implement pread or pwrite, wrapper calls are trivial to produce. One less system call is, in this case, 50% fewer. --=20 Jon From robertmhaas@gmail.com Thu Oct 7 00:39:57 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 04D3B1337B77 for ; Wed, 6 Oct 2010 21:39:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 83780-03 for ; Thu, 7 Oct 2010 00:39:50 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 78ADC1336971 for ; Wed, 6 Oct 2010 21:39:49 -0300 (ADT) Received: by iwn9 with SMTP id 9so94193iwn.19 for ; Wed, 06 Oct 2010 17:39:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=UX1//7UN1L4HTLQ8Qr3XO82sVYhxv5fCha/CGTDWa5g=; b=Z4YESYGnyGvAKVkufEdDK6iqD1905V1JVSL5PrdZJsgBUKnMxK25sqpdqQjW/0cdoa R+Z1C20zKhPAN1k8sp2fCDFWwR36D6PfncUMbb1VcKjXcg4+kCwaZgxpWYkJgg7ih4bp m3MjWsvL8yhiwXPWFl8LEQdH06ZgO3g53HjWM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=RYYxxk9CptEUDF7AeKvacAgCcCxFcqUBykJaEe4no7EfZ37aTC3mxhIWI6j76UYByc /K/Kf37e4WBBR/v+1d8k+CkdoD5DORmwOk/WhKL3asn1eT3GPpHTU9MddtQ8tI7EDGDi CstJk5E+Jy9FwMqq8PlixxihgqzamwLT6Krs0= MIME-Version: 1.0 Received: by 10.42.74.137 with SMTP id w9mr1328924icj.201.1286411988935; Wed, 06 Oct 2010 17:39:48 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Wed, 6 Oct 2010 17:39:48 -0700 (PDT) In-Reply-To: References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> Date: Wed, 6 Oct 2010 20:39:48 -0400 Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Ivan Voras Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/45 X-Sequence-Number: 40464 On Wed, Oct 6, 2010 at 6:31 PM, Ivan Voras wrote: > On 10/04/10 20:49, Josh Berkus wrote: > >>> The other major bottleneck they ran into was a kernel one: reading from >>> the heap file requires a couple lseek operations, and Linux acquires a >>> mutex on the inode to do that. The proper place to fix this is >>> certainly in the kernel but it may be possible to work around in >>> Postgres. >> >> Or we could complain to Kernel.org. =A0They've been fairly responsive in >> the past. =A0Too bad this didn't get posted earlier; I just got back fro= m >> LinuxCon. >> >> So you know someone who can speak technically to this issue? I can put >> them in touch with the Linux geeks in charge of that part of the kernel >> code. > > Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK > cannot be fixed since you're modifying the global "strean position" > variable and something has got to lock that. Well, there are lock free algorithms using CAS, no? > OTOH, pread() / pwrite() don't have to do that. Hey, I didn't know about those. That sounds like it might be worth investigating, though I confess I lack a 48-core machine on which to measure the alleged benefit. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company From tgl@sss.pgh.pa.us Thu Oct 7 01:25:15 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E182C1337BFC for ; Wed, 6 Oct 2010 22:25:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 63818-09 for ; Thu, 7 Oct 2010 01:25:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 782D71337BDD for ; Wed, 6 Oct 2010 22:25:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o971P7uh016637; Wed, 6 Oct 2010 21:25:07 -0400 (EDT) To: Ivan Voras cc: pgsql-performance@postgresql.org Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance In-reply-to: References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> Comments: In-reply-to Ivan Voras message dated "Thu, 07 Oct 2010 00:31:19 +0200" Date: Wed, 06 Oct 2010 21:25:07 -0400 Message-ID: <16636.1286414707@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/46 X-Sequence-Number: 40465 Ivan Voras writes: > On 10/04/10 20:49, Josh Berkus wrote: >>> The other major bottleneck they ran into was a kernel one: reading from >>> the heap file requires a couple lseek operations, and Linux acquires a >>> mutex on the inode to do that. > Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK > cannot be fixed since you're modifying the global "strean position" > variable and something has got to lock that. Um, there is no "global stream position" associated with an inode. A file position is associated with an open-file descriptor. If Josh quoted the problem correctly, the issue is that the kernel is locking a file's inode (which may be referenced by quite a lot of file descriptors) in order to change the state of one file descriptor. It sure sounds like a possible source of contention to me. regards, tom lane From sfrost@snowman.net Thu Oct 7 01:30:20 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 3F3991337B50 for ; Wed, 6 Oct 2010 22:30:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 87806-04 for ; Thu, 7 Oct 2010 01:30:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id BE72F1337B79 for ; Wed, 6 Oct 2010 22:30:12 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id A521A22290; Wed, 6 Oct 2010 21:30:12 -0400 (EDT) Date: Wed, 6 Oct 2010 21:30:12 -0400 From: Stephen Frost To: Robert Haas Cc: Ivan Voras , pgsql-performance@postgresql.org Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Message-ID: <20101007013012.GN26232@tamriel.snowman.net> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="3X8HaB+/xMECYZdj" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.26-1-amd64 (x86_64) X-Uptime: 21:29:43 up 405 days, 12:54, 14 users, load average: 0.00, 0.05, 0.02 User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/47 X-Sequence-Number: 40466 --3X8HaB+/xMECYZdj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Robert Haas (robertmhaas@gmail.com) wrote: > Hey, I didn't know about those. That sounds like it might be worth > investigating, though I confess I lack a 48-core machine on which to > measure the alleged benefit. I've got a couple 24-core systems, if it'd be sufficiently useful to test with.. Stephen --3X8HaB+/xMECYZdj Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkytIqQACgkQrzgMPqB3kijOVQCfbNWaFFuADcG5SpKm7DJ4HkCy CdsAnilYKPRAKaUShd5UPFz2eKDnkpvC =x7W6 -----END PGP SIGNATURE----- --3X8HaB+/xMECYZdj-- From robertmhaas@gmail.com Thu Oct 7 02:01:36 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 951361337C57 for ; Wed, 6 Oct 2010 23:01:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 66115-01-3 for ; Thu, 7 Oct 2010 02:01:28 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 4C7461337B77 for ; Wed, 6 Oct 2010 23:01:20 -0300 (ADT) Received: by iwn9 with SMTP id 9so175819iwn.19 for ; Wed, 06 Oct 2010 19:01:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=p8WY4zkNTGIzRCycg69tlph/vAjV9GpTh/Af2GHm2VU=; b=t0eTUc+uX+/TQvYbq1a2cJDlZGHguZVczF3FXrvMAi1pLUl9BjV+herE6546NjicQ8 CCvh6lq4mEhLwnI2dqlYl4+Kt2lZM/Ty7LgLNnkxcnwkfVMOyy1Yv1RoVXI5mAzZMvxT dIULg+sSQhTTUVpVjg2OtGaHLXfh6rst/dPXM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=fW0CpmImiLBMxJyqD4Gae75MoaBFOn6E0CvSqawLxDiAZOu4bWm5OaJ/UG44KkV7F5 Wfqw/ZJ0pTEhVW4E/xYYwn5KdWinpFGnXpOzNN8csRbRScr8+ICGd8QNzi1EPPPMk7tJ 71wyPnUcT8WO07ZNwQYYclctVZBa6pmSfDAb4= MIME-Version: 1.0 Received: by 10.231.20.65 with SMTP id e1mr112966ibb.32.1286416880540; Wed, 06 Oct 2010 19:01:20 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Wed, 6 Oct 2010 19:01:20 -0700 (PDT) In-Reply-To: <20101007013012.GN26232@tamriel.snowman.net> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> Date: Wed, 6 Oct 2010 22:01:20 -0400 Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Stephen Frost Cc: Ivan Voras , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/48 X-Sequence-Number: 40467 On Wed, Oct 6, 2010 at 9:30 PM, Stephen Frost wrote: > * Robert Haas (robertmhaas@gmail.com) wrote: >> Hey, I didn't know about those. =A0That sounds like it might be worth >> investigating, though I confess I lack a 48-core machine on which to >> measure the alleged benefit. > > I've got a couple 24-core systems, if it'd be sufficiently useful to > test with.. It's good to be you. I don't suppose you could try to replicate the lseek() contention? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company From sfrost@snowman.net Thu Oct 7 02:07:15 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A00B1133616B for ; Wed, 6 Oct 2010 23:07:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 70614-04 for ; Thu, 7 Oct 2010 02:07:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id 318F91336DB6 for ; Wed, 6 Oct 2010 23:07:08 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id CAD902228A; Wed, 6 Oct 2010 22:07:07 -0400 (EDT) Date: Wed, 6 Oct 2010 22:07:07 -0400 From: Stephen Frost To: Robert Haas Cc: Ivan Voras , pgsql-performance@postgresql.org Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Message-ID: <20101007020707.GP26232@tamriel.snowman.net> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="/Ta7aTnt0awFX15n" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.26-1-amd64 (x86_64) X-Uptime: 22:02:07 up 405 days, 13:27, 14 users, load average: 0.04, 0.05, 0.00 User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/49 X-Sequence-Number: 40468 --/Ta7aTnt0awFX15n Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Robert Haas (robertmhaas@gmail.com) wrote: > It's good to be you. They're HP BL465 G7's w/ 2x 12-core AMD processors and 48G of RAM. Unfortunately, they currently only have local storage, but it seems unlikely that would be an issue for this. > I don't suppose you could try to replicate the lseek() contention? I can give it a shot, but the impression I had from the paper is that the lseek() contention wouldn't be seen without the changes to the lock manager...? Or did I misunderstand? Thanks, Stephen --/Ta7aTnt0awFX15n Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkytK0sACgkQrzgMPqB3kijrQQCfUPfNz/RJg/jANoY2lWhR3a3P 2MoAnRteuzESTyJC7XcsTPL8n/1Hskc0 =d5Hp -----END PGP SIGNATURE----- --/Ta7aTnt0awFX15n-- From ivoras@freebsd.org Thu Oct 7 12:19:35 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id F2AA11337C05 for ; Thu, 7 Oct 2010 09:19:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 41454-08 for ; Thu, 7 Oct 2010 12:19:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id 8C02E1337C02 for ; Thu, 7 Oct 2010 09:19:28 -0300 (ADT) Received: by qwf7 with SMTP id 7so292290qwf.19 for ; Thu, 07 Oct 2010 05:19:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:mime-version:sender:received :in-reply-to:references:from:date:x-google-sender-auth:message-id :subject:to:cc:content-type; bh=ZWGI5c1bCWVxFiPNvcZ8PGAECUgffNZPN4q6vB4Hdq4=; b=Vlom6bPgVAxWYK3yAdmf91SdY78YsCV1Unk0MqyEb0+ljk2lQUH5mb7a7l5qUsNzLF z1S2zN3ZP8iSfhjYYFLEPk8/ot77jpnMi6swN8tYo+KzTJN+jOz82m+Mq3nDL8Ag1jND ilF3kDtOJeysCKH/jTJGdoxzG/1iDyepQDfd8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type; b=aal2qx8bMxqvVnwpDEDG0VFyp8uHjZQ28+M3HUSl2EYHkY1oywAVfLxDJsGu2lxs73 7MNFqiXiA1+mXdh5DvpDBN6A4OYy8H+Arefju3nxdJv1PJDMLu5PwBTIoAGeipklGX5O O7FZ0yeZ/MwLs2ivwCGeELjdhBDgLB8L/O2XI= Received: by 10.224.178.204 with SMTP id bn12mr240080qab.47.1286453968838; Thu, 07 Oct 2010 05:19:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.229.234.81 with HTTP; Thu, 7 Oct 2010 05:19:08 -0700 (PDT) In-Reply-To: <16636.1286414707@sss.pgh.pa.us> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <16636.1286414707@sss.pgh.pa.us> From: Ivan Voras Date: Thu, 7 Oct 2010 14:19:08 +0200 X-Google-Sender-Auth: 0pTsZYpKoioCMRsKcET618IJFss Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance To: Tom Lane Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/52 X-Sequence-Number: 40471 On 7 October 2010 03:25, Tom Lane wrote: > Ivan Voras writes: >> On 10/04/10 20:49, Josh Berkus wrote: >>>> The other major bottleneck they ran into was a kernel one: reading from >>>> the heap file requires a couple lseek operations, and Linux acquires a >>>> mutex on the inode to do that. > >> Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK >> cannot be fixed since you're modifying the global "strean position" >> variable and something has got to lock that. > > Um, there is no "global stream position" associated with an inode. > A file position is associated with an open-file descriptor. You're right of course, I was pattern matching late last night on the "lseek()" and "locking problems" keywords and ignored "inode". > If Josh quoted the problem correctly, the issue is that the kernel is > locking a file's inode (which may be referenced by quite a lot of file > descriptors) in order to change the state of one file descriptor. > It sure sounds like a possible source of contention to me. Though it does depend on the details of how pg uses it. Forked processes share their parents' file descriptors. From robertmhaas@gmail.com Thu Oct 7 12:33:15 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 215C31336DB6 for ; Thu, 7 Oct 2010 09:33:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 62520-02 for ; Thu, 7 Oct 2010 12:33:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 816B11337C01 for ; Thu, 7 Oct 2010 09:33:07 -0300 (ADT) Received: by iwn9 with SMTP id 9so785717iwn.19 for ; Thu, 07 Oct 2010 05:33:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=Rm1DxoJB5Q3UEn6syjTMR4GkCURgyVl6iCd4/++LaxQ=; b=AXvbweW8k++83cYuyW7LwR3kJlMsBtxfo8JszTRasijEZfAhw8cYCAH+t6hSg0XzKD cFANL1ugwbS72oQgOj9rND1P4/X/doX7UbldzFe+UxAoOTYU1N/P5i6qu5VF9PVxt7lU sfhBk+3zkZlJjOK+O9mqBzgLmCCq+n4Ovkfns= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=JjZiHA0PpJpce9Hidy6vyE6dv31gZXhxjZLrR8J6C5Ifk1GUUfQxW06S0vzTCQgnmc NySzLSvbLUyPjvtxi3GHwVBuDShwexDhx0tR639AEx/4bD/prpSOobW2XpjLBL0klc4N xtJac36c9itJGRYevgjIpzHmo0BIKlzEQi8z4= MIME-Version: 1.0 Received: by 10.231.60.4 with SMTP id n4mr801169ibh.18.1286454787784; Thu, 07 Oct 2010 05:33:07 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Thu, 7 Oct 2010 05:33:07 -0700 (PDT) In-Reply-To: <20101007020707.GP26232@tamriel.snowman.net> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> <20101007020707.GP26232@tamriel.snowman.net> Date: Thu, 7 Oct 2010 08:33:07 -0400 Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Stephen Frost Cc: Ivan Voras , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/53 X-Sequence-Number: 40472 On Wed, Oct 6, 2010 at 10:07 PM, Stephen Frost wrote: > * Robert Haas (robertmhaas@gmail.com) wrote: >> It's good to be you. > > They're HP BL465 G7's w/ 2x 12-core AMD processors and 48G of RAM. > Unfortunately, they currently only have local storage, but it seems > unlikely that would be an issue for this. > >> I don't suppose you could try to replicate the lseek() contention? > > I can give it a shot, but the impression I had from the paper is that > the lseek() contention wouldn't be seen without the changes to the lock > manager...? =A0Or did I misunderstand? Looks like the lock manager problems hit at 28 cores, and the lseek problems at 36 cores. So your system might not even be big enough to manifest either problem. It's unclear to me whether a 48-core system would be able to see the lseek issues without improvements to the lock manager, but perhaps it would be possible by, say, increasing the number of lock partitions by 8x. It would be nice to segregate these issues though, because using pread/pwrite is probably a lot less work than rewriting our lock manager. Do you have tools to measure the lseek overhead? If so, we could prepare a patch to use pread()/pwrite() and just see whether that reduced the overhead, without worrying so much about whether it was actually a major bottleneck. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company From ivoras@freebsd.org Thu Oct 7 12:47:11 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 760821337B82 for ; Thu, 7 Oct 2010 09:47:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74794-10 for ; Thu, 7 Oct 2010 12:47:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id B41651337B50 for ; Thu, 7 Oct 2010 09:47:03 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1P3psE-0002iD-4A for pgsql-performance@postgresql.org; Thu, 07 Oct 2010 14:47:02 +0200 Received: from lara.cc.fer.hr ([161.53.72.113]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 07 Oct 2010 14:47:02 +0200 Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 07 Oct 2010 14:47:02 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Ivan Voras Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Date: Thu, 07 Oct 2010 14:47:06 +0200 Lines: 43 Message-ID: References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: lara.cc.fer.hr User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.9.1.9) Gecko/20100518 Thunderbird/3.0.4 In-Reply-To: X-Enigmail-Version: 1.0.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/55 X-Sequence-Number: 40474 On 10/07/10 02:39, Robert Haas wrote: > On Wed, Oct 6, 2010 at 6:31 PM, Ivan Voras wrote: >> On 10/04/10 20:49, Josh Berkus wrote: >> >>>> The other major bottleneck they ran into was a kernel one: reading from >>>> the heap file requires a couple lseek operations, and Linux acquires a >>>> mutex on the inode to do that. The proper place to fix this is >>>> certainly in the kernel but it may be possible to work around in >>>> Postgres. >>> >>> Or we could complain to Kernel.org. They've been fairly responsive in >>> the past. Too bad this didn't get posted earlier; I just got back from >>> LinuxCon. >>> >>> So you know someone who can speak technically to this issue? I can put >>> them in touch with the Linux geeks in charge of that part of the kernel >>> code. >> >> Hmmm... lseek? As in "lseek() then read() or write()" idiom? It AFAIK >> cannot be fixed since you're modifying the global "strean position" >> variable and something has got to lock that. > > Well, there are lock free algorithms using CAS, no? Nothing is really "lock free" - in this case the algorithms simply push the locking down to atomic operations on the CPU (and the memory bus). Semantically, *something* has to lock the memory region for however brief period of time and then propagate that update to other CPUs' caches (i.e. invalidate them). >> OTOH, pread() / pwrite() don't have to do that. > > Hey, I didn't know about those. That sounds like it might be worth > investigating, though I confess I lack a 48-core machine on which to > measure the alleged benefit. As Jon said, it will in any case reduce the number of these syscalls by half, and they can be wrapped by a C macro for the platforms which don't implement them. http://man.freebsd.org/pread (and just in case it's needed: pread() is a special case of preadv()). From Kevin.Grittner@wicourts.gov Thu Oct 7 17:29:54 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 21F191337DBE for ; Thu, 7 Oct 2010 14:29:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 31209-01-5 for ; Thu, 7 Oct 2010 17:29:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id BF11D1337ED1 for ; Thu, 7 Oct 2010 14:21:30 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 07 Oct 2010 12:21:30 -0500 Message-Id: <4CADBB410200002500036644@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 07 Oct 2010 12:21:21 -0500 From: "Kevin Grittner" To: "Robert Haas" , "Stephen Frost" Cc: "Ivan Voras" , Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> <20101007020707.GP26232@tamriel.snowman.net> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/58 X-Sequence-Number: 40477 Robert Haas wrote: > perhaps it would be possible by, say, increasing the number of > lock partitions by 8x. It would be nice to segregate these issues > though, because using pread/pwrite is probably a lot less work > than rewriting our lock manager. You mean easier than changing this 4 to a 7?: #define LOG2_NUM_LOCK_PARTITIONS 4 Or am I missing something? -Kevin From sfrost@snowman.net Thu Oct 7 18:06:38 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id ECF9B1337B5D for ; Thu, 7 Oct 2010 15:06:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 26392-05-2 for ; Thu, 7 Oct 2010 18:06:31 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id 302701337BB0 for ; Thu, 7 Oct 2010 15:06:21 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id 834C622941; Thu, 7 Oct 2010 14:06:20 -0400 (EDT) Date: Thu, 7 Oct 2010 14:06:20 -0400 From: Stephen Frost To: Kevin Grittner Cc: Robert Haas , Ivan Voras , pgsql-performance@postgresql.org Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance Message-ID: <20101007180620.GY26232@tamriel.snowman.net> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> <20101007020707.GP26232@tamriel.snowman.net> <4CADBB410200002500036644@gw.wicourts.gov> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="DAXZOU02cnSJSKTz" Content-Disposition: inline In-Reply-To: <4CADBB410200002500036644@gw.wicourts.gov> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.26-1-amd64 (x86_64) X-Uptime: 14:05:12 up 406 days, 5:30, 14 users, load average: 0.11, 0.11, 0.03 User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/60 X-Sequence-Number: 40479 --DAXZOU02cnSJSKTz Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote: > Robert Haas wrote: > > perhaps it would be possible by, say, increasing the number of > > lock partitions by 8x. It would be nice to segregate these issues > > though, because using pread/pwrite is probably a lot less work > > than rewriting our lock manager. > =20 > You mean easier than changing this 4 to a 7?: > =20 > #define LOG2_NUM_LOCK_PARTITIONS 4 > =20 > Or am I missing something? I'm pretty sure we were talking about the change described in the paper of moving to a system which uses atomic changes instead of spinlocks for certain locking situations.. If that's all the MIT folks did, they certainly made it sound like alot more. :) Stephen --DAXZOU02cnSJSKTz Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkyuDBwACgkQrzgMPqB3kigQRQCeP0d09qjc4+bBxKLSVIMSfqs7 xIkAnR/Qsfdlr7r+2Ek/acXHY4Ab1uMa =QS8v -----END PGP SIGNATURE----- --DAXZOU02cnSJSKTz-- From Kevin.Grittner@wicourts.gov Thu Oct 7 18:22:20 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 667761337B77 for ; Thu, 7 Oct 2010 15:22:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 30069-08 for ; Thu, 7 Oct 2010 18:22:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 3DB681336DB6 for ; Thu, 7 Oct 2010 15:22:12 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 07 Oct 2010 13:22:11 -0500 Message-Id: <4CADC97A0200002500036662@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 07 Oct 2010 13:22:02 -0500 From: "Kevin Grittner" To: "Stephen Frost" Cc: "Ivan Voras" , "Robert Haas" , Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> <20101007020707.GP26232@tamriel.snowman.net> <4CADBB410200002500036644@gw.wicourts.gov> <20101007180620.GY26232@tamriel.snowman.net> In-Reply-To: <20101007180620.GY26232@tamriel.snowman.net> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/61 X-Sequence-Number: 40480 Stephen Frost wrote: > Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote: >> Robert Haas wrote: >>> perhaps it would be possible by, say, increasing the number of >>> lock partitions by 8x. >> changing this 4 to a 7?: >> >> #define LOG2_NUM_LOCK_PARTITIONS 4 > I'm pretty sure we were talking about the change described in the > paper of moving to a system which uses atomic changes instead of > spinlocks for certain locking situations.. Well, they also mentioned increasing the number of lock partitions to reduce contention, and that seemed to be what Robert was talking about in the quoted section. Of course, that's not the *only* thing they did; it's just the point which seemed to be under discussion just there. -Kevin From robertmhaas@gmail.com Thu Oct 7 20:31:50 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 2E3DE1337BD0 for ; Thu, 7 Oct 2010 17:31:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 38544-03 for ; Thu, 7 Oct 2010 20:31:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 1E91A1337BCB for ; Thu, 7 Oct 2010 17:31:37 -0300 (ADT) Received: by iwn9 with SMTP id 9so265250iwn.19 for ; Thu, 07 Oct 2010 13:31:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=ov3ByRBOBH7FVk8bP9p3ybBSb5BAz4bYfC497Zb4JQ8=; b=uFdos0ADDrls5l4O6tImX90hVyd3G+RpRQhqg48te0uPz8xgfCkNyT0YgEjiBgU/fD YzB6x4JHyzkt/cDDe4X1WakddlJXCoZ7fOqi0BAZjiGoAqDkTWm4dOIcAE/7QTTZtLmV uSnn8GMf6HaDRvlF+w2OSIlYbeQsak6Y5hHaw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=KKXdEruhevu0FzybEaux2bvPl2Wj2RKAE6dpELDvZU6G2122UdsMpRLvijZ2gMf/+x KAzFFs81+DC/2HkvtQajmpD7MmCpC5RCSztZr5nUwJzlx+kITXXXMt1ix8utGlk/6Pn2 OvpGAyuqVvGmp/UZjzAb+M0Qd7dAwvXZh/OPY= MIME-Version: 1.0 Received: by 10.231.157.212 with SMTP id c20mr1403822ibx.186.1286483496396; Thu, 07 Oct 2010 13:31:36 -0700 (PDT) Received: by 10.231.10.131 with HTTP; Thu, 7 Oct 2010 13:31:36 -0700 (PDT) In-Reply-To: <4CADBB410200002500036644@gw.wicourts.gov> References: <20101004175545.GA2690@csail.mit.edu> <4CAA21C7.1030701@agliodbs.com> <20101007013012.GN26232@tamriel.snowman.net> <20101007020707.GP26232@tamriel.snowman.net> <4CADBB410200002500036644@gw.wicourts.gov> Date: Thu, 7 Oct 2010 16:31:36 -0400 Message-ID: Subject: Re: [HACKERS] MIT benchmarks pgsql multicore (up to 48)performance From: Robert Haas To: Kevin Grittner Cc: Stephen Frost , Ivan Voras , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/67 X-Sequence-Number: 40486 On Thu, Oct 7, 2010 at 1:21 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> perhaps it would be possible by, say, increasing the number of >> lock partitions by 8x. =A0It would be nice to segregate these issues >> though, because using pread/pwrite is probably a lot less work >> than rewriting our lock manager. > > You mean easier than changing this 4 to a 7?: > > #define LOG2_NUM_LOCK_PARTITIONS =A04 > > Or am I missing something? Right. They did something more complicated (and, I think, better) than that, but that change by itself might be enough to ameliorate the lock contention enough to see the lsek() issue. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company