Esempio per un server con 4-8GB di RAM
# The settings provided below are ideal for a 4GB-8GB RAM server.
# You can adjust values accordingly for higher or lower spec systems, but generally,
# it's an almost "one size, fits all" setup.
# Use DB diagnostics tools like:
# https://launchpad.net/mysql-tuning-primer or http://blog.mysqltuner.com/download/
# to get more insight on your MySQL DB's resource usage and more...
# Special thanks to Yves Trudeau (Percona) for his valuable help on compiling this setup.
[mysqld]
# START
query_cache_limit=1M
query_cache_size=32M # Use 64M, 128M etc. (for bigger implementations)
query_cache_type=1
# Connections: If we want 100 connections spawned across 1 user only (max_user_connections),
# leave 20-50 more for system processes.
# If user connections were 200 and we had 2 users,
# the max_connections value would be something between 220-250.
# Increasing the connections does not guarantee better performance, but it DOES guarantee additional memory usage.
max_connections=120
max_user_connections=100
wait_timeout=300 # Time in seconds to keep active connections. The default is 28800 which hogs the connection limits on high traffic sites. 300 is a reasonable value especially on split web/db server setups.
# Key buffers: Sufficient at 64M for most cases,
# increase if mysql-tuning-primer tells you to
key_buffer=64M
key_buffer_size=64M
join_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_heap_table_size=384M
tmp_table_size=384M
table_cache=500 # Default is 64, but it's too low
table_definition_cache=500 # Should be the same as table_cache
open_files_limit=1500 # Should be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage (default is 4250, which is high enough)
# InnoDB tweaks (uncomment for use with InnoDB tables only)
#innodb_additional_mem_pool_size=20M
#innodb_buffer_pool_size=5G # For a dedicated server with 8GBs, also check if /proc/sys/vm/swappiness is set to 0
#innodb_flush_log_at_trx_commit=0
#innodb_flush_method=O_DIRECT
#innodb_log_file_size=128M
#innodb_log_buffer_size=8M
# The minimum length of words to be indexed - Commented by default
# Uncomment below to enable searching for three-character words
#ft_min_word_len=3
# Log slow queries - Commented by default
# On Ubuntu servers, do this:
# $ cd /var/log/mysql/; sudo touch slow.log; sudo chmod 0640 slow.log; sudo chown mysql:adm slow.log
# otherwise MySQL may not be able to load the slow.log file
log-slow-queries=/var/log/mysql/slow.log
long_query_time=3
#log-queries-not-using-indexes
# Advanced
low_priority_updates=1
concurrent_insert=2
thread_cache_size=20 # More here: http://hashmysql.org/index.php?title=Tuning_System_Variables
thread_concurrency=8 # set to 2X the number of processors in your machine for best performance
# END
Esempio per un server con 16GB di RAM
[mysqld]
datadir=/var/lib/mysql
tmpdir=/var/lib/mysqltmp
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
table_cache=2048
thread_cache_size=32
back_log=100
max_connect_errors=10000
open-files=10000
interactive_timeout=400
wait_timeout=300
max_connections=500
skip-bdb
log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=2
log-queries-not-using-indexes
max_allowed_packet=128M
tmp_table_size=256M
max_heap_table_size=256M
query_cache_size=32M
query_cache_limit = 6M
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=16M
join_buffer_size=2M
default-storage-engine=InnoDB
key_buffer_size=128M
myisam_sort_buffer_size=64M
innodb_log_file_size=100M
innodb_buffer_pool_size=6G
innodb_additional_mem_pool_size=20M
innodb_support_xa = 0
[mysql.server]
user=mysql
[mysqld_safe]
