magento-mysql

Magento default mysql settings

default/startup settings for mysql database.
please read this before changing anything!

GENERAL OPTIONS

1. max_connections
   How many connections to allow. Watch max_used_connections value

2. thread_cache
   Cache to prevent excessive thread creation
   50-100 is good value. Watch threads_created
   

3. table_cache/table_open_cache
   Cache of opened table instances
   Single table may have multiple entries
   Watch opened_tables status value
   Start with 4096
   

4. open_files_limit
   MyISAM tables require up to 2 file handlers
   Each connection is file handler too
   Safe to set to 65535 in most systems
   

5. table_definition_cache
   Cache table definitions (CREATE TABLE)
   Only one entry per table
   Watch Opened_table_definitions
   Set to number of tables + 10% unless 50K+ tables
   

6. back_log
   Need adjustment if many connections/sec
   2048 is reasonable value
   

7. max_allowed_packet
   Limits maximum size of query
   Limits internal string variable size
   16MB is a good value
   

8. max_connect_errors
   Prevent password brute force attack
   Can cause "Host Blocked" error messages
   Value around 1000000 is good
   

9. skip_name_resolve
   Avoid DNS lookup on connection. Faster and Safer
   Do not use host names in GRANTs
   

10. old_passwords
    Should NOT be enabled. Will cause insecure password hash to be used.
    

11. log_bin
    Enable for replication and point in time recovery
    Set to mysql-bin to avoid default naming
    

12. sync_binlog
    Make Binlog durable. Set to 1 if have RAID with BBU or Flash
    Can be really performance killer with slow drives.
    

13. expire_log_days
    Purge old binary logs after this number of days
    14 (2 weeks) is a good value with weekly backups
    

14. tmp_table_size
    

15. max_heap_table_size
    Typically set to same value (workload based)
    Created_tmp_disk_tables status variable
    Beware BLOB/TEXT fields cause on disk table with any size.
    

16. query_cache_size
    Enable query cache only if it is tested to provide significant gains
    Often causes stalls and contention
    Do not set over 512M
    

17. sort_buffer_size
    In memory buffer used for sorting
    Watch sort_merge_passes
    Consider setting for session for large queries
    Values up to 1MB are good default
    Large values hurt performance of small queries
    

18. join_buffer_size
    Helps performance of Joins with no indexes
    Better get rid of such Joins
    8MB can be reasonable value
    

19. default_storage_engine
    Use Innodb engine for tables if not specified
    

20. read_rnd_buffer_size
    Buffer for reading rows in sorted offer
    Specifies Maximum Value
    Values around 16MB often make sense
    Do not mix with read_buffer_size
    

21. Tmpdir
    Specify location of temporary directory
    Tmpfs often good choice unless very large temporary space is needed.
    tmpdir=/dev/shm
    

    MyISAM OPTIONS
    

22. key_buffer_size
    Cache MyISAM Indexes.
    Does Not cache data.
    Up to 30% of memory if using MyISAM only
    

23. myisam_recover
    Automatically repair corrupted MyISAM tables after crash. BACKUP,FORCE is a good value.
    

24. myisam_sort_buffer_size
    Buffer used for building MyISAM indexes by Sort. 8MB-256MB are good values
    

25. low_priority_updates
    Allow higher concurrency for SELECTs
    May starve update queries
    

26. bulk_insert_buffer_size
    Buffer to optimize Bulk Inserts
    Values of 1/4 of key_buffer_size make sense
    Note it is per connection value
    

     INNODB MEMORY SETTINGS
    

27. innodb_buffer_pool_size
    The most important setting. Often 80%+ of memory is allocated here.
    

28. innodb_buffer_pool_instances
    Reduce contention. Set to 4+ in MySQL 5.5+
    

29. innodb_log_buffer_size
    Buffer for log files. Good Values 4MB-128MB
    Not only reduce writes but help contention
    

30. innodb_ibuf_max_size
    Control size of Insert buffer. Default is 1/2 of Buffer pool. Smaller values are good for SSD
    

     INNODB IO OPTIONS
    

31. innodb_flush_log_at_trx_commit
    Control Durability
    1=flush and sync; 2=flush; 0=neither
    

32. Innodb_flush_method
    Controls how Innodb Performs IO
    O_DIRECT good value for most servers
    

33. innodb_auto_lru_dump
    Percona Server Feature to warmup quickly
    300 (seconds) is a good value
    

34. innodb_io_capacity
    Controls Innodb Assumption about Disk Performance. Increase for faster drives
    

35. innodb_read_io_threads
    

36. innodb_write_io_threads
    Control number of threads doing reads and writes
    MySQL 5.5 has async IO so very high values might not be needed
    4 is good default. Higher for large IO systems.
    

37. innodb_flush_neighbor_pages
    Percona Server feature to control how flushing works
    Disable (set to 0) for SSD
    

     OTHER INNODB OPTIONS
    

38. innodb_log_file_size
    Size of redo log file. Larger logs better performance but longer recovery.
    

39. innodb_log_files_in_group
    Leave at 2 which is default.
    

40. innodb_file_per_table
    Store each Innodb table in separate file. Usually Good choice
    

41. innodb=force
    Enable so MySQL does not start if Innodb could not initialize.
    Otherwise it might start but error on access to all Innodb tables.
    

42. innodb_lock_wait_timeout
    How long to wait for row level locks before bailing out
    

43. innodb_old_blocks_time
    Helps to make buffer pool scan resistant
    Values around 1000 make sense
    

44. innodb_file_format
    Which file format Innodb will use
    Antelope is default legacy format
    Barracuda allows to use new features like compression
    

45. innodb_stats_on_metadata
    Update statistics on meta data access
    Such as Information_schema queries
    Typically best disabled for more workloads Set to 0
    Innodb will still refresh stats when table changes significantly
    

46. performance_schema
    Enable Performance Schema in MySQL 5.5+
    Watch potential overhead.
    

47. log_slow_queries
    Enable Slow Query Log. Old but very helpful.
    

48. long_query_time
    Especially with long_query_time set to 0 periodically to get sample of the load
    

49. log_slow_verbosity=full
    Get a lot more data about queries in Percona Server
    

50. low_warnings=2
    Get warnings about disconnects and other minor issues in error log.
    More information but it can get spammy
    

51. userstat_running=1
    Get advanced Table and Index usage statistics in Percona Server and MariaDB