Mysql5.5 InnoDB Storage engine configuration and Optimization

The environment is CentOS, 1 GB memory, and Mysql5.5.30.
Add the following in/etc/my. cnf:
Copy codeThe Code is as follows:
Skip-external-locking
Skip-name-resolve
Max_connections = 1024
Query_cache_size = 16 M
Sort_buffer_size = 1 M
Table_cache = 256
Innodb_buffer_pool_size = 128 M
Innodb_additional_mem_pool_size = 4 M
Innodb_log_file_size = 128 M
Innodb_log_files_in_group = 2
Innodb_log_buffer_size = 2 M
Innodb_flush_log_at_trx_commit = 1

Stop the mysql service before modification:
Service mysqld restart
An error is reported when the log size is changed and restarted:
130319 04:45:14 mysqld_safe mysqld from pid file/var/run/mysqld. pid ended
130319 05:02:43 mysqld_safe Starting 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's own implementation
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.0 M
130319 5:03:02 InnoDB: Completed initialization of buffer 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' registry as a storage engine failed.
130319 5:03:02 [ERROR] Aborting
Delete the original ib_logfile0 and ib_logfile1 in/var/lib/mysql and then restart mysql.
Service mysqld start
Normal start:
130319 05:20:59 mysqld_safe mysqld from pid file/var/run/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.0 M
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 schedents: Loaded 0 events
130319 5:23:04 [Note]/usr/libexec/mysqld: ready for connections.
Version: '5. 5.30 'socket:'/var/lib/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 // The Most Important Innodb parameter. this parameter is similar to the key_buffer_size of MyISAM, but it is also different. This parameter is mainly used to cache indexes, data, and buffer when data is inserted in the innodb table. The larger the value is set, the less disk I/O required to access data in the table, generally, it is half of the memory and cannot exceed 2 GB. Otherwise, the system will crash. It is the primary parameter for Innodb acceleration and optimization. This parameter is used to allocate memory. The default value of this parameter is 8 MB, which is a very small value. If it is a dedicated DB server, it can account for 70%-80% of the memory. This parameter cannot be changed dynamically. Therefore, you need to consider the allocation. If the allocation is too large, Swap will take up too much, resulting in slow Mysql query. If your data is small, you can allocate about 10% of your data size as the value of this parameter.
For example, if the data size is 50 M, innodb_buffer_pool_size = 64 M is assigned to this value.
Innodb_additional_mem_pool_size = 16 M // The value of the internal directory used to store Innodb does not need to be allocated too large, and the system can automatically tune it. No need to set too high. Generally, it is enough to set 16 MB for big data. If there are more tables, you can increase the size as appropriate. If this value is automatically increased, it will be displayed in the error log.
Innodb_log_file_size = 256 M // the size of each log file in the log group. Generally, it is 25% of innodb_buffer_pool_size. It is officially recommended to be 40-50% of innodb_buffer_pool_size. Generally, it is better to add up to 2 GB of LOG files. The specific situation also depends on the transaction size and data size. Note: the size of the value allocation has a great relationship with the database write speed, transaction size, and recovery after an exception or restart.
Innodb_log_files_in_group = 2 // specify several log groups. Allocation principle: Generally we can use 2-3 daily value groups. The default value is two.
Innodb_log_buffer_size = 3 M // buffer the transaction in the memory. Allocation principle: the limit is 2-8 m. This value does not need to be too large. The memory in it is generally written to the disk once a second. The specific write method is related to your transaction commit method. It is generally appropriate to specify a maximum of 3 m for oracle and other databases.
Innodb_flush_logs_at_trx_commit = 0 // The allocation principle for controlling the transaction commit mode: this parameter has only three values, 0, 1, and 2. Check the acceptable level. The default value is 1. Do not change the master database. A transaction with higher performance can be set to 0 or 2, but will be lost for one second. Note: the setting of this parameter has a great impact on the performance of Innodb, so I would like to explain it here. When the value is 1: The transaction LOG of innodb writes the Daily Value file after each commit, and refreshes the daily value to the disk. This can avoid losing any transaction. When the value is 2: log buffering is written to a file for each commit, but disk operations are not refreshed for log files, the log file is refreshed once per second when the value is 2. However, it should be noted that, due to process calling problems, it cannot be guaranteed that 100% occurs every second. Thus, the performance is the fastest. However, the transaction in the last second will be deleted only when the operating system crashes or powers down. When the value is 0, the log buffer is written to the log file once per second, and the disk operation is refreshed for the log file, but no operation is performed on the commit of a transaction. The crash of the mysqld process will delete the last second of the transaction before the crash.