- 论坛徽章:
- 0
|
本帖最后由 justin033 于 2010-04-26 23:26 编辑
InnoDB plugin 新特性测试 http://hi.baidu.com/unidba/blog/item/b6f02a0298a8dee708fa9331.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 编辑 ] |
|
免费注册
发表于 2010-01-28 00:55
发表于 2010-01-28 08:01
