可以用這個命令得到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性能調節提示”。