You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Matteo Durighetto <m....@miriade.it> on 2020/10/26 08:58:57 UTC
alter table logging slow
Hello,
I found an expected behaviour on apache ignite.
If you do ( TABLE partitioned with 1 backup and ATOMIC mode ):
alter table .. nologging;
set stream on ;
.. loading a lot of data with jdbc ..
.. close connection to flush data ..
.. reopen connection..
alter table .. logging
The last step "alter table logging" writes a lot of data and it's "slow",
if we load the table
with 26 threads in about 10 minute and every thread uses a dedicated
partition, , the nologging phase is around 11 minutes.
I would like to understand what it is doing and how to speed up the
process, I try to understand from SqlAlterTableCommand.java what it's
doing, but it's not straightforward.
Kind Regards
Matteo Durighetto
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
M I R I A
D E - P L A Y T H E C H A N G E
Via Castelletto 11, 36016 Thiene VI
Tel. 0445030111 - Fax 0445030100
Website: http://www.miriade.it/
<http://www.miriade.it/>
. . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
*Clausola di riservatezza. *Le
informazioni contenute o allegate al presente messaggio sono dirette
unicamente al Destinatario sopra indicato. In caso di ricezione da parte di
persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque
riceva questa comunicazione per errore è tenuto ad informare immediatamente
il mittente e a distruggere il messaggio. Si informa che un eventuale
trattamento di dati personali contenuti nella presente comunicazione, in
assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR),
è punito secondo la normativa vigente in materia. Si informa che la casella
di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri
dipendenti e collaboratori esclusivamente per rendere la prestazione
lavorativa.
*Confidentiality notice.* This email and any files
transmitted with it are confidential and intended solely for the use of the
individual or entity to whom they are addressed. This message contains
confidential information and is intended only for the individual named. If
you are not the named addressee you should not disseminate, distribute or
copy this e-mail. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. Any form of data processing in absence of the lawfulness principles
provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will
be punishable by law. This e-mail address is property of Miriade S.r.l. and
it is used by its employees solely for working.
Re: alter table logging slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Usually it is I/O bound, meaning, you can only speed it up by having more,
faster disks.
However, in some cases, tuning persistence settings such as checkpoint
frequency, checkpoint page buffer size and checkpoint thread pool size may
help. You can also try the direct-io module, but you may see mixed results.
Regards,
--
Ilya Kasnacheev
пн, 26 окт. 2020 г. в 16:00, Matteo Durighetto <m....@miriade.it>:
> Hello Ilya ,
> thank you for your answer, it's make sense. I see
> the class called it's changewal and as you wrote, it's probably call a
> flush of the cache .
> Is it possible to speed up the flush to disk with more threads ? I think
> they are controlled by system pool or is it another parameter?
>
> Kind regards
>
> Matteo Durighetto
>
>
>
>
>
> Il giorno lun 26 ott 2020 alle ore 13:05 Ilya Kasnacheev <
> ilya.kasnacheev@gmail.com> ha scritto:
>
>> Hello!
>>
>> I guess it has to write all the data to disk. After 'alter table logging'
>> returns, you are guaranteed consistency on this table, meaning all of its
>> pages have to be persisted to disk. Obviously, it may take a lot of time if
>> you have many gigabytes to flush.
>>
>> Regards,
>> --
>> Ilya Kasnacheev
>>
>>
>> пн, 26 окт. 2020 г. в 11:59, Matteo Durighetto <m....@miriade.it>:
>>
>>> Hello,
>>> I found an expected behaviour on apache ignite.
>>> If you do ( TABLE partitioned with 1 backup and ATOMIC mode ):
>>>
>>> alter table .. nologging;
>>> set stream on ;
>>> .. loading a lot of data with jdbc ..
>>> .. close connection to flush data ..
>>> .. reopen connection..
>>> alter table .. logging
>>>
>>> The last step "alter table logging" writes a lot of data and it's
>>> "slow", if we load the table
>>> with 26 threads in about 10 minute and every thread uses a dedicated
>>> partition, , the nologging phase is around 11 minutes.
>>>
>>> I would like to understand what it is doing and how to speed up the
>>> process, I try to understand from SqlAlterTableCommand.java what it's
>>> doing, but it's not straightforward.
>>>
>>> Kind Regards
>>>
>>> Matteo Durighetto
>>>
>>>
>>>
>>>
>>> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>>>
>>> M I R I A D E - P L A Y T H E C H A N G E
>>>
>>> Via Castelletto 11, 36016 Thiene VI
>>> Tel. 0445030111 - Fax 0445030100
>>> Website: http://www.miriade.it/
>>>
>>> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
>>> *Clausola di riservatezza. *Le informazioni contenute o allegate al presente messaggio sono dirette unicamente al Destinatario sopra indicato. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque riceva questa comunicazione per errore è tenuto ad informare immediatamente il mittente e a distruggere il messaggio. Si informa che un eventuale trattamento di dati personali contenuti nella presente comunicazione, in assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR), è punito secondo la normativa vigente in materia. Si informa che la casella di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri dipendenti e collaboratori esclusivamente per rendere la prestazione lavorativa.
>>>
>>> *Confidentiality notice.* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. Any form of data processing in absence of the lawfulness principles provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will be punishable by law. This e-mail address is property of Miriade S.r.l. and it is used by its employees solely for working.
>>>
>>>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> M I R I A D E - P L A Y T H E C H A N G E
>
> Via Castelletto 11, 36016 Thiene VI
> Tel. 0445030111 - Fax 0445030100
> Website: http://www.miriade.it/
>
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> *Clausola di riservatezza. *Le informazioni contenute o allegate al presente messaggio sono dirette unicamente al Destinatario sopra indicato. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque riceva questa comunicazione per errore è tenuto ad informare immediatamente il mittente e a distruggere il messaggio. Si informa che un eventuale trattamento di dati personali contenuti nella presente comunicazione, in assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR), è punito secondo la normativa vigente in materia. Si informa che la casella di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri dipendenti e collaboratori esclusivamente per rendere la prestazione lavorativa.
>
> *Confidentiality notice.* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. Any form of data processing in absence of the lawfulness principles provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will be punishable by law. This e-mail address is property of Miriade S.r.l. and it is used by its employees solely for working.
>
>
Re: alter table logging slow
Posted by Matteo Durighetto <m....@miriade.it>.
Hello Ilya ,
thank you for your answer, it's make sense. I see
the class called it's changewal and as you wrote, it's probably call a
flush of the cache .
Is it possible to speed up the flush to disk with more threads ? I think
they are controlled by system pool or is it another parameter?
Kind regards
Matteo Durighetto
Il giorno lun 26 ott 2020 alle ore 13:05 Ilya Kasnacheev <
ilya.kasnacheev@gmail.com> ha scritto:
> Hello!
>
> I guess it has to write all the data to disk. After 'alter table logging'
> returns, you are guaranteed consistency on this table, meaning all of its
> pages have to be persisted to disk. Obviously, it may take a lot of time if
> you have many gigabytes to flush.
>
> Regards,
> --
> Ilya Kasnacheev
>
>
> пн, 26 окт. 2020 г. в 11:59, Matteo Durighetto <m....@miriade.it>:
>
>> Hello,
>> I found an expected behaviour on apache ignite.
>> If you do ( TABLE partitioned with 1 backup and ATOMIC mode ):
>>
>> alter table .. nologging;
>> set stream on ;
>> .. loading a lot of data with jdbc ..
>> .. close connection to flush data ..
>> .. reopen connection..
>> alter table .. logging
>>
>> The last step "alter table logging" writes a lot of data and it's "slow",
>> if we load the table
>> with 26 threads in about 10 minute and every thread uses a dedicated
>> partition, , the nologging phase is around 11 minutes.
>>
>> I would like to understand what it is doing and how to speed up the
>> process, I try to understand from SqlAlterTableCommand.java what it's
>> doing, but it's not straightforward.
>>
>> Kind Regards
>>
>> Matteo Durighetto
>>
>>
>>
>>
>> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>>
>> M I R I A D E - P L A Y T H E C H A N G E
>>
>> Via Castelletto 11, 36016 Thiene VI
>> Tel. 0445030111 - Fax 0445030100
>> Website: http://www.miriade.it/
>>
>> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
>> *Clausola di riservatezza. *Le informazioni contenute o allegate al presente messaggio sono dirette unicamente al Destinatario sopra indicato. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque riceva questa comunicazione per errore è tenuto ad informare immediatamente il mittente e a distruggere il messaggio. Si informa che un eventuale trattamento di dati personali contenuti nella presente comunicazione, in assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR), è punito secondo la normativa vigente in materia. Si informa che la casella di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri dipendenti e collaboratori esclusivamente per rendere la prestazione lavorativa.
>>
>> *Confidentiality notice.* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. Any form of data processing in absence of the lawfulness principles provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will be punishable by law. This e-mail address is property of Miriade S.r.l. and it is used by its employees solely for working.
>>
>>
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
M I R I A
D E - P L A Y T H E C H A N G E
Via Castelletto 11, 36016 Thiene VI
Tel. 0445030111 - Fax 0445030100
Website: http://www.miriade.it/
<http://www.miriade.it/>
. . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . .
*Clausola di riservatezza. *Le
informazioni contenute o allegate al presente messaggio sono dirette
unicamente al Destinatario sopra indicato. In caso di ricezione da parte di
persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque
riceva questa comunicazione per errore è tenuto ad informare immediatamente
il mittente e a distruggere il messaggio. Si informa che un eventuale
trattamento di dati personali contenuti nella presente comunicazione, in
assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR),
è punito secondo la normativa vigente in materia. Si informa che la casella
di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri
dipendenti e collaboratori esclusivamente per rendere la prestazione
lavorativa.
*Confidentiality notice.* This email and any files
transmitted with it are confidential and intended solely for the use of the
individual or entity to whom they are addressed. This message contains
confidential information and is intended only for the individual named. If
you are not the named addressee you should not disseminate, distribute or
copy this e-mail. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. Any form of data processing in absence of the lawfulness principles
provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will
be punishable by law. This e-mail address is property of Miriade S.r.l. and
it is used by its employees solely for working.
Re: alter table logging slow
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I guess it has to write all the data to disk. After 'alter table logging'
returns, you are guaranteed consistency on this table, meaning all of its
pages have to be persisted to disk. Obviously, it may take a lot of time if
you have many gigabytes to flush.
Regards,
--
Ilya Kasnacheev
пн, 26 окт. 2020 г. в 11:59, Matteo Durighetto <m....@miriade.it>:
> Hello,
> I found an expected behaviour on apache ignite.
> If you do ( TABLE partitioned with 1 backup and ATOMIC mode ):
>
> alter table .. nologging;
> set stream on ;
> .. loading a lot of data with jdbc ..
> .. close connection to flush data ..
> .. reopen connection..
> alter table .. logging
>
> The last step "alter table logging" writes a lot of data and it's "slow",
> if we load the table
> with 26 threads in about 10 minute and every thread uses a dedicated
> partition, , the nologging phase is around 11 minutes.
>
> I would like to understand what it is doing and how to speed up the
> process, I try to understand from SqlAlterTableCommand.java what it's
> doing, but it's not straightforward.
>
> Kind Regards
>
> Matteo Durighetto
>
>
>
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> M I R I A D E - P L A Y T H E C H A N G E
>
> Via Castelletto 11, 36016 Thiene VI
> Tel. 0445030111 - Fax 0445030100
> Website: http://www.miriade.it/
>
> . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
> *Clausola di riservatezza. *Le informazioni contenute o allegate al presente messaggio sono dirette unicamente al Destinatario sopra indicato. In caso di ricezione da parte di persona diversa è vietato qualunque tipo di distribuzione o copia. Chiunque riceva questa comunicazione per errore è tenuto ad informare immediatamente il mittente e a distruggere il messaggio. Si informa che un eventuale trattamento di dati personali contenuti nella presente comunicazione, in assenza dei presupposti di liceità previsti dall’art 6 Reg. 679/16 (GDPR), è punito secondo la normativa vigente in materia. Si informa che la casella di posta elettronica è di titolarità di Miriade S.r.l. è concessa ai propri dipendenti e collaboratori esclusivamente per rendere la prestazione lavorativa.
>
> *Confidentiality notice.* This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. Any form of data processing in absence of the lawfulness principles provided by the art. 6 of Reg. UE 697/16 (GDPR) is prohibited and it will be punishable by law. This e-mail address is property of Miriade S.r.l. and it is used by its employees solely for working.
>
>