第7章:优化 / 7.5. 优化MySQL服务器 / 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_doublewrite                TRUE
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_pct        90
innodb_max_purge_lag              0
innodb_status_file                FALSE
innodb_table_locks                TRUE
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-count                86400
master-ssl                        FALSE
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
ndbcluster                        FALSE
ndb-connectstring                 (No default value)
ndb-mgmd-host                     (No default value)
ndb-nodeid                        0
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-freq        20
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-links                TRUE
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_size                1000
expire_logs_days                  0
flush_time                        0
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_tickets        500
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_groups        1
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_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connect_errors                10
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_size                0
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_concurrency                10
thread_stack                      196608
time_format                       (No default value)
tmp_table_size                    33554432
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
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_sizetable_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 BYORDER BY操作,应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。

如果已经安装了MySQLsupport-files目录包含一些不同的my.cnf示例文件:my-huge.cnfmy-.cnfmy-medium.cnfmy-small.cnf。可以使用这些文件来优化系统。

请注意如果在命令行中为mysqldmysqld_safe指定一个选项,它只在该次服务器调用中保持有效。要想每次服务器运行时使用该选项,将它放在选项文件中。

要想看参数更改的效果,应执行:

shell> mysqld --key_buffer_size=32M --verbose ---help

变量值列于输出的最后。确保--verbose---help选项在最后。否则,在命令行中列于它们后面的选项的效果不会反映到输出中。

关于调节InnoDB存储引擎的信息,参见15.2.11节,“InnoDB性能调节提示”