Earlier, I mentioned some
MySQL optimization resources. Here is the guide on
optimizing sysctl.conf. Today, I will be optimizing MySQL 5.0.15 on a Red Hat Enterprise Linux ES release 3 with dual Xeon 2GHz processors and 2GB RAM.
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.cnf
to 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.cnf
Getting 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;
SHOW STATUS;
On shell prompt
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=60
Increase connect_timeout from 5 to 10
connect_timeout=10
Decrease interactive_timeout from 28800 to 100
interactive_timeout=120
Increase join_buffer_size from 131072 to 1M
join_buffer_size=1M
Increase query_cache_size from 0 to 128M
query_cache_size=128M
Increase query cache limit from 1048576 to 2M
query_cache_limit=2M
Increase max allowed packet size from 1M to 16M
max_allowed_packet=16M
Increase table cache cache from 256 to 1024
table_cache=1024
Increase sort buffer size from 1M
sort_buffer_size=2M
Increase read buffer size from 1M
read_buffer_size=2M
Increase read_rnd_buffer_size to 4M
read_rnd_buffer_size=4M
Other settings
sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M
MySQL Query CacheTo maximize use of MySQL query cache, similar queries used by an application should be identical, i.e. their casing must mache. From MySQL:
Queries are compared before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROM tbl_name
Select * from tbl_name
Queries 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.
When is a query not be cached?From
MySQL documentation (Full list of when a MySQL query is not cached by MySQL server):
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 parameter |
FOUND_ROWS() | GET_LOCK() | LAST_INSERT_ID() |
LOAD_FILE() | MASTER_POS_WAIT() | NOW() |
RAND() | RELEASE_LOCK() | SYSDATE() |
UNIX_TIMESTAMP() with no parameters | USER() |
|
How can you "tell" MySQL server to cache a query?
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 like
SELECT SQL_CACHE ... ;
In contrast, tell MySQL 5 server not to cache your query by using SQL_NO_CACHE in your SQL statements.
SELECT SQL_NO_CACHE ... ;
Please note that a user on dev.mysql.com pointed out the following,
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.
Another user,
Uwe, pointed out the following
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.
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)
Estimating the performance by counting disk seeksAccording 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)) + 1
Note that in MySQL, an index block is normally 1K (1024 bytes) . The data pointer is usually 4 bytes. According to MySQL,
For a 500,000-row table with an index length of 3 bytes (medium integer), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks. 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.
For more see
MySQL optimization.
Other NotesBe 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 OptimizationCustom Benchmarks,
Super-Smack (can bring a system to its knees)MySQL and 50GB of transaction tables and 200GB of other customer dataStorage Engines and Table TypesMySQL OptimizationMySQL Query CacheMySQL 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
