Menu
▾
▴
Re: [Postgres-xc-general] gtm_problem_and_slow_write
|
From: Matej J. <je...@ts...> - 2013-11-29 15:31:19
|
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>
>
>
|
