Home / server / Questions / 977090
In Process
G. G.
Asked: 2019-07-30 00:31:56 +0800 CST

Tune Percona Server 5.7

  • 772

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. Percona server 5.7 After running mysqltuner.pl I have these suggestions:

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?

mysql

2 Answers

  1. Mysqltuner is pessimistic in the area of "max memory".

    8GB is probably fine for the settings you have. But, watch out for "swapping". If there is swapping, we need to lower some setting(s).

    (Move to dba.stackexchange.com if you need further advice.)

    • 0

  2. Suggestions for your my.cnf [mysqld] section

    join_buffer_size=2M  # from 4M requested per connection
thread_cache_size=100  # from 128K for CAP suggested in 5.7 REFMAN
thread_pool_size=32  # from 32M  a major difference
table_open_cache=1024  # from 256M that would be a LOT of open tables
table_definition_cache=512  # from 256M for shared table definitions all connections
sort_buffer_size=2M  # from 16M requested per connection
max_connect_errors=10  # from 100 why give a hacker/cracker so many attempts?
innodb_lru_scan_depth=100  # from 1024 to reduce CPU cycles used every second for function

    Disclaimer: I am the content author of website mentioned in my profile, Network profile and we have free Utility Scripts and more suggestions are available.

    • 0