-
-
Save pelligrag/cd15e18843aba439b85f74d7579df23c to your computer and use it in GitHub Desktop.
Optimized my.cnf configuration for MySQL/MariaDB (on Ubuntu, CentOS, Almalinux etc. servers)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [client] | |
| port = 3306 | |
| socket = /var/run/mysqld/mysqld.sock | |
| [mysqld_safe] | |
| socket = /var/run/mysqld/mysqld.sock | |
| nice = 0 | |
| [mysqld] | |
| 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 | |
| # ============================================================ # | |
| # =============== 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 | |
| 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 | |
| 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-size = 16M |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment