01.04.2022, 10:12
Die Basis von Opensimulator, MySQL, ist ja schnell installiert und läuft dann. Allerdings ist dann auch erst einmal alles. MySQL bietet als Datenbank jede Menge Stellschrauben, d.h. Konfigurationsparameter für Caches, die man justieren kann, um die Performanz des Systems auf der aktuellen Hardware zu optimieren und steigern.
Eine einfache Möglichkeit, um seine MySQL-Instanz zu tunen, ist das mysqltuner.pl Skript.
Der Download ist schnell getan:
wget mysqltuner.pl -O mysqltuner.pl
Damit das Skript Sinn macht, sollte die MySQL-Instanz mindestens 48 Stunden bereits in Betrieb sein. Der Hintergrund ist einfach, dass MySQL interne Statisken führt, und je mehr Daten darin vorhanden sind, desto mehr Daten hat das Skript, um Optimierungsempfehlungen zu geben.
Das Skript benötigt administrativen Zugriff auf einen MySQL-Server,, und ein Lauf sieht dann typischerweise so aus:
Die Empfehlungen unter "Recommendations" sind dann das, was man justieren kann. Hierbei gilt allerdings, dass man nicht alle blindlings übernehmen sollte, sondern auch noch im Hinterkopf beachten, was sonst noch auf dem System läuft. Nicht dass man der Datenbank dadurch allen Speicher zugesteht beispielsweise und dann andere Serverprozesse kaum noch etwas davon übrig haben.
Das Tool ist sicherlich kein Zaubermittel, um aus einer lahmen Krücke ein Rennpferd zu machen. Aber es ist eine wertvolle Hilfe, bei den Myriaden an Konfigurationsmöglichkeiten von MySQL die zu identifizieren, mit denen man doch einiges an Verbesserung der Performanz erreichen kann. Es liefert also wertvolle Hinweise, und es schadet sicher nicht, es einfach mal testen. Es ist schließlich Opensource, und vom Download bis Einsatz sind es keine zwei Minuten.
Eine einfache Möglichkeit, um seine MySQL-Instanz zu tunen, ist das mysqltuner.pl Skript.
Der Download ist schnell getan:
wget mysqltuner.pl -O mysqltuner.pl
Damit das Skript Sinn macht, sollte die MySQL-Instanz mindestens 48 Stunden bereits in Betrieb sein. Der Hintergrund ist einfach, dass MySQL interne Statisken führt, und je mehr Daten darin vorhanden sind, desto mehr Daten hat das Skript, um Optimierungsempfehlungen zu geben.
Das Skript benötigt administrativen Zugriff auf einen MySQL-Server,, und ein Lauf sieht dann typischerweise so aus:
Code:
>> MySQLTuner 1.9.8
* 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.5.10-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/mysqld.err exists
[--] Log file: /var/log/mysql/mysqld.err(4M)
[OK] Log file /var/log/mysql/mysqld.err is not empty
[OK] Log file /var/log/mysql/mysqld.err is smaller than 32 Mb
[OK] Log file /var/log/mysql/mysqld.err is readable.
[!!] /var/log/mysql/mysqld.err contains 14921 warning(s).
[!!] /var/log/mysql/mysqld.err contains 3797 error(s).
[--] 1 start(s) detected in /var/log/mysql/mysqld.err
[--] 1) 2022-03-11 13:18:26 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/log/mysql/mysqld.err
[--] 1) 2022-03-11 13:17:59 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MEMORY tables: 300.6K (Tables: 43)
[--] Data in MyISAM tables: 701.3M (Tables: 901)
[--] Data in InnoDB tables: 6.6G (Tables: 2257)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[!!] User ''@'kraftzwerg' is an anonymous account. Remove with DROP USER ''@'kraftzwerg';
[!!] User ''@'localhost' is an anonymous account. Remove with DROP USER ''@'localhost';
[!!] failed to execute: SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) FROM mysql.global_priv WHERE
user != ''
AND JSON_CONTAINS(Priv, '"mysql_native_password"', '$.plugin') AND JSON_CONTAINS(Priv, '""', '$.authentication_string')
AND NOT JSON_CONTAINS(Priv, 'true', '$.account_locked')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'sysloguser'@'localhost' has user name as password.
[!!] User 'marcsql7'@% does not specify hostname restrictions.
[!!] User 'nextcloud'@% does not specify hostname restrictions.
[!!] User 'oc_admin'@% does not specify hostname restrictions.
[!!] User 'oc_admin1'@% does not specify hostname restrictions.
[!!] User 'oc_admin2'@% does not specify hostname restrictions.
[!!] User 'oc_admin3'@% does not specify hostname restrictions.
[!!] User 'powerdns'@% does not specify hostname restrictions.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 20d 19h 28m 24s (27M q [15.112 qps], 502K conn, TX: 562G, RX: 5G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 11.7G
[--] Max MySQL memory : 3.2G
[--] Other process memory: 0B
[--] Total buffers: 1.3G global + 10.0M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.5G (13.19% of installed RAM)
[OK] Maximum possible memory usage: 3.2G (27.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (107/27M)
[OK] Highest usage of available connections: 14% (28/200)
[OK] Aborted connections: 0.00% (1/502890)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 41.2% (16M cached / 40M selects)
[!!] Query cache prunes per day: 33273
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 733K sorts)
[!!] Joins performed without indexes: 15236
[!!] Temporary tables created on disk: 65% (606K on disk / 931K total)
[OK] Thread cache hit rate: 99% (31 created / 502K connections)
[OK] Table cache hit rate: 96% (9M hits / 10M requests)
[!!] table_definition_cache(400) is lower than number of tables(3391)
[OK] Open file limit used: 2% (1K/80K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] Binlog cache memory access: 99.56% (578739 Memory / 581309 Total)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.8% (13.3M used / 64.0M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/91.7M
[OK] Read Key buffer hit rate: 97.4% (28M cached / 746K reads)
[!!] Write Key buffer hit rate: 80.2% (1M cached / 845K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/6.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 1/1.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 8 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: 100.00% (996150977 hits/ 996173258 total)
[!!] InnoDB Write Log efficiency: 272.73% (400556 hits/ 146871 total)
[OK] InnoDB log waits: 0.00% (0 waits / 547427 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 99.8% (233M cached / 574K 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/mysqld.err file
Check error line(s) in /var/log/mysql/mysqld.err file
Remove Anonymous User accounts - there are 2 anonymous accounts.
Set up a Secure Password for 'sysloguser'@'localhost' user: SET PASSWORD FOR 'sysloguser'@'localhost' = PASSWORD('secure_password');
Restrict Host for 'marcsql7'@'%' to 'marcsql7'@LimitedIPRangeOrLocalhost
RENAME USER 'marcsql7'@'%' TO 'marcsql7'@LimitedIPRangeOrLocalhost;
Restrict Host for 'nextcloud'@'%' to 'nextcloud'@LimitedIPRangeOrLocalhost
RENAME USER 'nextcloud'@'%' TO 'nextcloud'@LimitedIPRangeOrLocalhost;
Restrict Host for 'oc_admin'@'%' to 'oc_admin'@LimitedIPRangeOrLocalhost
RENAME USER 'oc_admin'@'%' TO 'oc_admin'@LimitedIPRangeOrLocalhost;
Restrict Host for 'oc_admin1'@'%' to 'oc_admin1'@LimitedIPRangeOrLocalhost
RENAME USER 'oc_admin1'@'%' TO 'oc_admin1'@LimitedIPRangeOrLocalhost;
Restrict Host for 'oc_admin2'@'%' to 'oc_admin2'@LimitedIPRangeOrLocalhost
RENAME USER 'oc_admin2'@'%' TO 'oc_admin2'@LimitedIPRangeOrLocalhost;
Restrict Host for 'oc_admin3'@'%' to 'oc_admin3'@LimitedIPRangeOrLocalhost
RENAME USER 'oc_admin3'@'%' TO 'oc_admin3'@LimitedIPRangeOrLocalhost;
Restrict Host for 'powerdns'@'%' to 'powerdns'@LimitedIPRangeOrLocalhost
RENAME USER 'powerdns'@'%' TO 'powerdns'@LimitedIPRangeOrLocalhost;
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Variables to adjust:
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 4.0M, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 3391 or -1 (autosizing if supported)
performance_schema=ON
key_buffer_size (~ 13M)
innodb_buffer_pool_size (>= 6.6G) if possible.Die Empfehlungen unter "Recommendations" sind dann das, was man justieren kann. Hierbei gilt allerdings, dass man nicht alle blindlings übernehmen sollte, sondern auch noch im Hinterkopf beachten, was sonst noch auf dem System läuft. Nicht dass man der Datenbank dadurch allen Speicher zugesteht beispielsweise und dann andere Serverprozesse kaum noch etwas davon übrig haben.
Das Tool ist sicherlich kein Zaubermittel, um aus einer lahmen Krücke ein Rennpferd zu machen. Aber es ist eine wertvolle Hilfe, bei den Myriaden an Konfigurationsmöglichkeiten von MySQL die zu identifizieren, mit denen man doch einiges an Verbesserung der Performanz erreichen kann. Es liefert also wertvolle Hinweise, und es schadet sicher nicht, es einfach mal testen. Es ist schließlich Opensource, und vom Download bis Einsatz sind es keine zwei Minuten.
