*************************************************************
MyISAM storage engine options
concurrent_inserts
= {0 | 1 | 2}
Allows for inserts into MyISAM tables without
blocking reads.
delayed_key_write =
{OFF | ON | ALL}
Delays flushing to disk for index changes to batch the
changes for greater performance.
key_buffer =
buffer_size
This configures the size of the MyISAM index buffer.
检测key_buffer使用 通过检查cache命中率
hit_ratio=(key_reads/key_read_requests)*100
key_blocks_unused
key_cache_block_size
The formula for finding the percentage of the index buffer that is in use is:
100* (1 – (key_blocks_unused * key_cache_block_size) / key_buffer_size)
InnoDB storage engine options
innodb_buffer_pool_size =
buffer_size
A static variable that specifies the size of the cache
for InnoDB data and indexes.
innodb_flush_log_at_trx_
commit = number
There are three possible options {0|1|2}. This
dynamic system variable manages how often the
InnoDB log buffer is written (flushed) to the log file.
innodb_flush_method =
IO_access_method
This static variable determines how the InnoDB
storage engine interacts with the operating system
with respect to I/O operations.
innodb_log_buffer_size =
buffer_size
Buffer used for writes to the InnoDB logs. Unless you
use very large BLOBs this static variable should not
be over 8 MB, and can be set to 2 Mb.
innodb_log_file_size =
log_file_size
A static variable that determines the size of each
Innodb log file (ib_logfile).
innodb_log_files_in_group =
number_log_files
A static variable that determines the total number of
Innodb log files.
innodb_max_dirty_pages_pct= N This dynamic variable specifies the maximum
percentage of pages in the in Innodb buffer pool that
can be dirty — that is, changed in the buffer pool in
memory without being saved to disk. Defaults to 90
(%).
innodb_thread_concurrency = N This dynamic variable determines the maximum
number of system threads inside InnoDB. A good
number to start is twice the number of CPUs.
Calculate the ratio of unused data pages to the total number of pages:
Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total
If the ratio is high (close to 1), then the InnoDB buffer pool is probably set too high. A less
likely cause is that the innodb_max_dirty_pages_pct system variable is set too low, and
dirty pages are being flushed very often, freeing up pages long before they are needed.
Conversely, if the ratio is low, the size of the InnoDB buffer pool may need to be set higher.
Using the information you have about the free memory on your system, increase the InnoDB
buffer pool size, restart mysqld, and continue to monitor the status variables after the newly
sized InnoDB buffer pool has been used for a while. Continue the adjust-monitor-adjust cycle,
and once your system is at the right level, continue to monitor levels, making sure to check
performance once every month or two.
mysql bible
转载
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
MySQL及MySQL Workbench下载与安装
安装MySQLworkbench以及环境配置的详细教程
mysql MySQL MySQLworkbench -
Python Bible - Ch23 多线程编程
'''在Python中,可以通过继承threa
python Python 子线程 -
《OpenGL 超级宝典(Super Bible)第七版》 有关 PBO 的 Example
该代码除了使用了 PBO 还是加入了 OpenMP 代码:
opengl #include 2d 迭代