- 论坛徽章:
- 0
|
回复 1# 红豆抹茶
1、InnoDB存储引擎和其他常用引擎有什么区别，您更偏爱哪一种，原因是什么？
2、简述InnoDB存储引擎有哪些优点和缺点？
3、在使用InnoDB存储引擎的过程中你所遇到的困难和解决的方法。
个人理解如下：
1 innodb比MYISAM快
2 性能并不足以让你修改自己表的engine
3在空间分配上，innodb的空间是myisam的2x-3x
4当表的engine从myisam convert to innodb的时候，要注意的索引的不同 ，尤其是PK，innodb要求每一个表都有一个PK，myisam的第一unique
Key 就作为第一个PK
5 在shou table status的时候，显示信息rows,avg_row_length可能会有offset,offset是2的倍数（这个偶还在测试中）
6 尽量多使用innodb而不是myisam
7 不要让 Begin 。。。commit的执行时间超过5秒钟
8 使用begin，而不是 autocommit=0,该值不建议使用，default value =1
9 针对mysiam,不必使用optimize table ，除非show table status 显示可以节省10%以上的空间
针对innodb，不必使用optimize table
10 engine使用bufferpool也不同，
myisam 的索引缓存在key_buffer_size,而数据使用OS缓存，
innodb 的bufferpool使用innodb_buffer_size
至于engine的偏好，因热而异吧~~~~~~~~
至于遇到的问题，比较典型的一个是drupal ---汉化包上传超时处理方法，请参考（http://blog.chinaunix.net/uid-22334392-id-3346990.html）
解决的方法是修改 innodb_flush_log_at_trx_commit
它描述的是innodb flush log的频率，具有好几个不同的值，如果该值设置不当，innodb的效率可能比较低，
0表示 在事务提交的时候不一定写log ，写log的频率是1秒钟然后将log写到log file 然后flush到磁盘
1表示每次事务提交的时候，都将logbuffer写到log file同时将log file flush到磁盘
2表示每次事务提交的时候，都将log buffer写到log file，但是什么时候flush 到磁盘则有OS 来决定，OS flush的频率仍是1秒（该值为平均值，具体的情况要看OS的负载）
具体选择何值，与你的应用息息相关。
具体的解释如下
innodb_flush_log_at_trx_commit
Command-Line Format --innodb_flush_log_at_trx_commit[=#]
Option-File Format innodb_flush_log_at_trx_commit
Option Sets Variable Yes, innodb_flush_log_at_trx_commit
Variable Name innodb_flush_log_at_trx_commit
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type enumeration
Default 1
Valid Values
0
1
2
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.
参考文档地址：
http://docs.oracle.com/cd/E17952 ... h_log_at_trx_commit |
|
免费注册
楼主: 红豆抹茶
发表于 2012-11-23 12:06
