MySQL: Five Dials to Set

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

“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).

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

“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.”

Complete Story

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends, & analysis