postgres-xc-general

[Postgres-xc-general] gtm_problem_and_slow_write

[Matej J. <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

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[鈴木 幸市 <ko...@in...>]

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
> 

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[Matej J. <je...@ts...>]

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

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[Matej J. <je...@ts...>]

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>

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[鈴木 幸市 <ko...@in...>]

I’m waiting for a opinion from the guy familiar with this.

Sorry for inconvenience.
---
Koichi Suzuki

2013/11/20 17:31、Matej Jellus <je...@ts...<mailto:je...@ts...>> のメール：

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...><mailto: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...><mailto: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...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general




--

S pozdravom, best regards
                                                                                                               Matej Jellus

<mail_podpis.png>

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

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[Matej J. <je...@ts...>]

Hello,
we solved the problem with GTM - when we deleted the sequence it stopped 
writing extra data to log file. But the volume of inserted data per 
minute is still very small.

On 27. 11. 2013 10:52, Koichi Suzuki wrote:
> This could be a cause.   Are you using sequence heavily?  GTM backs up 
> its restart point every 2000 updates of Transaction ID or each 
> sequences value.
>
> Regards;
>
> ---
> Koichi Suzuki
>
>
> 2013/11/27 Matej Jellus <je...@ts... <mailto:je...@ts...>>
>
>     The value on the first line is increasing realy slow. The sequence
>     is increasing fast.
>     gtm.control :
>     prev :
>     727618
>     gps.public.ai_value\00  30957   1       1       1
>     9223372036854775806     f       t       1
>     next :
>     727618
>     gps.public.ai_value\00  30958   1       1       1
>     9223372036854775806     f       t       1
>
>
>     Thank you, best regards
>     Matej
>
>     On 27. 11. 2013 10:33, Koichi Suzuki wrote:
>>     Could you visit GTM's data directory and watch the file gtm.control?
>>     The first line is current restart point of GXID value.   If you
>>     watch this value periodically, it shows how many transactions are
>>     coming to GTM.
>>
>>     If the value does not increase so much, then please see another
>>     line, which are current value of sequences.   This will tell how
>>     often GTM has to backup itself.  If not there could be something
>>     wrong in GTM.
>>
>>     Best Regards;
>>
>>     ---
>>     Koichi Suzuki
>>
>>
>>     2013/11/27 Matej Jellus <je...@ts...
>>     <mailto:je...@ts...>>
>>
>>         Hello,
>>         I don't understand how it can create so many transactions. If
>>         I start phppgadmin, create non-partitioning table and insert
>>         there one row, nothing is written to log file. If I create
>>         partitioning table and insert there row, it writes
>>         'Saving transaction restoration info, backed-up gxid' to log
>>         file. If I send there "INSERT INTO <table> VALUES(...),
>>         (...), (...);" it writes that row three times to log file. I
>>         don't know how it is related to the partitioning, but it
>>         surely has dependencies.
>>
>>
>>         Thank you, best regards
>>         Matej
>>
>>         On 25. 11. 2013 8:47, Koichi Suzuki wrote:
>>>         Your experiment says that your application created more than
>>>         hundreds of thousand of transactions in a second.    Write
>>>         to GTM will be done once every two thousand transactions.  
>>>          I'm wondering how it happens and how it is related to the
>>>         partitioning.
>>>
>>>         Regards;
>>>
>>>         ---
>>>         Koichi Suzuki
>>>
>>>
>>>         2013/11/20 Matej Jellus <je...@ts...
>>>         <mailto:je...@ts...>>
>>>
>>>             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...>
>>>>>             <mailto: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...>
>>>>>>>             <mailto: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...
>>>>>>>>             <mailto: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 <tel:%2B421%2032%20744%205921>
>>>             fax: +421 32 744 5922 <tel:%2B421%2032%20744%205922>
>>>             e-mail:je...@ts... <mailto:je...@ts...>
>>>             url: www.tssgroup.sk <http://www.tssgroup.sk>
>>>             url: www.gpsmonitoring.sk <http://www.gpsmonitoring.sk>
>>>
>>>
>>>             ------------------------------------------------------------------------------
>>>             Shape the Mobile Experience: Free Subscription
>>>             Software experts and developers: Be at the forefront of
>>>             tech innovation.
>>>             Intel(R) Software Adrenaline delivers strategic insight
>>>             and game-changing
>>>             conversations that shape the rapidly evolving mobile
>>>             landscape. Sign up now.
>>>             http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
>>>             _______________________________________________
>>>             Postgres-xc-general mailing list
>>>             Pos...@li...
>>>             <mailto: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 <tel:%2B421%2032%20744%205921>
>>         fax: +421 32 744 5922 <tel:%2B421%2032%20744%205922>
>>         e-mail:je...@ts... <mailto:je...@ts...>
>>         url: www.tssgroup.sk <http://www.tssgroup.sk>
>>         url: www.gpsmonitoring.sk <http://www.gpsmonitoring.sk>
>>
>>
>
>     -- 
>
>     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 <tel:%2B421%2032%20744%205921>
>     fax: +421 32 744 5922 <tel:%2B421%2032%20744%205922>
>     e-mail:je...@ts... <mailto:je...@ts...>
>     url: www.tssgroup.sk <http://www.tssgroup.sk>
>     url: www.gpsmonitoring.sk <http://www.gpsmonitoring.sk>
>
>

Re: [Postgres-xc-general] gtm_problem_and_slow_write

[鈴木 幸市 <ko...@in...>]

Sorry for the late response.     I suspect that you’re using serval() for all the DML.    If so, I advice to use nextval() instead by setting up increment value to your most favorite value.   Or, if you use setval(), you should specify increment value to the most common value you use.

This decreases a chance that GTM determines sequence value is incremented too much and have to backup the next value.

Good luck;
---
Koichi Suzuki

2013/11/30 0:31、Matej Jellus <je...@ts...<mailto:je...@ts...>> のメール：

Hello,
we solved the problem with GTM - when we deleted the sequence it stopped writing extra data to log file. But the volume of inserted data per minute is still very small.

On 27. 11. 2013 10:52, Koichi Suzuki wrote:
This could be a cause.   Are you using sequence heavily?  GTM backs up its restart point every 2000 updates of Transaction ID or each sequences value.

Regards;

---
Koichi Suzuki


2013/11/27 Matej Jellus <je...@ts...<mailto:je...@ts...>>
The value on the first line is increasing realy slow. The sequence is increasing fast.
gtm.control :
prev :
727618
gps.public.ai_value\00  30957   1       1       1       9223372036854775806     f       t       1
next :
727618
gps.public.ai_value\00  30958   1       1       1       9223372036854775806     f       t       1


Thank you, best regards
Matej

On 27. 11. 2013 10:33, Koichi Suzuki wrote:
Could you visit GTM's data directory and watch the file gtm.control?
The first line is current restart point of GXID value.   If you watch this value periodically, it shows how many transactions are coming to GTM.

If the value does not increase so much, then please see another line, which are current value of sequences.   This will tell how often GTM has to backup itself.  If not there could be something wrong in GTM.

Best Regards;

---
Koichi Suzuki


2013/11/27 Matej Jellus <je...@ts...<mailto:je...@ts...>>
Hello,
I don't understand how it can create so many transactions. If I start phppgadmin, create non-partitioning table and insert there one row, nothing is written to log file. If I create partitioning table and insert there row, it writes
'Saving transaction restoration info, backed-up gxid' to log file. If I send there "INSERT INTO <table> VALUES(...), (...), (...);" it writes that row three times to log file. I don't know how it is related to the partitioning, but it surely has dependencies.


Thank you, best regards
Matej

On 25. 11. 2013 8:47, Koichi Suzuki wrote:
Your experiment says that your application created more than hundreds of thousand of transactions in a second.    Write to GTM will be done once every two thousand transactions.    I'm wondering how it happens and how it is related to the partitioning.

Regards;

---
Koichi Suzuki


2013/11/20 Matej Jellus <je...@ts...<mailto:je...@ts...>>
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...><mailto: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...><mailto: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...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general




--

S pozdravom, best regards
                                                                                                               Matej Jellus

<メールの添付ファイル.png>

Technický pracovník - GPS monitoring

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

------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing
conversations that shape the rapidly evolving mobile landscape. Sign up now.
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-general mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general



--

S pozdravom, best regards
                                                                                                               Matej Jellus

<メールの添付ファイル.png>

Technický pracovník - GPS monitoring

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


--

S pozdravom, best regards
                                                                                                               Matej Jellus

<メールの添付ファイル.png>

Technický pracovník - GPS monitoring

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


------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk_______________________________________________
Postgres-xc-general mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-general