7.5. 优化MySQL服务器
- 7.5.1. 系统因素和启动参数的调节
- 7.5.2. 调节服务器参数
- 7.5.3. 控制查询优化器的性能
- 7.5.4. 编译和链接怎样影响MySQL的速度
- 7.5.5. MySQL如何使用内存
- 7.5.6. MySQL如何使用DNS
7.5.1. 系统因素和启动参数的调节
我们从系统级因素开始，因为必须尽早地进行部分决策以获得较大性能。在其它情况下，快速浏览该节就足够了。但是，了解一下更改该层次的参数能够获得多少性能提高是很有意义的。
使用的操作系统很重要。为了更好地使用多CPU机器，应使用Solaris(因为其线程工作得很好)或Linux(因为2.4和以后的内核有很好的SMP支持)。请注意默认情况旧的Linux内核有一个2GB的文件大小限制。如果有这样的一个内核并且需要文件大于2GB，应得到ext2文件系统的大文件支持(LFS)补丁。其它文件系统例如ReiserFS和XFS没有此2GB限制。
将MySQL用于生产前，我们建议你在想用的平台上对它进行测试。
其它技巧：
·如果有足够的RAM，可以移除所有的交换设备。有些操作系统即使有自由内存也使用交换设备。
·使用--skip-external-locking MySQL选项以避免外部锁定。该选项默认开启。
请注意只要你只运行一个服务器，--skip-external-locking选项不影响MySQL的功能。只要记住运行myisamchk之前关闭服务器(或锁定并刷新相关表)。在一些系统上该选项是强制的，因为在任何情况下外部锁定均不工作。
不能使用--skip-external-locking的唯一情况是对相同的数据运行多个MySQL服务器(非客户)的情况，或者如果没有事先告诉服务器刷新并锁定一个表即运行myisamchk来检查(非修复)该表。请注意一般不建议使用多个MySQL服务器来并行访问相同的数据，除了使用MySQL Cluster时。
即使使用--skip-external-locking，仍然可以使用LOCK TABLES和UNLOCK TABLES。
7.5.2. 调节服务器参数
可以用这个命令得到mysqld服务器默认缓存区的大小：
shell> mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表。输出包括默认值并且看上去象这样：
help TRUE
abort-slave-event-count 0
allow-suspicious-udfs FALSE
auto-increment-increment 1
auto-increment-offset 1
automatic-sp-privileges TRUE
basedir /home/jon/bin/mysql/
bdb FALSE
bind-address (No default value)
character-set-client-handshake TRUE
character-set-server latin1
character-sets-dir/home/jon/bin/mysql/share/mysql/charsets/
chroot (No default value)
collation-server latin1_swedish_ci
completion-type 0
concurrent-insert 1
console FALSE
datadir /home/jon/bin/mysql/var/
default-character-set latin1
default-collation latin1_swedish_ci
default-time-zone (No default value)
disconnect-slave-event-count 0
enable-locking FALSE
enable-pstack FALSE
engine-condition-pushdown FALSE
external-locking FALSE
gdb FALSE
large-pages FALSE
init-connect (No default value)
init-file (No default value)
init-slave(No default value)
innodb TRUE
innodb_checksums TRUE
innodb_data_home_dir (No default value)
innodb_doublewriteTRUE
innodb_fast_shutdown 1
innodb_file_per_table FALSE
innodb_flush_log_at_trx_commit 1
innodb_flush_method (No default value)
innodb_locks_unsafe_for_binlog FALSE
innodb_log_arch_dir (No default value)
innodb_log_group_home_dir (No default value)
innodb_max_dirty_pages_pct90
innodb_max_purge_lag 0
innodb_status_fileFALSE
innodb_table_locksTRUE
innodb_support_xa TRUE
isam FALSE
language /home/jon/bin/mysql/share/mysql/english
local-infile TRUE
log /home/jon/bin/mysql/var/master1.log
log-bin /home/jon/bin/mysql/var/master1
log-bin-index (No default value)
log-bin-trust-routine-creators FALSE
log-error /home/jon/bin/mysql/var/master1.err
log-isam myisam.log
log-queries-not-using-indexes FALSE
log-short-format FALSE
log-slave-updates FALSE
log-slow-admin-statements FALSE
log-slow-queries (No default value)
log-tc tc.log
log-tc-size 24576
log-update(No default value)
log-warnings 1
low-priority-updates FALSE
master-connect-retry 60
master-host (No default value)
master-info-file master.info
master-password (No default value)
master-port 3306
master-retry-count86400
master-sslFALSE
master-ssl-ca (No default value)
master-ssl-capath (No default value)
master-ssl-cert (No default value)
master-ssl-cipher (No default value)
master-ssl-key (No default value)
master-user test
max-binlog-dump-events 0
memlock FALSE
myisam-recover OFF
ndbclusterFALSE
ndb-connectstring (No default value)
ndb-mgmd-host (No default value)
ndb-nodeid0
ndb-autoincrement-prefetch-sz 32
ndb-distibution KEYHASH
ndb-force-send TRUE
ndb_force_send TRUE
ndb-use-exact-count TRUE
ndb_use_exact_count TRUE
ndb-shm FALSE
ndb-optimized-node-selection TRUE
ndb-cache-check-time 0
ndb-index-stat-enable TRUE
ndb-index-stat-cache-entries 32
ndb-index-stat-update-freq20
new FALSE
old-alter-table FALSE
old-passwords FALSE
old-style-user-limits FALSE
pid-file /home/jon/bin/mysql/var/hostname.pid1
port 3306
relay-log (No default value)
relay-log-index (No default value)
relay-log-info-file relay-log.info
replicate-same-server-id FALSE
report-host (No default value)
report-password (No default value)
report-port 3306
report-user (No default value)
rpl-recovery-rank 0
safe-user-create FALSE
secure-auth FALSE
server-id 1
show-slave-auth-info FALSE
skip-grant-tables FALSE
skip-slave-start FALSE
slave-load-tmpdir /tmp/
socket /tmp/mysql.sock
sporadic-binlog-dump-fail FALSE
sql-mode OFF
symbolic-links TRUE
tc-heuristic-recover (No default value)
temp-pool TRUE
timed_mutexes FALSE
tmpdir (No default value)
use-symbolic-linksTRUE
verbose TRUE
warnings 1
back_log 50
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
connect_timeout 5
date_format (No default value)
datetime_format (No default value)
default_week_format 0
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size1000
expire_logs_days 0
flush_time0
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (No default value)
group_concat_max_len 1024
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_concurrency_tickets500
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_mirrored_log_groups1
innodb_open_files 300
innodb_sync_spin_loops 20
innodb_thread_concurrency 20
innodb_commit_concurrency 0
innodb_thread_sleep_delay 10000
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
long_query_time 10
lower_case_table_names 0
max_allowed_packet1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors10
max_connections 100
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_join_size 4294967295
max_length_for_sort_data 1024
max_relay_log_size0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
multi_range_count 256
myisam_block_size 1024
myisam_data_pointer_size 6
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
open_files_limit 0
optimizer_prune_level 1
optimizer_search_depth 62
preload_buffer_size 32768
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type 1
query_cache_wlock_invalidate FALSE
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only FALSE
read_rnd_buffer_size 262144
div_precision_increment 4
record_buffer 131072
relay_log_purge TRUE
relay_log_space_limit 0
slave_compressed_protocol FALSE
slave_net_timeout 3600
slave_transaction_retries 10
slow_launch_time 2
sort_buffer_size 2097144
sync-binlog 0
sync-frm TRUE
sync-replication 0
sync-replication-slave-id 0
sync-replication-timeout 10
table_cache 64
table_lock_wait_timeout 50
thread_cache_size 0
thread_concurrency10
thread_stack 196608
time_format (No default value)
tmp_table_size 33554432
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
updatable_views_with_limit1
wait_timeout 28800
如果有一个mysqld服务器正在运行，通过连接它并执行这个命令，可以看到实际上使用的变量的值：
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标：
mysql>SHOW STATUS；
使用mysqladmin还可以获得系统变量和状态信息：
shell> mysqladmin variables
shell> mysqladmin extended-status
关于所有系统和状态变量的完全描述参见5.3.3节，“服务器系统变量”和5.3.4节，“服务器状态变量”。
MySQL使用完全可以升级的算法，因此通常运行时可以用很少的内存。然而，通常情况若给MySQL更多的内存性能会更好。
当调节MySQL服务器时，要配置的两个最重要的变量是key_buffer_size和table_cache。在试图更改其它变量前你应先确信已经适当地配置了这些变量。
下面的例子显示了部分典型的不同的运行时配置的变量值。
·如果至少有256MB内存和许多表，想要在中等数量的客户时获得最大性能，应使用：
·shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
· --sort_buffer_size=4M --read_buffer_size=1M &
·如果只有128MB内存和少量表，但仍然要进行大量的排序，可以使用：
·shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
如果有许多并行连接，交换问题会发生，除非mysqld已经配置成为每个连接分配很少的内存。如果有足够的内存用于所有连接，mysqld会执行得更好。
·对于少量内存和大量连接，应使用：
·shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
· --read_buffer_size=100K &
或甚至为：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--table_cache=32 --read_buffer_size=8K \
--net_buffer_length=1K &
如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。
如果已经安装了MySQL，support-files目录包含一些不同的my.cnf示例文件：my-huge.cnf、my-大.cnf、my-medium.cnf和my-small.cnf。可以使用这些文件来优化系统。
请注意如果在命令行中为mysqld或mysqld_safe指定一个选项，它只在该次服务器调用中保持有效。要想每次服务器运行时使用该选项，将它放在选项文件中。
要想看参数更改的效果，应执行：
shell> mysqld --key_buffer_size=32M --verbose ---help
变量值列于输出的最后。确保--verbose和---help选项在最后。否则，在命令行中列于它们后面的选项的效果不会反映到输出中。
关于调节InnoDB存储引擎的信息，参见15.2.11节，“InnoDB性能调节提示”。
7.5.3. 控制查询优化器的性能
查询优化器的任务是发现执行SQL查询的最佳方案。因为“好”方案和“坏”方案之间的性能差别会巨大(也就是说，秒相对于小时或甚至天)，大多数查询优化器，包括MySQL的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询，MySQL优化器所调查的可能的方案数随查询中所引用的表的数目呈指数增长。对于小数量的表(典型小于7-10)，这不是一个问题。然而，当提交的查询更大时，查询优化所花的时间会很容易地成为服务器性能的主要瓶颈。
查询优化的一个更加灵活的方法是允许用户控制优化器详尽地搜索最佳查询评估方案。一般思想是优化器调查的方案越少，它编译一个查询所花费的时间越少。另一方面，因为优化器跳过了一些方案，它可能错过一个最佳方案。
优化器关于方案数量评估的行为可以通过两个系统变量来控制：
·optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案，并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而，如果你认为优化器错过了一个更好的查询方案，则该选项可以关闭(optimizer_prune_level=0)，风险是查询编辑花费的时间更长。请注意即使使用该启发，优化器仍然可以探测呈指数数目的方案。
·optimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案，应查看多深，以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如，如果optimizer_search_depth接近于查询中表的数量，对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时，如果用optimizer_search_depth等于3或4编辑，对于同一个查询，编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值，该变量可以设置为0，告诉优化器自动确定该值。
7.5.4. 编译和链接怎样影响MySQL的速度
下列大多数测试是在Linux上并用MySQL基准进行的，但是它们能对其它操作系统和工作负载给出一些指示。
当你用-static链接时，可以得到最快的可执行文件。
在Linux上，最好用pgcc和-O3编译服务器。为了用这些选项编译“sql_yacc.cc”，需要大约200M内存，因为gcc或pgcc需要大量的内存使所有函数嵌入（inline）。在配置MySQL时，也应该设定CXX=gcc以避免包括libstdc++库(它不需要)。请注意对于某些版本的pgcc，生成的二进制只能运行在真Pentium处理器上，即使你使用编译器选项说明你想让最终的代码在所有x586-类处理器上工作(例如AMD)。
只通过使用一个较好的编译器或较好的编译器选项，在应用中能得到10-30%的加速。如果你自己编译SQL服务器，这特别重要！
当我们测试Cygnus CodeFusion或Fujitsu编译器时，二者均还没足够不出错来让MySQL启用优化进行编译。
标准MySQL二进制分发编译为支持所有字符集。当你自己编译MySQL时，应只包括将使用的字符集的支持。通过configure的--with-charset选项来控制。
这里是我们做过的一些测量表：
·如果你使用pgcc并用-O6编译，mysqld服务器比用gcc 2.95.2快11%。
·如果你动态地链接(没有-static)，在Linux中结果慢了13%。注意你仍能在客户应用程序中使用动态链接MySQL库。只有服务器对性能是关键的。
·如果你用strip mysqld剥离mysqld二进制，生成的二进制可以快4%。
·对于在同一主机上运行的客户与服务器之间的连接，如果你使用TCP/IP而非Unix套接字文件进行连接，结果慢7.5%。(在Unix中，如果你连接localhost主机，MySQL默认使用一个套接字文件）。
·对于从客户到服务器的TCP/IP连接，从另一台主机连接一台远程服务器要比连接同一主机上的服务器慢8-11%，即使通过100Mb/s以太网进行连接。
·当使用安全连接运行我们的基准测试时(所有数据用内部SSL支持进行加密)，性能比未加密连接慢55%。
·如果你用--with-debug=full编译，大多数查询慢20%。部分查询时间会很长；例如，MySQL基准的运行要慢35%。如果你使用--with-debug(没有=full)，速度只下降15%。对于用--with-debug=full编译的mysqld版本，可以用--skip-safemalloc选项启动以便在运行时禁用内存检查。执行速度则接近用--with-debug配置的时候。
·在Sun UltraSPARC-Iie上，用Forte 5.0编译的服务器比用gcc 3.2编译的要快4%。
·在Sun UltraSPARC-Iie上，用Forte 5.0编译的32位模式服务器比64位模式服务器要快4%。
·用gcc 2.95.2编译带-mcpu=v8 -Wa的UltraSPARC，使用-xarch=v8plusa选项性能会提高4%。
·在Solaris 2.5.1上，在单个处理器上MIT-pthreads比带原生线程的Solaris慢8-12%。如果有更大的负载/cpus，差别应该更大。
·在Linux-x86上使用gcc编译而不用帧指针(-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp)，可以使mysqld快1-4%。
MySQL AB提供的Linux上的二进制MySQL分发一般用pgcc编译。我们必须返回到常规gcc，因为pgcc中有一个bug，使生成的二进制不能在AMD上运行。我们将继续使用gcc直到该bug被解决。同时，如果你有一个非AMD机，你可以用pgcc编译构建一个更快的二进制。标准MySQL Linux二进制是通过静态链接，以使它更快并且更加易于移植。
7.5.5. MySQL如何使用内存
下面的列表中列出了mysqld服务器使用内存的一些方法。在适用的地方，给出了内存相关的系统变量名：
·键缓存(变量key_buffer_size)被所有线程共享；服务器使用的其它缓存则根据需要分配。参见7.5.2节，“调节服务器参数”。
·每个连接使用具体线程的空间：
o堆栈(默认64KB，变量thread_stack)
o连接缓存区(变量net_buffer_length)
o结果缓存区(变量net_buffer_length)
连接缓存区和结果缓存区可以根据需要动态扩充到max_allowed_packet。当某个查询运行时，也为当前查询字符串分配内存。
·所有线程共享相同的基本内存。
·只有压缩MyISAM表映射到内存。这是因为4GB的32位内存空间不足以容纳大多数大表。当64位地址空间的系统变得越来越普遍后，我们可以增加常规的内存映射支持。
·对表进行顺序扫描的请求将分配一个缓存区(变量read_buffer_size)。
·当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读 缓存区(变量read_rnd_buffer_size)以避免硬盘搜索。
·所有联合在一个令牌内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置mysqld的tmp_table_size选项来增加临时表的大小，或设置客户程序中的SQL选项SQL_BIG_TABLE。参见13.5.3节，“SET语法”。
·进行排序的大多数请求将分配一个排序缓存区，并根据结果集的大小为两个临时文件分配零。参见A.4.4节，“MySQL将临时文件储存在哪里”。
·几乎所有解析和计算在局部内存中完成。小项目不需要内存，因此避免了普通的慢内存分配和释放。只为不期望的大字符串分配内存；使用函数malloc()和free()来完成。
·对于每个打开的MyISAM表，索引文件打开一次；数据文件为每个并行运行的线程打开一次。对于每个并行线程，将分配一个表结构、一个每个列的列结构和大小为3 * N的缓存区(其中N是最大行的长度，而不是计算BLOB列)。一个BLOB列需要5至8个字节加上BLOB数据的长度。MyISAM存储引擎维护一个额外的行缓存区供内部应用。
·对于每个具有BLOB列的表，将对缓存区进行动态扩大以读入大的BLOB值。如果你扫描一个表，则分配一个与最大的BLOB值一样大的缓存区。
·所有使用的表的句柄结构保存在高速缓存中并以FIFO管理。默认情况，高速缓存有64个入口。如果某个表同时被两个运行的线程使用，高速缓存则为该提供两个入口。参见7.4.9节，“MySQL如何打开和关闭表”。
·当并行执行的线程结束时，FLUSH TABLE语句或mysqladmin flush-table命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭。这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。
ps和其它系统状态程序可以报导mysqld使用很多内存。这可以是在不同的内存地址上的线程栈造成的。例如，Solaris版本的ps将栈间未用的内存算作已用的内存。你可以通过用swap -s检查可用交换区来验证它。我们用商业内存漏洞探查器测试了mysqld，因此应该有没有内存漏洞。
7.5.6. MySQL如何使用DNS
当新的客户连接mysqld时，mysqld创建一个新的线程来处理请求。该线程先检查是否主机名在主机名缓存中。如果不在，线程试图解析主机名：
·如果操作系统支持线程安全gethostbyaddr_r ()和gethostbyname_r()调用，线程使用它们来执行主机名解析。
·如果操作系统不支持线程安全调用，线程锁定一个互斥体并调用gethostbyaddr()和gethostbyname()。在这种情况下，在第1个线程解锁互斥体前，没有其它线程可以解析不在主机名缓存中的主机名。
你可以用--skip-name-resolve选项启动mysqld来禁用DNS主机名查找。然而，在这种情况下，你只可以使用MySQL中的授权表中的IP号。
如果你有一个很慢的DNS和许多主机，你可以通过用--skip-name-resolve禁用DNS查找或增加HOST_CACHE_SIZE定义(默认值：128)并重新编译mysqld来提高性能。
你可以用--skip-host-cache选项启动服务器来禁用主机名缓存。要想清除主机名缓存，执行FLUSH HOSTS语句或执行mysqladmin flush-hosts命令。
如果你想要完全禁止TCP/IP连接，用--skip-networking选项启动mysqld。