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
