innodb相关参数

innodb_buffer_pool_size

定义innodb存储引擎的表数据及索引的缓存大小,与myisam不同,myisam的key_buffer只能缓存索引。一般情况下设置物理内存的70%–80%,不能设置过大,会导致操作系统page in唾page out

这个link中,描述了如何去调节innodb_buffer_pool_size

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

innodb_flush_log_at_trx_commit

文档描述:
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.

如果想获得更好的情况,可以设置成0或2,如果设置成0,在crash时,可能会丢失1秒钟的事务数据。如果设置成2,在crash时,数据的完整性不会受到影响,因为已经向日志文件中写入log

Innodb_additional_mem_pool_size

文档描述:
The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 1MB.

Innodb_lock_wait_timeout

文档描述:
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back.innodb_lock_wait_timeout applies to InnoDB row locks only(只针对行锁). A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

innodb_support_xa

文档描述:
When the variable is enabled (the default), InnoDB support for two-phase commit in XA transactions is enabled, which causes an extra disk flush for transaction preparation.
If you do not wish to use XA transactions, you can disable this variable to reduce the number of disk flushes and get better InnoDB performance.
Having innodb_support_xa enabled on a replication master — or on any MySQL server where binary logging is in use — ensures that the binary log does not get out of sync compared to the table data.

Innodb_log_buffer_size

文档描述:
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 1MB. Sensible values range from 1MB to 8MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

Innodb_log_file_size

文档描述:
The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

文章评论: “innodb相关参数

  1. Hello there, You have done an excellent job. I’ll definitely digg it and personally suggest
    to my friends. I am confident they’ll be benefited from this website.

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>