mysql5.1 优化_7. 优化 - [ MySQL 5.1参考手册 ] - 在线原生手册 - php中文网

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_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性能调节提示”。