The innodb_buffer_pool_size of InnoDB and the key_buffer_size of MyISAM

A. Key_buffer_size is very important for the MyISAM table.

If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load--remember, the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some of the memory for them, and in many cases the data peso is much more. However, it is always necessary to check whether all the Key_buffer are exploited--. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

Key_buffer_size = 2000M

Or

Key_buffer_size = 1G

two. innodb_buffer_pool_size is very important for the InnoDB table.

InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules that apply to Key_buffer are-if your data is small and not exploding, you don't have to set the innodb_buffer_pool_size too big.

Innodb_buffer_pool_size = 8000M

Or

Innodb_buffer_pool_size = 15G

the explanation of the other cattle people

#InnoDB存储数据字典, the internal data structure of the buffer pool, 16MB is big enough.

Innodb_additional_mem_pool_size = 16M

#InnoDB用于缓存数据, index, lock, insert buffer, data dictionary, etc.

#如果是专用的DB服务器, a InnoDB engine-oriented scenario that typically sets 50% of the physical memory

#如果是非专用DB服务器, you can try setting it to 1/4 of memory first, and if there is a problem, adjust it

#默认值是8M, very pit X, which also leads many people to think InnoDB is better than MyISAM.

Innodb_buffer_pool_size = 4G

#InnoDB共享表空间初始化大小, the default is 10MB, also very pit X, change to 1GB, and automatically expand

Innodb_data_file_path = Ibdata1:1g:autoextend

#如果不了解本选项, the proposed setting is 1, can better protect the data reliability, has certain influence on the performance, but can control

Innodb_flush_log_at_trx_commit = 1

#抱 complain InnoDB is 100 times times slower than MyISAM. So you probably forgot to adjust the value. The default value of 1 means that every instruction outside the transaction commit or transaction needs to write the log to the (flush) hard disk, which is time consuming. Especially when using battery-powered caching (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table, it means that the system cache is written without writing to the hard disk. The log still flush to the hard drive every second, so you won't normally lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security aspect is poor, even if MySQL hangs may lose the transaction data. A value of 2 only loses data when the entire operating system hangs

#InnoDB的log buffer, usually set to 64MB is enough

Innodb_log_buffer_size = 64M

#InnoDB redo log size, usually setting 256MB is enough

Innodb_log_file_size = 256M

#InnoDB Redo log filegroup, usually set to 2 is sufficient

Innodb_log_files_in_group = 2

#启用InnoDB的独立表空间模式, easy to manage

innodb_file_per_table = 1

#启用InnoDB的status file for administrators to view and monitor

Innodb_status_file = 1

#设置事务隔离级别为 read-commited, improve transaction efficiency, usually meet transaction consistency requirements

Transaction_isolation = read-committed

[Mysqld]

#禁止开启自动事务

init_connect= ' SET autocommit=0 '

Innodb_buffer_pool_size = 4G

Key_buffer_size = 1G

user = MySQL

Socket =/var/run/mysqld/mysqld.sock

Port = 3306

Basedir =/usr

DataDir =/var/lib/mysql

Tmpdir =/tmp

Skip-external-locking

#bind-address = 127.0.0.1

#

# * Fine Tuning

#

Key_buffer = 16M

Max_allowed_packet = 16M

Thread_stack = 192K

Thread_cache_size = 8

# This replaces the startup script and checks MyISAM tables if needed

# The They are touched

Myisam-recover = BACKUP

#max_connections = 100

#table_cache = 64

#thread_concurrency = 10

# * Query Cache Configuration

Query_cache_limit = 1M

Query_cache_size = 16M

#general_log_file =/var/log/mysql/mysql.log

#general_log = 1

Log_error =/var/log/mysql/error.log

# Here you can-queries with especially long duration

Log_slow_queries =/var/log/mysql/mysql-slow.log

Long_query_time = 0.05

#log-queries-not-using-indexes

Expire_logs_days = 10

Max_binlog_size = 100M

# * InnoDB

# InnoDB is enabled by default with a 10MB datafile in/var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

# chroot =/var/lib/mysql/

effect of —————————————————————————————————————————————————— innodb_pool_buffer_size on the performance of InnoDB

Innodb_pool_buffer_size is the most important parameter affecting InnoDB performance, which refers to the size of the InnoDB buffer. In general, the larger the InnoDB throughput (in TPs) the higher it is. So, without affecting the other programs on the server working properly, this value is always the greater the better.

But when InnoDB do crash recovery, the large pool buffer will make recovery incredibly slow. One compromise solution is to start with a small pool buffer, and then switch to the large pool bufer when the restore is complete.

Why the small pool buffer can speed up the recovery.
The process of tracking recovery with oprofile found that the insertion Order of the flush_list pages consumed most of the time, and large pool buffer would make the Flush list more and more dirty pages, while the time complexity of the insertion sort was O (n^2). Instead, the small pool buffer will let flush list of dirty page in time flush, so that the flush list inside the number of dirty pages is always not very large, there are new dirty data inserted in sequence will not have a big overhead.