☰

优化我的 MySQL 服务器

tag-icon • tag-icon apache-2.2 mysql database
优化我的 MySQL 服务器

我刚刚从 3gb vps 升级到 8gb 专用服务器，它不再能处理我的网站。我使用 sphinx 全文搜索引擎构建了一个搜索引擎，每天收到 10 万到 20 万个查询，“其中很多是 googlebot 等”。

无论如何，我需要帮助将 MySQL 配置到一个非常依赖数据库的网站。数据库大约有 10 GB，并且数据不断被查询和插入。有人能推荐一些设置来帮助我在这个新的 8GB 服务器上发挥 MySQL 的最佳性能吗？我将不胜感激。

我正在使用 MyISAM 表。

这是我的 my.cnf 文件。有人能告诉我我应该怎么做或者你会怎么做吗？

服务器的规格如下：

  • 四核至强 X3220
  • 8 GB DDR2
  • 2 个 500 GB SATA2

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

#bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 256M
max_allowed_packet      = 16M
sort_buffer_size        = 32M
#mysiam_sort_buffer_size = 32M
tmp_table_size          = 128M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 500
table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 1M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

答案1

http://mysqltuner.pl/mysqltuner.pl是一个能够识别任何潜在问题的优秀脚本。

最好在 MySQL 运行至少一个小时左右后运行它。如果您刚刚重新启动 MySQL，那么它没有太多的统计数据可供使用。

获得http://mysqltuner.pl/mysqltuner.pl&& perl mysqltuner.pl

答案2

在这里你可以找到几个可用于调整 MySQL 服务器的脚本

http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/

相关内容