Skip to content

Instantly share code, notes, and snippets.

@juanrdlo

juanrdlo/my.cnf

Forked from fevangelou/my.cnf
Created March 26, 2021 12:20
Show Gist options
  • Save juanrdlo/36bee4bcfc4cf8cf6c280ac3869f45a8 to your computer and use it in GitHub Desktop.
Save juanrdlo/36bee4bcfc4cf8cf6c280ac3869f45a8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # Optimized my.cnf configuration for MySQL/MariaSQL
    # === Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers) ===
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
  2. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -122,7 +122,7 @@ low_priority_updates = 1
    concurrent_insert = 2

    # === Connection Settings ===
    max_connections = 100 # UPD
    max_connections = 100 # UPD - Important: high no. of connections = high RAM consumption

    back_log = 512
    thread_cache_size = 100
  3. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -71,7 +71,7 @@ port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld]
    # Required Settings
    # === Required Settings ===
    basedir = /usr
    bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
    datadir = /var/lib/mysql
  4. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 19 additions and 16 deletions.
    35 changes: 19 additions & 16 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -82,14 +82,15 @@ port = 3306
    skip_external_locking
    skip_name_resolve
    socket = /var/run/mysqld/mysqld.sock

    # Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

    tmpdir = /tmp
    user = mysql

    # InnoDB Settings
    # === SQL Compatibility Mode ===
    # Enable for b/c with databases created in older MySQL/MariaDB versions
    # (e.g. when using null dates)
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

    # === InnoDB Settings ===
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 4G # Use up to 70-80% of RAM
    @@ -108,17 +109,19 @@ innodb_read_io_threads = 64
    innodb_write_io_threads = 64
    #innodb_io_capacity = 1000 # Max is 2000

    # MyISAM Settings
    #query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    #query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    #query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    # === MyISAM Settings ===
    # The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
    # Do NOT un-comment on MySQL 8.x+
    #query_cache_limit = 4M # UPD
    #query_cache_size = 64M # UPD
    #query_cache_type = 1 # Enabled by default

    key_buffer_size = 32M # UPD

    low_priority_updates = 1
    concurrent_insert = 2

    # Connection Settings
    # === Connection Settings ===
    max_connections = 100 # UPD

    back_log = 512
    @@ -140,14 +143,14 @@ wait_timeout = 180
    # For example you can use value 0.01 for 10 milliseconds timeout.
    # More info at: https://mariadb.com/kb/en/aborting-statements/

    # Buffer Settings
    # === Buffer Settings ===
    innodb_sort_buffer_size = 2M # UPD
    join_buffer_size = 4M # UPD
    read_buffer_size = 3M # UPD
    read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    # Table Settings
    # === Table Settings ===
    # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    @@ -163,10 +166,10 @@ open_files_limit = 60000 # UPD - This can be 2x to 3x the table_o
    max_heap_table_size = 128M
    tmp_table_size = 128M

    # Search Settings
    # === Search Settings ===
    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    # Logging
    # === Logging ===
    log_error = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes = 1
    long_query_time = 5
    @@ -175,8 +178,8 @@ slow_query_log_file = /var/lib/mysql/mysql_slow.log

    [mysqldump]
    # Variable reference
    # For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    # For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
    # For MySQL 5.7+: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    # For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
    quick
    quote_names
    max_allowed_packet = 512M
  5. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@
    # ~ Updated February 2021 ~
    #
    #
    # The settings provided below are a starting point for a 8GB - 16GB RAM server with 4-8 CPU cores.
    # The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores.
    # If you have different resources available you should adjust accordingly to save CPU, RAM & disk I/O usage.
    #
    # The settings marked with a specific comment or the word "UPD" (after the value)
  6. @fevangelou fevangelou revised this gist Feb 23, 2021. 1 changed file with 11 additions and 9 deletions.
    20 changes: 11 additions & 9 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -2,10 +2,10 @@
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # ~ Updated January 2020 ~
    # ~ Updated February 2021 ~
    #
    #
    # The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
    # The settings provided below are a starting point for a 8GB - 16GB RAM server with 4-8 CPU cores.
    # If you have different resources available you should adjust accordingly to save CPU, RAM & disk I/O usage.
    #
    # The settings marked with a specific comment or the word "UPD" (after the value)
    @@ -91,13 +91,13 @@ user = mysql

    # InnoDB Settings
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 2 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM
    innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 4G # Use up to 70-80% of RAM
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 512M
    innodb_log_file_size = 1G
    innodb_stats_on_metadata = 0

    #innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
    @@ -106,11 +106,12 @@ innodb_stats_on_metadata = 0
    # the overall load produced by MySQL/MariaDB.
    innodb_read_io_threads = 64
    innodb_write_io_threads = 64
    #innodb_io_capacity = 1000 # Max is 2000

    # MyISAM Settings
    query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    #query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    #query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    #query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

    key_buffer_size = 32M # UPD

    @@ -140,6 +141,7 @@ wait_timeout = 180
    # More info at: https://mariadb.com/kb/en/aborting-statements/

    # Buffer Settings
    innodb_sort_buffer_size = 2M # UPD
    join_buffer_size = 4M # UPD
    read_buffer_size = 3M # UPD
    read_rnd_buffer_size = 4M # UPD
    @@ -177,4 +179,4 @@ slow_query_log_file = /var/lib/mysql/mysql_slow.log
    # For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
    quick
    quote_names
    max_allowed_packet = 64M
    max_allowed_packet = 512M
  7. @fevangelou fevangelou revised this gist Jan 22, 2020. 1 changed file with 18 additions and 28 deletions.
    46 changes: 18 additions & 28 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # === Optimized my.cnf configuration for MySQL/MariaSQL on cPanel/WHM servers ===
    # Optimized my.cnf configuration for MySQL/MariaSQL
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    @@ -35,18 +35,11 @@
    # the first time you apply this configuration:
    #
    # $ rm -rvf /var/lib/mysql/ib_logfile*
    # $ touch /var/lib/mysql/mysql.sock
    # $ touch /var/lib/mysql/mysql.pid
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ /scripts/restartsrv_mysql
    # $ service mysql restart
    #
    # or use the shorthand command:
    # $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; /scripts/restartsrv_mysql
    #
    # IMPORTANT: If you edit this file from the Engintron WHM app in cPanel/WHM,
    # then you DO NOT need to execute the above terminal commands. When you save
    # the file through the Engintron WHM app, these terminal commands will be
    # executed automatically after the file is saved on disk.
    # $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart
    #
    # 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
    # properly configured. A good example of a "clean" /etc/hosts file is something like this:
    @@ -55,35 +48,27 @@
    # ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
    # 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly!
    #
    # Finally restart the database using the related cPanel script:
    # Finally restart the database service:
    #
    # $ /scripts/restartsrv_mysql
    # $ service mysql restart
    #
    # 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder
    # (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group.
    # Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this:
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ chmod 0755 /var/lib/mysql
    #
    # Finally restart the database using the related cPanel script:
    #
    # $ /scripts/restartsrv_mysql
    #
    # 4. Adjust SQL settings under "Tweak Settings" in WHM:
    # After applying the optimized my.cnf file, you'll also want to DISABLE the following 3 settings
    # in the "SQL" tab of Tweak Settings in WHM:
    # Finally restart the database service:
    #
    # - Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?
    # - Allow cPanel & WHM to determine the best value for your MySQL max_allowed_packet configuration?
    # - Allow cPanel & WHM to determine the best value for your MySQL innodb_buffer_pool_size configuration?
    # $ service mysql restart
    #
    #
    # ~ FIN ~


    [mysql]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    socket = /var/run/mysqld/mysqld.sock

    [mysqld]
    # Required Settings
    @@ -92,10 +77,15 @@ bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote
    datadir = /var/lib/mysql
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    pid_file = /var/lib/mysql/mysql.pid
    pid_file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip_external_locking
    socket = /var/lib/mysql/mysql.sock
    skip_name_resolve
    socket = /var/run/mysqld/mysqld.sock

    # Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

    tmpdir = /tmp
    user = mysql

    @@ -128,7 +118,7 @@ low_priority_updates = 1
    concurrent_insert = 2

    # Connection Settings
    max_connections = 100 # UPD - Important: high no. of connections = more RAM consumption
    max_connections = 100 # UPD

    back_log = 512
    thread_cache_size = 100
    @@ -156,9 +146,9 @@ read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    # Table Settings
    # In systemd managed systems like CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
    # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7 (when it's supported in cPanel), please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 40000 # UPD
    table_open_cache = 40000 # UPD
  8. @fevangelou fevangelou revised this gist Jan 22, 2020. 1 changed file with 31 additions and 21 deletions.
    52 changes: 31 additions & 21 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # Optimized my.cnf configuration for MySQL/MariaSQL
    # === Optimized my.cnf configuration for MySQL/MariaSQL on cPanel/WHM servers ===
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    @@ -35,11 +35,18 @@
    # the first time you apply this configuration:
    #
    # $ rm -rvf /var/lib/mysql/ib_logfile*
    # $ touch /var/lib/mysql/mysql.sock
    # $ touch /var/lib/mysql/mysql.pid
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ service mysql restart
    # $ /scripts/restartsrv_mysql
    #
    # or use the shorthand command:
    # $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart
    # $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; /scripts/restartsrv_mysql
    #
    # IMPORTANT: If you edit this file from the Engintron WHM app in cPanel/WHM,
    # then you DO NOT need to execute the above terminal commands. When you save
    # the file through the Engintron WHM app, these terminal commands will be
    # executed automatically after the file is saved on disk.
    #
    # 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
    # properly configured. A good example of a "clean" /etc/hosts file is something like this:
    @@ -48,27 +55,35 @@
    # ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
    # 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly!
    #
    # Finally restart the database service:
    # Finally restart the database using the related cPanel script:
    #
    # $ service mysql restart
    # $ /scripts/restartsrv_mysql
    #
    # 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder
    # (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group.
    # Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this:
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ chmod 0755 /var/lib/mysql
    #
    # Finally restart the database service:
    # Finally restart the database using the related cPanel script:
    #
    # $ /scripts/restartsrv_mysql
    #
    # 4. Adjust SQL settings under "Tweak Settings" in WHM:
    # After applying the optimized my.cnf file, you'll also want to DISABLE the following 3 settings
    # in the "SQL" tab of Tweak Settings in WHM:
    #
    # $ service mysql restart
    # - Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?
    # - Allow cPanel & WHM to determine the best value for your MySQL max_allowed_packet configuration?
    # - Allow cPanel & WHM to determine the best value for your MySQL innodb_buffer_pool_size configuration?
    #
    #
    # ~ FIN ~


    [mysql]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    socket = /var/lib/mysql/mysql.sock

    [mysqld]
    # Required Settings
    @@ -77,15 +92,10 @@ bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote
    datadir = /var/lib/mysql
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    pid_file = /var/run/mysqld/mysqld.pid
    pid_file = /var/lib/mysql/mysql.pid
    port = 3306
    skip_external_locking
    skip_name_resolve
    socket = /var/run/mysqld/mysqld.sock

    # Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

    socket = /var/lib/mysql/mysql.sock
    tmpdir = /tmp
    user = mysql

    @@ -108,17 +118,17 @@ innodb_read_io_threads = 64
    innodb_write_io_threads = 64

    # MyISAM Settings
    query_cache_limit = 4M # UPD - Option supported up to MySQL v5.7, remove this line on MySQL 8.x
    query_cache_size = 64M # UPD - Option supported up to MySQL v5.7, remove this line on MySQL 8.x
    query_cache_type = 1 # Option supported up to MySQL v5.7, remove this line on MySQL 8.x
    query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x
    query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

    key_buffer_size = 32M # UPD

    low_priority_updates = 1
    concurrent_insert = 2

    # Connection Settings
    max_connections = 100 # UPD
    max_connections = 100 # UPD - Important: high no. of connections = more RAM consumption

    back_log = 512
    thread_cache_size = 100
    @@ -146,9 +156,9 @@ read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    # Table Settings
    # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
    # In systemd managed systems like CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # E.g. for MySQL 5.7 (when it's supported in cPanel), please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 40000 # UPD
    table_open_cache = 40000 # UPD
  9. @fevangelou fevangelou revised this gist Jan 22, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -135,8 +135,8 @@ wait_timeout = 180

    # For MariaDB 10.1.1+ only (disabled by default)
    #max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
    # The variable is of type double,
    # thus you can use subsecond timeout. For example you can use value 0.01 for 10 milliseconds timeout.
    # The variable is of type double, thus you can use subsecond timeout.
    # For example you can use value 0.01 for 10 milliseconds timeout.
    # More info at: https://mariadb.com/kb/en/aborting-statements/

    # Buffer Settings
  10. @fevangelou fevangelou revised this gist Jan 22, 2020. 1 changed file with 47 additions and 21 deletions.
    68 changes: 47 additions & 21 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -2,24 +2,33 @@
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # === Updated December 2018 ===
    # ~ Updated January 2020 ~
    #
    #
    # The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    # RAM and disk I/O usage.
    # The settings marked with a specific comment or the word "UPD" after the value
    # If you have different resources available you should adjust accordingly to save CPU, RAM & disk I/O usage.
    #
    # The settings marked with a specific comment or the word "UPD" (after the value)
    # should be adjusted for your system by using database diagnostics tools like:
    #
    # https://github.com/major/MySQLTuner-perl
    # or
    # https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6)
    # https://github.com/RootService/tuning-primer (supports MySQL up to v5.7)
    #
    # Run either of these scripts before optimizing your database, at least 1 hr after the optimization & finally
    # at least once a day for 3 days (without restarting the database) to see how your server performs and if you need
    # to re-adjust anything. The more MySQL/MariaDB runs without restarting, the more usage data it gathers, so these
    # diagnostics scripts will report in mode detail how MySQL/MariaDB performs.
    #
    #
    # Note that if there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
    # IMPORTANT NOTE: If there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
    #
    #
    # THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING
    # --- THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING ---
    #
    # If any terminal commands are mentioned, make sure you execute them as "root" user.
    # If MySQL cannot start or restart, then perform the following actions.
    #
    # If MySQL or MariaDB cannot start (or restart), then perform the following actions.
    #
    # 1. If the server had the stock database configuration and you added or updated any
    # "innodb_log_*" settings (as suggested below), then execute these commands ONLY
    @@ -37,7 +46,7 @@
    #
    # 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
    # ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
    # 1.2.3.4 hostname.domain.tld hostname # Replace accordingly!
    # 1.2.3.4 hostname.domain.tld hostname # <-- Replace accordingly!
    #
    # Finally restart the database service:
    #
    @@ -52,6 +61,10 @@
    # Finally restart the database service:
    #
    # $ service mysql restart
    #
    #
    # ~ FIN ~


    [mysql]
    port = 3306
    @@ -60,8 +73,7 @@ socket = /var/run/mysqld/mysqld.sock
    [mysqld]
    # Required Settings
    basedir = /usr
    bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote servers to connect to this server's
    # database instance
    bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
    datadir = /var/lib/mysql
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    @@ -70,15 +82,17 @@ port = 3306
    skip_external_locking
    skip_name_resolve
    socket = /var/run/mysqld/mysqld.sock
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES # Enable for b/c with databases
    # using null dates

    # Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

    tmpdir = /tmp
    user = mysql

    # InnoDB Settings
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 2 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    @@ -94,11 +108,11 @@ innodb_read_io_threads = 64
    innodb_write_io_threads = 64

    # MyISAM Settings
    query_cache_limit = 4M # UPD - Option supported up to MySQL v5.7
    query_cache_size = 48M # UPD - Option supported up to MySQL v5.7
    query_cache_type = 1 # Option supported up to MySQL v5.7
    query_cache_limit = 4M # UPD - Option supported up to MySQL v5.7, remove this line on MySQL 8.x
    query_cache_size = 64M # UPD - Option supported up to MySQL v5.7, remove this line on MySQL 8.x
    query_cache_type = 1 # Option supported up to MySQL v5.7, remove this line on MySQL 8.x

    key_buffer_size = 48M # UPD
    key_buffer_size = 32M # UPD

    low_priority_updates = 1
    concurrent_insert = 2
    @@ -113,19 +127,31 @@ thread_stack = 192K
    interactive_timeout = 180
    wait_timeout = 180

    # For MySQL 5.7+ only (disabled by default)
    #max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
    # This option may be useful to address aggressive crawling on large sites,
    # but it can also cause issues (e.g. with backups). So use with extreme caution and test!
    # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

    # For MariaDB 10.1.1+ only (disabled by default)
    #max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)
    # The variable is of type double,
    # thus you can use subsecond timeout. For example you can use value 0.01 for 10 milliseconds timeout.
    # More info at: https://mariadb.com/kb/en/aborting-statements/

    # Buffer Settings
    join_buffer_size = 4M # UPD
    read_buffer_size = 3M # UPD
    read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    # Table Settings
    # In systemd managed systems like Ubuntu 16.04 or CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
    # In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 10000 # UPD
    table_open_cache = 10000 # UPD
    table_definition_cache = 40000 # UPD
    table_open_cache = 40000 # UPD
    open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
    # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
    # In systemd managed systems this limit must also be set in:
  11. @fevangelou fevangelou revised this gist Jul 17, 2019. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -94,9 +94,9 @@ innodb_read_io_threads = 64
    innodb_write_io_threads = 64

    # MyISAM Settings
    query_cache_limit = 4M # UPD
    query_cache_size = 48M # UPD
    query_cache_type = 1
    query_cache_limit = 4M # UPD - Option supported up to MySQL v5.7
    query_cache_size = 48M # UPD - Option supported up to MySQL v5.7
    query_cache_type = 1 # Option supported up to MySQL v5.7

    key_buffer_size = 48M # UPD

  12. @fevangelou fevangelou revised this gist Dec 16, 2018. 1 changed file with 14 additions and 9 deletions.
    23 changes: 14 additions & 9 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # === Updated July 2018 ===
    # === Updated December 2018 ===
    #
    # The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    @@ -77,16 +77,21 @@ user = mysql

    # InnoDB Settings
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 1 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 1G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb_buffer_pool_instances = 2 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 128M
    innodb_log_file_size = 512M
    innodb_stats_on_metadata = 0

    #innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
    #innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
    # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
    # the overall load produced by MySQL/MariaDB.
    innodb_read_io_threads = 64
    innodb_write_io_threads = 64

    # MyISAM Settings
    query_cache_limit = 4M # UPD
    @@ -109,8 +114,8 @@ interactive_timeout = 180
    wait_timeout = 180

    # Buffer Settings
    join_buffer_size = 3M # UPD
    read_buffer_size = 2M # UPD
    join_buffer_size = 4M # UPD
    read_buffer_size = 3M # UPD
    read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    @@ -119,9 +124,9 @@ sort_buffer_size = 4M # UPD
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 8000 # UPD
    table_open_cache = 8000 # UPD
    open_files_limit = 24000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
    table_definition_cache = 10000 # UPD
    table_open_cache = 10000 # UPD
    open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
    # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
    # In systemd managed systems this limit must also be set in:
    # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
  13. @fevangelou fevangelou revised this gist Jul 9, 2018. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # === Updated May 2018 ===
    # === Updated July 2018 ===
    #
    # The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    @@ -105,8 +105,8 @@ back_log = 512
    thread_cache_size = 100
    thread_stack = 192K

    interactive_timeout = 60
    wait_timeout = 60
    interactive_timeout = 180
    wait_timeout = 180

    # Buffer Settings
    join_buffer_size = 3M # UPD
  14. @fevangelou fevangelou revised this gist May 6, 2018. 1 changed file with 0 additions and 5 deletions.
    5 changes: 0 additions & 5 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -140,11 +140,6 @@ long_query_time = 5
    slow_query_log = 0 # Disabled for production
    slow_query_log_file = /var/lib/mysql/mysql_slow.log

    # Binary Logging
    expire_logs_days = 7
    log_bin = var/lib/mysql/mysql_bin
    sync_binlog = 1

    [mysqldump]
    # Variable reference
    # For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
  15. @fevangelou fevangelou revised this gist May 2, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -105,8 +105,8 @@ back_log = 512
    thread_cache_size = 100
    thread_stack = 192K

    interactive_timeout = 90
    wait_timeout = 90
    interactive_timeout = 60
    wait_timeout = 60

    # Buffer Settings
    join_buffer_size = 3M # UPD
  16. @fevangelou fevangelou revised this gist May 2, 2018. No changes.
  17. @fevangelou fevangelou revised this gist May 2, 2018. 1 changed file with 4 additions and 6 deletions.
    10 changes: 4 additions & 6 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -26,13 +26,11 @@
    # the first time you apply this configuration:
    #
    # $ rm -rvf /var/lib/mysql/ib_logfile*
    # $ touch /var/lib/mysql/mysql.sock
    # $ touch /var/lib/mysql/mysql.pid
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ service mysql restart
    #
    # or use the shorthand command:
    # $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; service mysql restart
    # $ rm -rvf /var/lib/mysql/ib_logfile*; chown -R mysql:mysql /var/lib/mysql; service mysql restart
    #
    # 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
    # properly configured. A good example of a "clean" /etc/hosts file is something like this:
    @@ -57,7 +55,7 @@

    [mysql]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    socket = /var/run/mysqld/mysqld.sock

    [mysqld]
    # Required Settings
    @@ -67,11 +65,11 @@ bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote
    datadir = /var/lib/mysql
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    pid_file = /var/lib/mysql/mysql.pid
    pid_file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip_external_locking
    skip_name_resolve
    socket = /var/lib/mysql/mysql.sock
    socket = /var/run/mysqld/mysqld.sock
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES # Enable for b/c with databases
    # using null dates
    tmpdir = /tmp
  18. @fevangelou fevangelou revised this gist May 2, 2018. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -72,6 +72,8 @@ port = 3306
    skip_external_locking
    skip_name_resolve
    socket = /var/lib/mysql/mysql.sock
    #sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES # Enable for b/c with databases
    # using null dates
    tmpdir = /tmp
    user = mysql

  19. @fevangelou fevangelou revised this gist May 2, 2018. 1 changed file with 115 additions and 93 deletions.
    208 changes: 115 additions & 93 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,15 @@
    # Optimized MySQL configuration by Fotis Evangelou - Updated July 2017
    # Optimized my.cnf configuration for MySQL/MariaSQL
    #
    # The settings provided below are a starting point for a 4GB - 8GB RAM server with 4 CPU cores.
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # === Updated May 2018 ===
    #
    # The settings provided below are a starting point for a 2GB - 4GB RAM server with 2-4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    # RAM and disk I/O usage.
    # The settings marked with a specific comment or the word "UPD" after the value
    # should be adjusted for your system by using MySQL DB diagnostics tools like:
    # http://mysqltuner.com/
    # should be adjusted for your system by using database diagnostics tools like:
    # https://github.com/major/MySQLTuner-perl
    # or
    # https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6)
    #
    @@ -14,119 +18,137 @@
    #
    #
    # THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING
    # If MySQL cannot start or restart (most probably), then perform the following actions.
    # If any terminal commands are mentioned, make sure you execute them as "root" user:
    # If any terminal commands are mentioned, make sure you execute them as "root" user.
    # If MySQL cannot start or restart, then perform the following actions.
    #
    # 1. If the server had the stock MySQL configuration and you addded or updated any
    # "innodb-log-*" settings (as suggested below), then execute these commands ONLY
    # 1. If the server had the stock database configuration and you added or updated any
    # "innodb_log_*" settings (as suggested below), then execute these commands ONLY
    # the first time you apply this configuration:
    #
    # $ rm -rvf /var/lib/mysql/ib_logfile*
    # $ touch /var/lib/mysql/mysql.sock
    # $ touch /var/lib/mysql/mysql.pid
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ service mysql restart
    #
    # or use the shorthand command:
    # $ rm -rvf /var/lib/mysql/ib_logfile*; touch /var/lib/mysql/mysql.sock; touch /var/lib/mysql/mysql.pid; chown -R mysql:mysql /var/lib/mysql; service mysql restart
    #
    # 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
    # properly configured. A good example of a "clean" /etc/hosts file is something like this:
    #
    # 127.0.0.1 localhost localhost.localdomain
    # ::1 localhost localhost.localdomain
    # 1.2.3.4 hostname.domain.tld hostname
    # 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
    # ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
    # 1.2.3.4 hostname.domain.tld hostname # Replace accordingly!
    #
    # Finally restart MySQL:
    # Finally restart the database service:
    #
    # $ service mysql restart
    #
    # 3. If the database service cannot restart even after the first 2 steps, make sure the database data folder
    # (common for either MySQL or MariaDB) "/var/lib/mysql" is owned by the "mysql" user AND group.
    # Additionally, the folder itself can have 0751 or 0755 file permissions. To fix it, simply do this:
    # $ chown -R mysql:mysql /var/lib/mysql
    # $ chmod 0755 /var/lib/mysql
    #
    # Finally restart the database service:
    #
    # $ service mysql restart

    [client]
    [mysql]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0
    socket = /var/lib/mysql/mysql.sock

    [mysqld]
    # Basic
    # Required Settings
    basedir = /usr
    bind-address = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance
    bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote servers to connect to this server's
    # database instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max-allowed-packet = 128M
    max-connect-errors = 1000000
    pid-file = /var/run/mysqld/mysqld.pid
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    pid_file = /var/lib/mysql/mysql.pid
    port = 3306
    skip-external-locking
    skip-name-resolve
    socket = /var/run/mysqld/mysqld.sock
    skip_external_locking
    skip_name_resolve
    socket = /var/lib/mysql/mysql.sock
    tmpdir = /tmp
    user = mysql

    # InnoDB Settings
    default-storage-engine = InnoDB
    innodb-buffer-pool-instances = 2 # Use 1 instance per 1GB of InnoDB pool size
    innodb-buffer-pool-size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb-file-per-table = 1
    innodb-flush-log-at-trx-commit = 0
    innodb-flush-method = O_DIRECT
    #innodb-io-capacity = 1000
    #innodb-io-capacity_max = 3000
    innodb-log-buffer-size = 16M
    innodb-log-file-size = 128M
    #innodb-max-dirty-pages-pct = 0
    #innodb-thread-concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better contain CPU usage
    # E.g. if your system has 8 CPUs, try 6 or 7 and check the overall load from MySQL.

    # MyISAM Query Cache Settings
    query-cache-limit = 4M # UPD
    query-cache-size = 48M # UPD
    query-cache-type = 1

    key-buffer-size = 48M # UPD

    low-priority-updates = 1
    concurrent-insert = 2

    # Common
    max-connections = 100 # UPD
    back-log = 512

    wait-timeout = 90
    interactive-timeout = 90

    join-buffer-size = 2M # UPD
    read-buffer-size = 2M # UPD
    read-rnd-buffer-size = 4M # UPD
    sort-buffer-size = 4M # UPD

    thread-cache-size = 100 # UPD (most of the times you probably won't need to change this)
    thread-stack = 192K

    max-heap-table-size = 128M
    tmp-table-size = 128M

    table-definition-cache = 8000 # UPD
    table-open-cache = 8000 # UPD
    #table-open-cache-instances = 16 # For MySQL 5.7+ only
    open-files-limit = 24000 # UPD

    ft-min-word-len = 3 # Minimum length of words to be indexed for search results

    expire-logs-days = 7
    #log-bin
    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    long-query-time = 8
    max-binlog-size = 100M
    #server-id = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    #sync-binlog = 0
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 1 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_size = 1G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 128M
    #innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
    # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
    # the overall load produced by MySQL/MariaDB.

    # MyISAM Settings
    query_cache_limit = 4M # UPD
    query_cache_size = 48M # UPD
    query_cache_type = 1

    key_buffer_size = 48M # UPD

    low_priority_updates = 1
    concurrent_insert = 2

    # Connection Settings
    max_connections = 100 # UPD

    back_log = 512
    thread_cache_size = 100
    thread_stack = 192K

    interactive_timeout = 90
    wait_timeout = 90

    # Buffer Settings
    join_buffer_size = 3M # UPD
    read_buffer_size = 2M # UPD
    read_rnd_buffer_size = 4M # UPD
    sort_buffer_size = 4M # UPD

    # Table Settings
    # In systemd managed systems like Ubuntu 16.04 or CentOS 7, you need to perform an extra action for table_open_cache & open_files_limit
    # to be overriden (also see comment next to open_files_limit).
    # E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
    # and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
    table_definition_cache = 8000 # UPD
    table_open_cache = 8000 # UPD
    open_files_limit = 24000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
    # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
    # In systemd managed systems this limit must also be set in:
    # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
    # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

    max_heap_table_size = 128M
    tmp_table_size = 128M

    # Search Settings
    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    # Logging
    log_error = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes = 1
    long_query_time = 5
    slow_query_log = 0 # Disabled for production
    slow_query_log_file = /var/lib/mysql/mysql_slow.log

    # Binary Logging
    expire_logs_days = 7
    log_bin = var/lib/mysql/mysql_bin
    sync_binlog = 1

    [mysqldump]
    # Variable reference
    # For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    # For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
    quick
    quote-names
    max-allowed-packet = 16M

    [mysql]

    [isamchk]
    key-buffer-size = 16M
    quote_names
    max_allowed_packet = 64M
  20. @fevangelou fevangelou revised this gist Jul 15, 2017. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # Optimized MySQL configuration by Fotis Evangelou - Updated December 2016
    # Optimized MySQL configuration by Fotis Evangelou - Updated July 2017
    #
    # The settings provided below are a starting point for a 4GB - 8GB RAM server with 4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    @@ -50,7 +50,7 @@ basedir = /usr
    bind-address = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max-allowed-packet = 32M
    max-allowed-packet = 128M
    max-connect-errors = 1000000
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    @@ -89,8 +89,8 @@ concurrent-insert = 2
    max-connections = 100 # UPD
    back-log = 512

    wait-timeout = 300
    interactive-timeout = 300
    wait-timeout = 90
    interactive-timeout = 90

    join-buffer-size = 2M # UPD
    read-buffer-size = 2M # UPD
    @@ -103,18 +103,18 @@ thread-stack = 192K
    max-heap-table-size = 128M
    tmp-table-size = 128M

    table-definition-cache = 6000 # UPD
    table-open-cache = 6000 # UPD
    table-definition-cache = 8000 # UPD
    table-open-cache = 8000 # UPD
    #table-open-cache-instances = 16 # For MySQL 5.7+ only
    open-files-limit = 18000 # UPD
    open-files-limit = 24000 # UPD

    ft-min-word-len = 3 # Minimum length of words to be indexed for search results

    expire-logs-days = 7
    #log-bin
    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    long-query-time = 3
    long-query-time = 8
    max-binlog-size = 100M
    #server-id = 1
    slow-query-log = 1
  21. @fevangelou fevangelou revised this gist Dec 2, 2016. 1 changed file with 101 additions and 55 deletions.
    156 changes: 101 additions & 55 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,41 @@
    # Optimized MySQL configuration by Fotis Evangelou - Updated December 2016
    #
    # The settings provided below are a starting point for a 4GB - 8GB RAM server with 4 CPU cores.
    # If you have less or more resources available you should adjust accordingly to save CPU,
    # RAM and disk I/O usage.
    # The settings marked with a specific comment or the word "UPD" after the value
    # should be adjusted for your system by using MySQL DB diagnostics tools like:
    # http://mysqltuner.com/
    # or
    # https://launchpad.net/mysql-tuning-primer (supports MySQL up to v5.6)
    #
    #
    # Note that if there is NO comment after a setting value, then 99,9% of the times you won't need to adjust it.
    #
    #
    # THINGS TO DO AFTER YOU UPDATE MY.CNF - TROUBLESHOOTING
    # If MySQL cannot start or restart (most probably), then perform the following actions.
    # If any terminal commands are mentioned, make sure you execute them as "root" user:
    #
    # 1. If the server had the stock MySQL configuration and you addded or updated any
    # "innodb-log-*" settings (as suggested below), then execute these commands ONLY
    # the first time you apply this configuration:
    #
    # $ rm -rvf /var/lib/mysql/ib_logfile*
    # $ service mysql restart
    #
    # 2. If the setting "bind-address" is not commented out, then make sure the file /etc/hosts is
    # properly configured. A good example of a "clean" /etc/hosts file is something like this:
    #
    # 127.0.0.1 localhost localhost.localdomain
    # ::1 localhost localhost.localdomain
    # 1.2.3.4 hostname.domain.tld hostname
    #
    # Finally restart MySQL:
    #
    # $ service mysql restart
    #

    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    @@ -7,80 +45,88 @@ socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    # Basic
    basedir = /usr
    bind-address = 127.0.0.1
    bind-address = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max-allowed-packet = 16M
    myisam-recover = BACKUP # Comment out in Ubuntu 16.04+
    max-allowed-packet = 32M
    max-connect-errors = 1000000
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip-external-locking
    skip-name-resolve
    socket = /var/run/mysqld/mysqld.sock
    thread-stack = 192K
    tmpdir = /tmp
    user = mysql

    # ============================================================ #
    # =============== Custom server tweaks [start] =============== #
    # ============================================================ #

    default-storage-engine = InnoDB
    innodb-buffer-pool-size = 4G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb-buffer-pool-instances = 4 # If innodb-buffer-pool-size is 1GB or more, use the same absolute number for this value as well - so if innodb-buffer-pool-size is 4GB, use just 4. If innodb-buffer-pool-size is less than 1GB, just use 1 or disable entirely by adding a comment.
    innodb-file-per-table = 1
    innodb-flush-log-at-trx-commit = 0
    innodb-flush-method = O_DIRECT
    innodb-log-buffer-size = 8M
    innodb-log-file-size = 128M

    query-cache-limit = 3M
    query-cache-size = 48M
    query-cache-type = 1

    max-connections = 80
    max-user-connections = 60

    key-buffer-size = 48M

    wait-timeout = 300

    join-buffer-size = 3M
    sort-buffer-size = 3M
    read-buffer-size = 3M
    read-rnd-buffer-size = 3M

    max-heap-table-size = 128M
    tmp-table-size = 128M

    table-definition-cache = 5000
    table-open-cache = 5000
    open-files-limit = 15000
    # InnoDB Settings
    default-storage-engine = InnoDB
    innodb-buffer-pool-instances = 2 # Use 1 instance per 1GB of InnoDB pool size
    innodb-buffer-pool-size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb-file-per-table = 1
    innodb-flush-log-at-trx-commit = 0
    innodb-flush-method = O_DIRECT
    #innodb-io-capacity = 1000
    #innodb-io-capacity_max = 3000
    innodb-log-buffer-size = 16M
    innodb-log-file-size = 128M
    #innodb-max-dirty-pages-pct = 0
    #innodb-thread-concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better contain CPU usage
    # E.g. if your system has 8 CPUs, try 6 or 7 and check the overall load from MySQL.

    # MyISAM Query Cache Settings
    query-cache-limit = 4M # UPD
    query-cache-size = 48M # UPD
    query-cache-type = 1

    key-buffer-size = 48M # UPD

    low-priority-updates = 1
    concurrent-insert = 2

    # Common
    max-connections = 100 # UPD
    back-log = 512

    wait-timeout = 300
    interactive-timeout = 300

    join-buffer-size = 2M # UPD
    read-buffer-size = 2M # UPD
    read-rnd-buffer-size = 4M # UPD
    sort-buffer-size = 4M # UPD

    thread-cache-size = 100 # UPD (most of the times you probably won't need to change this)
    thread-stack = 192K

    low-priority-updates = 1
    concurrent-insert = 2
    thread-cache-size = 40
    max-heap-table-size = 128M
    tmp-table-size = 128M

    ft-min-word-len = 3 # Minimum length of words to be indexed for search results
    table-definition-cache = 6000 # UPD
    table-open-cache = 6000 # UPD
    #table-open-cache-instances = 16 # For MySQL 5.7+ only
    open-files-limit = 18000 # UPD

    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long-query-time = 5
    expire-logs-days = 7
    max-binlog-size = 100M
    ft-min-word-len = 3 # Minimum length of words to be indexed for search results

    # ============================================================ #
    # ============== Custom server tweaks [finish] =============== #
    # ============================================================ #
    expire-logs-days = 7
    #log-bin
    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    long-query-time = 3
    max-binlog-size = 100M
    #server-id = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    #sync-binlog = 0

    [mysqldump]
    quick
    quote-names
    max-allowed-packet = 16M
    max-allowed-packet = 16M

    [mysql]

    [isamchk]
    key-buffer-size = 16M
    key-buffer-size = 16M
  22. @fevangelou fevangelou revised this gist Jul 21, 2016. 1 changed file with 53 additions and 63 deletions.
    116 changes: 53 additions & 63 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,85 +1,75 @@
    # Optimized MySQL configuration by Fotis Evangelou - Updated Jan 2016
    #
    # The settings provided below are a starting point for a 4GB-8GB RAM server with 4 CPU cores.
    # If you have less or more resources available you MUST adjust accordingly to save CPU, RAM and disk I/O usage.
    # To fine tune these settings for your system, use MySQL DB diagnostics tools like:
    # https://launchpad.net/mysql-tuning-primer
    # or
    # http://blog.mysqltuner.com/download/
    # Note that if there is no comment beside a setting, then you don't need to adjust it.

    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    basedir = /usr
    bind-address = 127.0.0.1 # Comment this line if you want remote clients to connect to your MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max_allowed_packet = 16M
    myisam-recover = BACKUP
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    basedir = /usr
    bind-address = 127.0.0.1
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max-allowed-packet = 16M
    myisam-recover = BACKUP # Comment out in Ubuntu 16.04+
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip-external-locking
    socket = /var/run/mysqld/mysqld.sock
    thread_stack = 192K
    tmpdir = /tmp
    user = mysql
    socket = /var/run/mysqld/mysqld.sock
    thread-stack = 192K
    tmpdir = /tmp
    user = mysql

    # ============================================================ #
    # =============== Custom server tweaks [start] =============== #
    # ============================================================ #

    default_storage_engine = InnoDB
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M # Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.
    default-storage-engine = InnoDB
    innodb-buffer-pool-size = 4G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
    innodb-buffer-pool-instances = 4 # If innodb-buffer-pool-size is 1GB or more, use the same absolute number for this value as well - so if innodb-buffer-pool-size is 4GB, use just 4. If innodb-buffer-pool-size is less than 1GB, just use 1 or disable entirely by adding a comment.
    innodb-file-per-table = 1
    innodb-flush-log-at-trx-commit = 0
    innodb-flush-method = O_DIRECT
    innodb-log-buffer-size = 8M
    innodb-log-file-size = 128M

    query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_type = 1
    query-cache-limit = 3M
    query-cache-size = 48M
    query-cache-type = 1

    max_connections = 80 # Total connection limit for all MySQL users. Adjusting this too high will result in excess resource usage even if it's not required. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    max_user_connections = 60 # Per user connection limit
    max-connections = 80
    max-user-connections = 60

    key_buffer_size = 48M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    key-buffer-size = 48M

    wait_timeout = 300 # Using 300 secs for maintaining connections keeps the entire connections count low, thus decreasing resource consumption.
    wait-timeout = 300

    join_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    sort_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_rnd_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    join-buffer-size = 3M
    sort-buffer-size = 3M
    read-buffer-size = 3M
    read-rnd-buffer-size = 3M

    max_heap_table_size = 128M # According to Percona, these values do not affect performance. Percona sets those to 32M. 128M is a reasonable value though.
    tmp_table_size = 128M # See above.
    max-heap-table-size = 128M
    tmp-table-size = 128M

    table_definition_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table_open_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    open_files_limit = 10000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table-definition-cache = 5000
    table-open-cache = 5000
    open-files-limit = 15000

    low_priority_updates = 1
    concurrent_insert = 2
    thread_cache_size = 40 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    thread_concurrency = 8 # Set to twice the number of cores (2 x 4 cores in this example)
    low-priority-updates = 1
    concurrent-insert = 2
    thread-cache-size = 40

    ft_min_word_len = 3 # Minimum length of words to be indexed for search results
    ft-min-word-len = 3 # Minimum length of words to be indexed for search results

    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long_query_time = 5
    expire_logs_days = 7
    max_binlog_size = 100M
    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long-query-time = 5
    expire-logs-days = 7
    max-binlog-size = 100M

    # ============================================================ #
    # ============== Custom server tweaks [finish] =============== #
    @@ -88,9 +78,9 @@ max_binlog_size = 100M
    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M
    max-allowed-packet = 16M

    [mysql]

    [isamchk]
    key_buffer = 16M
    key-buffer-size = 16M
  23. @fevangelou fevangelou revised this gist Jan 3, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -41,7 +41,7 @@ innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M # Delete the ib_logdata* files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.
    innodb_log_file_size = 128M # Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.

    query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
  24. @fevangelou fevangelou revised this gist Jan 3, 2016. 1 changed file with 53 additions and 53 deletions.
    106 changes: 53 additions & 53 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -9,77 +9,77 @@
    # Note that if there is no comment beside a setting, then you don't need to adjust it.

    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    basedir = /usr
    bind-address = 127.0.0.1 # Comment this line if you want remote clients to connect to your MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max_allowed_packet = 16M
    myisam-recover = BACKUP
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    basedir = /usr
    bind-address = 127.0.0.1 # Comment this line if you want remote clients to connect to your MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max_allowed_packet = 16M
    myisam-recover = BACKUP
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip-external-locking
    socket = /var/run/mysqld/mysqld.sock
    thread_stack = 192K
    tmpdir = /tmp
    user = mysql
    socket = /var/run/mysqld/mysqld.sock
    thread_stack = 192K
    tmpdir = /tmp
    user = mysql

    # ============================================================ #
    # =============== Custom server tweaks [start] =============== #
    # ============================================================ #

    default_storage_engine = InnoDB
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M # Delete the ib_logdata* files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.
    default_storage_engine = InnoDB
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M # Delete the ib_logdata* files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.

    query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_type = 1
    query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_type = 1

    max_connections = 80 # Total connection limit for all MySQL users. Adjusting this too high will result in excess resource usage even if it's not required. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    max_user_connections = 60 # Per user connection limit
    max_connections = 80 # Total connection limit for all MySQL users. Adjusting this too high will result in excess resource usage even if it's not required. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    max_user_connections = 60 # Per user connection limit

    key_buffer_size = 48M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    key_buffer_size = 48M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    wait_timeout = 300 # Using 300 secs for maintaining connections keeps the entire connections count low, thus decreasing resource consumption.
    wait_timeout = 300 # Using 300 secs for maintaining connections keeps the entire connections count low, thus decreasing resource consumption.

    join_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    sort_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_rnd_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    join_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    sort_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_rnd_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    max_heap_table_size = 128M # According to Percona, these values do not affect performance. Percona sets those to 32M. 128M is a reasonable value though.
    tmp_table_size = 128M # See above.
    max_heap_table_size = 128M # According to Percona, these values do not affect performance. Percona sets those to 32M. 128M is a reasonable value though.
    tmp_table_size = 128M # See above.

    table_definition_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table_open_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    open_files_limit = 10000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table_definition_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table_open_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    open_files_limit = 10000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    low_priority_updates = 1
    concurrent_insert = 2
    thread_cache_size = 40 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    thread_concurrency = 8 # Set to twice the number of cores (2 x 4 cores in this example)
    low_priority_updates = 1
    concurrent_insert = 2
    thread_cache_size = 40 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    thread_concurrency = 8 # Set to twice the number of cores (2 x 4 cores in this example)

    ft_min_word_len = 3 # Minimum length of words to be indexed for search results
    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long_query_time = 5
    expire_logs_days = 7
    max_binlog_size = 100M
    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long_query_time = 5
    expire_logs_days = 7
    max_binlog_size = 100M

    # ============================================================ #
    # ============== Custom server tweaks [finish] =============== #
    @@ -88,9 +88,9 @@ max_binlog_size = 100M
    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M
    max_allowed_packet = 16M

    [mysql]

    [isamchk]
    key_buffer = 16M
    key_buffer = 16M
  25. @fevangelou fevangelou revised this gist Jan 3, 2016. No changes.
  26. @fevangelou fevangelou revised this gist Jan 3, 2016. No changes.
  27. @fevangelou fevangelou revised this gist Jan 3, 2016. 1 changed file with 94 additions and 72 deletions.
    166 changes: 94 additions & 72 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,74 +1,96 @@
    # The settings provided below are ideal for a 4GB-8GB RAM server.
    # You can adjust values accordingly for higher or lower spec systems, but generally,
    # it's an almost "one size, fits all" setup.
    # Use DB diagnostics tools like:
    # https://launchpad.net/mysql-tuning-primer or http://blog.mysqltuner.com/download/
    # to get more insight on your MySQL DB's resource usage and more...
    # Special thanks to Yves Trudeau (Percona) for his valuable help on compiling this setup.
    # Optimized MySQL configuration by Fotis Evangelou - Updated Jan 2016
    #
    # The settings provided below are a starting point for a 4GB-8GB RAM server with 4 CPU cores.
    # If you have less or more resources available you MUST adjust accordingly to save CPU, RAM and disk I/O usage.
    # To fine tune these settings for your system, use MySQL DB diagnostics tools like:
    # https://launchpad.net/mysql-tuning-primer
    # or
    # http://blog.mysqltuner.com/download/
    # Note that if there is no comment beside a setting, then you don't need to adjust it.

    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    default_storage_engine=InnoDB # Options are MyISAM & InnoDB. Prefer the latter

    query_cache_limit=2M
    query_cache_size=64M
    query_cache_type=1

    # Connections: If we want 100 connections spawned across 1 user only (max_user_connections),
    # leave 20-50 more for system processes.
    # If user connections were 200 and we had 2 users,
    # the max_connections value would be something between 220-250.
    # Increasing the connections does not guarantee better performance, but it DOES guarantee additional memory usage.
    max_connections=120
    max_user_connections=100

    wait_timeout=300 # Time in seconds to keep active connections. The default is 28800 which hogs the connection limits on high traffic sites. 300 is a reasonable value especially on split web/db server setups.

    # Key buffers: Sufficient at 64M for most cases,
    # increase if mysql-tuning-primer tells you to
    key_buffer=64M # Deprecated in MySQL 5.6
    key_buffer_size=64M # Replaces "key_buffer" for newer MySQL versions

    join_buffer_size=1M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M

    max_heap_table_size=128M
    tmp_table_size=128M

    table_cache=500 # Default is 64, but it's too low - for MySQL 5.6, use "table_open_cache"
    table_definition_cache=500 # Should be the same as table_cache
    open_files_limit=1500 # Should be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage (default is 4250, which is high enough)

    # InnoDB Settings
    innodb_buffer_pool_size=1G # Use up to 70-80% of RAM. Also check if /proc/sys/vm/swappiness is set to 0
    innodb_file_per_table=1
    innodb_flush_log_at_trx_commit=0
    innodb_flush_method=O_DIRECT
    innodb_log_buffer_size=8M
    innodb_log_file_size=128M

    # The minimum length of words to be indexed - Commented by default
    # Uncomment below to enable searching for three-character words
    ft_min_word_len=3

    # For MySQL 5.5 or older
    log-slow-queries=/var/log/mysql/slow.log
    long_query_time=3
    #log-queries-not-using-indexes

    # For MySQL 5.6
    #long_query_time=3
    #slow_query_log=1
    #slow_query_log_file=/var/log/mysql/slow.log

    # === Regarding the MySQL log file location ===
    # On Ubuntu servers, do this:
    # $ cd /var/log/mysql/; sudo touch slow.log; sudo chmod 0640 slow.log; sudo chown mysql:adm slow.log
    # otherwise MySQL may not be able to load the slow.log file

    # Advanced
    low_priority_updates=1
    concurrent_insert=2
    thread_cache_size=20 # More here: http://hashmysql.org/index.php?title=Tuning_System_Variables
    thread_concurrency=8 # set to 2X the number of processors in your machine for best performance
    basedir = /usr
    bind-address = 127.0.0.1 # Comment this line if you want remote clients to connect to your MySQL instance
    datadir = /var/lib/mysql
    lc-messages-dir = /usr/share/mysql
    max_allowed_packet = 16M
    myisam-recover = BACKUP
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    skip-external-locking
    socket = /var/run/mysqld/mysqld.sock
    thread_stack = 192K
    tmpdir = /tmp
    user = mysql

    # ============================================================ #
    # =============== Custom server tweaks [start] =============== #
    # ============================================================ #

    default_storage_engine = InnoDB
    innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M # Delete the ib_logdata* files in /var/lib/mysql whenever you change this, even the first time you set it up, as the default value is 8M. Don't go above 256M.

    query_cache_limit = 3M # The max size of queries to cache. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_size = 48M # The total query cache size for MyISAM tables. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    query_cache_type = 1

    max_connections = 80 # Total connection limit for all MySQL users. Adjusting this too high will result in excess resource usage even if it's not required. Use Tuning Primer or MySQL Tuner to adjust accordingly.
    max_user_connections = 60 # Per user connection limit

    key_buffer_size = 48M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    wait_timeout = 300 # Using 300 secs for maintaining connections keeps the entire connections count low, thus decreasing resource consumption.

    join_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    sort_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    read_rnd_buffer_size = 2M # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    max_heap_table_size = 128M # According to Percona, these values do not affect performance. Percona sets those to 32M. 128M is a reasonable value though.
    tmp_table_size = 128M # See above.

    table_definition_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    table_open_cache = 3000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    open_files_limit = 10000 # Use Tuning Primer or MySQL Tuner to adjust accordingly.

    low_priority_updates = 1
    concurrent_insert = 2
    thread_cache_size = 40 # Use Tuning Primer or MySQL Tuner to adjust accordingly.
    thread_concurrency = 8 # Set to twice the number of cores (2 x 4 cores in this example)

    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    log-error = /var/lib/mysql/mysql_error.log
    log-queries-not-using-indexes = 1
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql_slow.log
    long_query_time = 5
    expire_logs_days = 7
    max_binlog_size = 100M

    # ============================================================ #
    # ============== Custom server tweaks [finish] =============== #
    # ============================================================ #

    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M

    [mysql]

    [isamchk]
    key_buffer = 16M
  28. @fevangelou fevangelou created this gist Nov 5, 2015.
    74 changes: 74 additions & 0 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    # The settings provided below are ideal for a 4GB-8GB RAM server.
    # You can adjust values accordingly for higher or lower spec systems, but generally,
    # it's an almost "one size, fits all" setup.
    # Use DB diagnostics tools like:
    # https://launchpad.net/mysql-tuning-primer or http://blog.mysqltuner.com/download/
    # to get more insight on your MySQL DB's resource usage and more...
    # Special thanks to Yves Trudeau (Percona) for his valuable help on compiling this setup.

    [mysqld]
    default_storage_engine=InnoDB # Options are MyISAM & InnoDB. Prefer the latter

    query_cache_limit=2M
    query_cache_size=64M
    query_cache_type=1

    # Connections: If we want 100 connections spawned across 1 user only (max_user_connections),
    # leave 20-50 more for system processes.
    # If user connections were 200 and we had 2 users,
    # the max_connections value would be something between 220-250.
    # Increasing the connections does not guarantee better performance, but it DOES guarantee additional memory usage.
    max_connections=120
    max_user_connections=100

    wait_timeout=300 # Time in seconds to keep active connections. The default is 28800 which hogs the connection limits on high traffic sites. 300 is a reasonable value especially on split web/db server setups.

    # Key buffers: Sufficient at 64M for most cases,
    # increase if mysql-tuning-primer tells you to
    key_buffer=64M # Deprecated in MySQL 5.6
    key_buffer_size=64M # Replaces "key_buffer" for newer MySQL versions

    join_buffer_size=1M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M

    max_heap_table_size=128M
    tmp_table_size=128M

    table_cache=500 # Default is 64, but it's too low - for MySQL 5.6, use "table_open_cache"
    table_definition_cache=500 # Should be the same as table_cache
    open_files_limit=1500 # Should be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage (default is 4250, which is high enough)

    # InnoDB Settings
    innodb_buffer_pool_size=1G # Use up to 70-80% of RAM. Also check if /proc/sys/vm/swappiness is set to 0
    innodb_file_per_table=1
    innodb_flush_log_at_trx_commit=0
    innodb_flush_method=O_DIRECT
    innodb_log_buffer_size=8M
    innodb_log_file_size=128M

    # The minimum length of words to be indexed - Commented by default
    # Uncomment below to enable searching for three-character words
    ft_min_word_len=3

    # For MySQL 5.5 or older
    log-slow-queries=/var/log/mysql/slow.log
    long_query_time=3
    #log-queries-not-using-indexes

    # For MySQL 5.6
    #long_query_time=3
    #slow_query_log=1
    #slow_query_log_file=/var/log/mysql/slow.log

    # === Regarding the MySQL log file location ===
    # On Ubuntu servers, do this:
    # $ cd /var/log/mysql/; sudo touch slow.log; sudo chmod 0640 slow.log; sudo chown mysql:adm slow.log
    # otherwise MySQL may not be able to load the slow.log file

    # Advanced
    low_priority_updates=1
    concurrent_insert=2
    thread_cache_size=20 # More here: http://hashmysql.org/index.php?title=Tuning_System_Variables
    thread_concurrency=8 # set to 2X the number of processors in your machine for best performance