MySql my.cnf推荐设置

Question

我在谷歌上做了一些研究，读过一些博客，但我没有发现对我来说真正有效的东西。在我的mysql测试配置多次崩溃之后，我别无选择，只能在这里寻求帮助。

有谁能给我一些提示，说明在my.cnf中推荐使用以下规范的重型数据库服务器(12个表，超过50G数据)的值：

root@blah:/etc/mysql# lscpu 
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    16
Core(s) per socket:    1
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 62
Stepping:              4
CPU MHz:               2600.092
BogoMIPS:              5200.18
Hypervisor vendor:     Xen
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              20480K
NUMA node0 CPU(s):     0-15
root@blah:/etc/mysql# free -m
             total       used       free     shared    buffers     cached
Mem:         64237       2747      61490         19        189       1811
-/+ buffers/cache:        746      63491
Swap: 

    2047          0       2047

此服务器仅用作数据库，因此我不必担心其他任何事情。只是为了确保MySql得到所有的资源，并以最好的方式使用所有的资源。

我拥有的my.cnf文件如下所示：

root@blah:/etc/mysql# cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
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        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * 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
#
# Error log - should be very few entries.
#
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/

Answer 1

我看到你有64 is内存，它全部用于MySQL服务器，这很不错。首先，我建议增加您的缓存大小，tmp_tables等，您可以搜索那些谷歌。如果您使用的是InnoDB表，我建议您也修改这些表。下面我会给出一些建议，但最终取决于你该做什么，你能做的最好的就是阅读并测试不同的吐露。

若要检查配置参数，请使用此查询、修改“部分”或完全省略它，这样您就可以确定您的设置是否存在：

show variables like '%log%'; 

跳过名称解析，这将使mysql更快，因为它不需要解析DNS，请确保在连接到服务器后置时使用IP地址：

[mysqld]
skip-name-resolve

建议为服务器上每1GB RAM的这两个值提供64M。这会提高性能，将这些值更改为您的需要。

tmp_table_size= 2000M
max_heap_table_size= 2000M
max_tmp_tables=300

记录慢速查询，long_query_time是查询时间长的秒数，相应调整

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql-slow.log
long_query_time = 1

一些设置，您需要调整，我给以下。

sort_buffer_size=10M 
read_buffer_size=10M 
table_open_cache=8000
query_cache_limit=50M
join_buffer=10M

这些通常是您需要调整的主要设置，这里的数字仅供您参考，您应该相应地更改/调整它们。阅读Mysql文档中每个文档的含义。

如果您正在运行phpMyAdmin，则会出现数据库状态，然后到那里，它将显示您的危险信号。除此之外，您还可以手动检查一些参数，如:显示全局状态，如'Opened_tables'；

偶尔检查一下是否有支离破碎的表，或者它们是否需要修复。此命令解决了以下问题：

mysqlcheck -u root --auto-repair --optimize --all-databases

另一个用户工具是mysqltuner，安装并运行它。

希望这能有所帮助。

Answer 2

我不同意这些意见：

tmp_table_size= 2000 m max_heap_table_size= 2000 m

如果您编写了一些需要多个tmp表的复杂查询，您可能很快就会耗尽RAM。我建议不超过1%的内存。

table_open_cache应该是几百。如果将其设置为8000，则意味着您可能有那么多表，这意味着模式的错误设计。

我什么都没看到InnoDB？当然，所有的表都将使用InnoDB引擎。它的主要设置是

innodb_buffer_pool_size = 45G  -- about 70% of RAM
innodb_buffer_pool_size_instances = 16
innodb_file_per_table = ON
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G

如果它是一个具有大量写操作的生产系统，请关闭查询缓存：

query_cache_type = 0
query_cache_size = 0

在运行了一段时间之后，还可以进行进一步的微调。请提供SHOW VARIABLES和SHOW GLOBAL STATUS。

不要使用OPTIMIZE；这很费时，而且通常不会带来任何好处，特别是对于InnoDB。mysqltuner有一些缺陷；它抱怨太多的表是“支离破碎的”；这是一个虚假的警告。

Answer 3

有人不能建议像这样最好的配置文件。在启动配置之前，有许多事情要知道，例如，存储引擎、索引、使用过的查询，甚至是使用的应用程序类型，它是多线程的吗，使用了多少连接，使用的是什么版本的MySQL服务器等等。尽管如此，还是有人这样做.是的，他有一些伟大的经验，这是我做不到的。

在我的脑海中有几件事:您有64 GB的内存，这对服务器非常好。您的默认存储引擎在这里将是innoDB，为此尝试添加缓冲区大小，这可能是您内存的60-65%。如果您只使用MyISAM，然后添加myisam恢复力，备份选项也(按照dev.mysql.com的版本)，您可以给max_heap_table_size和tmp_table_size一般1GB，增加所有缓冲区。