Not the answer you need?
Register and ask your own question!

Help for tuning Percona 5.7

giannisgzgiannisgz EntrantCurrent User Role Novice
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:
General recommendations:
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)

My.cnf settings are:
[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?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.