Re: Simulating Clog Contention

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