Every web developer knows that in these days almost every dynamic web site uses some kind of database to store data. An application generating (X)HTML code sends queries for data to database and database returns this data back to the caller – in this case the application. Also, data from users or content in itself is stored in a database. The most widespread database engine, used primarily for web application is MySQL. Yes, I’m sure you all know this…
I wanna know more!
But maybe some of you would like to know something more about optimization of MySQL to improve its performance and speed up dynamics of whole page generation. I plan to write a mini series about MySQL optimization and this first article is something like an intro to the topic, some start point which we move from. And since performance depends on configuration, this post guides you through configuration directives, parameters or commands which have influence to optimized MySQL run.
The list of all MySQL command options and variables is available in MySQL Reference Manual and I can’t see a reason to overwrite it here, into this post. Also, the information how to use options, whether place them into the file or put them on command line is clearly explained at the same place. What I suppose to write here is a bunch of information extracted from this source with focus on the optimization, add some lightly explaining words and present it to you as an integral knowledge. So, let’s move forward to the MySQL optimization magics!
Note: All this stuff concerning MySQL optimization is related to version 5.0, which I currently use.
Configuration Directives
back_log (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 50
Well tuned server is capable work simultaneously with many connections at the same time. Every connection must be delegated from the main thread of MySQL and get its own new thread. The back_log parameter determines the number of connections allowed in the stack when the main thread handles requests for new connection.
datadir (filename) | manual
Cmd-line: Yes | Option file: Yes | Default: /var/db/mysql/
It is common practice to have MySQL data separated on other partition or disc. Using this option we can set the path to directory where MySQL data will be stored. Keep in mind that you must move or copy the MySQL privilege tables to selected location too!
default-storage-engine (string) | manual
Cmd-line: Yes | Option file: Yes | Default: MyISAM
MySQL supports several types of engines (tables) from which everyone has some advantages. Based on your needs, you should choose the right one for your data. If you use very often some type of engine, you can set this as a default by this option.
init-file (filename) | manual
Cmd-line: Yes | Option file: Yes | Default: not set
There’s possibility to execute a series of commands at MySQL start-up by placing them into the file which name is set as a value of this option. For instance, we need to truncate some table when MySQL starts. So, put this command into the file mysqlinitcmds.sql and set option init-file="/path/to/the/file/mysqlinitcmds.sql".
key_buffer_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 8388608
This option determines amount of memory (buffer size) allocated for index blocks. The bigger buffer the more index blocks in there and the higher speed of index lookups (because of less need to read from disc).
log-slow-queries (boolean) | manual
Cmd-line: Yes | Option file: Yes | Default: false
This option defines the file (optional parameter) where all queries which execution exceeded the long_query_time are logged. If optional parameter filename is omitted, slow queries are inserted into the slow_log table of mysql database. By exploring the slow_log table or mysqldumpslow output you can get an overview of your database server bottlenecks. There will be separate post dedicated to slow queries written on this blog.
max_allowed_packet (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 1048576
In the world of MySQL, a packet is equivalent to a single SQL statement or a row returned to the caller. The max_allowed_packet can have a value equal to the server RAM. You probably will not concern this option unless storing extra large columns, such as BLOBs.
max_connections (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 100
This parameter determines maximum number of simultaneous database connections. You can check current database connections by reviewing the max_used_connections parameter in the SHOW STATUS output. If you see the number close to 100, you should consider bumping the maximum upward, but keep in mind that more allowed connections results to more memory consumption, because MySQL allocates additional memory to every opened connection.
net_buffer_length (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 16384
This parameter determines the initial buffer allocation for each client-server connection.
read_buffer_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 131072
This parameter determines the amount of memory available for any thread which sequentialy scans a table.
skip-name-resolve (none) | manual
Cmd-line: Yes | Option file: Yes | Default: disabled
If enabled, MySQL will not resolve hostnames, what means that all values in the Host column of privilege table must be an IP addresses or localhost. Resolving hostnames takes some time, so if you are OK with IP use only, enable this parameter by writing skip-name-resolve on separate line in the config file (most probably my.cnf).
skip-networking (none) | manual
Cmd-line: Yes | Option file: Yes | Default: disabled
If enabled, MySQL will not listen to TCP/IP connections. This is reasonable in cases when MySQL installation resides on the same server from which connections are iniciated.
thread_cache_size (numeric) | manual
Cmd-line: Yes | Option file: Yes | Default: 0
This option determines the number of threads allowed to be stored in the cache for reuse. When new connection is initiated, MySQL first looks into the cache and if there’s available any thread, MySQL use it. If the cache is empty, MySQL creates new thread. So, if you deployed your distribution at the place where many connections is required, you should raise the value of this option from default zero.
Summary
Finally, I’d like to say that optimization is not one-time matter, but a process. Check of the stated above configuration parameters (options) is just first step to your MySQL performance pick-up. In the next parts of this MySQL optimization mini series we will go through another possibilities how to get more power from MySQL. Stay tuned!