Change behavior of innodb_flush_log_at_trx_commit_session variable

[Vadim Tkachenko]

1. We need to check if we can define innodb_flush_log_at_trx_commit as session variable, so one can change it as
SET SESSION innodb_flush_log_at_trx_commit=<new value>

2. If that is not possible we need to change innodb_flush_log_at_trx_commit_session behavior.

New behavior :
-It accepts only 0-2 values.
-when connection starts innodb_flush_log_at_trx_commit_session takes value from innodb_flush_log_at_trx_commit.

[Yasufumi Kinoshita (yasufumi-kinoshita)]

innodb_flush_log_at_trx_commit is currently global variable.
If it changes to session value, the compatibility is broken.

For session variable, the GLOBAL is only default value when starting session.

I think you ignore the compatibility. I cannot accept it.

[Vadim Tkachenko (vadim-tk)]

Can you show me scenario when compatibility is broken?
I do not see it.

On Sun, Sep 26, 2010 at 10:21 PM, Yasufumi Kinoshita
<email address hidden> wrote:
> innodb_flush_log_at_trx_commit is currently global variable.
> If it changes to session value, the compatibility is broken.
>
> For session variable, the GLOBAL is only default value when starting
> session.
>
> I think you ignore the compatibility. I cannot accept it.
>
> --
> Change behavior of innodb_flush_log_at_trx_commit_session variable
> https://bugs.launchpad.net/bugs/635399
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Percona Server with XtraDB: Triaged
>
> Bug description:
> 1. We need to check if we can define innodb_flush_log_at_trx_commit as session variable, so one can change it as
> SET SESSION innodb_flush_log_at_trx_commit=<new value>
>
> 2. If that is not possible we need to change innodb_flush_log_at_trx_commit_session behavior.
>
> New behavior :
> -It accepts only 0-2 values.
> -when connection starts innodb_flush_log_at_trx_commit_session takes value from innodb_flush_log_at_trx_commit.
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/percona-server/+bug/635399/+subscribe
>

--
Vadim Tkachenko, CTO, Percona Inc.
Phone [phone],  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

[Peter Zaitsev (pz-percona)]

Yasufumi,

Can you come up with use case when compatibility will really be broken (not
in theory) ?

If it changes to be SESSION variable changing it in my.cnf file will still
work
Also SET GLOBAL innodb_flush_log_at_trx_commit=X will also work for all
practical purposes - the new transactions in
other connections will use new behavior.

On Sun, Sep 26, 2010 at 10:21 PM, Yasufumi Kinoshita <
<email address hidden>> wrote:

> innodb_flush_log_at_trx_commit is currently global variable.
> If it changes to session value, the compatibility is broken.
>
> For session variable, the GLOBAL is only default value when starting
> session.
>
> I think you ignore the compatibility. I cannot accept it.
>
> --
> Change behavior of innodb_flush_log_at_trx_commit_session variable
> https://bugs.launchpad.net/bugs/635399
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
>
> Status in Percona Server with XtraDB: Triaged
>
> Bug description:
> 1. We need to check if we can define innodb_flush_log_at_trx_commit as
> session variable, so one can change it as
> SET SESSION innodb_flush_log_at_trx_commit=<new value>
>
> 2. If that is not possible we need to change
> innodb_flush_log_at_trx_commit_session behavior.
>
> New behavior :
> -It accepts only 0-2 values.
> -when connection starts innodb_flush_log_at_trx_commit_session takes value
> from innodb_flush_log_at_trx_commit.
>
>
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Training Workshops
http://www.percona.com/training/

[Yasufumi Kinoshita (yasufumi-kinoshita)]

"
Also SET GLOBAL innodb_flush_log_at_trx_commit=X will also work for all
practical purposes - the new transactions in
other connections will use new behavior.
"
it is wrong.

All thd values are made independent.
So, not affect to new transactions on exist sessions.
(affect new sessions only)

[Peter Zaitsev (pz-percona)]

Yasufumi,

Yeas I understand that.... but people do not set this option back and forth.

I think it is a lot less confusing than adding another variable with very
complex logic.

If you want 100% compatibility we can go the same way as with long query
time and have
variable use_session_innodb_flush_log_at_trx_commit which you would need
to set to 1
to enable such behavior.

On Sun, Sep 26, 2010 at 11:08 PM, Yasufumi Kinoshita <
<email address hidden>> wrote:

> "
> Also SET GLOBAL innodb_flush_log_at_trx_commit=X will also work for all
> practical purposes - the new transactions in
> other connections will use new behavior.
> "
> it is wrong.
>
> All thd values are made independent.
> So, not affect to new transactions on exist sessions.
> (affect new sessions only)
>
> --
> Change behavior of innodb_flush_log_at_trx_commit_session variable
> https://bugs.launchpad.net/bugs/635399
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona Server.
>
> Status in Percona Server with XtraDB: Triaged
>
> Bug description:
> 1. We need to check if we can define innodb_flush_log_at_trx_commit as
> session variable, so one can change it as
> SET SESSION innodb_flush_log_at_trx_commit=<new value>
>
> 2. If that is not possible we need to change
> innodb_flush_log_at_trx_commit_session behavior.
>
> New behavior :
> -It accepts only 0-2 values.
> -when connection starts innodb_flush_log_at_trx_commit_session takes value
> from innodb_flush_log_at_trx_commit.
>
>
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Training Workshops
http://www.percona.com/training/

[Yasufumi Kinoshita (yasufumi-kinoshita)]

How about
innodb_use_global_flush_log_at_trx_commit = [0|1] ?
(default 1)

0: session use
1: keep compatibility

[Vadim Tkachenko (vadim-tk)]

Yasufumi,

I accept it.
Please implement.

[Fred Linhoss (fred-linhoss)]

Documentation changes:

Page changed:
http://www.percona.com/docs/wiki/percona-server:features:innodb_io

Indexes updated (added innodb_use_global_flush_log_at_trx_commit):
http://www.percona.com/docs/wiki/percona-server:features:indexes:index_system_variables
http://www.percona.com/docs/wiki/percona-server:features:indexes:variable_reference

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-426