免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 3743 | 回复: 5

InnoDB plugin 新特性测试 [复制链接]

论坛徽章:
0
发表于 2010-01-28 00:55 |显示全部楼层
本帖最后由 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 编辑 ]

论坛徽章:
0
发表于 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.ibd

论坛徽章:
8
综合交流区版块每周发帖之星
日期:2015-12-02 15:03:53数据库技术版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-09-14 06:20:00金牛座
日期:2014-10-10 11:23:34CU十二周年纪念徽章
日期:2013-10-24 15:41:34酉鸡
日期:2013-10-19 10:17:1315-16赛季CBA联赛之北京
日期:2017-03-06 15:12:44
发表于 2010-01-28 08:01 |显示全部楼层
顶下

论坛徽章:
0
发表于 2010-01-28 08:51 |显示全部楼层
很奇怪,用key_block_size=16建的表,数据没压缩,不知道中间操作是不是有问题!

论坛徽章:
0
发表于 2010-01-30 10:37 |显示全部楼层
关注新技术的人不多呀。。。

论坛徽章:
0
发表于 2010-02-01 09:53 |显示全部楼层
兄弟,文字好像少了点呀。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP