Оптимизация MariaDB 10.5 тюнером MySQLTuner 2.5.1

1.

table_definition_cache =2000

2.

Он говорит, что надо переводить все таблицы в InnodB и даёт запросы, которые надо выполнить в БД

Можете еще подсказать, как в Shell,  IspManager.
Примерно написать запрос не к таблицам выборочно, а сразу ко всей базе данных сайта?

Ко всей БД это не применяется.

Вы можете выполнить запрос:

Он выдаст список уже готовых запросов и их выполните.

После 1 строки выдало:

-bash: syntax error near unexpected token `('    

Какой БАШ это запросы для mysql :)

Можете в phpmyadmin делать, чтобы нагляднее было.

Список большой получился, экспорт сделал в Эксель, вижу все строки, типа:

ALTER TABLE `oc_voucher` ENGINE=InnoDB;

Получилось 161 строка, вручную долго, как списком можно?

З,Ы.

Получилось, просто закинул весь список в SQL (кнопка в верхнем меню) и выполнились запросы.

Так вы весь список сразу и вставляйте. Не надо по 1 строчке вставлять.

Debian 11, оперативной памяти 6G

В итоге получился такой листинг, что еще можно поправить в конфиге БД?

 >>  MySQLTuner 2.5.1                                                                                                                                                        
         * Jean-Marie Renouard <jmrenouard@gmail.com>                                                                                                                        
         * Major Hayden <major@mhtx.net>                                                                                                                                     
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/                                                                                                   
 >>  Run with '--help' for additional options and output filtering                                                                                                           
                                                                                                                                                                             
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture


-------- Storage Engine Statistics -----------------------------------------------------------------                                                                         
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 427.9M (Tables: 1367)
[OK] Total fragmented tables: 0


[OK] Currently running supported MySQL version 10.5.21-MariaDB-0+deb11u1-log


-------- Log file Recommendations ------------------------------------------------------------------                                                                         
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (1M)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 MB
[OK] Log file /var/log/mysql/error.log is readable.
[!!] /var/log/mysql/error.log contains 6593 warning(s).
[!!] /var/log/mysql/error.log contains 579 error(s).
[--] 29 start(s) detected in /var/log/mysql/error.log
[--] 1) 2024-02-07 16:33:28 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 2) 2024-02-07 16:08:31 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3) 2024-02-07 14:31:07 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 4) 2024-02-07 14:28:01 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 5) 2024-02-07 13:30:20 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 6) 2024-02-07 12:54:44 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 7) 2024-02-07 12:44:18 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 8) 2024-02-07 11:57:38 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 9) 2024-02-07 11:47:22 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 10) 2024-02-07  9:37:48 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 18 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2024-02-07 16:33:27 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 2) 2024-02-07 16:08:30 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 3) 2024-02-07 14:30:16 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 4) 2024-02-07 14:27:14 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 5) 2024-02-07 12:54:43 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 6) 2024-02-07 12:44:17 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 7) 2024-02-07 11:57:37 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 8) 2024-02-07 11:47:21 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 9) 2024-02-07  0:22:09 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 10) 2024-02-06 23:59:58 0 [Note] /usr/sbin/mariadbd: Shutdown complete


-------- Analysis Performance Metrics --------------------------------------------------------------                                                                         
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.


-------- Views Metrics -----------------------------------------------------------------------------                                                                         


-------- Triggers Metrics --------------------------------------------------------------------------                                                                         


-------- Routines Metrics --------------------------------------------------------------------------                                                                         


-------- Security Recommendations ------------------------------------------------------------------                                                                         
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.


-------- CVE Security Recommendations --------------------------------------------------------------                                                                         
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION


-------- Performance Metrics -----------------------------------------------------------------------                                                                         
[--] Up for: 35m 59s (57K q [26.762 qps], 625 conn, TX: 118M, RX: 9M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 5.8G
[--] Max MySQL memory    : 4.9G
[--] Other process memory: 0B
[--] Total buffers: 4.4G global + 2.7M per thread (151 max threads)
[--] Performance_schema Max memory usage: 114M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.6G (78.55% of installed RAM)
[!!] Maximum possible memory usage: 4.9G (85.18% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (120/57K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Aborted connections: 0.32% (2/625)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 335
[!!] Temporary tables created on disk: 59% (2K on disk / 3K total)
[OK] Thread cache hit rate: 99% (5 created / 625 connections)
[OK] Table cache hit rate: 99% (61K hits / 62K requests)
[OK] table_definition_cache (2000) is greater than number of tables (1560)
[OK] Open file limit used: 0% (29/16K)
[OK] Table locks acquired immediately: 100% (743 immediate / 743 locks)


-------- Performance schema ------------------------------------------------------------------------                                                                         
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 114.2M
[--] Sys schema is not installed.


-------- ThreadPool Metrics ------------------------------------------------------------------------                                                                         
[--] ThreadPool stat is disabled.


-------- MyISAM Metrics ----------------------------------------------------------------------------                                                                         
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :5.0M
[--]  +-- KB Used Size :985.0K
[--]  +-- KB used :19.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....


-------- InnoDB Metrics ----------------------------------------------------------------------------                                                                         
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 4.0G ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 4.0G / 427.9M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5%): 512.0M * 1 / 4.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 32 for 1 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: 98.95% (1723822 hits / 1742085 total)
[!!] InnoDB Write Log efficiency: 132.24% (931 hits / 704 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1635 writes)


-------- Aria Metrics ------------------------------------------------------------------------------                                                                         
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[OK] Aria pagecache hit rate: 96.1% (40K cached / 1K reads)


-------- TokuDB Metrics ----------------------------------------------------------------------------                                                                         
[--] TokuDB is disabled.


-------- XtraDB Metrics ----------------------------------------------------------------------------                                                                         
[--] XtraDB is disabled.


-------- Galera Metrics ----------------------------------------------------------------------------                                                                         
[--] Galera is disabled.


-------- Replication Metrics -----------------------------------------------------------------------                                                                         
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server


-------- Recommendations ---------------------------------------------------------------------------                                                                         
General recommendations:                                                                                                                                                     
    Check warning line(s) in /var/log/mysql/error.log file                                                                                                                   
    Check error line(s) in /var/log/mysql/error.log file                                                                                                                     
    MySQL was started within the last 24 hours: recommendations may be inaccurate                                                                                            
    Reduce your overall MySQL memory footprint for system stability                                                                                                          
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.                                                                                  
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size                                                                
    Temporary table size is already large: reduce result set size                                                                                                            
    Reduce your SELECT DISTINCT queries without LIMIT clauses                                                                                                                
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB                                                                                  
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time                                                            
Variables to adjust:                                                                                                                                                         
    join_buffer_size (> 5.0K, or always use indexes with JOINs)                                                                                                              
    innodb_log_file_size should be (=1G) if possible, so InnoDB total log file size equals 25% of buffer pool size.                                                          
    innodb_log_buffer_size (> 64M)

Нижние параметры вроде, как незначительные.

Сам конфиг:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see

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

# this is only for the mysqld standalone daemon
[mysqld]
local-infile=0
innodb_file_per_table = 1

#
# * Basic Settings
#

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
skip-name-resolve       = ON

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#

key_buffer_size        = 5M
#max_allowed_packet     = 1G
#thread_stack           = 192K
#thread_cache_size      = 8
# 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_cache            = 64

table_definition_cache          = 2000
innodb_log_buffer_size                  = 64M
query_cache_size                = 0
query_cache_type                = 0
query_cache_limit               = 2M
join_buffer_size                = 5K
sort_buffer_size                = 2M
tmp_table_size                  = 256M
max_heap_table_size             = 256M
innodb_buffer_pool_size         = 4G
innodb_buffer_pool_instances    = 2
innodb_log_file_size            = 512M
performance_schema=ON


#
# * Logging and Replication
#

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 10
log_slow_verbosity     = query_plan,explain
log-queries-not-using-indexes
min_examined_row_limit = 1000

# 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
#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size        = 100M

#
# * SSL/TLS
#

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = 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
bind-address = 127.0.0.1
general-log = TRUE

#
# * 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!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G

# 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.5 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.5]

general_log_file       = /var/log/mysql/mysql.log

Тюнер учитывает только работу mysql (а на сервере ещё могут быть php, apache и т.д., которым тоже нужна RAM), к тому же значения там увеличиваются и до терабайта RAM

Вот только если у Вас баз на 10 Мб, какой смысл выделять буферы десятки гигабайтов

Советую гуглить параметры, которые предлагает менять тюнер, и уже самостоятельно рассчитывать значения

Просто копировать рекомендации тюнера - плохая идея

Самое интересное, что он будет бесконечно требовать увеличение параметров. Поэтому я лично уже много лет им не пользуюсь т.к. легче понять как это всё работает, чем бесконечно смотреть на информацию от этого скрипта, которая в 90% вообще бесполезная и просто мусор.