"Kill Idle Transactions" can do on Server?

[彭立勋]

Now "Kill Idle Transactions" function only can used by InnoDB storage engine.

If do that in Server, we can only change function code of do_command() as follows:

--- Percona-Server-5.1.59/sql/sql_parse.cc  2011-10-16 22:16:06.000000000 +0800

+++ Percona-Server-5.1.59-debug/sql/sql_parse.cc    2011-12-21 16:17:18.000000000 +0800

@@ -828,7 +828,11 @@

     the client, the connection is closed or "net_wait_timeout"

     number of seconds has passed

   */

-  my_net_set_read_timeout(net, thd->variables.net_wait_timeout);

+  if (thd->active_transaction()) {

+      my_net_set_read_timeout(net, thd->variables.changes_trx_idle_timeout);

+  } else {

+      my_net_set_read_timeout(net, thd->variables.net_wait_timeout);

+  }

   /*

     XXX: this code is here only to clear possible errors of init_connect.

I run my test case and success passed.

Is any other problem for change these code. 

Very appriciate for your advise.

--
Best Wishes

Lixun Peng (P.Linux)

Mobile Phone:
+86 18658156856 (Hangzhou)
Gtalk: penglixun(at)gmail.com
Twitter: http://www.twitter.com/plinux
Blog: http://www.penglixun.com

[Alexey Kopytov]

Hi Lixun,

On 21.12.11 12:46, 彭立勋 wrote:
> Now "Kill Idle Transactions" function only can used by InnoDB storage
> engine.
> If do that in Server, we can only change function code of do_command() as
> follows:
>

> *--- Percona-Server-5.1.59/sql/sql_parse.cc 2011-10-16 22:16:06.000000000
> +0800*
> *+++ Percona-Server-5.1.59-debug/sql/sql_parse.cc 2011-12-21
> 16:17:18.000000000 +0800*
> *@@ -828,7 +828,11 @@*
> * the client, the connection is closed or "net_wait_timeout"*
> * number of seconds has passed*
> * */*
> *- my_net_set_read_timeout(net, thd->variables.net_wait_timeout);*
> *+ if (thd->active_transaction()) {*
> *+ my_net_set_read_timeout(net,
> thd->variables.changes_trx_idle_timeout);*
> *+ } else {*
> *+ my_net_set_read_timeout(net, thd->variables.net_wait_timeout);*
> *+ }*
> *

The only minor problem with this implementation that I see is that it
should first check if the feature is on (i.e. changes_trx_idle_timeout >
0) and if it is, it should use the minimum of changes_trx_idle_timeout
and net_wait_timeout as the timeout value.

But generally I think it's a better approach to implement the feature.
One problem with the current implementation that it solves is that it
doesn't lock kernel_mutex once per second, even though the current
implementation does that for a very short period of time.

Regards,
Alexey

[彭立勋]

We should not kill the read only transaction I think. 

As you can see, the transaction only include select will not occupied in trx_list struct in 5.6.4 milestone.

We can provide user of three variable : changes_trx_idle_timeout, idle_trx_timeout, readonly_trx_idle_timeout.

idle_trx_timeout is the same as percona "kill idle transaction", it will kill all the transaction which exceed the time of idle_trx_timeout. Priority first.

changes_trx_idle_timeout will kill the transaction with "update", "delete", "insert" queries. 

readonly_trx_idle_timeout will kill the transaction with only "select" queries.

Because the "select transaction" require no locks,  the lock on undo segment is no matter to the server, it only cause the undo space larger. Most of user does not care about the undo space.

root@localhost : (none) 11:17:22> show  variables like "%idle%";

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| changes_trx_idle_timeout  | 10    |

| idle_trx_timeout   | 0     |

| readonly_trx_idle_timeout | 28800 |

+---------------------------+-------+

2011/12/22 Alexey Kopytov <alexey....@percona.com>

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

[Alexey Kopytov]

Lixun,

On 23.12.11 7:32, 彭立勋 wrote:
> We should not kill the read only transaction I think.
> As you can see, the transaction only include select will not occupied in
> trx_list struct in 5.6.4 milestone.
> We can provide user of three variable
> : changes_trx_idle_timeout, idle_trx_timeout, readonly_trx_idle_timeout.
> idle_trx_timeout is the same as percona "kill idle transaction", it will
> kill all the transaction which exceed the time of idle_trx_timeout.
> Priority first.
> changes_trx_idle_timeout will kill the transaction with "update", "delete",
> "insert" queries.
> readonly_trx_idle_timeout will kill the transaction with only "select"
> queries.
> Because the "select transaction" require no locks, the lock on undo
> segment is no matter to the server, it only cause the undo space larger.
> Most of user does not care about the undo space.
>

It actually does matter, the main goal of implementing
innodb_kill_idle_transaction was to prevent the undo log from growing
infinitely when the purge thread is blocked by a stalled transaction,
even if it's read-only.

But I agree, having a separate timeout value for read-only transactions
would make sense in some cases. Feel free to file a feature request.

Regards,
Alexey