Note: Please use this work-in-progress guide for reference only and at your own risk. I am creating this guide on a development server and will not be responsible if anything bad happens. If you find something is incorrect or can be made better please let me know by adding a comment to my blog (mysqldatabaseadministration)
my.cnf: If you have installed or upgraded to MySQL 5, chances are that you may not find the /etc/my.conf file. Don't worry as there are several sample configuration files that come with MySQL. You can invoke
locate my-large.cnfto find the sample configuration file for MySQL 5 on your server. By default, on Red Hat Enterprise, it can be located at the following location.
/usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf
Setting MySQL 5 global options:
To set global MySQL5 options, we will need to copy my-large.cnf to /etc/my.cnf
Setting MySQL 5 server-specific options:
By default, MySQL5 data directory is /var/lib/mysql. To set server-specific options for MySQL5, we can move the sample MySQL configuration file to our data directory.
For our MySQL5 optimization and tuning tutorial, we will be setting global MySQL5 configurations by copying the my.cnf file to /etc/ directory. In case, you are waiting for me to give you the command:
cp -p /usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf /etc/my.cnfGetting information about current values:
We can obtain information about the currently set values by running the following command. Abbreviated output is displayed for reference.
# mysqladmin variables -uroot -p"PASS"Output from a development server
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| 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 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| 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_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| 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_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| 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 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | server.net.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | CST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.15-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------+
Other commands to get MySQL status information, when connected to a MySQL server
SHOW VARIABLES;On shell prompt
SHOW STATUS;
mysqladmin variables
mysqladmin extended-status
Decrease the value of connect_timeout: By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours), please decrease its value according to your application needs. On the test server, I have the wait_timeout set to 60.
Open the file /etc/my.cnf and under [mysqld], place a line similar to following
wait_timeout=60Increase connect_timeout from 5 to 10
connect_timeout=10Decrease interactive_timeout from 28800 to 100
interactive_timeout=120Increase join_buffer_size from 131072 to 1M
join_buffer_size=1MIncrease query_cache_size from 0 to 128M
query_cache_size=128MIncrease query cache limit from 1048576 to 2M
query_cache_limit=2MIncrease max allowed packet size from 1M to 16M
max_allowed_packet=16MIncrease table cache cache from 256 to 1024
table_cache=1024Increase sort buffer size from 1M
sort_buffer_size=2MIncrease read buffer size from 1M
read_buffer_size=2MIncrease read_rnd_buffer_size to 4M
read_rnd_buffer_size=4MOther settings
sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M
MySQL Query Cache
To maximize use of MySQL query cache, similar queries used by an application should be identical, i.e. their casing must mache. From MySQL:
When is a query not be cached?Queries are compared before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROMtbl_name
Select * fromtbl_nameQueries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.
From MySQL documentation (Full list of when a MySQL query is not cached by MySQL server):
How can you "tell" MySQL server to cache a query?A query cannot be cached if it contains any of the following functions:
BENCHMARK()CONNECTION_ID()CURDATE()CURRENT_DATE()CURRENT_TIME()CURRENT_TIMESTAMP()CURTIME()DATABASE()ENCRYPT()with one parameterFOUND_ROWS()GET_LOCK()LAST_INSERT_ID()LOAD_FILE()MASTER_POS_WAIT()NOW()RAND()RELEASE_LOCK()SYSDATE()UNIX_TIMESTAMP()with no parametersUSER()
If
query_cache_type system variable on your MySQL server is set to ON or DEMAND, you can specify SQL_CACHE in your query to tell MySQL server explicity to use query cache for this query. An example will look something likeAnother user,SELECT SQL_CACHE ... ;
In contrast, tell MySQL 5 server not to cache your query by usingSQL_NO_CACHEin your SQL statements.Please note that a user on dev.mysql.com pointed out the following,SELECT SQL_NO_CACHE ... ;Using SQL_NO_CACHE does not bypass the cache mechanism, so it is not a
reliable way to time query speed. All it does is tell the server not to
cache the results of the specific query you're sending.
If you've already executed the query without the option, some other time, that cached result will be fetched.
To completely disable reading (AND writing) from/to cache use a comment at the beginning of the query. MySQL's ignoring the query cache if the query doesn't start with "select" (at least up to 4.1).
/* disable qcache */ SELECT * FROM tbl WHERE 1
This is very useful to test query performance in a running environment as you can't be sure that the query's not already in the cache.
How to optimize disk seek time on your MySQL 5 server?
To optimize disk seek time on your server, you can try distributing the data onto more than one hard disk drive.
Using BENCHMARK() function (Output shows 10000000 simple additions can be performed in 0.93 seconds)
Welcome to the MySQL monitor. Commands end with ; or \g.Estimating the performance by counting disk seeks
Your MySQL connection id is 558 to server version: 5.0.15-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.07 sec)
mysql> SELECT BENCHMARK(10000000,1+1);
+-------------------------+
| BENCHMARK(10000000,1+1) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.93 sec)
According to MySQL, using B-tree indexes, you need this many seeks to find a row:
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1For a 500,000-row table with an index length of 3 bytes (medium integer), the formula indicatesFor more see MySQL optimization.log(500,000)/log(1024/3*2/(3+4)) + 1=4seeks. This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.
Other Notes
Be aware that in case your server's CPU needs more data than can fit in the CPU cache, main memory bandwidth can become a bottleneck.
You can use crash-me to "find functions, types, and limits that you can use with a selection of database servers. crash-me does not check for every possible feature, but it is still reasonably comprehensive, performing about 450 tests." - MySQL
more complex MySQL permissions setup = more overhead
Resources:
Index Merge Optimization
Custom Benchmarks, Super-Smack (can bring a system to its knees)
MySQL and 50GB of transaction tables and 200GB of other customer data
Storage Engines and Table Types
MySQL Optimization
MySQL Query Cache
MySQL in batch mode, Foreign Keys, Searching on Two Keys, MySQL Program Options,
MySQL Instance Manager (Connecting to MySQL Instance Manager and Creating User Accounts) - serves for monitoring and management of MySQL Database Server instances
The typical startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:
* The MySQL Instance Manager is started with /etc/init.d/mysql script.
* The MySQL Instance Manager starts all instances and monitors them.
* If a server instance fails the MySQL Instance Manager restarts it.
* If the MySQL Instance Manager is shut down (for instance with the /etc/init.d/mysql stop command), all instances are shut down by the MySQL Instance Manager.
MySQL Installation Layouts
Side note: Google recently relaunched Urchin web analytics software as Google Analytics for advertisers and publishers. Details on Search Marketing Ambassador and here. Also, if you are a webmaster in addition to being a MySQL DBA, you can get $50 in credit from Yahoo! Search Marketing for sponsored search. Also see information on Google and Yahoo! sitemaps
Tags: mysql mysql5 database tuning optimization dba
9 comments:
Good tutorial
Thanks for taking the time to list this information. I'm trying to increase the size of my max_join_size to 18446744073709551615 but everything I've read and tried doesn't appear to be working. I'm sure it's me...
I added the following to my.cnf [mysqld] section and restarted mysql:
max_join_size=18446744073709551615
but mysqladmin variables returns this:
max_join_size 4294967295
Any ideas?
Thank you,
Jim
Hello
I just given a link to you.
# mysqladmin variables -uroot -p"PASS"
Better use -p without the password. Then you will get asked for it. Else You will have the password in the process list and in the command history.
thakns very much..
good document, thanks
Thanks for sharing. MySQL also provides many extentions to SQL which help performance in many common use scenarios such as Insert,Select, On duplicate key update and Replace.
thanks for mysql tuning guide.
Excellent guide on optimizing your mysql configuration for speed and stability!
thanks!
Post a Comment