MY SQL Standards

MySQL standards

In order to make a Db ACID complaint , innodb must be setup as the default , with the correct configuration

General settings

Directories
/mysqltemp
mysqld_multi:
log = /var/log/mysqld_multi.log

Per  Instance settings

server-id = n
This is required for a multi instance Db
port = 33nn
This defines the port to connect to an instance on (server_id=1, port=3306: server_id=2, port=3307 and so on)
log-error = /mysqllogs1/<instance>/<instance>_err.log
The error log file and folder
log-bin = /mysqllogs1/<instance>/
defines the binary log location
expire-logs_days = n
defines the amount of binary logs to keep (this is used by mysql after a log flush)
binlog_format = mixed
defines the format of the binlogs (the other options are statement and row based, or combined to mixed format)
default-storage-engine = innodb
defines the default engine (post 5.5.5 it is innodb, but this forces the issue)
datadir = /mysqldata1/<instance>/
defines where to place non innodb db files , including general log and error log
innodb_data_home_dir=
This is required or a tablespace creation error occurs (if left blank, then the full data path must be defined in the innodb_data_file_path)
innodb_data_file_path = /mysqldata1/<instance>/ibdata1:nG:autoextend:max:nG;/mysqldata1/<instance>/ibdata2:nG:autoextend:max:nG (default if not set is ibdata1:10M:autoextend)
defines initial tablespace and size (italics show additional items like autoextend, max size and addition tablespaces)
innodb_log_group_home_dir= /mysqllogs1/<instance>/
defines the location of the redologs
innodb_log_file_size = 256M (default if not set is 5M)
defines size of redologs (we are defaulting to 16m)
innodb_buffer_pool_size                =500m (default if not set is 128M)
innodb_additional_mem_pool_size =20m (default if not set is 8M)
innodb_log_buffer_size                  =16m (default if not set is 8M)
defines the size of the Db memory pool
innodb_flush_log_trx_commit=1
This is to ensure that the tables are ACID complaint
ATOMIC (successful execute of rolled back as a unit), CONSISTENT (state of the Db), ISOLATED (one transaction doesn’t affect the another), DURABLE (transaction changes recorded properly in Db)
innodb_lock_wait_timeout = 50 (default if not set is 50)
(50 is the default) number of seconds a transaction waits for a row lock before giving up
innodb_flush_method = O_DIRECT (default is not set)
The method by which the logfiles are flushed
innodb_file_per_table = 1
(1=on, 0 = off [default]), defines that each table is created as a file, rather than in the system tablespace.  For physical we may want this set to file per table to give added performance of extra spindles for the system tablespace.  For VM guests the norm will be to use shared tablespace and set this to 0.
max_allowed_packet = 16m
Although this should be set high in certain circumstances, our default is 16m
tmp_table_size = 32M (default 16M)
max_heap_table_size = 32M (default 16M)
Mysql will take the lower of the two values to determine when it starts writing temp tables in memory to disk which is much slower.  Set this as high as possible but bear in mind server available memory as each connection can use up to this value meaning 100 connections could potentially use 3 gig
skip-name-resolve
do not lookup connecting client in dns, which can cause connections to fail if not looked up sucessfully both ways
open-files-limit = 4096
this specifies the number of open files allowed in mysql.  Really only needs to be set if using file per table or high number of myisam tables where a lock all tables during a dump has exceeded the 1024 max file limit and needed adjusting.  This does not need to be set in most cases.
query_cache_size =  16 - 128M default (0)
The size of the query cache. Can increase performance of repeated selects if this is turned on.  Look at status Qcache_lowmem_prunes to see if this is increasing.  If it is then the query cache is filling to may be beneficial to increase this value.  Do not increase above 128M as can have a negative effect on performance. Mysql has to manage this cache.
query_cache_limit = 1M
The maximum size of a single query that can be stored in the query cache.