|
InnoDB Plugin 1.0.4 这次加入了不少第三方的代码，个人比较在意的是 Google 和 Percona 提供的部分。Innodb从而实现了性能上很大的提升，不想以前在并发稍大（比如>8)，吞吐量升值会下将，现在却又很大的提升。 个人关注的改进部分有： 1： Multiple Background Threads 最初由Google提供的补丁，参数 innodb_file_io_threads 可以设置io threads的数量，之前是假的… 2：Master Thread I/O Capacity Tuning 之前innodb在代码里面写死了 innodb_io_capacity 为100，但是现在db服务器很多都是用多块硬盘做raid10，IOPS 一般都远不止100，因此这次改进之后 innodb_io_capacity 变成可以动态调整的参数，用于DBA选择一个合适的值。 3：Group Commit 这个据说以前4.x之前就支持的，现在又回来了，支持多个事务同时提交（主要是redo log，之前是因为binlog的2-phase commit protocal的原因中止的），从而提高吞吐量。 4：Adaptive Flushing 这个非常有用，大家知道在脏数据到达设置的阀值比例之后，会开始主动做checkpoint，当checkpoint无可避免的时候，这时候会堵塞用户线程，从而出现性能的突然下降。现在这个问题得到巧妙的解决，会根据算法动态的调整checkpoint的速率，避免出现性能的突然降低。 REFERENCE: http://www.innodb.com/wp/2009/08/11/innodb-plugin-104-released/ http://www.innodb.com/wp/products/innodb_plugin/license/third-party-contributions-in-innodb-plugin-1-0-4/ 以上转自http://www.mysqlab.net/blog/2009/08/innodb-plugin-104-for-mysql-5137/ 其实给mysql-5.1.38打上innodb-1.0.4的补丁很简单，下载mysql-5.1.38 ，innodb-1.0.4 把innodb-1.0.4 替换mysql-5.1.38中的storge/innobase，名称同样为innobase 然后就是编译安装 下面是我的安装步骤 #CFLAGS=”-O3″ CXX=g++ CXXFLAGS=”-O3 -felide-constructors -fno-exceptions -fno-rtti” ./configure –prefix=/usr/local/mysql –localstatedir=/data/mysql –enable-assembler –with-system-type=”RedHat-Enterprise” –with-client-ldflags=-all-static –with-mysqld-ldflags=-all-static –with-pthread –enable-static –with-big-tables –without-ndb-debug –with-charset=utf8 –with-extra-charsets=all –with-collation=utf8_general_ci –without-debug –enable-thread-safe-client –enable-local-infile –with-plugins=max make make install groupadd mysql useradd -g mysql mysql cd /usr/local/mysql bin/mysql_install_db –user=mysql chown -R root:mysql . chown -R mysql:mysql /data/mysql chgrp -R mysql . cp share/mysql/my-medium.cnf /etc/my.cnf cp share/mysql/mysql.server /etc/init.d/mysqld chkconfig –add mysqld 启动以后，进入mysql shell mysql> show plugins; +———————+———-+——————–+———+———+ | Name | Status | Type | Library | License | +———————+———-+——————–+———+———+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL | +———————+———-+——————–+———+———+ 18 rows in set (0.00 sec) mysql> innodb 的一些变量 mysql> show global variables like ‘%innodb%’; +———————————+————————+ | Variable_name | Value | +———————————+————————+ | have_innodb | YES | | ignore_builtin_innodb | OFF | | 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 | 134217728 | | innodb_change_buffering | inserts | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | 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_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_lock_wait_timeout | 50 | | 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 | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_spin_wait_delay | 6 | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_sys_malloc | ON | | innodb_version | 1.0.4 | | innodb_write_io_threads | 4 | +———————————+————————+ 50 rows in set (0.00 sec) |
评论