Bei der Verwendung von MySQL-Tuner mache ich mir etwas Sorgen über den maximalen Speicher, den SQL offenbar belegen möchte. Ich möchte SQL lediglich optimieren, damit es besser läuft, und selbst herausfinden, wie ich das mache.
Hier sind meine MySQLTuner-Ergebnisse;
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.32
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(141K)
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 246 warning(s).
[!!] /var/log/mysqld.log contains 46 error(s).
[--] 16 start(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:30:59.930120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2021-01-14T11:08:52.530228Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2021-01-14T11:06:44.798420Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2021-01-14T10:58:56.636812Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2021-01-14T10:58:49.998564Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2021-01-14T10:23:15.649280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2021-01-14T09:35:59.598495Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2021-01-14T09:35:24.591837Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2021-01-14T09:26:15.821136Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2021-01-14T08:23:56.658016Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:27:51.895559Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2021-01-14T11:08:49.145150Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2021-01-14T11:06:24.969477Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2021-01-14T10:58:53.419431Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2021-01-14T09:35:54.472640Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2021-01-08T08:38:07.365178Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2021-01-07T20:25:04.834240Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 525.3M (Tables: 446)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 13m 25s (63K q [79.404 qps], 358 conn, TX: 381M, RX: 20M)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is disabled
[--] Physical Memory : 2.8G
[--] Max MySQL memory : 38.0G
[--] Other process memory: 0B
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/63K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.00% (0/358)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 11
[!!] Temporary tables created on disk: 57% (3K on disk / 5K total)
[OK] Thread cache hit rate: 97% (10 created / 358 connections)
[!!] Table cache hit rate: 16% (863 open / 5K opened)
[OK] table_definition_cache(1400) is upper than number of tables(725)
[OK] Open file limit used: 0% (11/40K)
[OK] Table locks acquired immediately: 100% (160 immediate / 160 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 90.7% (118 cached / 11 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 40.0M/525.3M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (240 %): 48.0M * 2/40.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 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: 99.74% (7453089 hits/ 7472246 total)
[!!] InnoDB Write Log efficiency: 72.13% (18568 hits/ 25743 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7175 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
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.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64
Read this before increasing for MariaDB
This is MyISAM only table_cache scalability problem, InnoDB not affected.
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (40000) variable
should be greater than table_open_cache (2000)
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 (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
innodb_buffer_pool_size (>= 525.3M) if possible.
innodb_log_file_size should be (=5M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
Meine CNF-Datei ist einfach, da ich nicht alle verschiedenen Variablen verstehe.
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=41943040
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
bind-address=127.0.0.1
skip-name-resolve=1
performance_schema=ON
Irgendwelche Ideen oder Ratschläge?
Antwort1
Warum ist die maximale Speicherauslastung so hoch?
Hier sehen wir uns die Einzelheiten an:
Ihr MySQL verwendet die Standardzahl max_connections150 (wodurch einem Datenbankadministratorbenutzer tatsächlich diese Zahl plus eins für die Verbindung zur Verfügung steht; daher wird die Zahl 151 angezeigt).
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
Dies sagt uns, wie viel Pufferspeicherplatz verbraucht würde, wenn alle diese Verbindungen gleichzeitig genutzt würden. Wenn Sie keinen Taschenrechner zur Hand haben, ergibt das 38.903,1 M (38,0 G).
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
Sie haben bisher bei 10 gleichzeitigen Verbindungen das Maximum erreicht, also max_connectionskann die Anzahl wahrscheinlich deutlich gesenkt werden. Ich würde den Wert als Ausgangspunkt auf 20 setzen und ihn nur bei Bedarf von dort aus erhöhen.
[OK] Highest usage of available connections: 6% (10/151)
Beunruhigender ist jedoch die Frage, wie viel Speicher tatsächlich genutzt wurde.
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
Sofern Sie keine großen Binärblobs in die Datenbank und aus ihr heraus verschieben müssen (und wenn das der Fall ist, überdenken Sie bitte Ihr Anwendungsdesign), können Sie den Wert erheblich reduzieren max_allowed_packet. Der Standardwert ist 64 MB, und Sie könnten ihn dort einstellen, um fast 3/4 der Speichernutzung (sowohl tatsächlich als auch potenziell) einzusparen. Wenn Sie „Paket zu groß“-Fehler erhalten, können Sie den Wert erhöhen, aber dazu wird RAM für jede offene Verbindung benötigt.
Antwort2
Wenn Sie Leistungsprobleme haben, besteht die Lösung wahrscheinlich in der Indizierung und/oder Neuformulierung von Abfragen. Wir empfehlen Ihnen, die langsamste Abfrage zu finden und uns zu beraten.
In der Zwischenzeit können Sie zwei Einstellungen für Ihren „kleinen“ 2,8 GB RAM anpassen:
innodb_buffer_pool_sizesollte etwa 500 M betragen.max_allowed_packet = 30M