howto: Optimizing MySQL

[Solidus]

Welcome! In this thread I'll be teaching you how to optimize MySQL for maximum website performance.
This tutorial is for servers with 512mb - 1GB of RAM.
Before we start, lets check the following,
I have a VPS, dedicated server, or access to the my.cnf file. Y/N?
If yes, lets continue.

Right, lets start by explaining the variables.

thread_cache_size
This variable determines how many threads MySQL will keep open in memory to handle new connections. A value of zero is not recommended, this means MySQL will always create new threads.

table_cache
When MySQL accesses a table, it places it in the cache so data can be retrieved faster.
You may need to increase the value if opened_tables number is high. You can find this number by typing this from SSH,

Code:

SHOW STATUS LIKE "open%tables%";

max_connections
Self explanatory. This defines the maximum number of connections allowed to MySQL. You may need to increase this, but not so high that MySQL can max out RAM usage.

join_buffer_size
A join buffer is allocated for each full join between tables. You should increase this value for a fast full join when adding indexes isn't an option.

sort_buffer_size
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.

interactive_timeout
This value will determine the amount of seconds during inactivity that MySQL will wait before closing an interactive connection such as mysql shell.

wait_timeout
Same definition as above, but for non-interactive sessions. This value shouldn't be too low otherwise it will drop connections unexpectedly. Nor should it be too high otherwise bad connections will remain open, preventing new connections to the database.

connect_timeout
The amount of seconds MySQL will wait before dropping a connection. A good value it between 10 and 60.

max_allowed_packet
This value will determine the maximum packet size allowed to be received. If this is too low, you might see errors. A good value is 20M.

max_connect_errors
The value determines how many interrupted connections can occur. Surpassing this value will result in the host being blocked from further connections. You can unblock hosts by using the FLUSH_TABLES statement.

key_buffer_size
This is the most useful variable to tweak. Getting a perfect value is important. The larger this value is, the more of your MyISAM table indexes will be stored in memory. This value should be at least a quarter, but no more than a half of your maximum available memory. For example, a server with 1GB of ram should have a key buffer size of around 300MB.

query_cache_size
If your database runs the same queries repeatedly, this variable is very useful. MySQL will cache the result set, avoiding the overhead of through data over and over again.

query_cache_limit
This is the maximum query size that will be cached.

tmp_table_size
This is the number of implicit temporary tables on disk created while executing statements. This is memory based.
tmp_table_size is useless without the max_heap_table_size, which is explained next.

max_heap_table_size
Your application may use HEAP tables, HEAP tables are stored in memory and if stale sessions aren't cleaned, you may begin to see errors. It is recommended that this value is the same as tmp_table_size.

----------------------------
That's the variables briefly explained to the best of my knowledge, some may not be 100% accurate though.

Now, here is the my.cnf I configured myself for my server.

Code:

thread_cache_size= 20
table_cache= 3000
max_connections= 200
myisam_sort_buffer_size= 16M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 4M
interactive_timeout = 25
wait_timeout = 300
connect_timeout = 10
max_allowed_packet = 20M
max_connect_errors = 10
key_buffer_size = 450M
query_cache_type = 1
query_cache_size = 40M
query_cache_limit = 5M
tmp_table_size = 6M
max_heap_table_size = 8M

This should be a good base for you to begin optimizing from.
I recommend installing MySQL Tuner. Here's how,
1: From SSH, type,

Code:

wget mysqltuner.pl

2: Then type,

Code:

chmod 775 mysqltuner.pl

3: You can now run the script by typing,

Code:

./mysqltuner.pl

OR

Code:

perl mysqltuner.pl

It will take a few seconds, and will give you alerts and suggestions about your MySQL configuration. If you need help with this, post your results here and I'll assist.

Any questions, just ask.
I'll continue to add to this with more optimizations, there is lots more to do, this is just the beginning.
This has been tested on MySQL 5.1 and MySQL 5.5, whilst running a MyBB Forum, a Wordpress blog and an image host.
Written exclusively for SupportForums.net
Thanks for reading.

[Deltron]

Thanks for putting the time forward to create this for us. I'm sure many members will find this beneficial.

[Peter L]

Nice tutorial to use for bigger boards. Do you advise against using your config?

[Solidus]

Not at all. There's nothing wrong with it. I do highly recommend running mysqltuner though, using mine wont be suitable for everyone.

[mycroft]

Is the mysqltuner there located in any part of the server?

Code:

wget mysqltuner.pl

[Unreal]

thanks for this tut im building my site atm

[Solidus]

Is the mysqltuner there located in any part of the server?

Code:

wget mysqltuner.pl

I'm not sure what you mean. If that doesn't work, try these,

Code:

yum install mysqltuner

^if you use CentOS/Fedora ect.

Code:

wget  http://mysqltuner.com/mysqltuner.pl

If you install using yum, you need to run the script without the ./

Code:

$ mysqltuner