Re: [Postgres-xc-general] gtm_problem_and_slow_write

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hello,
is it a problem of configuration? Or is it all bad? Should we wait for 
some answers?

Thank you, best regards
Matej

On 11. 11. 2013 15:28, Matej Jellus wrote:
> Hello, here is the process.
>
> When we created table with DISTRIBUTE BY REPLICATION TO NODE ( 
> datanode1 ), it has been inserting maybe 100 000 rows per minute.
> When we created table with DISTRIBUTE BY REPLICATION TO NODE ( 
> datanode1, datanode2 ), it has been inserting maybe 50 000 rows per 
> minute.
> When we created table witch  DISTRIBUTE BY MODULO ( cluster_id ) TO 
> NODE ( datanode1, datanode2 ) and has been writing to only one node, 
> it was inserting 170 000 rows per minute.
>
> But when we created it with partitioning, it has been 5000 rows per 
> minute.
>
> When we insert row into nonpartitioning table, no row was generated 
> into the gtm log file. When we insert row into partitioning table, 
> extra row is inserted into gtm log file : Saving transaction 
> restoration info, backed-up gxid: 409215
> LOCATION:  GTM_WriteRestorePointXid, gtm_txn.c:2649.
>
> When deleting or updating from nonpartitioning table, everything is 
> going quickly. But when we do the same thing with partitioning table, 
> it is 3 - 4 times slower.
>
> Below is example with partitioning. We are importing data to database, 
> so first we insert unit_id into table units. This will generate schema 
> for the year we are inserting, based on the column gps_timestamp. 
> After that it create table for the unit in the schema, which inherits 
> from primary talbe - units_data. Then we are inserting only to table 
> units_data.
>
> CREATE TABLE data.units ( unit_id uuid NOT NULL, cluster_id INT NOT 
> NULL );
>
> CREATE SEQUENCE ai_value;
>
> CREATE TABLE data.units_data (
>     row_id INTEGER NOT NULL DEFAULT nextval('ai_value'),
>     unit_id uuid NOT NULL,
>     cluster_id INT NOT NULL,
>     gps_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
>     gps_lat DOUBLE PRECISION NOT NULL,
>     gps_lng DOUBLE PRECISION NOT NULL,
>     others hstore,
>     PRIMARY KEY ( unit_id,cluster_id )
> );
>
> CREATE OR REPLACE FUNCTION generate_all(IN cluster_id INTEGER,  IN 
> unit_id UUID) RETURNS VOID AS $$
> DECLARE
>     ROK VARCHAR(4);
>     DATANODE_ID VARCHAR(1);
>     NAZOV_SCHEMY VARCHAR(13);
>     NAZOV_TABULKY VARCHAR;
>     NAZOV_END_TABULKY VARCHAR;
>     NAZOV_ROK_TABULKY VARCHAR;
> BEGIN
>     -- SELECT 2013 INTO ROK;
>     ROK := 2013;
>     SELECT cluster_id INTO DATANODE_ID;
>     NAZOV_SCHEMY := 'node_'||DATANODE_ID||'_y_'||ROK;
>     NAZOV_ROK_TABULKY := 'units_data_2013';
>     NAZOV_TABULKY := 'u_'||replace(unit_id::text,'-','');
>     NAZOV_END_TABULKY := NAZOV_TABULKY||'_m';
>     -- Ide sa vytvarat schema a tabulky
>         IF NOT EXISTS(SELECT * FROM information_schema.schemata WHERE 
> schema_name=NAZOV_SCHEMY) THEN
>             EXECUTE 'CREATE SCHEMA '||NAZOV_SCHEMY||';';
>         END IF;
>         IF NOT EXISTS(SELECT * FROM information_schema.tables where 
> table_name=NAZOV_ROK_TABULKY AND table_schema=NAZOV_SCHEMY) THEN
>             EXECUTE 'CREATE TABLE 
> '||NAZOV_SCHEMY||'.'||NAZOV_ROK_TABULKY||'( CHECK ( 
> date_part(''year''::text, gps_timestamp)=2013 ) ) INHERITS ( 
> data.units_data );';
>         END IF;
>         EXECUTE 'CREATE OR REPLACE RULE 
> "year2013_node'||DATANODE_ID||'_choice" AS ON INSERT TO 
> data.units_data WHERE date_part(''year''::text, 
> NEW.gps_timestamp)=2013 AND NEW.cluster_id='||DATANODE_ID||' DO 
> INSTEAD INSERT INTO '||NAZOV_SCHEMY||'.units_data_2013 VALUES(NEW.*);';
>
>         EXECUTE 'CREATE TABLE '||NAZOV_SCHEMY||'.'||NAZOV_TABULKY||'( 
> CHECK ( unit_id='''||unit_id||'''::uuid ) ) INHERITS 
> ('||NAZOV_SCHEMY||'.'||NAZOV_ROK_TABULKY||' );';
>
>         EXECUTE 'CREATE RULE "'||NAZOV_TABULKY||'_choice" AS ON INSERT 
> TO '||NAZOV_SCHEMY||'.units_data_2013 WHERE unit_id='''||unit_id||''' 
> DO INSTEAD INSERT INTO '||NAZOV_SCHEMY||'.'||NAZOV_TABULKY||' 
> VALUES(NEW.*);';
>     RETURN;
> END
> $$ LANGUAGE plpgsql;
>
> CREATE RULE "generate_all_rule" AS ON INSERT TO data.units DO ALSO 
> SELECT generate_all(NEW.cluster_id, NEW.unit_id);
>
> Thank you, best regards
> Matej
>
>
> On 11. 11. 2013 10:11, 鈴木 幸市 wrote:
>> This does not show anything special.   As I wrote, the messages in 
>> question don’t appear so often. Could you test how many of these 
>> messages are written using another case, for example, without using 
>> partitioning.   I think partitioning table has nothing to do with the 
>> issue but I’d like to know if the case happens in other DMLs and DDLs.
>>
>> Thank you.
>> ---
>> Koichi Suzuki
>>
>> 2013/11/11 17:49、Matej Jellus <je...@ts...> のメール：
>>
>>> Our situation :
>>> two machines
>>> first - primary GTM, coordinator, datanode
>>> second - standby GTM, coordinator, datanode
>>>
>>> GTM configuration below. We are inserting 5 thousand rows per 
>>> minute, which is too low for us. We need to insert 100 thousand rows 
>>> per minute.
>>> Starting primary GTM : /gtm_ctl -Z gtm start -D /var/pgxc/data_gtm
>>> Starting standby GTM : /gtm_ctl -Z gtm start -D /var/pgxc/data_gtm
>>> Starting coord : pg_ctl start -D /var/pgxc/ubuntu2_coord -Z 
>>> coordinator -l /tmp/logfile_ubuntu2_coord
>>>
>>> Tables in database are using partitioning. So we have one primary 
>>> table and we are creating child tables from it. For example :
>>> primary table :
>>> chema - data
>>> table - units_data
>>>
>>> child tables :
>>> schema : y_2013_node1 - tables, which will be saved in node 1
>>>    table : u_<unit_id> - child table from data.units_data
>>> schema : y_2013_node2 - tables, which will be saved in node 2
>>>    table : u_<unit_id> - child table from data.units_data
>>>
>>> Thank you, best regards
>>> Matej
>>>
>>>
>>> On 11. 11. 2013 7:49, 鈴木 幸市 wrote:
>>>> Message 1. will be written once every 2000 transactions.    The 
>>>> number looks too large.   Message 1 is written in the three cases:
>>>>
>>>> 1) When GTM starts,
>>>> 2) Once every 2000 transactions,
>>>> 3) When GTM standby is promoted to a master.
>>>>
>>>> Could you let me know how you start your cluster?    We’ve tested 
>>>> XC with DBT-1 and DBT-2 but didn’t have such frequent write at 
>>>> gtm.   You are inserting 5000 raws in a second so 2) should happen 
>>>> twice or three times a second.
>>>>
>>>> Message 2. is written when GTM standby connects to GTM. Are you 
>>>> using GTM standby?   If so, how you are starting it?
>>>>
>>>> We’re testing XC in different environment but didn’t have such GTM 
>>>> overload.   I’m interested in the situation.
>>>>
>>>> Best;
>>>> ---
>>>> Koichi Suzuki
>>>>
>>>> 2013/11/09 0:06、Matej Jellus <je...@ts...> のメール：
>>>>
>>>>> Hello,
>>>>>
>>>>> We have problem that postgres xc is very slow, gtm is writing to 
>>>>> log a
>>>>> lot of data. Is it able to be disabled?
>>>>> The most repeated lines are :
>>>>>
>>>>> 1:140130870839040:2013-11-08 15:42:49.357 CET -LOG: Saving 
>>>>> transaction
>>>>> restoration info, backed-up gxid: 373106
>>>>> LOCATION:  GTM_WriteRestorePointXid, gtm_txn.c:2649
>>>>> ( this is maybe 130 times per second )
>>>>>
>>>>> 1:140130879186688:2013-11-08 15:45:37.572 CET -LOG: Connection
>>>>> established with GTM standby. - 0x1f91398
>>>>> LOCATION:  gtm_standby_connect_to_standby_int, gtm_standby.c:400
>>>>>
>>>>> It is overloading the disk, i/o is 70%.
>>>>>
>>>>> Now it is inserting 5 thousand inserts per minute. We need more 
>>>>> inserts
>>>>> per minute.
>>>>>
>>>>> Thank you, best regards
>>>>> Matej
>>>>>
>>>>> ------------------------------------------------------------------------------ 
>>>>>
>>>>> November Webinars for C, C++, Fortran Developers
>>>>> Accelerate application performance with scalable programming 
>>>>> models. Explore
>>>>> techniques for threading, error checking, porting, and tuning. Get 
>>>>> the most
>>>>> from the latest Intel processors and coprocessors. See abstracts 
>>>>> and register
>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk 
>>>>>
>>>>> _______________________________________________
>>>>> Postgres-xc-general mailing list
>>>>> Pos...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>>>>
>>>
>

-- 

S pozdravom, best regards
*Matej Jellus*

TSS Group a.s.

*Technický pracovník - GPS monitoring*

TSS Group a.s.
K Zábraniu 1653
911 01 Trenčín
tel:  +421 32 744 5921
fax: +421 32 744 5922
e-mail:je...@ts... <mailto:je...@ts...>
url: www.tssgroup.sk <http://www.tssgroup.sk>
url: www.gpsmonitoring.sk <http://www.gpsmonitoring.sk>