`

mysql优化

阅读更多

MySQL 是一个很棒的 open source 数据库引擎,大部分的网站和博客都是由 MySQL 驱动的。MySQL 的默认安装占用的内存资源比较大(相对于一个只有 64MB 的 VPS来说),优化 MySQL 可以减少内存消耗,把更多的内存省下来留给其他程序。

MySQL 的配置文件在 /etc/mysql/my.cnf(Debian 5),为了方便调整配置,MySQL 为小资源系统提供了一个叫做 my-small.cnf 的配置文件,是给小于 32MB 内存的服务器设置的。我们可以在这个配置文件的基础上作小部分的调整。

先找到 /usr/share/doc/mysql-server-5.0/examples/my-small.cnf,然后覆盖 /etc/mysql/my.cnf(Debian)。如果是 CentOS 5 的话,路径是:/usr/share/doc/mysql-server-5.0.45/my-small.cnf,覆盖 /etc/my.cnf。

参数说明

如果不使用 BDB table 和 InnoDB table 的话,加入下面2行关闭不需要的表类型很有必要,关闭 innodb 可以省下大量内存,虽然 InnoDB 好处多多但是在一个64MB的 VPS 上并不能体现出来,并且很占内存。

skip-bdb
skip-innodb

key_buffer 是优化性能的重要参数,用来缓存 tables keys 和 indexes,增加这个值可以更好的处理索引,读和写都需要索引。这里设设置成 16K 足够了。table_cache 是所有线程打开的表的数量,增加值可以增大 MySQL 的文件描述符数量,避免频繁的打开表,原始 my-small.cnf 中 table_cache 设置成4有点小,一个 wordpress 的页面通常会涉及到10个左右的表,其他的程序比如 Drupal,MediaWiki 会涉及到更多,将table_cache改为8。

key_buffer = 16K
table_cache = 8

max_connections 是数据库最大的连接数量,可以根据自己博客/网站的访问量来定这个值。如果博客/网站经常出现:Too many connections 错误的信息说明需要增大 max_connections 的值。thread_concurrency 是最大并发线程数,通常设置为 CPU核数量×2,在 VPS 宿主机上如果服务器有2颗物理 CPU,而每颗物理 CPU 又支持 H.T 超线程(一个处理器上整合了两个逻辑处理器单元),所以实际取值为4 × 2 = 8。

如果我们在优化 php.ini 的时候设置了同时只有2个 php-cgi 运行的话,那么我们也应该只设置2个 MySQL 线程同时运行。

max_connections = 16
thread_concurrency = 2

对于博客/新闻网站来说,用得最多的就是查询,所以需要加入 query cache 的设置。query_cache_size 是执行查询所使用的缓冲大小。

query_cache_limit = 256K
query_cache_size = 4M

thread_stack 用来存放每个线程的标识信息,如线程 id,线程运行时环境等,可以通过设置 thread_stack 来决定给每个线程分配多大的内存。

sort_buffer_size 是每个需要排序的线程分配的缓冲区大小,增加该值可以加速 order by 和 group by 的操作。注意:该参数是以每个连接分配内存,也就是说,如果有16个连接,sort_buffer_size 为 64K,那么实际分配的内存为:16 × 64K = 1MB。如果设置的缓存大小无法满足需要,MySQL 会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级,所以 sort_buffer_size 对排序的性能影响很大。

read_buffer_size 是顺序读取数据时的缓冲区大小,与 sort_buffer_size 一样,该参数分配的内存也是以每连接为单位的。read_buffer_size 是用来当需要顺序读取数据的时候,如无发使用索引的情况下的全表扫描,全索引扫描等。在这种时候,MySQL 按照数据的存储顺序依次读取数据块,每次读取的数据快首先会暂存在 read_buffer_size 中,当 buffer 空间被写满或者全部数据读取结束后,再将 buffer 中的数据返回给上层调用者,以提高效率。

read_rnd_buffer_size 是随机读取数据时的缓冲区大小,与顺序读相对应。

net_buffer_size 用来存放客户端连接线程的连接信息和返回客户端的结果集的缓存大小。当 MySQL 接到请求后,产生返回结果集时,会在返回给请求线程之前暂存在在这个缓存中,等积累到一定大小的时候才开始向客户端发送,以提高网络效率。不 过,net_buffer_size 所设置的仅仅只是初始大小,MySQL 会根据实际需要自行申请更多的内存,但最大不会超过 max_allowed_packet。

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

skip-locking用来避免 MySQL 外部锁定,减少出错几率,增强稳定性。

skip-locking

优化后配置

经优化后,my.cnf 的配置如下,top 查看 mysqld 保持在 5M 一下。

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking

key_buffer = 16K
query_cache_limit = 256K
query_cache_size = 4M
max_allowed_packet = 1M
table_cache = 8

max_connections = 16
thread_concurrency = 2

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

内存计算公式

MySQL memory = key_buffer + max_connections *

(join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

分享到:
评论

相关推荐

    mysql优化笔记+资料

    以下是一份详细的MySQL优化笔记,涵盖了多个方面: 一、查询优化 1. 使用索引:为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型,适用于不同的查询场景。 2. 避免全表扫描:尽量使用...

    mysql优化配置大全

    mysql慢可能是配置不对,阅读一下这个可能对你有帮助 ...对于Discuz!... 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。

    php之mysql优化

    以下将详细介绍MySQL优化的各个方面,并结合提供的文件名进行推测,尽管没有实际内容,但我们可以根据文件名来讨论可能涉及的主题。 1. **索引优化**:`mysql_rel.sql`可能包含SQL脚本,其中创建表结构和数据的关系...

    mysql优化.pdf

    在进行MySQL优化时,我们需要对数据库进行深入分析,以便找到性能瓶颈并进行相应的调整。优化可以分为三个主要部分:优化概述、查询与索引优化分析以及配置优化。 首先,在优化概述部分,了解MySQL数据库常见瓶颈是...

    MySql优化.rar

    在"MySQL优化.rar"这个压缩包中,我们很显然会接触到关于MySQL数据库优化的详细内容,这包括但不限于查询优化、索引优化、存储引擎选择、架构设计等多个方面。 首先,查询优化是MySQL性能提升的关键步骤。通过对SQL...

    mysql 优化工具.rar

    为什么要开发这个MySQL 优化工具(Why)   “一键优化”功能,可以优化本地/远程需要优化的机器,将繁琐的优化工作“傻瓜”式操作 根据您的业务需求Step By Step优化的MySQL服务器参数,起到指引的作用,简化用户...

    深入浅出的mysql优化大全

    以下是一些深入浅出的MySQL优化策略: 1. 选择合适的字段属性: 在设计数据库表时,应尽可能减小字段宽度,以减少存储空间和查询时间。例如,邮政编码字段只需设定为CHAR(6)即可,无需使用VARCHAR或更大的类型。...

    MySql优化.pdf

    MySQL优化 MySQL优化是数据库管理中的一个重要方面,它包括但不限于配置调整、索引优化、查询优化、服务器监控等。良好的优化可以显著提升数据库的性能和响应速度,保证系统的稳定运行。 一、慢查询日志与慢查询...

    Mysql优化方法介绍.ppt

    MySQL 优化方法主要涵盖...总之,MySQL优化涉及多个层面,包括合理选择存储引擎、优化字段类型、建立合适索引以及编写高效的SQL语句。每个环节都对数据库性能有着直接影响,需要根据实际业务需求进行综合考虑和调整。

    MySQL优化.docx

    ### MySQL优化知识点详解 #### 一、MySQL简介 MySQL是一款由MySQL AB公司开发的开源数据库管理系统,后来该公司被Sun Microsystems收购。MySQL因其简单、高效、可靠的特点,在IT行业中迅速崭露头角,成为最受欢迎...

    MYSQL优化-一篇很好的优化文章

    21. MYSQL扩展/优化-提供更快的速度 22. MYSQL何时使用索引 23. MYSQL何时不使用索引 24. 学会使用EXPLAIN 25. 学会使用SHOW PROCESSLIST 26. 如何知晓MYSQL解决一条查询 27. MYSQL非常不错 28. MYSQL应避免...

    我的mysql 优化日记

    我的mysql优化日记 我的mysql优化日记 我的mysql优化日记 我的mysql优化日记

    MySQL优化大揭秘.rar

    MySQL优化是数据库管理中至关重要的一个环节,它旨在提高数据查询效率、降低系统资源消耗,以确保服务的稳定性和响应速度。"MySQL优化大揭秘"这个压缩包中的"SQL.pdf"很可能包含了关于如何优化MySQL数据库的详细教程...

    mysql优化-sql高级

    mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制

    Mysql优化 PPT

    MySQL优化是数据库管理中的关键环节,它涉及到性能提升、资源利用率优化以及系统稳定性的保障。这份"Mysql优化 PPT"可能会涵盖多个方面的内容,包括但不限于查询优化、索引策略、存储引擎选择、数据库架构设计、资源...

    大型门户网站核心技术-Mysql优化

    教程名称:大型门户网站核心技术-Mysql优化 课程目录:【】Mysql优化 资料【】Mysql优化01关键技术【】Mysql优化02表的设计【】Mysql优化03慢查询(一)【】Mysql优化04慢查询(二)【】Mysql优化05慢查询(三)【】Mysql...

    企业级MySQL优化(从引擎为你介绍怎么优化、集群方案)

    总结来说,企业级MySQL优化是一个综合性的任务,涵盖安装配置、引擎选择、算法理解、集群构建和日常维护等多个环节。通过深入理解MySQL的工作机制和优化技巧,可以有效地提升数据库性能,保障企业的业务运行。

    mysql优化方案

    本文讨论了 MYSQL 优化方案的多个方面,涵盖 BIOS 设置优化、IO 子系统优化、Schema 设计优化、索引设计优化和无法使用索引的场景等方面的知识点,旨在帮助读者更好地了解 MYSQL 优化的方法和策略。

Global site tag (gtag.js) - Google Analytics