- 论坛徽章:
- 0
|
xtradb:patch:innodb_expand_undo_slots.
More Concurrent Transactions Available
Description
This option is provided for servers that run out of undo slots. Use it if you find the following warning in the error log: “Warning: cannot find a free slot for an undo log”.
 We discourage its use unless you get this warning, because it breaks compatibility with other programs. Specifically, it makes the datafiles unusable for ibbackup or for a MySQL server that is not run with this option.
When you enable the option, the maximum number of undo slots is extended to 4072, instead of the default fixed value of 1024.
You can then check whether the expanded slots (1025-4072) are used by starting mysqld with innodb_extra_undoslots=OFF:
*
If the expanded slots are used: mysqld refuses to start and prints an error in the error log:
InnoDB: Error: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: The datafile is not normal for mysqld and disabled innodb_extra_undoslots.
InnoDB: Enable innodb_extra_undoslots if it was enabled before, and
InnoDB: ### don't use this datafile with other mysqld or ibbackup! ###
InnoDB: Cannot continue operation for the safety. Calling exit(1).
*
If the expanded slots are not used: mysqld starts and prints only a warning in the error log:
InnoDB: Warning: innodb_extra_undoslots option is disabled, but it was enabled before.
InnoDB: But extended undo slots seem not used, so continue operation.
System Variables
innodb_extra_undoslots
General Description:
Command Line Yes
Config File Yes
Variable Scope Global
Dynamic Variable No
Permitted Values:
Type BOOL
Default Value OFF
Range ON/OFF
Units Unitless http://www.percona.com/docs/wiki ... ts?redirect=1InnoDB plugin 新特性测试 http://hi.baidu.com/unidba/blog/ ... a8dee708fa9331.html
InnoDB plugin 新特性测试一
InnoDB plugin 新特性
* 可查看InnoDB plugin的版本号
* 快速索引创建：不用复制数据进行增加或删除索引
* 数据压缩：收缩表，显著地减少存储空间和I/O
* 新的记录格式：long BLOB, TEXT, VARCHAR字段的fully off-page存储
* 文件格式管理：保护向上和向下的版本兼容
* INFORMATION_SCHEMA字典表：新增关于压缩和锁的信息
* 性能和扩展性的加强
。更快的锁机制加强扩展性
。使用操作系统内存分配器（memory allocators）
。Controlling InnoDB insert buffering
。Controlling adaptive hash indexing
。改变InnoDB线程并发
。改变预读（read ahead）算法
。多后台I/O线程
。Group Commit
。Controlling master thread I/O rate
。Controlling flushing rate of dirty pages
。Using a portable PAUSE to InnoDB spin loop
。Control Over Spin Lock Polling
。Changing defaults of parameters
。Making Buffer Cache Scan Resistant
* Other changes for flexibility, ease of use and reliability
。Dynamic control of system configuration parameters
。TRUNCATE TABLE reclaims space
。InnoDB “strict mode”
。Control over statistics estimation
。Better error handling when dropping indexes
。More compact output of SHOW ENGINE INNODB MUTEX
。More Read Ahead Statistics
注：红色部分，比较实用的新特性
一。参数部分
MariaDB [(none)]> show global variables like '%innodb%';
+---------------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------------+------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_checkpoint | none |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 268435456 |
| innodb_change_buffering | inserts |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:20M:autoextend |
| innodb_data_home_dir | /data02/mysql |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | ON |
| innodb_enable_unsafe_group_commit | 0 |
| innodb_expand_import | 0 |
| innodb_extra_rsegments | 0 |
| innodb_extra_undoslots | OFF |
| innodb_fast_recovery | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | Antelope |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbor_pages | 1 |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 0 |
| innodb_ibuf_max_size | 134201344 |
| innodb_io_capacity | 200 |
| innodb_lock_wait_timeout | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /data02/mysql |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_overwrite_relay_log_info | OFF |
| innodb_read_ahead | linear |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_purge_thread | OFF |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.0.4-8 |
| innodb_write_io_threads | 4 |
+---------------------------------------+------------------------+
70 rows in set (0.01 sec)
MariaDB [(none)]> \! grep "Barracuda" /data02/mysql/popgo-dbsvr.err-old
100123 0:44:30 InnoDB: highest supported file format is Barracuda.
MariaDB [(none)]> show engine innodb mutex;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: *** NONE ***
+--------+-------------------+------------+
| Type | Name | Status |
+--------+-------------------+------------+
| InnoDB | log/log0log.c:833 | os_waits=7 |
+--------+-------------------+------------+
1 row in set (0.02 sec)
二。测试部分
创建压缩表
MariaDB [test]> CREATE TABLE `tbl_barracuda` (
-> `i` int(11) NOT NULL AUTO_INCREMENT,
-> `j` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY (`i`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 5 warnings (0.15 sec)
MariaDB [test]> show warnings;
+---------+------+----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+----------------------------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [test]> set global innodb_file_per_table=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
MariaDB [test]> CREATE TABLE`tbl_barracuda_2` ( `i` int(11) NOT NULL AUTO_INCREMENT, `j`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`i`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.15 sec)
MariaDB [test]> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [test]> set global innodb_file_format=barracuda;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> CREATE TABLE`tbl_barracuda_3` ( `i` int(11) NOT NULL AUTO_INCREMENT, `j`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`i`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.16 sec)
MariaDB [test]> show table status like '%barracuda%'\G
*************************** 1. row ***************************
Name: tbl_barracuda
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 14680064
Auto_increment: 1
Create_time: 2010-01-23 13:06:48
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED key_block_size=8
Comment:
*************************** 2. row ***************************
Name: tbl_barracuda_2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2010-01-23 13:20:12
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED key_block_size=8
Comment:
*************************** 3. row ***************************
Name: tbl_barracuda_3
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2010-01-23 13:21:48
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED key_block_size=8
Comment:
3 rows in set (0.01 sec)
MariaDB [test]>
注一：从上述创建InnoDB压缩表的测试过程和table status清晰看出，创建压缩表的前提是
1. 必须开启innodb_file_per_table参数on；
2. innodb_file_format InnoDB文件格式必须为Barracuda。
如下表格：
注二：参数key_block_size，默认是8K。选择合适的大小，很关键。如果设置太大会浪费内存空间，而且页不会经常被压缩。如果设置太小，插入或更新操作会很耗时的重压缩，而且B-Tree节点会经常的被分裂，导致使数据文件变大和索引效率变低。
注三：用InnoDB Hot Backup或xtrabackup在线备份InnoDB，要注意对应的版本的才能备份压缩的表。
MariaDB [test]>
[ 本帖最后由 justin033 于 2010-1-28 11:23 编辑 ]
__________________________________
Unix DBA门户 DBEye.Org
To be a dba Unix DBA QQ group: 10098435
机器再胜人类 Power还能做什么？| 2010您最想感谢的CUer是谁？ | 红帽RHCE超低价考试| 非诚勿扰 Unix专才招聘进行时！
justin033 (Popgo@XM)
* 发短消息
* 加为好友
justin033 (Popgo@XM)当前离线
UID
497905
帖子
248
精华
0
积分
3285
可用积分
3285
信誉积分
100
专家积分
0 (本版
空间积分
0
阅读权限
50
性别
男
在线时间
431 小时
注册时间
2005-07-21
最后登录
2010-11-13
个人空间 查看个人网站查看详细资料
精灵
帖子
248
主题
15
精华
0
可用积分
3285
专家积分
0 (本版:0)
在线时间
431 小时
注册时间
2005-07-21
最后登录
2010-11-13
状态：...当前离线...
[微博] [博客] [短信]
顶部 [评分] [报告] [回复] [引用]
2楼 发表于 2010-01-28 00:56 | 只看该作者
InnoDB plugin 新特性测试二
二。测试部分
测试压缩表的压缩量
1）创建两表
mysql> show table status\G
*************************** 1. row ***************************
Name: tbl_compressed
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 278528
Data_free: 0
Auto_increment: 1
Create_time: 2010-01-27 18:07:49
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=16
Comment:
*************************** 2. row ***************************
Name: tbl_nocompressed
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 278528
Data_free: 0
Auto_increment: 1
Create_time: 2010-01-27 18:06:58
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
2 rows in set (0.01 sec)
[root@db2 test]# du -sh ./*
8.0K ./db.opt
24K ./tbl_compressed.frm
376K ./tbl_compressed.ibd
24K ./tbl_nocompressed.frm
376K ./tbl_nocompressed.ibd
2）插入数据
mysql> insert into tbl_compressed select * from userlib.ul_users_a;
Query OK, 700226 rows affected (1 min 25.18 sec)
Records: 700226 Duplicates: 0 Warnings: 0
mysql> insert into tbl_nocompressed select * from userlib.ul_users_a;
Query OK, 700226 rows affected (56.22 sec)
Records: 700226 Duplicates: 0 Warnings: 0
[root@db2 test]# du -sh ./*
8.0K ./db.opt
24K ./tbl_compressed.frm
357M ./tbl_compressed.ibd
24K ./tbl_nocompressed.frm
357M ./tbl_nocompressed.ibd
注：奇怪的测试结果。。。？
后面又建了key_block_size为8k的表，如下：
mysql> show table status\G
*************************** 1. row ***************************
Name: tbl_compressed
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 708827
Avg_row_length: 169
Data_length: 120225792
Max_data_length: 0
Index_length: 240140288
Data_free: 5242880
Auto_increment: 46664554
Create_time: 2010-01-27 18:07:49
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=16
Comment:
*************************** 2. row ***************************
Name: tbl_compressed_8k
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 278528
Data_free: 0
Auto_increment: 1
Create_time: 2010-01-28 00:39:47
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8
Comment:
*************************** 3. row ***************************
Name: tbl_nocompressed
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 718862
Avg_row_length: 167
Data_length: 120225792
Max_data_length: 0
Index_length: 240140288
Data_free: 5242880
Auto_increment: 46664554
Create_time: 2010-01-27 18:06:58
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
3 rows in set (0.03 sec)
mysql> insert into tbl_compressed_8k select * from userlib.ul_users_a;
Query OK, 700226 rows affected (1 min 44.06 sec)
Records: 700226 Duplicates: 0 Warnings: 0
mysql> \! du -sh /data/mysql/test/*
8.0K /data/mysql/test/db.opt
24K /data/mysql/test/tbl_compressed_8k.frm
181M /data/mysql/test/tbl_compressed_8k.ibd
24K /data/mysql/test/tbl_compressed.frm
357M /data/mysql/test/tbl_compressed.ibd
24K /data/mysql/test/tbl_nocompressed.frm
357M /data/mysql/test/tbl_nocompressed.ibdhttp://bbs.chinaunix.net/thread-1652197-1-1.html |
|
免费注册
发表于 2011-03-14 16:20