MySQL configuration best practices



  • So we all use mySQL/Maria DB for various open source projects around here. I would like to start a discussion about best practices regarding mySQL. There are a ton of configuration options that you can make in the my.cnf configuration file. Some are very important, while others might not make that much of a difference. One of the things taht make a huge difference in regards to disk space, is the use of binary loging. Most barebone installations of mysql/mariadb, enable binary logging, but do not make any additional entries for log rotation/expiration of maximum file size.

    For example: adding these 2 lines to the my.cnf file is really important, if you are in fact using binary logging, and do not want mysql takign up all of your disk space with this.

    expire_logs_days=X
    max_binlog_size=X
    
    

    What are some other parameters to optimize mariadb/mysql? It will vary from workload to workload, but it would be great to get a discussion going on this.



  • Best practice is also to either manually configure security or more commonly to run the included secure script to lock things down.



  • Have you ever used Percona?



  • @fuznutz04

    if you are replicating 2 MySQL instances you need
    expire_logs_days=X
    to be enabled, however having a high value will truly eat alot of space.



  • @dbeato said in MySQL configuration best practices:

    Have you ever used Percona?

    No, never. What is it?



  • @emad-r said in MySQL configuration best practices:

    @fuznutz04

    if you are replicating 2 MySQL instances you need
    expire_logs_days=X
    to be enabled, however having a high value will truly eat alot of space.

    Very true.



  • @fuznutz04 Percona is use for HA of MYSQL and replication.



  • I use the lockdown script and mysql tuner. I am sure there are many other things I need to do but have not had time to investigate yet. I use mariadb.



  • This post is deleted!


  • @quixoticjustin

    But that only covers the security aspect, what we are talking about is performance tweaks this:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0	# Disabling symbolic-links is recommended to prevent assorted security risks
    default-storage-engine=innodb
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    # 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
    thread_concurrency=4  	#recommend 2x CPU cores [0 create as many as needed]
    thread_cache_size=100	#recommend 5% of max_connections
    
    # INNODB #
    innodb-buffer-pool-size=2G	#recommend 50% of RAM
    innodb-flush-log-at-trx-commit=1
    innodb-file-per-table=1
    innodb_write_io_threads=2	#recommend 50% of CPU
    innodb_read_io_threads=2	#recommend 50% of CPU
    innodb_log_buffer_size=256M 	#global buffer
    innodb_lock_wait_timeout=300	
    
    [mysqld_safe]
    log-error=/var/log/mariadb/mariadb.log
    pid-file=/var/run/mariadb/mariadb.pid
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    

    That said if you dont know what you are doing, it is best to leave it default in a database world.



  • @emad-r

    What is your practice for log rotation? For example, the error log. If that grows (obviously indicating other issues) , and needs to be rotated, what procedure do you use to rotate logs?



  • @fuznutz04 said in MySQL configuration best practices:

    @emad-r

    What is your practice for log rotation? For example, the error log. If that grows (obviously indicating other issues) , and needs to be rotated, what procedure do you use to rotate logs?

    Currently the third party vendor uses this:

    expire_logs_days = 10
    max_binlog_size = 100M

    In our MySQL instance.