- 7.5 Optimizing the MySQL Server
- 7.5.1 How Compiling and Linking Affects the Speed of MySQL
- 7.5.2 System Factors and Startup Parameter Tuning
- 7.5.3 Tuning Server Parameters
- 7.5.4 Controlling Query Optimizer Performance
- 7.5.5 The MySQL Query Cache
- 7.5.6 Examining Thread Information
- 7.5.7 How MySQL Uses Threads for Client Connections
- 7.5.8 How MySQL Uses Memory
- 7.5.9 Enabling Large Page Support
- 7.5.10 How MySQL Uses Internal Temporary Tables
- 7.5.11 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/jon/bin/mysql-5.0/ bdb FALSE bind-address (No default value) character-set-client-handshake TRUE character-set-filesystem binary character-set-server latin1 character-sets-dir /home/jon/bin/mysql-5.0/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-5.0/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 federated TRUE 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_adaptive_hash_index TRUE 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_rollback_on_timeout FALSE innodb_status_file FALSE innodb_support_xa TRUE innodb_table_locks TRUE isam FALSE language /home/jon/bin/mysql-5.0/share/mysql/english/ 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-bin-trust-routine-creators FALSE log-error 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 merge TRUE myisam-recover OFF ndbcluster FALSE new FALSE old-passwords FALSE old-style-user-limits FALSE pid-file /home/jon/bin/mysql-5.0/var/tonfisk.pid port 3306 port-open-timeout 0 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 secure-file-priv (No default value) server-id 0 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 sysdate-is-now FALSE 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 10 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 div_precision_increment 4 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_commit_concurrency 0 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 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 long_query_time 10 lower_case_table_names 0 max_allowed_packet 1048576 max_binlog_cache_size 18446744073709547520 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 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 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 9223372036853727232 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 plugin_dir 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 4096 read_buffer_size 131072 read_only FALSE read_rnd_buffer_size 262144 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 table_cache 64 table_lock_wait_timeout 50 thread_cache_size 0 thread_concurrency 10 thread_stack 262144 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, “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_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_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_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.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.2.13.1, “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
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)
Add your own comment.