You can determine the default buffer sizes used by the mysqld server using this command:
shell> mysqld --verbose --help
This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:
abort-slave-event-count 0 allow-suspicious-udfs FALSE auto-increment-increment 1 auto-increment-offset 1 automatic-sp-privileges TRUE back_log 50 basedir /home/jon/bin/mysql-5.5/ bind-address (No default value) binlog-row-event-max-size 1024 binlog_cache_size 32768 binlog_format (No default value) bulk_insert_buffer_size 8388608 character-set-client-handshake TRUE character-set-filesystem binary character-set-server latin1 character-sets-dir /home/jon/bin/mysql-5.5/share/mysql/charsets/ chroot (No default value) collation-server latin1_swedish_ci completion-type 0 concurrent-insert 1 connect_timeout 10 console FALSE datadir . datetime_format %Y-%m-%d %H:%i:%s date_format %Y-%m-%d default-character-set latin1 default-collation latin1_swedish_ci default-storage-engine MyISAM default-time-zone (No default value) default_week_format 0 delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 disconnect-slave-event-count 0 div_precision_increment 4 engine-condition-pushdown TRUE expire_logs_days 0 external-locking FALSE flush_time 0 ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (No default value) gdb FALSE general_log FALSE general_log_file (No default value) group_concat_max_len 1024 help TRUE init-connect (No default value) init-file (No default value) init-slave (No default value) innodb TRUE innodb-adaptive-hash-index TRUE innodb-additional-mem-pool-size 1048576 innodb-autoextend-increment 8 innodb-autoinc-lock-mode 1 innodb-buffer-pool-size 8388608 innodb-checksums TRUE innodb-commit-concurrency 0 innodb-concurrency-tickets 500 innodb-data-file-path (No default value) innodb-data-home-dir (No default value) innodb-doublewrite TRUE innodb-fast-shutdown 1 innodb-file-io-threads 4 innodb-file-per-table FALSE innodb-flush-log-at-trx-commit 1 innodb-flush-method (No default value) innodb-force-recovery 0 innodb-lock-wait-timeout 50 innodb-locks-unsafe-for-binlog FALSE innodb-log-buffer-size 1048576 innodb-log-file-size 5242880 innodb-log-files-in-group 2 innodb-log-group-home-dir (No default value) innodb-max-dirty-pages-pct 90 innodb-max-purge-lag 0 innodb-mirrored-log-groups 1 innodb-open-files 300 innodb-rollback-on-timeout FALSE innodb-stats-on-metadata TRUE innodb-status-file FALSE innodb-support-xa TRUE innodb-sync-spin-loops 20 innodb-table-locks TRUE innodb-thread-concurrency 8 innodb-thread-sleep-delay 10000 interactive_timeout 28800 join_buffer_size 131072 keep_files_on_create FALSE key_buffer_size 8384512 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /home/jon/bin/mysql-5.5/share/mysql/english/ large-pages FALSE lc-time-names en_US local-infile TRUE log (No default value) log-bin (No default value) log-bin-index (No default value) log-bin-trust-function-creators FALSE log-error log-isam myisam.log log-output FILE log-queries-not-using-indexes FALSE log-short-format FALSE log-slave-updates FALSE log-slow-admin-statements FALSE log-slow-slave-statements FALSE log-tc tc.log log-tc-size 24576 log-warnings 1 log_slow_queries (No default value) long_query_time 10 low-priority-updates FALSE lower_case_table_names 0 master-retry-count 86400 max-binlog-dump-events 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_connections 151 max_connect_errors 10 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 memlock FALSE min_examined_row_limit 0 multi_range_count 256 myisam-recover-options OFF myisam_block_size 1024 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal myisam_use_mmap FALSE ndb-autoincrement-prefetch-sz 1 ndb-cache-check-time 0 ndb-connectstring (No default value) ndb-extra-logging 0 ndb-force-send TRUE ndb-index-stat-enable FALSE ndb-mgmd-host (No default value) ndb-nodeid 0 ndb-optimized-node-selection TRUE ndb-report-thresh-binlog-epoch-slip 3 ndb-report-thresh-binlog-mem-usage 10 ndb-shm FALSE ndb-use-copying-alter-table FALSE ndb-use-exact-count TRUE ndb-use-transactions TRUE ndb_force_send TRUE ndb_use_exact_count TRUE ndb_use_transactions TRUE net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new FALSE old FALSE old-alter-table FALSE old-passwords FALSE old-style-user-limits FALSE open_files_limit 1024 optimizer_prune_level 1 optimizer_search_depth 62 pid-file /home/jon/bin/mysql-5.5/var/tonfisk.pid plugin-load (No default value) plugin_dir /home/jon/bin/mysql-5.5/lib/mysql/plugin port 3306 port-open-timeout 0 preload_buffer_size 32768 profiling_history_size 15 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 4096 read_buffer_size 131072 read_only FALSE read_rnd_buffer_size 262144 relay-log (No default value) relay-log-index (No default value) relay-log-info-file relay-log.info relay_log_purge TRUE relay_log_space_limit 0 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 secure-file-priv (No default value) server-id 0 show-slave-auth-info FALSE skip-grant-tables FALSE skip-slave-start FALSE slave-exec-mode STRICT slave-load-tmpdir /tmp slave_compressed_protocol FALSE slave_net_timeout 3600 slave_transaction_retries 10 slow-query-log FALSE slow_launch_time 2 slow_query_log_file (No default value) socket /tmp/mysql.sock sort_buffer_size 2097144 sporadic-binlog-dump-fail FALSE sql-mode OFF symbolic-links TRUE sync-binlog 0 sync-frm TRUE sysdate-is-now FALSE table_definition_cache 256 table_open_cache 400 tc-heuristic-recover (No default value) temp-pool TRUE thread_cache_size 0 thread_concurrency 10 thread_stack 262144 timed_mutexes FALSE time_format %H:%i:%s tmpdir (No default value) tmp_table_size 16777216 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 updatable_views_with_limit 1 verbose TRUE wait_timeout 28800
For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement:
mysql> SHOW VARIABLES;
You can also see some statistical and status indicators for a running server by issuing this statement:
mysql> SHOW STATUS;
System variable and status information also can be obtained using mysqladmin:
shell>mysqladmin variablesshell>mysqladmin extended-status
For a full description of all system and status variables, see Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.
MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally you get better performance by giving MySQL more memory.
When tuning a MySQL server, the two most important variables to
configure are key_buffer_size
and table_open_cache. You
should first feel confident that you have these set
appropriately before trying to change any other variables.
The following examples indicate some typical variable values for different runtime configurations.
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:
shell>
mysqld_safe --key_buffer_size=64M --table_open_cache=256 \--sort_buffer_size=4M --read_buffer_size=1M &If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
shell>
mysqld_safe --key_buffer_size=16M --sort_buffer_size=1MIf there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.
With little memory and lots of connections, use something like this:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \--read_buffer_size=100K &Or even this:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \--table_open_cache=32 --read_buffer_size=8K \--net_buffer_length=1K &
If you are performing GROUP BY or
ORDER BY operations on tables that are much
larger than your available memory, increase the value of
read_rnd_buffer_size to speed
up the reading of rows following sorting operations.
You can make use of the example option files included with your MySQL distribution; see Section 4.2.3.3.2, “Preconfigured Option Files”.
If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.
To see the effects of a parameter change, do something like this:
shell> mysqld --key_buffer_size=32M --verbose --help
The variable values are listed near the end of the output. Make
sure that the --verbose and
--help options are last.
Otherwise, the effect of any options listed after them on the
command line are not reflected in the output.
For information on tuning the InnoDB storage
engine, see Section 13.6.14.1, “InnoDB Performance Tuning Tips”.