mysql5.6-独立undo log

参考：http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html


This feature involves the following new or renamed configuration options:

innodb_undo_tablespaces
innodb_undo_directory
innodb_rollback_segments becomes innodb_undo_logs. The old name is still available for compatibility.


To use this feature, follow these steps:


Decide on a path to hold the undo logs. You will specify that path as the argument to the innodb_undo_directory option in your MySQL configuration file or startup script. For embedded MySQL installations, an absolute path must be specified.


Decide on a starting value for the innodb_undo_logs option. You can start with a relatively low value and increase it over time to examine the effect on performance.


Decide on a non-zero value for the innodb_undo_tablespaces option. The multiple undo logs specified by the innodb_undo_logs value are divided between this number of separate tablespaces (represented by .ibd files). This value is fixed for the life of the MySQL instance, so if you are uncertain about the optimal value, estimate on the high side.


Create a new MySQL instance, using the values you chose in the configuration file or in your MySQL startup script. Use a realistic workload with data volume similar to your production servers. Alternatively, use the transportable tablespaces feature to copy existing database tables to your newly configured MySQL instance. See Section 14.4.6, “Copying File-Per-Table Tablespaces to Another Server” for more information.


Benchmark the performance of I/O intensive workloads.


Periodically increase the value of innodb_undo_logs and rerun performance tests. Find the value where you stop experiencing gains in I/O performance.


Deploy a new production instance using the ideal settings for these options. Set it up as a slave server in a replication configuration, or transfer data from an earlier production instance.


*****************
innodb_undo_tablespaces:
用于设定创建的undo表空间的个数，在Install db时初始化后，就再也不能被改动了；


默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为16，那么就会创建命名为undo001~undo016的undo tablespace文件，每个文件的默认大小为10M


修改该值可能会导致Innodb无法完成初始化


innodb_undo_directory:
当开启独立undo表空间时，指定undo文件存放的目录


如果我们想转移undo文件的位置，只需要修改下该配置，并将undo文件拷贝过去就可以了。


innodb_undo_logs:
用于表示回滚段的个数（早期版本的命名为 innodb_rollback_segments  ），该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数;


默认为128个回滚段


*******************


Use the VARCHAR data type instead of CHAR to store variable-length strings or for columns with many NULL values. A CHAR(N) column always takes
 N characters to store data, even if the string is shorter or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O.


InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. When each change is followed by a commit (as with the default autocommit setting), the I/O throughput of the storage device puts a cap on the number of potential operations per second.


关于big事务和rollback


Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows. If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially taking several times as long to perform as the original DML operations. Killing the database process does not help, because the rollback starts again on server startup.


To minimize the chance of this issue occurring:


Increase the size of the buffer pool so that all the DML changes can be cached rather than immediately written to disk.


Set innodb_change_buffering=all so that update and delete operations are buffered in addition to inserts.


Consider issuing COMMIT statements periodically during the big DML operation, possibly breaking a single delete or update into multiple statements that operate on smaller numbers of rows.


To get rid of a runaway rollback once it occurs, increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or kill the server and restart with innodb_force_recovery=3, as explained in Section 14.15.1, “The InnoDB Recovery Process”.


This issue is expected to be less prominent in MySQL 5.5 and higher because the default setting innodb_change_buffering=all allows update and delete operations to be cached in memory, making them faster to perform in the first place, and also faster to roll back if needed. Make sure to use this parameter setting on servers that process long-running transactions with many inserts, updates, or deletes.