Skip to content

Instantly share code, notes, and snippets.

@jens1o

jens1o/my.cnf

Forked from fevangelou/my.cnf
Created January 29, 2024 22:10
Download ZIP

Revisions

  1. @fevangelou fevangelou revised this gist Dec 16, 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 (on Ubuntu, CentOS, Almalinux etc. servers) ===
    # === Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers) ===
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
  2. @fevangelou fevangelou revised this gist Dec 16, 2021. No changes.
  3. @fevangelou fevangelou revised this gist Dec 16, 2021. 1 changed file with 17 additions and 11 deletions.
    28 changes: 17 additions & 11 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,8 @@
    # === Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS etc. servers) ===
    # === Optimized my.cnf configuration for MySQL/MariaSQL (on Ubuntu, CentOS, Almalinux etc. servers) ===
    #
    # by Fotis Evangelou, developer of Engintron (engintron.com)
    #
    # ~ Updated November 2021 ~
    # ~ Updated December 2021 ~
    #
    #
    # The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores.
    @@ -74,6 +74,7 @@ socket = /var/run/mysqld/mysqld.sock
    basedir = /usr
    bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections
    datadir = /var/lib/mysql
    #default_authentication_plugin = mysql_native_password # Enable in MySQL 8+ or MariaDB 10.6+ for backwards compatibility with common CMSs
    max_allowed_packet = 256M
    max_connect_errors = 1000000
    pid_file = /var/run/mysqld/mysqld.pid
    @@ -100,6 +101,7 @@ innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 1G
    innodb_sort_buffer_size = 4M # UPD - Defines how much data is read into memory for sorting operations before writing to disk (default is 1M / max is 64M)
    innodb_stats_on_metadata = 0
    #innodb_use_fdatasync = 1 # Only (!) for MySQL v8.0.26+

    @@ -147,7 +149,11 @@ wait_timeout = 180
    # More info at: https://mariadb.com/kb/en/aborting-statements/

    # === Buffer Settings ===
    innodb_sort_buffer_size = 2M # UPD
    # Handy tip for managing your database's RAM usage:
    # The following values should be treated carefully as they are added together and then multiplied by your "max_connections" value.
    # Other options will also add up to RAM consumption (e.g. tmp_table_size). So don't go switching your "join_buffer_size" to 1G, it's harmful & inefficient.
    # Use one of the database diagnostics tools mentioned at the top of this file to count your database's potential total RAM usage, so you know if you are within
    # reasonable limits. Remember that other services will require enough RAM to operate properly (like Apache or PHP-FPM), so set your limits wisely.
    join_buffer_size = 4M # UPD
    read_buffer_size = 3M # UPD
    read_rnd_buffer_size = 4M # UPD
    @@ -181,16 +187,16 @@ tmp_table_size = 128M # Use same value as max_heap_table_size
    # === Search Settings ===
    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    # === Logging ===
    # Note: Binary logging is enabled by default in MySQL 8+
    # Disable entirely or otherwise configure in the following settings

    #skip_log_bin # Disable bin log entirely
    #expire_logs_days = 1 # Keep logs for 1 day - Deprecated in MySQL 8+
    #binlog_expire_logs_seconds = 86400 # Keep logs for 1 day - Use on MySQL 8+ only (!)
    # === Binary Logging ===
    disable_log_bin = 1 # Binary logging disabled by default
    #log_bin # To enable binary logging, uncomment this line & only one of the following 2 lines
    # that corresponds to your actual MySQL/MariaDB version.
    # Remember to comment out the line with "disable_log_bin".
    #expire_logs_days = 1 # Keep logs for 1 day - For MySQL 5.x & MariaDB before 10.6 only
    #binlog_expire_logs_seconds = 86400 # Keep logs for 1 day (in seconds) - For MySQL 8+ & MariaDB 10.6+ only

    # === Error & Slow Query Logging ===
    log_error = /var/lib/mysql/mysql_error.log

    log_queries_not_using_indexes = 0 # Disabled on production
    long_query_time = 5
    slow_query_log = 0 # Disabled on production
  4. @fevangelou fevangelou revised this gist Nov 25, 2021. 1 changed file with 17 additions and 9 deletions.
    26 changes: 17 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 October 2021 ~
    # ~ Updated November 2021 ~
    #
    #
    # The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores.
    @@ -11,9 +11,9 @@
    # 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 (supports MySQL up to v8)
    # https://github.com/major/MySQLTuner-perl
    # or
    # https://github.com/RootService/tuning-primer (supports MySQL up to v5.7)
    # https://github.com/BMDan/tuning-primer.sh
    #
    # 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
    @@ -65,7 +65,6 @@
    #
    # ~ FIN ~


    [mysql]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    @@ -120,7 +119,7 @@ innodb_write_io_threads = 64
    #query_cache_size = 64M # UPD
    #query_cache_type = 1 # Enabled by default

    key_buffer_size = 32M # UPD
    key_buffer_size = 24M # UPD

    low_priority_updates = 1
    concurrent_insert = 2
    @@ -162,10 +161,19 @@ sort_buffer_size = 4M # 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
    # open files limit usually set in /etc/sysctl.conf and /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)
    # - /etc/systemd/system/mysql.service.d/override.conf (for MySQL 5.7+ in Ubuntu) or
    # - /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+ in CentOS) or
    # - /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
    # otherwise changing open_files_limit will have no effect.
    #
    # To edit the right file execute:
    # $ systemctl edit mysql (or mysqld or mariadb)
    # and set "LimitNOFILE=" to something like 100000 or more (depending on your system limits for MySQL)
    # or use "LimitNOFILE=infinity" for MariaDB only.
    # Finally merge the changes with:
    # $ systemctl daemon-reload; systemctl restart mysql (or mysqld or mariadb)

    max_heap_table_size = 128M # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
    tmp_table_size = 128M # Use same value as max_heap_table_size
    @@ -174,7 +182,7 @@ tmp_table_size = 128M # Use same value as max_heap_table_size
    ft_min_word_len = 3 # Minimum length of words to be indexed for search results

    # === Logging ===
    # Note: Binary logging is enabled by default
    # Note: Binary logging is enabled by default in MySQL 8+
    # Disable entirely or otherwise configure in the following settings

    #skip_log_bin # Disable bin log entirely
  5. @fevangelou fevangelou revised this gist Nov 11, 2021. 1 changed file with 23 additions and 11 deletions.
    34 changes: 23 additions & 11 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 February 2021 ~
    # ~ Updated October 2021 ~
    #
    #
    # The settings provided below are a starting point for a 8-16 GB RAM server with 4-8 CPU cores.
    @@ -11,7 +11,7 @@
    # 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
    # https://github.com/major/MySQLTuner-perl (supports MySQL up to v8)
    # or
    # https://github.com/RootService/tuning-primer (supports MySQL up to v5.7)
    #
    @@ -89,25 +89,29 @@ user = mysql
    # 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
    # Crappy SQL queries/schema? Go bold!
    #sql_mode = ""

    # === InnoDB Settings ===
    default_storage_engine = InnoDB
    innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size
    innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size - max is 64
    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 = 1G
    innodb_stats_on_metadata = 0
    #innodb_use_fdatasync = 1 # Only (!) for MySQL v8.0.26+

    #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
    #innodb_io_capacity = 1000 # Max is 2000
    #innodb_io_capacity = 2000 # Depends on the storage tech - use 2000 for SSD, more for NVMe
    #innodb_io_capacity_max = 4000 # Usually double the value of innodb_io_capacity

    # === MyISAM Settings ===
    # The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
    @@ -132,13 +136,13 @@ 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).
    #max_execution_time = 90000 # 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)
    #max_statement_time = 90 # 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/
    @@ -163,17 +167,25 @@ open_files_limit = 60000 # UPD - This can be 2x to 3x the table_o
    # /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
    max_heap_table_size = 128M # Increase to 256M or 512M if you have lots of temporary tables because of missing indices in JOINs
    tmp_table_size = 128M # Use same value as max_heap_table_size

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

    # === Logging ===
    # Note: Binary logging is enabled by default
    # Disable entirely or otherwise configure in the following settings

    #skip_log_bin # Disable bin log entirely
    #expire_logs_days = 1 # Keep logs for 1 day - Deprecated in MySQL 8+
    #binlog_expire_logs_seconds = 86400 # Keep logs for 1 day - Use on MySQL 8+ only (!)

    log_error = /var/lib/mysql/mysql_error.log
    log_queries_not_using_indexes = 1

    log_queries_not_using_indexes = 0 # Disabled on production
    long_query_time = 5
    slow_query_log = 0 # Disabled for production
    slow_query_log = 0 # Disabled on production
    slow_query_log_file = /var/lib/mysql/mysql_slow.log

    [mysqldump]
    @@ -182,4 +194,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 = 512M
    max_allowed_packet = 1024M
  6. @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)
    #
  7. @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
  8. @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
  9. @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
  10. @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)
  11. @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
  12. @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
  13. @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
  14. @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
  15. @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:
  16. @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

  17. @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
  18. @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
  19. @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
  20. @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
  21. @fevangelou fevangelou revised this gist May 2, 2018. No changes.
  22. @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
  23. @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

  24. @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
  25. @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
  26. @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
  27. @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
  28. @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.
  29. @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
  30. @fevangelou fevangelou revised this gist Jan 3, 2016. No changes.