Skip site navigation (1) Skip section navigation (2)

Re: Simulating Clog Contention

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Simulating Clog Contention
Date: 2012-01-19 14:36:59
Message-ID: 4F182A8B.10008@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 12.01.2012 14:31, Simon Riggs wrote:
> In order to simulate real-world clog contention, we need to use
> benchmarks that deal with real world situations.
>
> Currently, pgbench pre-loads data using COPY and executes a VACUUM so
> that all hint bits are set on every row of every page of every table.
> Thus, as pgbench runs it sees zero clog accesses from historical data.
> As a result, clog access is minimised and the effects of clog
> contention in the real world go unnoticed.
>
> The following patch adds a pgbench option -I to load data using
> INSERTs, so that we can begin benchmark testing with rows that have
> large numbers of distinct un-hinted transaction ids. With a database
> pre-created using this we will be better able to simulate and thus
> more easily measure clog contention. Note that current clog has space
> for 1 million xids, so a scale factor of greater than 10 is required
> to really stress the clog.

No doubt this is handy for testing this particular area, but overall I 
feel this is too much of a one-trick pony to include in pgbench.

Alternatively, you could do something like this:

do $$
declare
   i int4;
   naccounts int4;
begin
   select count(*) into naccounts from pgbench_accounts;
   for i in 1..naccounts loop
     -- use a begin-exception block to create a new subtransaction
     begin
       update pgbench_accounts set abalance = abalance where aid = i;
     exception
       when division_by_zero then raise 'unexpected division by zero 
error';    end;
   end loop;
end;
$$;

after initializing the pgbench database, to assign distinct xmins to all 
rows. Another idea would be to run pg_dump in --inserts mode, edit the 
dump to remove BEGIN/COMMIT from it, and restore it back.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2012-01-19 14:42:23
Subject: Re: IDLE in transaction introspection
Previous:From: Brad EdigerDate: 2012-01-19 14:04:07
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group