0

Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day. I am really not server administrator,

This is mysqltuner.pl result,

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 16.9G (Tables: 4633)
[--] Data in MyISAM tables: 13.3M (Tables: 150)
[--] Data in Aria tables: 4.1M (Tables: 1)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

[--] Up for: 14m 59s (9M q [10K qps], 7K conn, TX: 5G, RX: 2G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory     : 125.8G
[--] Max MySQL memory    : 367.5G
[--] Other process memory: 0B
[--] Total buffers: 52.4G global + 322.6M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 75.8G (60.21% of installed RAM)
[!!] Maximum possible memory usage: 367.5G (292.12% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (22/9M)
[OK] Highest usage of available connections: 7% (74/1000)
[OK] Aborted connections: 0.00% (0/7652)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 48.7% (8M cached / 16M selects)
[!!] Query cache prunes per day: 27571979
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 135K sorts)
[!!] Joins performed without indexes: 5959
[OK] Temporary tables created on disk: 16% (27K on disk / 164K total)
[OK] Thread cache hit rate: 99% (74 created / 7K connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (7000) is greater than number of tables (4945)
[OK] Open file limit used: 1% (364/32K)
[OK] Table locks acquired immediately: 100% (884 immediate / 884 locks)
[OK] Binlog cache memory access: 99.45% (5746 Memory / 5778 Total)-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/5.9M
[!!] Read Key buffer hit rate: 91.5% (317 cached / 27 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 50.0G / 16.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 7.0G * 2/50.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 50
[--] Number of InnoDB Buffer Pool Chunk: 400 for 50 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (693596513 hits / 694467712 total)
[OK] InnoDB Write Log efficiency: 99.80% (6253500 hits / 6265916 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12416 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/40.0K
[OK] Aria pagecache hit rate: 95.4% (151K cached / 6K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 4 server(s).
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] No replication setup for this server or replication not started.
Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
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_size (> 64M)
    join_buffer_size (> 256.0M, or always use indexes with JOINs)
    performance_schema=ON
    key_buffer_size (~ 24M)

I see Maximum possible memory usage: 367.5G (292.12% of installed RAM) is very high but dont find how to reduce it, Also if you can help me to configure my.cnf better for have real better performance i will be really greatfull :)

edit adding .cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
skip-name-resolve
sql_mode        = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

#
# * Fine Tuning
#
open_files_limit               = 20240
sort_buffer_size               = 2097144
table_cache            = 7000
table_open_cache               = 7000
table_definition_cache         = 7000
tmp_table_size                 = 128M
max_heap_table_size            = 128M
max_connections                = 1000
connect_timeout                = 10
wait_timeout                   = 120
interactive_timeout            = 1800
max_allowed_packet             = 64M
bulk_insert_buffer_size        = 16M
thread_stack                   = 256K
thread_cache_size              = 128
thread_pool_size               = 24
log_warnings                   = 1

# MyISAM
key_buffer_size                = 128M
join_buffer_size               = 256M
myisam_recover_options         = BACKUP
myisam_repair_threads          = 1
myisam_sort_buffer_size        = 512M
concurrent_insert              = 2
read_buffer_size               = 131072
read_rnd_buffer_size           = 262144

#
# * Query Cache Configuration
#
query_cache_type    = 1
query_cache_limit      = 32M
query_cache_size        = 64M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file       = /var/log/mysql/mysql.log
general_log            = 0
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
slow_query_log         = on
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 4
#log_slow_rate_limit    = 1000
#log_slow_verbosity     = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id              = 1
auto_increment_increment       = 1
auto_increment_offset          = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 2
max_binlog_size        = 100M
binlog_format                  = ROW
sync_binlog                    = 0
#binlog_do_db           = include_database_name
#binlog_ignore_db       = exclude_database_name

#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

## InnoDB tuning

innodb_file_per_table          = on
innodb_buffer_pool_size        = 50G
innodb_buffer_pool_instances   = 50
innodb_log_file_size           = 7G
innodb_log_buffer_size         = 2G
innodb_thread_concurrency      = 16
innodb_read_io_threads         = 16
innodb_write_io_threads        = 16
innodb_open_files              = 1000
innodb_io_capacity             = 800
innodb_io_capacity_max         = 2000
innodb_lock_wait_timeout       = 60
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = true
innodb_use_native_aio          = 1
innodb_flush_log_at_trx_commit = 2

default_storage_engine         = InnoDB

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]

Adding Top command but dont know how to fix :)

with top commande i have this

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND   
 4162 mysql     20   0   64,4g  17,4g  10608 S 102,6  13,9 941:18.01 mysqld    
 9985 www-fou+  20   0  395960 221516 126856 R 100,0   0,2  14:55.02 php-fpm7.1
 1593 www-fou+  20   0  381424 203876 123500 R  99,7   0,2  11:24.43 php-fpm7.1
19099 www-fou+  20   0  381788 204924 124484 S  93,4   0,2  10:35.16 php-fpm7.1
24495 www-fou+  20   0  383684 212224 129636 R  69,1   0,2  11:37.65 php-fpm7.1
15019 www-fou+  20   0  339196 127520  89412 S  66,4   0,1   2:51.54 php-fpm7.1
11226 www-fou+  20   0  361036 181036 121012 S  65,8   0,1   8:38.26 php-fpm7.1
13478 www-fou+  20   0  333628 157600 125252 S  12,2   0,1  12:15.50 php-fpm7.1
26195 www-fou+  20   0  343160 165036 123072 S   6,2   0,1  11:20.56 php-fpm7.1
22301 www-fou+  20   0  340908 148244 108692 S   3,0   0,1  12:58.10 php-fpm7.1
21292 www-data  20   0 2316000  23908   4088 S   1,6   0,0   0:30.03 apache2   
29112 www-fou+  20   0  330464 156296 126860 S   1,6   0,1  10:24.75 php-fpm7.1
 3229 root      20   0   12680   5084   2944 R   1,3   0,0   0:02.16 top       
18330 www-data  20   0 2249248  28736   3908 S   1,3   0,0   4:00.44 apache2   
31957 www-fou+  20   0  326216 150860 125900 S   1,3   0,1  12:48.35 php-fpm7.1
  892 www-fou+  20   0 1044388 195248  15528 S   1,0   0,1 766:53.93 node      
14357 www-fou+  20   0  331084 121516  91724 S   1,0   0,1   2:17.92 php-fpm7.1

Thank you,

Mathieu

2 Answers 2

1

The computation of "Maximum possible memory" is essentially impossible. The formula in that tool is pessimistic and should be ignored.

Some tips:

max_connections probably does not need to be 1000. See SHOW GLOBAL STATUS LIKE 'Max_used_connections'; to see how close to 1000 you got since the last restart.

innodb_buffer_pool_size is 50G on 128GB of RAM. That setting is low unless you have some memory-hungry apps on the same server. It is usually safe to set it took 70% of available RAM.

Don't use MyISAM; switch your tables to InnoDB. Then lower key_buffer_size to only 20M.

Unless you have a demonstrated use for the "query cache", turn it off. A lot of CPU is wasted by "Query cache prunes per day: 27571979".

I don't see any other settings that worry me.

0

I do not have a answer itself, but someting to look at besides the MySQL configuration): have you looked at the system processes, like using the "top" command, to see wich process is accounting for or to the 100% cpu-usage? It might or might not the MySQL process itself. There could be a looping Application process connected to the MySQL server Or Your server has a HD going to be bad and the system is struggeling to store data.

1
  • You have running a few PHP scripts also taking lots of cpu time. I do not know if they are respo9mnsib;le for the cpu load or that they are the "victum" of the MySQL being very busy. Do you know what those PHP scripts are doing? Like are they looping causing a heavy load on the MySQL server? Or is it a matter of missing indexes that slows down the queries the PHP scripts are executing?
    – Martie
    Apr 6, 2023 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.