(dv) 4.0:Sample MySQL configuration for (dv) 4.0 512MB
- This page was last modified on February 5, 2011, at 13:17.
From (mt) Community Wiki
Contents |
This is an INCOMPLETE article. Please excuse our dust as we improve and organize the CommunityWiki. Help us keep this article up-to-date by joining our wiki!
Information
This is a configuration file for MySQL version 5.0. It is tailored for a (dv) version 3.5 with 512MB of memory, but could be used as a starting point for a server with more memory. I highly advise using MySQLTuner from Using MySQLTuner on your Dedicated-Virtual Server for further adjustments after your MySQL server has been running for at least 24 hours.
This is a modified version of the standard MySQL configuration. You should back up your existing configuration first before overwriting it with this.
cp /etc/my.cnf /etc/my.cnf.bak
Steps to install
- Backup your current configuration:
cp /etc/my.cnf /etc/my.cnf.bak
- Get the text from "The Configuration" where it needs to be (/etc/my.cnf).
- Run this command to create your slow-query log file, and restart mysql:
touch /var/log/mysql.slow-queries.log && chown mysql:mysql /var/log/mysql.slow-queries.log && /etc/init.d/mysqld restart
The configuration
############################################################################### ### /etc/my.cnf - MySQL configuration ### For: MySQL 5.1 on a server which is not dedicated to MySQL ### ### This file was initially designed for (mt) Media Temple ### (dv) 4.0 512MB ### ### Version 1.0 ### ### Any line with configuration after a '#' indicates the default setting, ### but is included in this way for easy modification (and for reference). ### ############################################################################## ### ### To activate this configuration, you must first create the slow query log ### file and then restart mysql. You only need to do this once after you ### begin using this configuration file. ### ### Copy and paste this command: ### touch /var/log/mysql.slow-queries.log && /etc/init.d/mysqld restart ### ############################################################################## [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user = mysql max_connections = 100 max_allowed_packet = 32M # Comment this out if you need LOAD DATA INFILE, but disable again # when you're done. This is a security risk! local-infile = 0 # tmp_table_size/max_heap_table_size: Default is 16MB, but this causes many # temporary tables to be written to disk, and is ineffecient. # Keep tmp_table_size and max_heap_table_size the same! tmp_table_size = 64M max_heap_table_size = 64M ### query-cache settings ### query-cache-type = 1 query-cache-size = 16M query_cache_limit = 2M ### Buffer size settings ### #sort_buffer_size = 2M #join_buffer_size = 128K # key_buffer_size is important for MyISAM tables. Should be larger than size of index data. key_buffer_size = 16M thread_cache_size = 8 # table_cache: Approximate by taking max_connections and multiplying by the # largest number of tables that can be open for any join. table_cache = 512 ### Timing options ### interactive_timeout = 100 wait_timeout = 20 connect_timeout = 15 ### Log slow queries ### # Make sure you create this file before you start mysql or it will not work! # Use the this command: touch /var/log/mysql.slow-queries.log slow-query-log = 1 slow-query-log-file = /var/log/mysql.slow-queries.log # Duration a query must run (in seconds) to get logged long_query_time = 1 ### InnoDB settings ### # See http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html # for InnoDB settings; They largely depend on your application. innodb_buffer_pool_size = 4M [mysqld_safe] log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid open_files_limit = 2048 [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