Make innodb_file_per_table session variable

[Peter Zaitsev]

Innodb has design limitation which makes DROP TABLE very slow with large buffer pool:

http://bugs.mysql.com/bug.php?id=51325

This is serious problem for applications which use many transient tables (create/drop a lot of Innodb Tables)

As a workaround one can create tables in main tablespace which is a lot faster.

Having innodb_file_per_table as session variable would allow to have self contained scripts which chose to create tables
in main Innodb tablespace only for duration of their operation.

[Vadim Tkachenko (vadim-tk)]

It looks more like hack for me.

Proper solution is
to support TABLESPACE option in CREATE TABLE like in NDB.

i.e:
CREATE TABLE t1 (
    -> c1 INT STORAGE DISK,
    -> c2 INT STORAGE MEMORY
    -> ) TABLESPACE 'system'

or TABLESPACE 'local'

'system' is to put file in ibdata1,
'local' to use separate file.

[Peter Zaitsev (pz-percona)]

Vadim,

The proper solution would be to have full table space support with having 1
to many mapping, but
I think this hack would be much easier to do and the change can be small and
safe.

You also would open a can of warms this way - show create table will be
specific for Percona Server
(if you make it complete) and when it will not work with original MySQL.

So I think your suggestion is great but it is a large project which will
take a lot of time to make it right
However converting global varioable to session is something we've done many
times and usually it
has taken small amount of time to do.

On Thu, Feb 3, 2011 at 8:51 AM, Vadim Tkachenko <email address hidden> wrote:

> It looks more like hack for me.
>
> Proper solution is
> to support TABLESPACE option in CREATE TABLE like in NDB.
>
> i.e:
> CREATE TABLE t1 (
> -> c1 INT STORAGE DISK,
> -> c2 INT STORAGE MEMORY
> -> ) TABLESPACE 'system'
>
> or TABLESPACE 'local'
>
> 'system' is to put file in ibdata1,
> 'local' to use separate file.
>
> --
> You received this bug notification because you are a direct subscriber
> of the bug.
> https://bugs.launchpad.net/bugs/712591
>
> Title:
> Make innodb_file_per_table session variable
>
> Status in Percona Server with XtraDB:
> New
>
> Bug description:
> Innodb has design limitation which makes DROP TABLE very slow with
> large buffer pool:
>
> http://bugs.mysql.com/bug.php?id=51325
>
> This is serious problem for applications which use many transient
> tables (create/drop a lot of Innodb Tables)
>
> As a workaround one can create tables in main tablespace which is a
> lot faster.
>
> Having innodb_file_per_table as session variable would allow to have self
> contained scripts which chose to create tables
> in main Innodb tablespace only for duration of their operation.
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/percona-server/+bug/712591/+subscribe
>

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

Join us for Percona Live Event, San Francisco, Feb 16
http://www.percona.com/events/percona-live-san-francisco-2011/

[Valentine Gostev (longbow)]

Peter,

I may suggest another workaround:
If user needs to have some tables in their own table spaces (innodb_file_per_table) and the same time it is faster to create/drop transient tables while they are in system table space, the necessary tables are created during innodb_file_per_table is enabled, then server is restarted with innodb_file_per_table disabled:

tables created during innodb_file_per_table enabled remain in their own table spaces when we disable option, newly created tables are stored in system one.

The only limitation I see now: that if we want to create a table in separate ibd files we will have to restart the server.

[Peter Zaitsev (pz-percona)]

Valentine,

Yes. You can even change innodb_file_per_table without restart in recent
plugin version, MySQL 5.5 and Percona Server.

The problem with this approach is still the global effect on multi user
system. We have "normal" tables which when created must go
in separate tables and they are created by users in not predictable pace.
And there are transient Innodb tables which can be created in the main
tablespace for speed.

This approach though would work for tightly controlled systems which users
do not table creates/alters for non transient data

On Fri, Feb 4, 2011 at 4:58 AM, Valentine Gostev
<email address hidden>wrote:

> Peter,
>
> I may suggest another workaround:
> If user needs to have some tables in their own table spaces
> (innodb_file_per_table) and the same time it is faster to create/drop
> transient tables while they are in system table space, the necessary tables
> are created during innodb_file_per_table is enabled, then server is
> restarted with innodb_file_per_table disabled:
>
> tables created during innodb_file_per_table enabled remain in their own
> table spaces when we disable option, newly created tables are stored in
> system one.
>
> The only limitation I see now: that if we want to create a table in
> separate ibd files we will have to restart the server.
>
> --
> You received this bug notification because you are a direct subscriber
> of the bug.
> https://bugs.launchpad.net/bugs/712591
>
> Title:
> Make innodb_file_per_table session variable
>
> Status in Percona Server with XtraDB:
> New
>
> Bug description:
> Innodb has design limitation which makes DROP TABLE very slow with
> large buffer pool:
>
> http://bugs.mysql.com/bug.php?id=51325
>
> This is serious problem for applications which use many transient
> tables (create/drop a lot of Innodb Tables)
>
> As a workaround one can create tables in main tablespace which is a
> lot faster.
>
> Having innodb_file_per_table as session variable would allow to have self
> contained scripts which chose to create tables
> in main Innodb tablespace only for duration of their operation.
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/percona-server/+bug/712591/+subscribe
>

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

Join us for Percona Live Event, San Francisco, Feb 16
http://www.percona.com/events/percona-live-san-francisco-2011/

[Stewart Smith (stewart)]

problem with session variable, a mysqldump and restore would change how tables are stored even with exact same server config.

[Valentine Gostev (longbow)]

The fix for bug http://bugs.mysql.com/bug.php?id=51325 has been pushed to patch innodb_fix_misc. Is there still need adjust a session scope to innodb_file_per_table?

[Shahriyar Rzayev (rzayev-sehriyar)]

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