Mysql5.5 InnoDB Storage Engine settings

Environment for CentOS system, 1G memory, Mysql5.5.30.

Add in/ETC/MY.CNF:

The code is as follows	Copy Code
Skip-external-locking Skip-name-resolve Max_connections = 1024 Query_cache_size = 16M Sort_buffer_size = 1M Table_cache = 256 Innodb_buffer_pool_size = 128M Innodb_additional_mem_pool_size = 4M Innodb_log_file_size = 128M Innodb_log_files_in_group = 2 Innodb_log_buffer_size = 2M Innodb_flush_log_at_trx_commit = 1

Stop the MySQL service before you modify it:

Service mysqld Restart

Error due to modified log size reboot:

130319 04:45:14 mysqld_safe mysqld from PID File/var/run/mysqld/mysqld.pid ended
130319 05:02:43 mysqld_safe Start ing mysqld daemon with databases From/var/lib/mysql
130319 5:02:54 [note] Plugin ' federated ' is disabled.
130319 5:02:59 innodb:the InnoDB Memory heap is disabled
130319 5:02:59 innodb:mutexes and rw_locks use InnoDB ' own Implem Entation
130319 5:02:59 innodb:compressed tables use zlib 1.2.3
130319 5:02:59 innodb:using Linux native AIO
130319 5:03:01 innodb:initializing buffer pool, size = 128.0M
130319 5:03:02 innodb:completed initialization of BUF Fer pool
InnoDB:Error:log file./ib_logfile0 is of different size 0 5242880 bytes
Innodb:than specified in the . cnf file 0 134217728 bytes!
130319 5:03:02 [ERROR] Plugin ' InnoDB ' init function returned ERROR.
130319 5:03:02 [ERROR] Plugin ' InnoDB ' registration as a STORAGE ENGINE.
130319 5:03:02 [ERROR] aborting

Will/var/lib/mysql under the original IB_LOGFILE0, ib_logfile1 deleted and then restart MySQL.

Service mysqld Start

Normal Startup:

130319 05:20:59 mysqld_safe mysqld from PID File/var/run/mysqld/mysqld.pid ended
130319 05:22:53 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
130319 5:22:56 [note] Plugin ' federated ' is disabled.
130319 5:22:57 innodb:the InnoDB memory heap is disabled
130319 5:22:57 innodb:mutexes and rw_locks use InnoDB ' s own implementation
130319 5:22:57 innodb:compressed tables use zlib 1.2.3
130319 5:22:57 innodb:using Linux native AIO
130319 5:22:57 innodb:initializing buffer pool, size = 128.0M
130319 5:22:57 innodb:completed initialization of buffer pool
130319 5:22:57 innodb:highest Supported file format is barracuda.
130319 5:22:58 innodb:waiting for the background threads to start
130319 5:22:59 innodb:5.5.30 started; Log sequence Number 2621452
130319 5:22:59 [note] Server hostname (bind-address): ' 0.0.0.0 '; port:3306
130319 5:22:59 [note]-' 0.0.0.0 ' resolves to ' 0.0.0.0 ';
130319 5:22:59 [note] Server socket created on IP: ' 0.0.0.0 '.
130319 5:23:00 [Warning] ' proxies_priv ' entry ' @ root@server08 ' ignored in--skip-name-resolve mode.
130319 5:23:04 [note] Event scheduler:loaded 0 Events
130319 5:23:04 [note]/usr/libexec/mysqld:ready for connections.
Version: ' 5.5.30 ' socket: '/var/lib/mysql/mysql.sock ' port:3306 mysql Community Server (GPL) by Remi

Use the Mysqladmin variables command to view all parameter variables.

InnoDB Engine Configuration Optimization

INNODB_BUFFER_POOL_SIZE=1G//A InnoDB Most important parameter, this parameter and MyISAM's key_buffer_size have similarities, but also have the difference. This parameter mainly caches the index of the InnoDB table, the data, the buffer when inserting data, the larger the set, the less disk I/O required to access the data in the table, generally half of the memory, no more than 2G, otherwise the system will crash. Optimization of the primary parameters for InnoDB acceleration. This parameter allocates the principle of memory: This parameter is assigned only 8M, which can be said to be a very small value. If it is a dedicated DB server, then he can take up 70%-80% of the memory. This parameter cannot be changed dynamically, so the allocation needs to be considered more. Excessive allocation, will make swap too much, resulting in MySQL query very slow. If your data is relatively small, then the allocation is your data size + 10% as the value of this parameter.

For example: The data size is 50M, then assign innodb_buffer_pool_size=64m to this value

INNODB_ADDITIONAL_MEM_POOL_SIZE=16M//The internal directory used to hold InnoDB This value is not allocated too large, the system can be automatically tuned. Do not set too high. Usually the larger data set 16M is sufficient, if the table is more, you can increase the appropriate. If this value is automatically incremented, it will appear in the error log.

innodb_log_file_size=256m//The size of each log file in the log group, typically Innodb_buffer_pool_size 25%, is the official recommendation of Innodb_buffer_pool_size 40-50%. General control in several log file addition size within 2G is preferred. The specific situation also needs to look at your transaction size, data size as the basis. Description: This value allocation size and database write speed, transaction size, abnormal restart after the recovery has a great relationship.

innodb_log_files_in_group=2//Specify how many log groups you have. Allocation principle: Generally we can use 2-3 Day value group. The default is two.

INNODB_LOG_BUFFER_SIZE=3M//transaction buffering in storage. Allocation principle: Control in 2-8m. This value is not too much. The memory inside him is typically written to disk one second. The way you write is related to how your transaction is submitted. In Oracle and other databases to understand this, the general maximum designation of 3M more appropriate.

Innodb_flush_logs_at_trx_commit=0//Control Transaction Submission Method Allocation principle: This parameter has only 3 values, 0,1,2 Please confirm your own acceptable level. The default is 1, please do not change the master library. A higher performance can be set to 0 or 2, but a second transaction is lost. Note: The setting of this parameter has a great effect on the performance of InnoDB, so explain it here. When this value is 1 o'clock: InnoDB transaction Log writes the day value file after each commit and flushes the daily value to disk. This can be done without losing any one transaction. When this value is 2 o'clock: in each commit, the log buffer is written to the file, but the log file is not flushed to disk operations, and the refresh of the log files occurs once per second in the case of a value of 2. It should be noted, however, that due to the problem of process invocation, 100% per second is not guaranteed to occur. Which is the fastest in performance. However, the last second transaction is removed by the operating system crash or power off. When this value is 0 o'clock: Log buffers are written to log files once per second, and disk operations are refreshed on log files, but no action is taken on a transaction commit. The crash of the MYSQLD process deletes the last second of the transaction before the crash.