- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.5 Optimizing the MySQL Server
- 7.5.1 System Factors and Startup Parameter Tuning
- 7.5.2 Tuning Server Parameters
- 7.5.3 Controlling Query Optimizer Performance
- 7.5.4 The MySQL Query Cache
- 7.5.5 Examining Thread Information
- 7.5.6 How Compiling and Linking Affects the Speed of MySQL
- 7.5.7 How MySQL Uses Threads for Client Connections
- 7.5.8 How MySQL Uses Memory
- 7.5.9 How MySQL Uses Internal Temporary Tables
- 7.5.10 How MySQL Uses DNS
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:
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/mysql/ bind-address (No default value) character-set-client-handshake TRUE character-set-server latin1 character-sets-dir /home/mysql/share/mysql/charsets/ chroot (No default value) collation-server latin1_swedish_ci completion-type 0 concurrent-insert 1 console FALSE datadir /home/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 TRUE 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_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/mysql/share/mysql/english local-infile TRUE log /home/mysql/var/master1.log log-bin /home/mysql/var/master1 log-bin-index (No default value) log-bin-trust-routine-creators FALSE log-error /home/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/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_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_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_open_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
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.3, “System Variables”, and Section 5.1.6, “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, you should 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, you should 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.2.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.5.11, “InnoDB Performance Tuning Tips”.
MySQL Enterprise. For expert advice on tuning system parameters subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
In Windows if you want to turn off lower_case_table_names option then in my.ini file add the line
set-variable=lower_case_table_names=0
if you put line like lower_case_table_names=OFF it fails in prior versions to 4.0.6. To avoid table name problems, best is to always have a unique naming convention and keep this auto lower case option OFF, so when you deploy applications in other system, it wont create any problems.
You should mention more about setting the max_allowed_packet server/client side requirements, problems with compression and about the loose- prefix for "all?" variables?
http://bugs.mysql.com/bug.php?id=1011
Please incorporate good explanation from Monty.
[client]
loose-max_allowed_packet=1G
[mysqld]
loose-max_allowed_packet=1G
options.
Please add specific information on how to maximize buffer settings on servers with excess memory (> 2GB)
Watch out for the remark above: "For variable names that end in _size, you may need to specify them without _size."
However, the configuration files that come with MySQL (my-huge.cnf) etc distribution have parameter names that are not in accordance with the above:
For the MySQL daemon:
"key_buffer" instead of "key_buffer_size"
For the isamcheck/mysisamcheck:
"key_buffer" instead of "key_buffer_size"
"sort_buffer_size" instead of "sort_buffer"
"read_buffer" instead of "read_buffer_size"
"write_buffer" instead of "write_buffer_size"
mysql-3.23.58 shipped with RedHat FC3 hides the "mysqld" executable behind the safe_mysqld script. To get a list of
the current settings, use
/usr/libexec/mysqld --verbose --help
At a VPSV2 (Verio Virtual Private Server Version 2)
FreeBSD port: mysql-server-4.1.21 (default MySQL install)
The configuration file is "/etc/my.cnf ", you can modify parameters there.
It has the max_connections parameter set to 30 by default.
I set:
max_connections=50
wait_timeout=900
And restarted the server:
# mysqladmin shutdown –p
# safe_mysqld &
Add your own comment.