Optimizing MySQL Database Performance Can be Done by Editing my.cnf File. By default, MySQL Server is configured for very low RAM server. Previously we wrote about how to Fix Sudden Error Establishing a Database Connection in WordPress. Actually, that was not truly only for WordPress, it will work for any CMS. That Fix works nice and is intended as a quick fix. After you are running the Server nicely, you must understand why MySQL Gone Away or Error Establishing a Database Connection are appearing. That is what we will do with Optimizing MySQL Database Performance by tuning its settings file named my.cnf. Also, you must optimize the PHP part in the way we have described in APC Configuration and Optimization for Rackspace Cloud Server (PVHVM).
Optimizing MySQL Database Performance : The Server Setup and Other Parts We Have Considered
We are taking that you are using PVHVM Virtualization Mode. We are assuming that you are running WordPress on a Single Performance Server on PVHVM mode in the way we described in the Full step by step guide to Install WordPress on Ubuntu 13.10 on Rackspace.
Location of my.cnf vary. You should run this command :
1 | mysqld --help --verbose |
It will give you an informative output like :
1 2 3 4 5 | Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf |
---
You should consider – which is Global and Loads first in order. If ~/.my.cnf is Global and order is like above, you must have the same settings (copy one from another, you can use FTP if you feel uncomfortable). As MySQL is very important part of the setup, you must take a full image backup of the server plus backup of WordPress or the software you are running. In case you want to start Editing from /etc/my.cnf ; you must copy it to your computer for urgent and quick rescue.
query_cache_size is the size of cache as MySQL can cache the result set, avoiding the overhead of running through the data over and over.
key_buffer is the size of the buffer used with indexes. With a larger buffer, SQL command will finish faster. We keep one forth in numerical value of the physical RAM of the server for SQL server.
We provided two examples of explanation, you must read the docs to learn about the others.
This is a standard settings for a 2GB server :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [mysqld] local-infile=0 skip-locking query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=128 key_buffer=16M join_buffer=1M max_allowed_packet=16M table_cache=1024 record_buffer=1M sort_buffer_size=2M read_buffer_size=2M max_connect_errors=10 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M server-id=1 [safe_mysqld] err-log=/var/log/mysqld.log open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout |
You should change one by one (or add if does not exist), restart mysql server to check if fronted is going fine (else comment out or change the last thing…simple) and finally restart the whole server ( reboot is the command ) after stopping it :
1 2 3 4 5 | sudo /etc/init.d/mysql stop #or service mysqld stop #or sudo stop mysql |
Obviously, if you replace the word stop with restart or start, the desired easy to understand function will take place.
DO NOT try to optimize MySQL in Rackspace Deployment. They are fully optimized. There is a Perl script for MySQL Tuning, do not use it for PVHVM unless you are a database Guru.
