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) ...
Until now, this is the best mysql setup I have ever tried on small VPS servers. I did not used the Basic Settings because it crashed my mysql.
The rest setup really worked like a rocket! Thanks man!
Thank you!
Thank you for your configuration. Can you share what could be changed with more RAM?
No worries! Each MySQL connection uses memory, therefore if you have more RAM, then you can serve more concurrent connections, see
max_connections. The rest depends on how busy your application is. For example, you may want to increase the buffer pool size.