MySQLTuner.pl - Adjust your join queries to always utilize indexes

[tholler]

Hallo zusammen,
auch auf die Gefahr hin, dass ich jetzt zum xten Mal die gleiche Frage stelle, aber irgendwie verstehe ich die (englischen) Erklärungen nicht/falsch.

OK, Hintergrund:
Ich habe einen KVM-Server mit CentOS 6.8 und der aktellen cPanel-Version 11.58. Die letzten Umstellungen waren das Update auf PHP 5.6 und MySQL auff MariaDB 10.0.27 - von der 10.1er Version wurde mir abgeraten - wäre noch nicht stabil.

Ich versuche seit Tagen/Wochen meine Kombination PHP 5.6 und MySQL zu optimieren, da der Server, sobald mehr als 5 - 10 User gleichzeitig auf einen Bildershop zugreifen, 4x 100% CPU-Last anzeigt.

Hardware:
4Core-KVM-CPU, 8 GB RAM, 300 GB HD.

Ein Tipp, den ich bekommen habe, war, dass ich die MY.CNF löschen/umbenennen soll, MySQL restart und nach 1 - 2 Tagen mysqltuner.pl laufen lasse. Die Variablenwerte habe ich dann in eine neue MY.CNF eingetragen.
OK, das hat in der Performance etwas gebracht. Aber was ich nicht verstehe, sind die Recommendations (außer MySQL started within the last 24 hours :-)

OK, hier die Ausgaben vom mysqltuner

Code: Select all

 >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[[0;34m--[0m] Skipped version check for MySQLTuner script
[[0;31m!![0m] Currently running unsupported MySQL version 10.0.27-MariaDB
[[0;32mOK[0m] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+Aria [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;32m+FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MEMORY [0m[0;32m+MRG_MyISAM [0m[0;32m+MyISAM [0m[0;32m+PERFORMANCE_SCHEMA [0m
[[0;34m--[0m] Data in Aria tables: 1M (Tables: 2)
[[0;34m--[0m] Data in MyISAM tables: 764M (Tables: 249)
[[0;34m--[0m] Data in InnoDB tables: 949M (Tables: 1525)
[[0;34m--[0m] Data in MEMORY tables: 0B (Tables: 2)
[[0;32mOK[0m] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[[0;32mOK[0m] There are no anonymous accounts for any database users
[[0;32mOK[0m] All database users have passwords assigned
[[0;31m!![0m] User 'munin@localhost' has user name as password.
[[0;31m!![0m] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[[0;32mOK[0m] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[[0;34m--[0m] Up for: 51m 19s (54K q [17.598 qps], 2K conn, TX: 415M, RX: 9M)
[[0;34m--[0m] Reads / Writes: 97% / 3%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Physical Memory     : 7.7G
[[0;34m--[0m] Max MySQL memory    : 2.0G
[[0;34m--[0m] Other process memory: 1.3G
[[0;34m--[0m] Total buffers: 1.4G global + 2.9M per thread (200 max threads)
[[0;34m--[0m] P_S Max memory usage: 0B
[[0;34m--[0m] Galera GCache Max memory usage: 0B
[[0;32mOK[0m] Maximum reached memory usage: 1.4G (18.34% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 2.0G (25.50% of installed RAM)
[[0;32mOK[0m] Overall possible memory usage with other process is compatible with memory available
[[0;32mOK[0m] Slow queries: 0% (0/54K)
[[0;32mOK[0m] Highest usage of available connections: 3% (6/200)
[[0;32mOK[0m] Aborted connections: 0.05%  (1/2052)
[[0;32mOK[0m] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (2 temp sorts / 4K sorts)
[[0;31m!![0m] Joins performed without indexes: 127
[[0;31m!![0m] Temporary tables created on disk: 32% (1K on disk / 5K total)
[[0;32mOK[0m] Thread cache hit rate: 99% (11 created / 2K connections)
[[0;31m!![0m] Table cache hit rate: 5% (1K open / 18K opened)
[[0;32mOK[0m] Open file limit used: 8% (363/4K)
[[0;32mOK[0m] Table locks acquired immediately: 100% (49K immediate / 49K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[[0;34m--[0m] Performance schema is disabled.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[[0;34m--[0m] ThreadPool stat is enabled.
[[0;34m--[0m] Thread Pool Size: 4 thread(s).
[[0;34m--[0m] Using default value is good enough for your version (10.0.27-MariaDB)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[[0;31m!![0m] Key buffer used: 18.3% (51M used / 281M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 268.0M/267.0M
[[0;31m!![0m] Read Key buffer hit rate: 90.4% (5K cached / 551 reads)
[[0;31m!![0m] Write Key buffer hit rate: 1.5% (654 cached / 644 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[[0;34m--[0m] AriaDB is enabled.
[[0;32mOK[0m] Aria pagecache size / total Aria indexes: 128.0M/160.0K
[[0;32mOK[0m] Aria pagecache hit rate: 100.0% (833K cached / 414 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 950.0M/949.0M
[[0;32mOK[0m] InnoDB buffer pool instances: 1
[[0;34m--[0m] InnoDB Buffer Pool Chunk Size not used or defined in your version
[[0;32mOK[0m] InnoDB Read buffer efficiency: 99.99% (59649979 hits/ 59658036 total)
[[0;31m!![0m] InnoDB Write Log efficiency: 62.62% (2493 hits/ 3981 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 1488 writes)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[[0;34m--[0m] TokuDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[[0;34m--[0m] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[[0;34m--[0m] Galera Synchronous replication: NO
[[0;34m--[0m] No replication slave(s) for this server.
[[0;34m--[0m] This is a standalone server.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    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: http://bit.ly/1mi7c4C
    Beware that open_files_limit (4096) variable 
    should be greater than table_open_cache ( 1000)
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_open_cache (> 1000)

Und die my.cnf

Code: Select all

[mysqld]
bind-address = 127.0.0.1 			# 15.09.2016
skip-name-resolve = 1 				# 15.09.2016
max_connections = 200				# 200 16.09.2016
wait_timeout = 60					# 60 16.09.2016
interactive_timeout = 60			# 60 16.09.2016

innodb_file_per_table=1				# 16.09.2016
innodb_buffer_pool_size = 950M 		# 950M 15.09.2016
innodb_buffer_pool_instances = 1 	# 1 15.09.2016

thread_cache_size = 4 				# 15.09.2016
table_open_cache = 1000 			# 400 15.09.2016 - 1000 16.09.2016
join_buffer_size = 256K 			# 128k 15.09.2016 - 256k 16.09.2016

query_cache_type = 0 				# 0 15.09.2016
tmp_table_size = 64M 				# 16M 15.09.2016 - 64M 16.09.2016
max_heap_table_size = 64M 			# 16M 15.09.2016 - 64M 16.09.2016
key_buffer_size = 268M 				# 268M 15.09.2016

Jetzt bin ich mal auf Eure Vorschläge, Erklärungen und Hilfen gespannt, denn ich stehe gerade "im Wald" und brauche HILFE!!!

Viele Grüße
Thorsten

[Joe User]

Ich habe Dir unten mal eine my.cnf zusammengestellt, welche ganz gut passen sollte. Du musst darin gegebenenfalls noch die Pfade an Dein System anpassen und es kann sein, dass Dein MariaDB beim Starten (bitte ins MySQL-Log schauen) eventuell wegen ein/zwei Optionen meckert, dass er sie nicht kennt, dann diese bitte einfach aus der my.cnf löschen.

Bevor Du diese my.cnf einsetzt, bitte ein komplettes Backup der Datenbanken durchführen und die InnoDB-Files löschen (nochmal: vorher Backup anlegen!). Dann die neue my.cnf anlegen, MariaDB starten und das Backup zurückspielen, zum Schluss dann noch "mysql_upgrade" ausführen.

Code: Select all

# Zuerst ein Backup der Datenbanken anlegen
mysqldump --master-data=2 --delete-master-logs --flush-logs --add-locks --create-options --allow-keywords --complete-insert --triggers --routines --events --order-by-primary --set-gtid-purged=OFF --tz-utc --hex-blob --single-transaction --all-databases -uroot -p > /tmp/mysqldump.sql

# MySQL stoppen

# Pfade selbst anpassen
# Die InnoDB-Files löschen
rm -f /var/lib/mysql/ibdata[0-9]
rm -f /var/lib/mysql/ib_logfile[0-9]
rm -f /var/lib/mysql/ib_buffer_pool
rm -f /var/lib/mysql/ibtmp[0-9]

# my.cnf anlegen/anpassen

# MySQL starten

# Backup zurückspielen
mysql -uroot -p < /tmp/mysqldump.sql

# mysql_upgrade ausführen
mysql_upgrade --force

Die neue my.cnf

Code: Select all

[client]
port                            = 3306
socket                          = /var/run/mysqld.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /var/run/mysqld.sock
bind-address                    = 127.0.0.1
basedir                         = /usr
datadir                         = /var/lib/mysql
log-bin                         = mysql-bin
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
enforce-gtid-consistency        = 1
gtid-mode                       = ON
explicit-defaults-for-timestamp = 1
myisam-recover-options          = FORCE,BACKUP
open_files_limit                = 32768
table_open_cache                = 8192
table_definition_cache          = 4096
max_allowed_packet              = 64M
key_buffer_size                 = 256M
myisam_sort_buffer_size         = 16M
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 128M
tmp_table_size                  = 128M
query_cache_type                = 0
query_cache_size                = 0
#query_cache_size                = 128M
#query_cache_limit               = 4M
#query_cache_min_res_unit        = 4K
long_query_time                 = 0.5
innodb_buffer_pool_size         = 2G
innodb_data_home_dir            = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:1G;ibdata2:1G;ibdata3:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
log-queries-not-using-indexes
skip-symbolic-links
skip-name-resolve

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

Hoffe, es hilft.

[tholler]

Hallo Joe,
danke für die Mühe. Bevor ich da was probiere, eine Frage und ein Hinweis:

Es handelt sich im ein Produktiv-Server, also unsersage ich mir selber diese gutgemeinten Vorschläge, gerade wenn es um das Löschen von Files geht. Ich kann es mir nicht leisten, einen Ausfall zu haben, da steigen mir die Kunden aufs Dach.

Wie schnell kann man, bei einem Fehler, die Datenbank zurückschreiben? ggf. probiere ich das mal in der Nacht, wenn keiner Online ist.

innodb_buffer_pool_size mit 2G kommt mir etwas sehr hoch vor; nicht dass der Server dann nicht mehr aus den Puschen kommt oder nur noch am Swappen ist.

Viele Grüße
Thorsten

[Joe User]

Zum innodb_buffer_pool kann ich https://dev.mysql.com/doc/refman/5.7/en ... -pool.html und https://dev.mysql.com/doc/refman/5.7/en ... esize.html als Lesestoff empfehlen.
Die 2G sind lediglich das Maximum, in der Praxis wird immer nur der wirklich benötigte Speicher auch belegt, es hängt also von Deinen Daten ab. Swappen sollte das System deshalb normalerweise nicht, ausser Du hast unter 2G RAM oder bei mehreren anderen speicherlastigen Programmen unter 4G RAM.

Das Zurückschreiben dauert halt solange, wie Du zum Abarbeiten der Befehle benötigst, plus die Zeit die mysqldump zum Anlegen des Backups und mysql zum Einlesen des Backups benötigen. Dies ist also von Deiner Datenbankgrösse abhängig.

[Joe User]

Könnte auch hilfreich sein: http://dba.stackexchange.com/questions/ ... -pool-size