Wednesday, November 16, 2005

MySQL 5 Optimization and Tuning Guide

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 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:

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 seeks
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)) + 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 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:

9 comments:

Satya Prakash said...

Good tutorial

Anonymous said...

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

Satya Prakash said...

Hello

I just given a link to you.

Anonymous said...

# 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.

anton said...

thakns very much..

S K Pradeep kumar said...

good document, thanks

Wicked Web Programmers said...

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.

Chris said...

thanks for mysql tuning guide.

Anonymous said...

Excellent guide on optimizing your mysql configuration for speed and stability!

thanks!