Mysql5.5.5 InnoDB storage engine configuration and optimization

Environment is CentOS system, 1G memory, Mysql 5.5.30.
In /etc/ my.cnf add:

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 modification:
service mysqld restart
If the log size is changed, an error will be reported:
130319 04:45:14 mysqld_safe mysqld from pid file /var/run/mysqld/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.0M
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' registration as a STORAGE ENGINE failed.
130319 5:03:02 [ERROR] Aborting
Delete /var/lib/mysql from the original ib_logfile0, ib_logfile1 and 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 // one of the most important parameters of Innodb. This parameter is similar to key_buffer_size of MyISAM, but there are also differences. This parameter mainly caches the index of innodb table, data and the buffer when inserting data. The larger the setting is, the less disk I/O is needed to access the data in the table. Generally, 1 is half of the memory, not more than 2G, otherwise the system will crash. Accelerate the optimization of the primary parameter for Innodb. Principle of memory allocation for this parameter: this parameter is allocated by default only 8M, which is a very small value. If it is a dedicated DB server, it can take up 70-80% of the memory. This parameter cannot be changed dynamically, so allocation needs to be considered. If the allocation is too large, Swap will take up too much, resulting in extremely slow queries for Mysql. If your data is small, the allocable value for this parameter is your data size + 10% or so.
For example, if the data size is 50M, then assign innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size=16M // for the internal directory of Innodb this value does not need to be allocated too large, the system can adjust it automatically. You don't have to set it too high. Usually 16M is enough for big data setting. If there are more tables, it can be appropriately increased. If this value is automatically increased, it will be displayed in error log.
innodb_log_file_size=256M // the size of each log file in the log group, 1 is generally 25% of innodb_buffer_pool_size, and the official recommendation is 40-50% of innodb_buffer_pool_size. It is better to control the sum size of several LOG files within 2G. It also depends on your transaction size, based on the data size. Note: the size of this value allocation has a lot to do with the write speed of the database, the transaction size, and the recovery after an abnormal restart.
innodb_log_files_in_group=2 // specify how many log groups you have. Allocation principle: 1 generally we can use 2-3 daily value groups. The default is two.
innodb_log_buffer_size = 3M // the transaction is buffered in memory. Allocation principle: control at 2-8M. This value does not need to be too much. Its internal memory is written to disk 1 time per second. How you write depends on how you commit your transaction. Learn about this in databases such as oracle, where a 1 Max of 3M is appropriate.
innodb_flush_logs_at_trx_commit = 0 // this parameter has only 3 values, 0, 1, 2 please confirm the acceptable level of 1. The default is 1. Please do not change the main library. Higher performance ones can be set to 0 or 2, but will lose 1 second of the transaction. Note: the setting of this parameter has a great impact on the performance of Innodb, so here is one more note. When this value is 1: innodb's transaction LOG writes to the daily value file after each commit and does a flush to disk for the daily value. This does not lose any transaction. When this value is 2: at each commit, the log buffer is written to the file, but the log file is not flushed for disk operation, and the log file is flushed once per second at a value of 2. It is important to note, however, that there is no guarantee that this will happen 100% of the time per second due to process invocation issues. So it's the fastest in terms of performance. But it takes an operating system crash or power failure to delete the last second of the transaction. When this value is 0: the log buffer is written to the log file once per second, and the log file is flushed for disk operation, but nothing is done for 1 transaction commit. The crash of the mysqld process removes the last second of the transaction before the crash.