The day has come when you have a VPS or even your own dedicated server. The problem is that a poorly configured server can hamstring your efforts at running your website.
Poor MySQL configuration can result in larger load times and more processing time for your MySQL queries.Use this guide as a starting point to configure and optimize MySQL for your server. This guide is assuming you are using WHM/cPanel.
What You Will Need
This guide will assume that you have Root SSH access to your server.
This will involve a few commands that you may not recognize. Don’t worry! We’ve got your back.
Optimizing MySQL
Log in with Root SSH.
Once you are in there, you will type in the following:
vi /etc/my.cnf
This will open up a VI editor. In case you are not familiar with the interface, here is a quick glossary:
- Insert: By default, you have to be in “insert” mode to type anything in. Press I to activate insert mode, press ESC to leave it.
- To exit without saving, type :q! anytime.
- To exit with saving, type :wq
What we are going to do is type in some new configurations.
[mysqld]
max_connections = 300
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 12M
query_cache_type = 1
tmp_table_size = 16M
skip-innodb
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
That’s it! This is not a true, “One size fits all” kind of thing, but this is a great first step. Keep an eye on your server for a few days/weeks after implementing this and see where it needs improvement. For instance, often MySQL heavy websites (e-commerce sites) can have a higher value of query_cache_size (we said 12M. This can go up to 24M, 36M, or sometimes even 64M depending on your situation).
When you run your own dedicated server/VPS, you have to make sure things are set up to run efficiently. Often times your sites will start to run slowly, time out unexpectedly, and otherwise suffer from performance issues. These settings will help combat those problems.