Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrB7-00085n-2u for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Mar 2014 09:34:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WLrB6-0006Vn-2y for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Mar 2014 09:34:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrB5-0006Ve-2g for pgsql-hackers@postgresql.org; Fri, 07 Mar 2014 09:34:51 +0000 Received: from smtp-outbound-1.vmware.com ([208.91.2.12]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WLrAz-0006At-J0 for pgsql-hackers@postgresql.org; Fri, 07 Mar 2014 09:34:49 +0000 Received: from sc9-mailhost1.vmware.com (sc9-mailhost1.vmware.com [10.113.161.71]) by smtp-outbound-1.vmware.com (Postfix) with ESMTP id 380AB28293; Fri, 7 Mar 2014 01:34:42 -0800 (PST) Received: from zcs-prod-ext-mta-2.vmware.com (zcs-prod-ext-mta-2.vmware.com [10.113.62.224]) by sc9-mailhost1.vmware.com (Postfix) with ESMTP id 21377182B8; Fri, 7 Mar 2014 01:34:42 -0800 (PST) Received: from zcs-prod-ext-mta-2.vmware.com (localhost.localdomain [127.0.0.1]) by zcs-prod-ext-mta-2.vmware.com (Postfix) with ESMTP id 779D2C005D; Fri, 7 Mar 2014 01:34:39 -0800 (PST) Received: from [192.168.1.90] (zimbra-prod-ext-proxy-vip.vmware.com [10.113.63.87]) by zcs-prod-ext-mta-2.vmware.com (Postfix) with ESMTPSA; Fri, 7 Mar 2014 01:34:38 -0800 (PST) Message-ID: <531992AF.2080306@vmware.com> Date: Fri, 07 Mar 2014 11:34:39 +0200 From: Heikki Linnakangas Organization: VMware User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20131103 Icedove/17.0.10 MIME-Version: 1.0 To: Robert Haas CC: Greg Stark , Tan Tran , PostgreSQL-development Subject: Re: GSoC on WAL-logging hash indexes References: <54977A8C-4AF5-4BCC-B021-78E9CA978077@gmail.com> <531873E8.6050802@vmware.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -6.9 (------) 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 On 03/06/2014 09:34 PM, Robert Haas wrote: > On Thu, Mar 6, 2014 at 8:11 AM, Heikki Linnakangas > wrote: >> I don't think it's necessary to improve concurrency just to get WAL-logging. >> Better concurrency is a worthy goal of its own, of course, but it's a >> separate concern. > > To some extent, I agree, but only to some extent. To make hash > indexes generally usable, we really need to solve both problems. When > I got rid of just some of the heavyweight locking in commit > 76837c1507cb5a5a0048046233568447729e66dd, the results were pretty > dramatic at higher levels of concurrency: > > http://www.postgresql.org/message-id/CA+Tgmoaf=nOJxLyzGcbrrY+pe-0VLL0vfHi6tjdM3fFtVwsOmw@mail.gmail.com > > But there was still an awful lot of contention inside the heavyweight > lock manager, and I don't think hash indexes are going to be ready for > prime time until we solve that problem. Hmm. You suggested ensuring that a scan always has at least a pin, and split takes a vacuum-lock. That ought to work. There's no need for the more complicated maneuvers you described, ISTM that you can just replace the heavy-weight share lock with holding a pin on the primary page of the bucket, and an exclusive lock with a vacuum-lock. Note that _hash_expandtable already takes the exclusive lock conditionally, ie. if it doesn't get the lock immediately it just gives up. We could do the same with the cleanup lock. Vacuum could also be enhanced. It currently takes an exclusive lock on the bucket, then removes any dead tuples and finally "squeezes" the bucket by moving tuples to earlier pages. But you only really need the exclusive lock for the squeeze-phase. You could do the dead-tuple removal without the bucket-lock, and only grab for the squeeze-phase. And the squeezing is optional, so you could just skip that if you can't get the lock. But that's a separate patch as well. One more thing we could do to make hash indexes more scalable, independent of the above: Cache the information in the metapage in backend-private memory. Then you (usually) wouldn't need to access the metapage at all when scanning. Store a copy of the bitmask for that bucket in the primary page, and when scanning, check that it matches the cached value. If not, refresh the cached metapage and try again. So, there seems to be a few fairly simple and independent improvements to be made: 1. Replace the heavy-weight lock with pin & vacuum-lock. 2. Make it crash-safe, by adding WAL-logging 3. Only acquire the exclusive-lock (vacuum-lock after step 1) in VACUUM for the squeeze phase. 4. Cache the metapage. We still don't know if it's going to be any better than B-tree after all that's done, but the only way to find out is to go ahead and implement it. This seems like a great GSoC project to me. We have a pretty good idea of what we want to accomplish. It's uncontroversial: I don't think anyone is going to object to improving hash indexes (one could argue that it's a waste of time, but that's different from objecting to the idea). And it consists of a few mostly independent parts, so it's possible to do incrementally which makes it easier to track progress, and we'll probably have something useful at the end of the summer even if it doesn't all get finished. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers