我为提高mysql性能做出调整的配置项(四)
= 512M：innodb缓存表和索引的内存空间，官网建议我们设置为物理内存的50-80%，但是请注意是专注于数据服务的机器，如果你的操作
系统包含数据库和其他服务，请考虑其他服务占用的内存，对于
Linux来说，这个参数会占用swap文件的大小。
?
The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server. The larger you set the innodb_buffer_pool_size value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size.
?
?
innodb_additional_mem_pool_size = 20M：用来设置innodb存储数据目录信息和其他内部数据结构的内存池大小。
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 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 8MB. This variable relates to the InnoDB internal memory allocator, which is unused if innodb_use_sys_malloc is enabled.
?
?
innodb_log_file_size = 128M：用来设置每个日志文件在内存池中的大小，但是请注意，其他资料说“一旦设置了该参数，你就需要把原来的ib_logfile备份删除掉，否则mysql服务在重启的时候会出错"，我个人还没有进行测试。
?
The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit InnoDB System Variables 1945 but not exceed it. The default value is 48MB. 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. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.
?
innodb_log_buffer_size = 16M：为innodb向磁盘刷新日志文件的缓存大小，可以相对应减少磁盘的IO。
?
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.
?
innodb_flush_log_at_trx_commit = 2：设置二进制日志刷新磁盘的时间点。
?
?
innodb_lock_wait_timeout = 20：事务等待超时的时间，默认为50秒。
?
The length of time in seconds an InnoDB transaction waits 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
InnoDB System Variables
1941
transaction waits at most this many seconds for write access to the row before issuing the following