I’m a bit confused about which settings to change in my my.conf file to optimise for my server (the mysql server keeps crashing due to our high traffic).
Here’s the my.cnf file:
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking key_buffer = 16M max_allowed_packet = 16M myisam-recover = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 query_cache_limit = 1M query_cache_size = 16M
And the server specs are:
CPU Cores 2 cores RAM (Memory) 1GB SSD (Disk Space) 20GB
Running Ubuntu 12.04 LTS
Here are my partitions:
NAME FSTYPE SIZE MOUNTPOINT LABEL vda 20G ├─vda1 ext4 476M /boot ├─vda2 swap 477M [SWAP] └─vda3 ext4 19.1G /
MySQL Tuner is a useful tool that will connect to a running MySQL instance and offer suggestions for how it can be best configured for your workload. The longer the server has been running for, the better the advice mysqltuner can provide. In a production environment, consider waiting for at least 24 hours before running the tool. You can get install mysqltuner from the Ubuntu repositories:
sudo apt-get install mysqltuner
Then once its been installed, run it:
and wait for its final report. The top section provides general information about the database server, and the bottom section provides tuning suggestions to alter in your my.cnf. Most of these can be altered live on the server without restarting, look through the official MySQL documentation (link in Resources section) for the relevant variables to change in production. The following is part of an example report from a production database which shows there may be some benefit from increasing the amount of query cache:
-------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: key_buffer_size (> 1.4G) query_cache_size (> 32M) table_cache (> 64) innodb_buffer_pool_size (>= 22G)
One final comment on tuning databases: Whilst we can broadly say that certain settings are the best, performance can vary from application to application. For example, what works best for WordPress might not be the best for Drupal, Joomla or proprietary applications. Performance is dependent on the types of queries, use of indexes, how efficient the database design is and so on. You may find it useful to spend some time searching for database tuning tips based on what applications you’re using it for. Once you get past a certain point any adjustments you make will only result in minor improvements, and you’ll be better off either improving the application, or looking at scaling up your database environment through either using more powerful hardware or by adding slave servers.
- Watch the performance metrics section of the mysqltuner report. Keep maximum possible memory under 50%.
max_heap_table_size variable: keep these equal and high.
join_buffer_size, increase in small amounts; it will be multiplied by
innodb_buffer_pool_size. Make it high.