This is an old revision of the document!
Table of Contents
MySQL Configuration
Development Environment Configuration
Here is the configuration for tahiti:
# Digital Trike MySQL Configuration # # Ubuntu specific # Optimized for 2GB installation # UTF-8 connections # InnoDB default engine # Binary logging # # Percona configuration: # - Production server # - Non-dedicated # - OLTP # - Non-replica # - Amazon AWS # - No virtualization # - Hardware RAID # - 4 CPUs # - 2 GB RAM # - Linux # - Less than 100 tables # - Percona Server 5.5 # - Data directory /var/lib/mysql # - Preferred engine InnoDB # - Percona Server # - InnoDB buffer pool - 1456M # - InnoDB log files - 128M # - InnoDB 100% durable # - Use O_DIRECT # - Binary logging # - Thread cache - 50 # - Key buffer - 32M # - Connection limit - 500 # - Table cache - unset # - Strict SQL mode # - SYSDATE() deterministic # - Force InnoDB to start # - Make InnoDB stricter # # Digital Trike configuration: # - UTF8 connections # - Log queries not using indexes # - Slow query log: 1 second # - Slow query log: /var/log/mysql/mysql-slow.log # # Ubuntu configuration: # - port 3306 # - socket: /var/run/mysqld/mysqld.sock # - datadir: /var/lib/mysql # - tmpdir: /tmp # - error log: /var/log/mysql/mysql.log # - skip external locking # - Binary log size of 100 MB [client] # Ubuntu port = 3306 socket = /var/run/mysqld/mysqld.sock # Digital Trike default-character-set = utf8 [mysqld_safe] # Ubuntu socket = /var/run/mysqld/mysqld.sock nice = 0 # Digital Trike character-set-server = utf8 [mysqld] # Ubuntu user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking # bind-address = 127.0.0.1 log_error = /var/log/mysql/mysql.log pid-file = /var/run/mysqld/mysqld.pid # Ubuntu defaults - unused # key_buffer = 16M # max_allowed_packet = 16M # thread_stack = 192K # thread_cache_size = 8 # myisam-recover = BACKUP # query_cache_limit = 1M # query_cache_size = 16M # expire_logs_days = 10 # max_binlog_size = 100M # Digital Trike - Encoding character-set-server = utf8 # Digital Trike - Logging log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 max_binlog_size = 100M # Percona - General default_storage_engine = InnoDB # Percona - MyISAM key_buffer_size = 32M myisam_recover = FORCE,BACKUP # Percona - Safety max_allowed_packet = 16M max_connect_errors = 1000000 # sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY # sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE sysdate_is_now = 1 innodb = FORCE innodb_strict_mode = 1 # Percona - Binary Logging log_bin = /var/lib/mysql/mysql-bin expire_logs_days = 14 sync_binlog = 1 # Percona - Caches and Limits tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 query_cache_size = 0 max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 table_definition_cache = 1024 table_open_cache = 2048 # Digital Trike - Caching # query_cache_type = 1 # query_cache_size = 128M # Percona - InnoDB innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 # Tweaked for tahiti because it's using so much RAM innodb_buffer_pool_size = 256M # 2GB RAM (original configuration) # innodb_buffer_pool_size = 1456M # 3GB, 4GB RAM # innodb_buffer_pool_size = 2G [mysqldump] # Ubuntu quick quote-names max_allowed_packet = 16M # Digital Trike default-character-set = utf8 [mysql] # Ubuntu # Digital Trike default-character-set = utf8 [mysqladmin] # Digital Trike default-character-set = utf8 [mysqlcheck] # Digital Trike default-character-set = utf8 [mysqlimport] # Digital Trike default-character-set = utf8 [isamchk] # Ubuntu - unused key_buffer # key_buffer = 16M !includedir /etc/mysql/conf.d/
Update Runtime Configuration
For MB sizes, use bc 1024^2 * <num MB>
Set key_buffer_size to 16MB
SET GLOBAL key_buffer_size = 16777216;
Set max_allowed_packet to 1MB
SET GLOBAL max_allowed_packet = 1048576;
Set myisam_sort_buffer_size to 64MB
SET @@myisam_sort_buffer_size = 67108864;
Default Configuration Files
Small
# This is for a system with little memory (<= 64M) where MySQL is only used # from time to time and it's important that the mysqld daemon # doesn't use much resources. [mysqld] port = 3306 socket = /var/run/mysqld/mysqld.sock skip-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K server-id = 1
Medium
# This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) [mysqld] port = 3306 socket = /var/run/mysqld/mysqld.sock skip-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-bin=mysql-bin binlog_format=mixed server-id = 1
Large
# This is for a large system with memory = 512M where the system runs mainly # MySQL. [mysqld] port = 3306 socket = /var/run/mysqld/mysqld.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 log-bin=mysql-bin binlog_format=mixed server-id = 1
Default
key_buffer_size = 8M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 2M read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 8M thread_cache_size = 0 query_cache_size= 0 thread_concurrency = 10
