Re: [Postgres-xc-general] pgbench suggestion

[Koichi S. <koi...@gm...>]

The result has several noise.  Tables t, y, x, and s are all for my own test.

Sorry for inconvenience.
----------
Koichi Suzuki


2012/11/6 Koichi Suzuki <koi...@gm...>:
> pgxc_class has distribution definition of each table.    I'm using the
> following views as a handy tool:
>
> ----
> [koichi@linker:scripts]$ cat distr_view.sql
> DROP VIEW IF EXISTS xc_table_distribution;
>
>
> CREATE VIEW xc_table_distribution AS
>
> SELECT pg_class.relname relation,
>        CASE
>          WHEN pclocatortype = 'H' THEN 'Hash'
>          WHEN pclocatortype = 'M' THEN 'Modulo'
>          WHEN pclocatortype = 'N' THEN 'Round Robin'
>          WHEN pclocatortype = 'R' THEN 'Replicate'
>          ELSE 'Unknown'
>        END AS distribution,
>        pg_attribute.attname attname
>     FROM pg_class, pgxc_class, pg_attribute
>     WHERE pg_class.oid = pgxc_class.pcrelid
>           and pg_class.oid = pg_attribute.attrelid
>           and pgxc_class.pcattnum = pg_attribute.attnum
>  UNION
>
> SELECT pg_class.relname relation,
>        CASE
>          WHEN pclocatortype = 'H' THEN 'Hash'
>          WHEN pclocatortype = 'M' THEN 'Modulo'
>          WHEN pclocatortype = 'N' THEN 'Round Robin'
>          WHEN pclocatortype = 'R' THEN 'Replicate'
>          ELSE 'Unknown'
>        END AS distribution,
>        '- none -' attname
>    FROM pg_class, pgxc_class, pg_attribute
>    WHERE pg_class.oid = pgxc_class.pcrelid
>          and pg_class.oid = pg_attribute.attrelid
>          and pgxc_class.pcattnum = 0
>     ;
>
> COMMENT ON VIEW xc_table_distribution IS
> 'View to show distribution/replication attribute of the given table.';
>
> [koichi@linker:scripts]$
> ------
>
> Info for pgbench tables are as follows:
> -----------
> koichi=# select * from xc_table_distribution
> koichi-# ;
>      relation     | distribution | attname
> ------------------+--------------+----------
>  t                | Hash         | a
>  pgbench_branches | Hash         | bid
>  y                | Modulo       | a
>  pgbench_tellers  | Hash         | bid
>  pgbench_accounts | Hash         | bid
>  x                | Round Robin  | - none -
>  pgbench_history  | Hash         | bid
>  s                | Replicate    | - none -
> (8 rows)
>
> koichi=#
> ------
>
> Hope it helps.
>
> Regards;
> ----------
> Koichi Suzuki
>
>
> 2012/11/6 Tatsuo Ishii <is...@po...>:
>>> One suggestion.   Because set bid value is relatively small, it may be
>>> better to distribute tables using MODULO, not HASH.
>>
>> What is the distribution method when pgbench -k is used?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp