X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2A62852A5A for ; Mon, 14 Mar 2005 09:41:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46438-07 for ; Mon, 14 Mar 2005 09:41:30 +0000 (GMT) Received: from anchor-post-36.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id 0CCFC52A57 for ; Mon, 14 Mar 2005 09:41:29 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-36.mail.demon.net with esmtp (Exim 4.42) id 1DAm4l-000OMz-Jt; Mon, 14 Mar 2005 09:41:30 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id D2DA3166F3; Mon, 14 Mar 2005 09:41:22 +0000 (GMT) Message-ID: <42355C40.1070207@archonet.com> Date: Mon, 14 Mar 2005 09:41:20 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jacques Caron Cc: pgsql-performance@postgresql.org Subject: Re: Performance tuning References: <6.2.0.14.0.20050311155221.041aa528@pop.interactivemediafactory.net> In-Reply-To: <6.2.0.14.0.20050311155221.041aa528@pop.interactivemediafactory.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/256 X-Sequence-Number: 10961 Jacques Caron wrote: > I'm preparing a set of servers which will eventually need to handle a > high volume of queries (both reads and writes, but most reads are very > simple index-based queries returning a limited set of rows, when not > just one), and I would like to optimize things as much as possible, so I > have a few questions on the exact way PostgreSQL's MVCC works, and how > transactions, updates and vacuuming interact. I hope someone will be > able to point me in the right direction (feel free to give pointers if I > missed the places where this is described). > > From what I understand (and testing confirms it), bundling many queries > in one single transaction is more efficient than having each query be a > separate transaction (like with autocommit on). However, I wonder about > the limits of this: > > - are there any drawbacks to grouping hundreds or thousands of queries > (inserts/updates) over several minutes in one single transaction? Other > than the fact that the inserts/updates will not be visible until > committed, of course. Essentially turning autocommit off, and doing a > commit once in a while. 1. If any locks are held then they will be held for much longer, causing other processes to block. 2. PG needs to be able to roll back the changes - thousands of simple inserts are fine, millions will probably not be. > - does this apply only to inserts/selects/updates or also for selects? > Another way to put this is: does a transaction with only one select > actually have much transaction-related work to do? Or, does a > transaction with only selects actually have any impact anywhere? Does it > really leave a trace anywhere? Again, I understand that selects grouped > in a transaction will not see updates done after the start of the > transaction (unless done by the same process). There are implications if a SELECT has side-effects (I can call a function in a select - that might do anything). > - if during a single transaction several UPDATEs affect the same row, > will MVCC generate as many row versions as there are updates (like would > be the case with autocommit) or will they be grouped into one single row > version? I believe there will be many versions. Certainly for 8.0 that must be the case to support savepoints within a transaction. > Another related issue is that many of the tables are indexed on a date > field, and one process does a lot of updates on "recent" rows (which > lead to many dead tuples), but after that "older" rows tend to remain > pretty much unchanged for quite a while. Other than splitting the tables > into "old" and "recent" tables, is there any way to make vacuum more > efficient? Scanning the whole table for dead tuples when only a small > portion of the table actually has any does not feel like being very > efficient in this situation. Not really. > Other issue: every five minutes or so, I see a noticeable performance > drop as PostgreSQL checkpoints. This is 7.4.3 with pretty lousy > hardware, I know 8.0 with decent hardware and separate disk(s) for > pg_xlog will definitely help, but I really wonder if there is any way to > reduce the amount of work that needs to be done at that point (I'm a > strong believer of fixing software before hardware). I have already > bumped checkpoint_segments to 8, but I'm not quite sure I understand how > this helps (or doesn't help) things. Logs show 3 to 6 "recycled > transaction log file" lines at that time, that seems quite a lot of work > for a load that's still pretty low. Does grouping of more queries in > transactions help with this? Are there other parameters that can affect > things, or is just a matter of how much inserts/updates/deletes are > done, and the amount of data that was changed? You might be better off reducing the number of checkpoint segments, and decreasing the timeout. There is a balance between doing a lot of work in one go, and the overhead of many smaller bursts of activity. > Last point: some of the servers have expandable data (and will be > replicated with slony-I) and will run with fsync off. I have read > conflicting statements as to what exactly this does: some sources > indicate that setting fsync off actually switches off WAL/checkpointing, > others that it just prevents the fsync (or equivalent) system calls. > Since I still see checkpointing in that case, I guess it's not exactly > the former, but I would love to understand more about it. Really, I > would love to be able to set some tables or databases to "go as fast as > you can and don't worry about transactions, MVCC or anything like that", > but I'm not sure that option exists... Setting fsync=false means the sync isn't done, so data might still be cached below PG's level. I'm not sure it's ever going to be possible to mark a table as "ignore transactions" - it would be a lot of work, and means you couldn't guarantee transactions that included that table in any way. -- Richard Huxton Archonet Ltd