MySQL数据库磁盘优化

http://www.sina.com.cn 2008年06月23日 14:25  《程序员》
出处:IT专家网
磁盘搜索是性能的很大瓶颈。这个问题在数据大量增长以至于无法使用有效的缓存时尤为明显。或多或少随即访问大数据库时,就必然会有至少一次磁盘搜索来读数据,两次磁盘搜索来写数据。最小化这个问题的办法就是降低磁盘搜索次数。

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

  使用符号链接
  意思是,把 MyISAM 表的索引文件和/或数据文件从数据目录下通常的地方链接到其他磁盘上(这也是分盘)。如果这个磁盘没有其他用途的话,这对读写次数都比较好。详情请看"7.6.1 Using Symbolic Links"。

  分盘
  如果有好几个磁盘,就把第一个区块放在第一个磁盘,把第二个区块放在第二个磁盘,以此类推。这意味着正常的数据大小比分盘后的数据小(或者完全一样),这能获得更好性能。分盘完全依赖于操作系统以及分盘的大小,因此要用不同的分盘大小基准测试应用程序。详情请看"7.1.5 Using Your Own Benchmarks"。基准测试的速度的不同完全依赖于分盘大小。依赖分盘设置参数以及磁盘数量,会得到大量不同的测量结果。必须随机或者顺序选择优化方法。

  可能会为了可靠性采用 RAID 0+1,这时,就必须用 2*N 个驱动去来保存 N 个驱动器上的数据。如果有足够的内存这可能是最好的方法。不过,这也需要使用卷管理软件来有效地管理数据。

  另一个好办法是RAID的级别根据数据的重要性而定。例如,把能重新生成的有点重要的数据保存在RAID 0磁盘上,把很重要的数据如主机信息日志等保存在RAID 0+1或者RAID N磁盘上。RAID N在有很多个写入时可能会有问题,因为会同时请求更新同一个字节位。

  在Linux上,可以用 hdparm 来配置磁盘接口以获得更好的性能(在负载下高达100%也不是不可能的)。以下 hdparm 配置选项对MySQL就很合适,对其他应用程序可能也不错:

  hdparm -m 16 -d 1

  注意,当使用这个命令之后性能和可靠性会依赖硬件,因此我们强烈建议在使用 hdparm 后一定要做测试。请查阅 hdparm 的手册。如果没有正确使用 hdparm,则可能导致文件系统冲突,所以在试验之前备份一下。

  还可以在数据库使用的文件系统上设置其参数:如果无需知道文件的最后访问时间(这对数据库系统没用),则在挂载文件系统时使用 -o noatime 选项。这就会略过更新文件系统节点的最后访问时间,也就减少了磁盘搜索。在很多操作系统上,可以在挂载文件系统是使用 -o async 选项以异步更新它。如果你的机器相当的稳定,这会带来性能提升但可靠性并没牺牲多少(默认只能在Linux上这样用)。

1、使用符号链接

  可以把数据表或者数据库移动到别的目录下,然后用符号链接到新的位置来代替。你可能想这么做,例如,想要把表分布到不同的磁盘上以提高系统速度,就把它们移动到有更多剩余空间的磁盘上。

  建议只是把数据库链接到其他磁盘上,数据表的链接是最后的选择。

  1.1 在 Unix 上符号链接数据库

  在Unix上,给数据库做符号链接的方法是先在其他磁盘上创建一个目录,然后再把它链接到MySQL数据文件目录下。


  shell> mkdir /dr1/databases/test
  shell> ln -s /dr1/databases/test /path/to/datadir


  MySQL不支持把一个目录链接成多个数据库。只要没有在数据库间做符号链接,那么它就没问题。假使在MySQL数据文件目录下已经有一个数据库 db1 了,然后把 db1 链接到 db2 :


  shell> cd /path/to/datadir
  shell> ln -s db1 db2


  现在,在 db1 中的表 tbl_a,也会在 db2 中出现。如果有一个客户端要更新 db1.tbl_a 而另一个要更新 db1.tbl_a,这时就会出问题了。

  如果确实需要这么做,那么就修改一个源文件。要修改的文件根据MySQL版本不同而不同。MySQL 4.0或更新,在 `mysys/my_symlink.c' 文件中找到以下语句:

  if (!(MyFlags & MY_RESOLVE_LINK) ||
  (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

  MySQL 4.0以前,在 `mysys/mf_format.c' 文件中找到如下语句:

  if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

  然后把这个语句改成:

  if (1)

  在Windows上,在编译MySQL时使用选项 -DUSE_SYMDIR 就能内置支持目录符号链接。这可以让你把不同的数据库放到不同的磁盘上。

1.2 在 Unix 上符号链接数据表

  在MySQL 4.0以前,除非特别小心否则不要链接数据表。有一个问题是,当在一个符号链接表上执行 ALTER TABLE, REPAIR TABLE, 或 OPTIMIZE TABLE 时,符号链接就会被删除然后替换成原来的文件。这是因为执行这些语句时,需要在数据库目录下创建临时文件,然后在操作完成后把临时文件替换到原来的文件中去。

  最好不要在不能很好支持 realpath() 调用的操作系统上链接数据表(不过至少Linux和Solaris支持 realpath())。执行 SHOW VARIABLES LIKE 'have_symlink' 语句来检查你的系统是否支持符号链接。

  在MySQL 4.0,MyISAM 表完全支持符号链接。而其他表类型如果也做符号链接的话,则很可能在执行语句前会碰到一些奇怪的问题。

  MySQL 4.0中的 MyISAM 表符号链接以如下方式工作:

  在数据目录下,总是有表定义文件,以及数据文件,以及索引文件。数据和索引文件可以被移动到任何处然后用符号链接代替,但是表定义文件不可以。

  可以分别把数据和索引文件链接到不同目录下。

  在 mysqld 没有运行时可以用命令行 ln -s 手工完成符号链接。如果用SQL,可以在 CREATE TABLE 时使用选项 DATA DIRECTORY 和 INDEX DIRECTORY 告诉服务器使用符号链接。详情请看"14.2.6 CREATE TABLE Syntax"。

  myisamchk 不会替换符号链接的数据或索引文件。它直接在符号链接指向的文件上操作。任何临时文件都创建在数据或索引文件所在的目录下。

  当删除一个符号链接的表后,链接表及其指向的表都会被删除。这就是为什么不能以 root 身份运行 mysqld 的原因,同样地,不要允许用户有权写MySQL数据库目录。

  如果用 ALTER TABLE ... RENAME 语句重命名一个表且没有把它移动到其他数据库下,那么在数据库目录下的文件就被改名了,相应地,它指向的数据或索引文件也改名了。

  如果用 ALTER TABLE ... RENAME 语句把表移动到其他数据库下,则这个表就移动到其他数据库目录下。旧的链接及其所指向的文件都被删掉。换言之,新的表就不再被符号链接了。

  如果没有使用符号链接,那么就给 mysqld 增加选项 --skip-symbolic-links

  确保无人能删除或重命名数据文件目录以外的文件。

  在MySQL 4.0.15以前, SHOW CREATE TABLE 语句不会报告一个表是否有符号链接。mysqldump 也一样,它是用 SHOW CREATE TABLE 来产生 CREATE TABLE 语句的。

  表符号链接操作还不支持:

  ALTER TABLE 操作会忽略 DATA DIRECTORY 和 INDEX DIRECTORY 表选项。

  BACKUP TABLE 和 RESTORE TABLE 也没考虑符号链接。

  `.frm` 文件肯定不能被符号链接(在前面提到,索引及数据文件可以被符号链接)。企图这么做(比如用同义)的话就会导致一些错误。假设有在数据库目录下有一个数据库 db1,库里有一个表 tbl1,在 db1 目录下把 tbl2 符号链接到 tbl1:


  shell> cd /path/to/datadir/db1
  shell> ln -s tbl1.frm tbl2.frm
  shell> ln -s tbl1.MYD tbl2.MYD
  shell> ln -s tbl1.MYI tbl2.MYI


  现在如果有一个线程读取 db1.tbl1 而另一个线程更新 db1.tbl2 时就有问题了:

  查询缓存就会被愚弄了(它认为 tbl1 没有被更新,因此返回out-of-data结果)。

  在 tbl2 上执行 ALTER 语句也会失败。

1.3 在 Windows 上符号链接数据库

  从MySQL 3.23.16开始,Windows上支持用 -DUSE_SYMDIR 选项编译 mysqld-max 和 mysql-max-nt 服务器。这就可以通过符号链接把一个数据库放在其他磁盘上。这跟在Unix上做符号链接相似,只是设置过程不大一样而已。

  从MySQL 4.0开始,默认支持符号链接。如果不需要,用 skip-symbolic-links 选项关闭它。


  [mysqld]
  skip-symbolic-links


  在MySQL 4.0以前,默认不支持符号链接。想要支持它,就要在 `my.cnf' 或 `my.ini' 文件中增加如下内容:


  [mysqld]
  symbolic-links


  在Windows上,在MySQL数据文件目录下创建一个包含目标目录路径的文件来做符号链接。这个文件的名字叫 `db_name.sym`,db_name 是数据库的名字。

  假设MySQL数据文件目录是 `C:\mysql\data',现在想要把数据库 foo 放在 `D:\data\foo` 目录下。按以下方法设置:

  确认 `D:\data\foo` 目录存在,如果有必要就创建它。如果在数据文件目录下已经存在一个数据库目录名为 `foo`,那么就把它移动到下 `D:\data`。否则,符号链接就不生效。移动数据库的时候最好不要运行服务器,以避免可能出现的问题。

  创建一个文件 `C:\mysql\data\foo.sym`,它的内容是路径 D:\data\foo\。

  之后,数据库 foo 下的所有表都会创建到`D:\data\foo` 下。注意,如果在MySQL数据文件目录下已经存在该数据库目录,那么就不会使用符号链接了。

Powered By Google
不支持Flash
·城市对话改革30年 ·新浪城市同心联动 ·诚招合作伙伴 ·企业邮箱畅通无阻
不支持Flash