/Magento-mysql

Magento default mysql settings

Primary LanguagePerlGNU General Public License v3.0GPL-3.0

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