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 = 94.237.44.193
#
# * 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?