mysql 数据库常用参数配置_MySQL数据库参数配置

数据库参数配置

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

# Additional memory pool that is used by InnoDB to store metadata

# information. If InnoDB requires more memory for this purpose it will

# start to allocate it from the OS. As this is fast enough on most

# recent operating systems, you normally do not need to change this

# value. SHOW INNODB STATUS will display the current amount used.

innodb_additional_mem_pool_size = 64M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

# row data. The bigger you set this the less disk I/O is needed to

# access data in tables. On a dedicated database server you may set this

# parameter up to 80% of the machine physical memory size. Do not set it

# too large, though, because competition of the physical memory may

# cause paging in the operating system. Note that on 32bit systems you

# might be limited to 2-3.5G of user level memory per process, so do not

# set it too high.

innodb_buffer_pool_size = 5G

对于myisam，需要调整key_buffer_size

当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

Cretated_tmp_disk_tables 增加tmp_table_size

Handler_read_key 高表示索引正确 Handler_read_rnd高表示索引不正确

Key_reads/Key_read_requests 应小于0.01 计算缓存损失率，增加Key_buffer_size

Opentables/Open_tables 增加table_cache

select_full_join 没有实用索引的链接的数量。如果不为0，应该检查索引。

select_range_check 如果不为0，该检查表索引。

sort_merge_passes 排序算法已经执行的合并的数量。如果该值较大，应增加sort_buffer_size

table_locks_waited 不能立即获得的表的锁的次数，如果该值较高，应优化查询

Threads_created 创建用来处理连接的线程数。如果Threads_created较大，要增加 thread_cache_size值。

缓存访问率的计算方法Threads_created/Connections。