MySQL 编译安装及优化

注意：由于我是作为数据库服务器，所以安装系统时，只需要基本的，其余的都可以不安装。

 
新建一个名为 mysql 的用户组
# groupadd mysql
在 mysql 用户组下新建一个名为 mysql 的用户
# useradd -g mysql mysql
# tar xzf mysql-5.0.70.tar.gz
# cd mysql-5.0.70

 

源码编译 MYSQL ( 环境 CentOS 5.2 + Intel Pentium 4 630 3.00GHz + 4G RAM, 根据具体环境做相应的变更)

# CHOST="i686-pc-linux-gnu" CFLAGS="-O3 -msse -msse2 -msse3 -mmmx -mfpmath=sse,387 -mtune=prescott -march=prescott -pipe -fomit-frame-pointer -Wa,-march=prescott -finline-limit=400 -fforce-addr" CC="gcc" CXX="gcc" CXXFLAGS="${CFLAGS} -fvisibility-inlines-hidden -felide-constructors -fno-exceptions -fno-rtti" LDFLAGS="-Wl,-O2 -s" ./configure --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --enable-assembler --enable-thread-safe-client  --with-big-tables --with-charset=utf8 --with-collation=utf8_general_ci --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-comment=Source --with-server-suffix=-enterprise-gpl --with-mysqld-user=mysql --with-extra-charsets=gbk,latin1 --with-pthread --with-innodb --without-isam

 

至于具体的优化参数就懒得解释了，因为每个人的具体环境不一样。

 

GCC 编译优化可以参考

 

1. 英文好的看官方最新文档

http://gcc.gnu.org/onlinedocs/gcc-4.2.4/gcc/i386-and-x86_002d64-Options.html#i386-and-x86_002d64-Options

 

2. 英文不好的看

http://lamp.linux.gov.cn/Linux/optimize_guide.html

 

至于 MySQL 的编译优化可参与 MySQL 5.1 Reference Manual 中编译指南。

 

# make

编译的时间可能会比较长

# make install

 

编译安装完成后执行后续操作：

# cd /usr/local/mysql

# bin/mysql_install_db --user=mysql

# chown -R root . // 设置权限，注意后面有一个 "."

# chown -R mysql /var/lib/mysql // 设置 mysql 目录权限

# chgrp -R mysql . // 注意后面有一个 "."，同时让mysql组用户有同root的权限

# cp share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf

# cp share/mysql/mysql.server /etc/init.d/mysqld // 开机自动启动 mysql

# chmod 755 /etc/init.d/mysqld

# chkconfig --add mysqld  //让 MySQL 随机启动

# /etc/init.d/mysqld start // 启动 MySQL

# bin/mysqladmin -u root password "password_for_root"

# service mysqld stop // 关闭 MySQL

 

由于我是把它作为独立的数据服务器，为了数据库系统时间的准确性，最好装 NTP 服务。
# yum install –y ntp
# crontab -e
0 03 * * * /usr/sbin/ntpdate 1.cn.pool.ntp.org

中国及亚洲 NTP 服务器
server 1.cn.pool.ntp.org
server 1.asia.pool.ntp.org
server 3.asia.pool.ntp.org

以上命令设置好后存盘。
# /sbin/service crond reload

 

MySQL 的优化分为两部分，一是服务器物理硬件的优化；二是 MySQL 自身(my.cnf) 的优化。

 

1.服务器硬件对 MySQL 性能的影响

a. 磁盘寻道能力,以目前高转速 SATA 硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200 次，这是物理特性决定的，没有办法改变。MySQL 每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O 是制约MySQL 性能的最大因素之一，对于日均访问量在100万 PV 以上的应用，由于磁盘I/O 的制约，MySQL 的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案。

 

使用 RAID-0+1 磁盘阵列，注意不要尝试使用 RAID-5，MySQL 在 RAID-5 磁盘阵列上的效率不会像你期待的那样快；抛弃传统的硬盘，使用速度更快的闪存式存储设备。经过一些测试，使用闪存式存储设备可比传统硬盘速度高出6-10倍左右。

 

b. CPU对于MySQL应用，推荐使用 S.M.P.架构的多路对称CPU。

 

c. 物理内存对于一台使用 MySQL 的 Database Server 来说非常重要，服务器内存建议不要小于2GB，推荐使用4GB 以上的物理内存。

 

2.MySQL自身因素

当解决了上述服务器硬件制约因素后，看看 MySQL 自身的优化是如何操作的。对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。下面介绍一些对性能影响较大的参数。

 

由于 my.cnf 文件的优化设置是与服务器硬件配置息息相关的，下面服务器硬件环境：

CPU:  Pentium 4 630 3.00GHz  Threads:2

内存: 4GB DDR2 667

硬盘: SATA 160GB

 

我们根据以上硬件配置结合一份已经优化好的 my.cnf 进行说明：

 

# vi /etc/my.cnf

以下只列出 my.cnf 文件中 [mysqld] 段落中的内容，其他段落内容对 MySQL 运行性能影响甚微，因而姑且忽略。

[mysqld]

 port = 3306

 serverid = 1

 socket = /tmp/mysql.sock

 

# skip-locking
避免 MySQL 的外部锁定，减少出错几率增强稳定性

 

# skip-name-resolve
禁止 MySQL 对外部连接进行 DNS 解析使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP 地址方式，否则 MySQL 将无法正常处理连接请求！
注意：如果从数据库服务器以外的 PC 连接到 DB Server 很慢时，很可能就是这个参数未打开。

 

# back_log

这个值指出在 MySQL 暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP 连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定 back_log 高于你的操作系统的限制将是无效的。当你观察你的主机进程列表，发现大量

264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL

的待连接进程时，就要加大 back_log 的值了。默认数值是50，一般 384 就够了。

 

# key_buffer_size

这对 MyISAM 表来说非常重要。如果只是使用 MyISAM 表，可以把它设置为可用内存的 30-40% 。合理的值取决于索引大小,数据量以及负载--记住: MyISAM 表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了。-- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的，这么做太浪费了。如果你很少使用 MyISAM 表，那么也保留不低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

 

# sort_buffer_size

查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占！如果有100 个连接，那么实际分配的总共排序缓冲区大小为 100 × 6 ＝ 600MB 。所以，对于内存在4GB 左右的服务器推荐设置为6-8M 。

 

# read_buffer_size

读查询操作所能使用的缓冲区大小。和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享！

 

# join_buffer_size

联合查询操作所能使用的缓冲区大小，和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享！

 

# query_cache_size

指定 MySQL 查询缓冲区的大小。可以通过在 MySQL 控制台执行以下命令观察：

# > SHOW VARIABLES LIKE '%query_cache%';

# > SHOW STATUS LIKE 'Qcache%';

如果 Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况；

如果 Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。

 

# max_connections

指定 MySQL 允许的最大连接进程数。如果在访问时经常出现 Too Many Connections 的错误提示，则需要增大该参数值。

 

# wait_timeout

指定一个请求的最大连接时间，对于4GB 左右内存的服务器可以设置为5-10 。

 

# thread_concurrency

该参数取值为服务器逻辑 CPU数量×2，在本例中，如果服务器有 2 颗逻辑CPU，而CPU 又支持 H.T 超线程，所以实际取值为4 × 2 ＝ 8

 

# skip-networking

开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常连接！

 

# innodb_buffer_pool_size

这对 Innodb 表来说非常重要。Innodb 相比 MyISAM 表对缓冲更为敏感。MyISAM 可以在默认的 key_buffer_size 设置下运行的可以，然而 Innodb 在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于 Innodb 把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用 Innodb 的话则可以设置它高达 70-80% 的可用内存。如果你的数据量不大，并且不会暴增，那么无需把innodb_buffer_pool_size 设置的太大了。

 

# innodb_additional_pool_size

这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB( 或者更大) ，就需要看一下Innodb 其他需要分配的内存有多少。

 

# innodb_log_file_size

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB ，跟据服务器大小而异。

 

# innodb_log_buffer_size

默认的设置在中等强度写入负载以及较短事务的情况下服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存(它每秒都会刷新一次),因此无需设置超过1 秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

 

# innodb_flush_logs_at_trx_commit

Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

 

# table_cache

Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

 

# thread_cache

Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

 

# query_cache

If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

 

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

 

注意：就像你看到的上面这些全局表量，它们都是依据硬件配置以及不同的存储引擎而不同，但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询，那么就无需增加 sort_buffer_size 的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。

通常在分析系统负载后才来设置会话变量。

 

另附：如果发现你的应用日志里会有 too many open files 时，应修改 linux 系统的 open file 数量，也可通过以下命令来查看那个程序打开的文件数最多。

# lsof -n|awk '{print $2}'|sort|uniq -c |sort -nr|more

 

可能出现的结果如下：
131    24204
  57    24244
  57    24231
  56    24264
其中第一行是打开的文件句柄数量，第二行是进程号。得到进程号后，我们可以通过 ps 命令得到进程的详细内容。
# ps -aef|grep 24204  
mysql    24204 24162 99 16:15 ?    00:24:25 /usr/sbin/mysqld

 

修改系统默认值 ulimit -HSn 4096
以上命令中，H指定了硬性大小，S指定了软性大小，n表示设定单个进程最大的打开文件句柄数量可以修改 /etc/profile 把上面命令加到最后，然后 source /etc/profile 再通过 ulimit -a 就可以看到修改后的结果了。

 

关于 lsof 命令的用法，详细可参考 IBM 技术社区中的一篇关于 lsof 的文章。