Hello. I am using a 4 core ubuntu cloud server with 8gb and ssds. There I have only one database for a Magento store which is about 2GB size.
Last month after some changes I made it seems that database performing as it should.
After running mysqltuner.pl I have these suggestions:
Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (1024) variable should be greater than table_open_cache (400) Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB Thread pool size for InnoDB usage (128) Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) join_buffer_size (> 4.0M, or always use indexes with JOINs) table_open_cache (> 400) thread_pool_size between 16 and 36 for InnoDB usage key_buffer_size (> 112.5M)
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /dev/shm lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp skip-name-resolve=1 #skip-grant-tables log-error = /var/log/mysql/error.log # Recommended in standard MySQL setup #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # Disabling symbolic-links is recommended to prevent assorted security risks #symbolic-links=0 # # * Basic Settings ## # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = localhost #bind-address = 18.104.22.168 # # * Fine Tuning # key_buffer_size = 64M join_buffer_size = 4M max_allowed_packet = 128M thread_stack = 192K thread_cache_size = 128K thread_pool_size = 32M connect_timeout=320 # default 10 table_open_cache = 256M table_definition_cache = 256M sort_buffer_size = 16M max_heap_table_size = 64M tmp_table_size = 64M interactive_timeout = 3600 max_connections = 600 max_connect_errors = 100 #skip_name_resolve #skip_secure_auth wait_timeout = 28800 innodb_buffer_pool_size = 4096M myisam_sort_buffer_size = 128M innodb_log_file_size = 512M innodb_buffer_pool_instances = 4 innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 2 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP #max_connections = 100 ##table_open_cache = 3000 #thread_concurrency = 5 # # * Query Cache Configuration # ##query_cache_limit = 256M ##query_cache_size = 64M ##query_cache_type = 1
As you can see with these settings I get: MySQL’s maximum memory usage is dangerously high
Can anyone help how to better tune this please?