pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Jacques Caron <jc@directinfos.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance tuning
Date: Mon, 14 Mar 2005 09:41:20 +0000
Message-ID: <42355C40.1070207@archonet.com> (raw)
In-Reply-To: <6.2.0.14.0.20050311155221.041aa528@pop.interactivemediafactory.net>
References: <6.2.0.14.0.20050311155221.041aa528@pop.interactivemediafactory.net>

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



view thread (15+ messages)

Message-ID: <42355C40.1070207@archonet.com>
Permalink:  ../42355C40.1070207@archonet.com/
Also on:    postgresql.org/message-id/42355C40.1070207@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, jc@directinfos.com
  Subject: Re: Performance tuning
  In-Reply-To: <42355C40.1070207@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox