极湖

原文: http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html
翻译: 小白（又名 风子）

MySQL优化
关于优化MySQL应对高访问网站的讨论
Tim,
首席技术长官，DCS

MySQL设置，高并发连接
本人有一网站每天需要负责大约3000用户，每天长达5到7小时的在线工作。所以在繁忙时段，我们要为2000多并发用户提供服务。在PHP和MySQL的设置比较合理的前提下，即使是入门级的Intel平台的服务器，MySQL一样可以应对的很好。

首先是硬件需求
1. 最好可以将Apache/PHP和MySQL分开跑在两台独立的服务器上，Linux版本及分发不限
2. 尽量减少服务器的其他负载，将最多的资源留给Apache/PHP和MySQL服务

我正在使用的服务器配置如下：
1. Apache/PHP: 奔腾III, 600MHz, 512M内存
2. MySQL: 双奔腾III, 750MHz, 2G内存

如此有偏向的配置主要是因为数据库的负荷非常重。我们的网站为会员制的在线教学系统，只有登录以后才能使用其中的功能，而且内容高度自订化。学生可以在系统中访问属于本人的一整套服务，譬如课程，成绩卡，时间表等等。教师可在网站上在线创建课程，包括课文，语音材料（包括文本生成的音频材料）等等。

1) PHP 程序编写： 一定要使用持续连接！
从Apache/PHP到MySQL的通讯中，建立和关闭连接是一个非常大的开销。通过使用持续连接，大型的数据访问网站可以共享连接来交换数据，避免了载入每页时都要重复的连接请求。有鉴于用户每次点击都可能会产生一次连接，持续连接的左右是相当重要的。
确定使用mysql_pconnect来取代mysql_connect，同时在php.ini或者程序文件中的ini_set行应当做出相应的调整。

2) Apache设置(httpd.conf)。我们的网站进行了很多针对性的优化，经过多次试验对比，我们最终确定了一组参数。最终的结果使我们在top可以得到最大的CPU空闲百分比。
MinSpareServers 10
MaxSpareServers 20
StartServers 70
MaxClients 255

3) Mysql设置(my.cnf)。在MySQL的配置文件重，我们在[mysqld]模块中加入了如下内容
set-variable = max_connections = 300
(这个数字必须大于Apache的MaxClients，否则Apache将无法充分利用连接)
set-variable = max_user_connections = 300
set-variable = table_cacle = 1200
(在数据库查询中可能发生的最多join表数量乘以max_user_connections)
在如上的参数重，max_connections和max_user_connections为我们提供稳定的服务提供了巨大的帮助。当Apache/PHP连接MySQL服务器时会被作为单用户来对待，MySQL默认并发连接数为1（译者注，鉴于原文撰写时间较早，当时的数据库默认配置较为保守。译者特地检查MySQL 5.0.32的默认位置文件，发现默认并发连接数已经提高到100，对于中小型网站来说已经游刃有余。不过网管对于这个参数的作用还是应当了然于胸的）。通过修改max_connections，使得Apache/PHP可以产生大量的持续连接。当Apache启动时，大量的并发连接已经就绪，之后的访问都可以利用这条通路来交换数据，从而大大的节约了建立连接时的系统开销。

一些MySQL的其他技巧：
set-variable = max_allowed_packet = 1M (数据健壮性检查，防止失控查询）
set-variable = max_connect_errors = 999999 (防止mysqld因为过多的连接错误而重启）

译者注: 本文原创于2003年，当时的网络条件和硬件条件和如今已经有了比较大的区别，软件配置也有了相当的变化，譬如set-variable在MySQL 4.0.2以后已经可以省略。所以本文仅供参考，为有志于深入了解Apache MySQL的管理员新手们提供一点辅助的参考资料，切莫生搬硬套。

MySQL性能调优
参考：
http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html

PostgreSQL性能调优
参考：
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://php.y-110.net/wiki/index.php?PostgreSQL%A1%A7%A5%C1%A5%E5%A1%BC%A5%CB%A5%F3%A5%B0%B4%AA%BD%EA
http://h2np.net/tips/tips12.htm
http://www.asahi-net.or.jp/~aa4t-nngk/pgsql5.html

[附]
SQL优化
http://www.geocities.jp/mickindex/database/db_optimize.html

Linux上shmmax参数的设置及含义
http://www.eygle.com/archives/2005/11/whats_mean_linux_shmmax.html

1. 修复并优化所有数据库:
本地:
# mysqlcheck -A -r -o -p

远程:
# mysqlcheck -A -r -o -p -h服务器地址

2. 修复并优化指定的数据库:
本地:
# mysqlcheck -u用户名 -p密码 -r -o 库名

远程:
# mysqlcheck --u用户名 -p密码 -h服务器地址 -r -o 库名

建库时指定编码：
CREATE DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

赋予用户权限：
GRANT ALL PRIVILEGES ON dbname.* TO dbuser@localhost IDENTIFIED BY "dbpass";

错误“ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client.”的对策：
SET PASSWORD FOR dbuser@"localhost"=OLD_PASSWORD('yourPassword');

原文地址:
http://developer.spikesource.com/wiki/index.php/How_to_change_the_mysql_database_location

Identify the database files you wish to migrate into the new data directory
You can use the following command to list the current databases in mysql

mysqlshow -u root -p

Keep this list available as you will reference it in a later step
Shutdown the MySQL database, if it is running

mysqladmin -u root -p shutdown

Locate the MySQL configuration file. This is usually located in the directory $OSS_HOME/var/mysql

Note: You should make a backup copy of each file you're editing before modifying the contents

The property you need to change is named datadir. The default value is $OSS_HOME/var/mysql, which places the database files in the same directory as the log files and other runtime generated files. This property is usually located in the [mysqld_safe] section in the configuration file

You need to change:

[mysqld_safe]
datadir      = /opt/oss/var/mysql

to

[mysqld_safe]
datadir      = /opt/oss/var/mysql/data

Save these changes
Create the directory you specified in the datadir property

mkdir -p /optoss/var/mysql/data

Check to make sure the user assigned to execute mysql has read/write privileges on this directory
You may need to modify the directory settings using chown and chmod

Move the databases listed in the first step to the new data directory

mv test /opt/oss/var/mysql/data

Note: You can use the copy (cp) command instead if you prefer. Remember to remove the copied files once the migration is complete
You will need to move/copy each database into the new data directory

After all the databases have been migrated to the new data directory, you should start MySQL

openpkg rc mysql start

The database files will now be managed under the new data directory. If you encounter any problems during startup, you check the hostname.err file located in the data directory.