Max's Blog

ibdata1这个垃圾文件除了reload整个database是没办法缩小的了，每个table用不同的索引文件看上去会舒服一点。。。

13.2.3.1. Using Per-Table Tablespaces

You can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining InnoDB tables.

You can enable multiple tablespaces by adding this line to the [mysqld] section of my.cnf:

[mysqld]
innodb_file_per_table

After restarting the server, InnoDB stores each newly created table into its own file tbl_name.ibd in the database directory where the table belongs. This is similar to what the MyISAM storage engine does, but MyISAM divides the table into a data file tbl_name.MYD and the index file tbl_name.MYI. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

If you remove the innodb_file_per_table line from my.cnf and restart the server, InnoDB creates tables inside the shared tablespace files again.

innodb_file_per_table affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you remove the option and restart the server, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

In this context, a “clean” .ibd file backup means:

You can make a clean backup .ibd file using the following method:

Another method for making a clean copy of an .ibd file is to use the commercial InnoDB Hot Backup tool:

昨晚在sun的论坛问了一下，今天既然10几篇回复，真tmd热情。。。

完善了一下那个简单的脚本，应该可以用了。。。

#!/usr/sbin/dtrace -s
#pragma D option quiet

dtrace:::BEGIN
{
 self->index = 0;
 tstart = timestamp;
}

hotspot$1:::method-entry
{
mname = strjoin(strjoin(copyinstr(arg1,arg2),”->”),copyinstr(arg3,arg4));
self->index++;
self->mname[self->index] = mname;
self->mtime[self->index] = timestamp;
@count[mname] = count();
}

hotspot$1:::method-return
{
mname = strjoin(strjoin(copyinstr(arg1,arg2),”->”),copyinstr(arg3,arg4));
mindex = (self->mname[self->index] == mname) ? -1 : 0;
mtime = (mindex == -1) ? self->mtime[self->index] : tstart;

/*
mname = (mindex == -1) ? mname : strjoin(“non-start-“, mname);
printf(“method : %s : start : %d\n”, mname, tstart);
*/

@time[mname] = avg(timestamp – mtime);
self->index += mindex;
}