MySQL优化

MySQL优化

系统性能相关的MySQL变量

系统性能相关的MySQL变量

原文摘录自:http://forge.mysql.com/wiki/ServerVariables

Memory-Related Variables
内存相关变量

These server variables control the amount of memory allocated to the various buffers and caches within MySQL.
以下这些服务器变量控制这MySQL分配给各种缓冲或者换缓存的内存总数。

join_buffer_size
(PER SESSION) Controls the amount of memory allocated to perform joins on tables that have no keys which can be used to perform a condition filter. Allocated for each table joined without necessary filter conditions

InnoDB性能调节提示

原文来自《MySQL 中文手册 5.1.x》

· 如果Unix的top工具或者Windows任务管理器显示,你的数据库的工作负荷的CPU使用率小于70%,则你的工作负荷可能是磁盘绑定的,可能你正生成太多的事务和提交,或者缓冲池太小。使得缓冲池更大一些会有帮助的,但不要设置缓冲池等于或超过物理内存的80%.

· 把数个修改放在一个事务里。如果事务对数据库修改,InnoDB在该事务提交时必须刷新日志到磁盘。因为磁盘旋转的速度至多167转/秒,如果磁盘没有骗操作系统的话,这就限制提交的数目为同样的每秒167次。

MySQL 服务器内存使用

Every so often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use.

经常有人问我配置MySQL时该如何估算内存的消耗。那么该使用什么公式来计算呢?

降序索引和减轻索引扫描

Descending indexing and loose index scan
降序索引和减轻索引扫描

Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general - these are features MySQL should get for all storage engines at some point.

分组提交和实时fsync

Group commit and real fsync
分组提交和实时fsync

During the recent months I’ve seen few cases of customers upgrading to MySQL 5.0 and having serious performance slow downs, up to 10 times in certain cases. What was the most surprising for them is the problem was hardware and even OS specific - it could show up with one OS version but not in the other. Even more interesting performance may be dramatically affected by –log-bin settings, which usually has just couple of percent overhead. So what is going on?

选择合适的innodb_log_file_size

Choosing proper innodb_log_file_size

选择合适的innodb_log_file_size

If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time before MySQL Server is operational again.

MySQL 优化(十一)

7.6 磁盘

  • 磁盘搜索是性能的很大瓶颈。这个问题在数据大量增长以至于无法使用有效的缓存时尤为明显。或多或少随即访问大数据库时,就必然会有至少一次磁盘搜索来读数据,两次磁盘搜索来写数据。最小化这个问题的办法就是降低磁盘搜索次数。

  • 增加有效磁盘马达数量(这能减少搜索时的开销)或者将不同的文件链接到不同的磁盘上又或者分盘:

    使用符号链接

MySQL 优化(十)

7.5.4
编译和连接如何影响MySQL的速度

以下的大部分测试都是在Linux上用MySQL的基准套件来做的,不过它们对其他操作系统以及不同的工作量也是有一定启示的。

-static 连接的话,MySQL执行速度的速度是最快的。
在Linux上,用 pgcc-O3
编译的代码最快。大概需要200MB的内存加上这些选项来编译 `sql_yacc.cc`,因为 gcc/pgcc

页面

Subscribe to RSS - MySQL优化