mysql innodb

浏览数：27 / 时间：2015年06月12日

InnoDB tablespace and logs

配置默认每个表拥有自己的表空间;

innodb_file_per_table=1

对于以前的表示不受影响的, 只对新表有效;

tablespace 可以自动增长,大小受到文件系统的限制,还可以放到裸设备里面, 大小不再不受文件系统限制

"裸设备的extent"

1;配置表空间;

innodb_data_file_path ibdata1:10M:autoextend

如要要配置绝对路径,需要把Innodb_data_home_dir 置空

Innodb_data_home_dir =

innodb_data_file_path = /tmp/ibdata1:10M:autoextend[:max20M]; /tmp/ibdata1:10M:autoextend[:max20M]

注意:　　

ACID:

首先看看默认是否打开:

mysql> show variables like ‘autocommit‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

测试1:

第一个窗口

set autocommit = 0 ;

insert into t1 values (1,2);

commit;

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(111,111);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

InnoDB buffers

innodb_buffer_pool_size =134217728

innodb_additional_mem_pool_size=8388608

innodb_max_dirty_pages_pct =75

mysql> show variables like ‘%innodb_buffer%‘;
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

mysql> show variables like ‘%have%‘;
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| have_compress        | YES      |
| have_crypt           | NO       |
| have_csv             | YES      |
| have_dynamic_loading | YES      |
| have_geometry        | YES      |
| have_innodb          | YES      |
| have_ndbcluster      | NO       |
| have_openssl         | DISABLED |
| have_partitioning    | YES      |
| have_profiling       | YES      |
| have_query_cache     | YES      |
| have_rtree_keys      | YES      |
| have_ssl             | DISABLED |
| have_symlink         | YES      |
+----------------------+----------+
14 rows in set (0.43 sec)