Re-configure default MySQL server to run more efficiently.
Below is the content of my.cnf file that we use on a Debian Wheezy server with MySQL 5.5. Server has 2GB of RAM.
MySQL dataroot /var/lib/mysql is mounted on a separate drive.
# The MySQL 5.5 database server configuration file # Server with 2GB of RAM + 1GB swap # By: www.lisenet.com # # Global total buffers (mysqltuner) include: # key_buffer_size # innodb_buffer_pool_size # innodb_additional_mem_pool_size # innodb_log_buffer_size # tmp_table_size # query_cache_size # # Per-thread buffers (mysqltuner) include: # read_buffer_size # read_rnd_buffer_size # sort_buffer_size # join_buffer_size # thread_stack # binlog_cache_size [mysqld] # *** Basic Settings *** 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 symbolic_links = 0 bind_address = 127.0.0.1 # *** Tuning *** myisam_recover = BACKUP max_connections = 100 wait_timeout = 3600 interactive_timeout = 3600 # nofile is set to 2048 for mysql user in limits.conf open_files_limit = 2048 table_open_cache = 1024 max_allowed_packet = 16M # Max size to which user-created MEMORY tables are permitted to grow # Make these equal max_heap_table_size = 256M tmp_table_size = 256M # *** Fine Tuning *** key_buffer_size = 128M join_buffer_size = 2M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 256K thread_stack = 256K thread_cache_size = 8 # *** Query Cache Configuration *** query_cache_type = 1 query_cache_limit = 4M query_cache_size = 64M # *** Logging *** # Be aware that this log type is a performance killer general_log_file = /var/log/mysql/mysql.log general_log = 0 #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log_queries_not_using_indexes # *** Replication and Binlogs *** # Binlogs are not enabled server_id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 7 max_binlog_size = 1G # *** InnoDB *** innodb_file_per_table = 1 innodb_flush_method = fsync innodb_flush_log_at_trx_commit = 1 innodb_log_file_size = 32M innodb_buffer_pool_size = 128M innodb_additional_mem_pool_size = 32M innodb_buffer_pool_instances = 1 innodb_log_buffer_size = 10M innodb_lock_wait_timeout = 100 innodb_data_file_path = ibdata1:16M:autoextend:max:2048M [mysqldump] quick quote_names max_allowed_packet = 16M [isamchk] key_buffer = 16M [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. !includedir /etc/mysql/conf.d/
Mysqltuner:
$ mysqltuner ... [--] Total buffers: 586.0M global + 6.5M per thread (100 max threads) [OK] Maximum possible memory usage: 1.2G (60% of installed RAM) ...