Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Jun 12, 2009

MySQL: Five Dials to Set

By Sean Hull

MySQL can be installed and up and running in a matter of minutes with RPMs or even quicker with tools like xampp. However, there are so many dials to turn, and options to set that even experienced DBAs can feel overwhelmed.

With that in mind, we'd like to focus your attention on ten areas that will put you on solid footing.

1. Connections

Connections are typically coming from a web server, as many MySQL databases back internet websites. Here are some of the parameters related to connections, and how to set them.

max_connections
This should be at minimum the sum of the maximum number of apache connections allowed from all your web servers. Keep in mind that each connection uses session memory (see below).

max_packet_allowed
The maximum packet size is typically the size of your largest dataset you'll need to return in one chunk. If you're using mysqldump remote, it may need to be larger.

aborted_connects
Check this system status counter, and be sure it is not increasing. If it is, your clients are getting errors connecting.

thread_cache_size
Incoming connections create a new thread in mysql each time they are opened. Since opening and closing connections in mysql is very cheap (resource-wise) and fast, persistent connections aren't as typical as they are with other databases such as Oracle. However, having threads pre-created does save some time, and that's what the mysql thread cache is for.

Keep an eye on threads_created and if it is increasing, make your thread cache bigger. It doesn't cost much in terms of memory to have 25, 50 or 100 thread_cache_size.

2. Query Cache

Caching queries in mysql includes both the parsed query plan, as well as the return dataset. If the underlying table data or structure changes, that will invalidate the entry in the query cache.

query_cache_min_res_unit
Blocks in the query cache are allocated in chunks of this size. Calculate the average size of your queries with the formula below, and set this variable according to that. MySQL will then use query cache memory more efficiently, caching more queries and wasting less memory.

query_cache_size
This sets the total size of the query cache.

query_cache_limit
This tells mysql to discard queries larger than this size. The theory goes that large queries run infrequently, such as a batch job doing a large report, so those results shouldn't clog up with query cache.

qcache hit ratio = qcache_hits / (qcache_hits + com_select)
Find these variables with:

SQL> show status like 'qcache%';
SQL> show status like 'com_%';

average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache
query_cache_size you can get with:

SQL> show variables like 'query%';
qcache_* status variables you can get with:
SQL> show status like 'qcache%';

3. temp tables

Memory is extremely fast, so when it comes to sorting, we want to keep the entire result set in memory while we're working on it. We can do this by tuning queries to make that set smaller, *OR* by setting these variables larger.

tmp_table_size
max_heap_table_size

Whenever you create temp tables in MySQL it will use the minimum of these two variables as the cutoff, beyond which it will build the temp table on disk. Keep in mind that there are many many sessions, all fighting for limited resources, so better to tune queries than just go setting this too high. Also, keep in mind tables with BLOB or TEXT will go straight to disk.

4. session memory

Each session in MySQL will use it's own memory for work it needs to do. This is memory that directly contributes to your SQL queries, so you want to make it large enough to satisfy those needs. However, you have to balance that with the number of concurrent sessions in your database at one time. What makes this a little bit of a black art here is that MySQL allocates these buffers as needed. So you can't just add them up and multiply by the number of sessions. That estimate will be much higher than MySQL typically uses. Best thing to do is to startup MySQL, connect all of your sessions, then keep and eye on the VIRT column of a top session. The number for the mysqld row usually stays pretty steady. That's your real-world total memory usage. Subtract all your static MySQL memory areas and you have the total session memory in the real world. Then divide by the number of sessions to get the average.

read_buffer_size
Caches blocks from sequential scans. This buffer is across storage engines, not just MyISAM tables.

sort_buffer_size
Any session that performs a sort allocates this buffer. Again beware setting it to a large value system-wide. Better to leave it at 1M or 2M, and then set it within a session, to a higher value for a specific query.

join_buffer_size
Any session that performs a join allocates this buffer. Set it to 1M or 2M and then modify it on a per-session basis as needed.

read_rnd_buffer_size
Used for sorting, and ORDER BY operations. You're better off setting this to 1M, and setting it as a session variable for queries that can use a larger value.

5. slow query log

The slow query log is a really useful feature of MySQL. As queries tend to be the often overlooked, yet most important part of a performing database, this is an important facility to have enabled.

log_slow_queries
Set this in the my.cnf file to turn it on. MySQL will default to putting the file in your datadir with a name hostname-slow.log but you can also give it a name when you specify this option.

long_query_time
This defaults to 10 seconds. I suggest leaving this alone. You can set the value dynamically to 1 second to turn it on, and if the database gets restarted, the log will be off by default. As of 5.1.21 and for folks who installed a Google patch, this option can be set in microseconds. This is a REALLY great feature, because once you wipeout all the queries 1 second and over, finer tuning becomes possible. This will help you eliminate problem SQL long before it becomes a bigger problem.

log_queries_not_using_indexes
This option is a good one to enable as well. It actually logs any queries that return all rows.

Conclusion

We've dipped into MySQL looking at a few dials that we can turn to get an initial MySQL vanilla install working for your specific application requirements.

Next month we will finish our discussion of ten dials, touching upon InnoDB buffers, MyISAM buffers, Index, table scans and query tuning, security, and lastly logging in MySQL.

» See All Articles by Columnist Sean Hull



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date