innodb_extra_rseg

Some write-intensive workloads on boxes with many CPUs have scalability problems. The contention is caused by the rollback segment, which is single: all transactions are serialized when needing to access the segment. With this patch you can now create and use multiple segments (up to 256).

NOTE: This feature is incompatible with InnoDB. As long as a single rollback segment is used, there is no problem; the database can still be used by both XtraDB and InnoDB. However, creating multiple rollback segments will cause an internal format change to the system tablespace. Once multiple segments have been created, the database will no longer be compatible with InnoDB.

The patch provides the following variable:

This option specifies the number of extra user rollback segments.

When you modify this variable, you must restart the MySQL server for the change to take effect. Please note that you must perform a slow shutdown (ie with innodb_fast_shutdown = 0). If you just perform a fast shutdown, the MySQL server will then restart without error but the additional segments will not be created.

To check that the extra segments have been created, you can run the following query:

SELECT COUNT(*) FROM information_schema.INNODB_RSEG;

The result should be the number of extra segments + 1 (as a default single segment always exists).

This patch provides the following table:

This table shows information about all the rollback segments (the default segment and the extra segments).

Here is an example of output with innodb_extra_rsegments = 8.

mysql> select * from information_schema.innodb_rseg;
+---------+----------+----------+---------+------------+-----------+
| rseg_id | space_id | zip_size | page_no | max_size   | curr_size |
+---------+----------+----------+---------+------------+-----------+
|       0 |        0 |        0 |       6 | 4294967294 |         1 |
|       1 |        0 |        0 |      13 | 4294967294 |         2 |
|       2 |        0 |        0 |      14 | 4294967294 |         1 |
|       3 |        0 |        0 |      15 | 4294967294 |         1 |
|       4 |        0 |        0 |      16 | 4294967294 |         1 |
|       5 |        0 |        0 |      17 | 4294967294 |         1 |
|       6 |        0 |        0 |      18 | 4294967294 |         1 |
|       7 |        0 |        0 |      19 | 4294967294 |         1 |
|       8 |        0 |        0 |      20 | 4294967294 |         1 |
+---------+----------+----------+---------+------------+-----------+
9 rows in set (0.00 sec)

Fix of InnoDB/XtraDB scalability of rollback segment