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
blockto 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