Datenbank Mysql optimieren

[Claudiocool]

Hallo Leute,

 

ich bin gerade dabei, mir hier Gedanken zu machen, wie man die DB-Zugriffe optimieren kann. Ich kann hier bis in die tiefsten Gründe gehen, also auch auf dem Server die entsprechende Konfigurationen ändern, das ist also zumindest von den Möglichkeiten her alles machbar.

 

Ich bin in dem Thema aber nicht so weit drin, daher wäre es super, wenn hier jemand wäre, der sich damit auskennt und vielleicht helfen könnte, das Step by Step zu machen.

 

Ich vermute, dass man dem Presta hier noch etwas Performance verpassen kann, wenn man das alles optimiert, daher denke ich, dass dieses Thema durchaus für viele hier interessant ist.

[TimmeHosting]

Kannst Du Deine aktuelle MySQL-Konfiguration posten?

 

Hast Du mal mysqltuner.pl ( https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl ) benutzt, um Deine aktuelle Konfiguration auf Optimierungsbedarf zu prüfen?

[Claudiocool]

Die Konfiguration scheint ziemlich oberflächlich zu sein, ich war da bisher auch noch nicht weiter dran.

 

Hier mal, was ich da so finde:

 

die my.cnf:

[mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 134217728
query_cache_limit = 1048576
query_cache_type = 1
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Und dann die Sachen aus dem my.cnf.d-Verzeichnis:

 

die client.cnf:

#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#


[client]

# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

Dann die mysql-clients.cnf:

#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

Und dann noch die server.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]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.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
[mysqld-5.5]

# These two groups are 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]

[mariadb-5.5]

Das oben genannte Tool wird via SSH auf den Server geladen?

[Scully]

Ja, das lädt man via ssh. wget

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod g+x mysqltuner.pl
./mysqltuner.pl

Zeile 1 lädt das File auf den Server, Zeile 2 Ausführungsrechte setzen, Zeile 3 ausführen.

Du benötigst den Perl - Interpreter, damit das läuft.

Und dann staunen, was der Dir alles erzählt.

[Claudiocool]

Okay, aber bevor ich staune werde ich lieber erst mal ein komplettes Backup ziehen

 

Hab da so die eine oder andere blöde Erfahrung gemacht

 

Ich gehe aber mal davon aus, dass sich das eine oder andere PS rausholen lässt....

Edited July 8, 2017 by Claudiocool (see edit history)

[TimmeHosting]

Du kannst viele Variablen sogar dynamisch setzen, d.h., ohne MySQL-Neustart: https://dev.mysql.com/doc/refman/5.6/en/dynamic-system-variables.html

 

Ganz bequem geht das sogar über phpMyAdmin - dort gibt es in der obersten Ebene, also noch bevor Du eine Datenbank auswählst, den Menüpunkt "Variables", über den Du Variablen anpassen und deren Auswirkung testen kannst. Nur, wenn die Änderungen auch nach einem MySQL-Neustart Bestand haben sollen, mußt Du Deine Änderungen auch in die my.cnf übernehmen.

[Claudiocool]

Okay, das habe ich gefunden, aber welche Variablen schaut man sich da nun sinnvollerweise an, um zu sehen, was da an Performance machbar ist, und vor allem, von welchen lässt man besser die Finger weg?

 

Wenn man dann Variablen gefunden hat, die was bringen, merkt man sich diese und versucht, diese danach in die my.cnf zu implementieren?

Und vor dem Neustart der MariaDB muss ich dann die Logs usw. löschen, weil der sonst nicht mehr startet?

 

Klingt irgendwie nach einem GAU, der dann da lauert....

[Scully]

Erstmal mysqltuner.pl laufen lassen. Der gibt dann grobe Anhaltspunkte über die Speicherzuweisung und Buffers.  Ziel: Speicher möglichst optimal nutzen. Speicher nur dort zuweisen, wo er auch benötigt wird. Oftmal haben MySQL DBs zB 100 Concurrent User gesetzt, was eine normaler Webseite kaum braucht. Aber jeder User mehr beansprucht schon beim Start Hauptspeicher. Also nur soviel setzen, wie sinnvoll plus eine Reserve.

 

In die Config würde ich erstmal gar nichts reinschreiben sondern die Befehle erstmal dynamisch setzen via phpmyadmin oder in einem Script, dass zB einmal am Tag laufen könnte.

 

Vorteil: Wenn man sich komplett verhaut, einfach die DB neu starten und alle dynamischen Änderungen sind weg.

 

Wir lassen das 1x täglich laufen. Dabei wird ein Parameter erst gecheckt. Passt dieser (nicht), dann werden alle relevanten Parameter neu geladen.

Idee dahinter: Wir haben eine fail-safe Basiskonfiguration, welche wir dynamisch anpassen können.

 

Code Schnipsel PHP:

$tuning_SQL = array();
$sql="SHOW VARIABLES like 'query_cache_size'";
$result = $link->ExecuteS($sql);
foreach ($result as $row){
    if ( $row['Value'] != 50331648 ) {
        $tuning_SQL = array(
            "SET Global max_connections   = 30; # was 50 before",
            "SET Global query_cache_size  = 50331648;      # 48 MB",
        //    "SET Global join_buffer_size  = 262144;        # 256 KB",
            "SET Global join_buffer_size  = 393216;        # 384 KB",        
            "SET Global key_buffer_size   = 2097152;       #   2 MB",
            "SET Global query_cache_limit = 786432;        # 768 KB",
            "SET Global query_cache_min_res_unit = 1536;   # 1.5KB",
            "Set Global myisam_sort_buffer_size = 2097152; # 2 MB",
            "Set Global sort_buffer_size = 4194304;        # 4 MB",
        //    "Set Global innodb_sort_buffer_size = 4194304; # 4 MB",        // as per MySQL 5.6
            "Set Global key_buffer_size = 1048576;         # 1 MB",
            "SET Global table_open_cache = 1500;",
            "SET Global table_definition_cache = 1500;",
            "SET Global long_query_time=2;",

        );
    }
}


foreach ($tuning_SQL as $sql) {
    echo "\n<br />SQL: " . htmlspecialchars($sql);
    $result = $link->query(utf8_encode($sql));
    if ($result === false) {
        echo "\n<br />ERROR while executing SQL.";
    } else {
        echo " OK ";
    }
}

[Scully]

Das mysqltuner Script sollte man idealerweise laufen lassen, wenn die DB ohne Unterbruch z.B. zwei oder drei Tage gelaufen ist. Ansonsten stellen sich Werte dar, die zu wenig aussagekräftig sind. Beispiel Ausgabe vom Script - hier nach 13 Stunden Laufzeit, was nicht ausreichend wäre für eine Beurteilung.

 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Assuming 2900 MB of physical memory
[--] Assuming 1900 MB of swap space
[OK] Currently running supported MySQL version 5.5.55-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 184M (Tables: 2115)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 534K (Tables: 57)
[!!] Total fragmented tables: 2115

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13h 17m 29s (98K q [2.053 qps], 1K conn, TX: 331M, RX: 15M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 208.0M global + 2.7M per thread (50 max threads)
[OK] Maximum possible memory usage: 342.4M (11% of installed RAM)
[OK] Slow queries: 0% (1/98K)
[OK] Highest usage of available connections: 22% (11/50)
[OK] Key buffer size / total MyISAM indexes: 16.0M/958.0K
[OK] Key buffer hit rate: 99.7% (7K cached / 23 reads)
[OK] Query cache efficiency: 62.6% (36K cached / 58K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 220
[!!] Temporary tables created on disk: 67% (10K on disk / 14K total)
[OK] Thread cache hit rate: 99% (11 created / 1K connections)
[!!] Table cache hit rate: 2% (400 open / 13K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 99% (31K immediate / 31K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/184.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    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 without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 184M)

 

[Scully]

Was noch wichtig ist:

MySQL unterscheidet zwischen u.a. zwischen MyISAM und INNODB Engine, welche unterschiedliche Memory-Bereiche beanspruchen. Es nutzt nichts, Parameter für MyISAM-Engine zu erhöhen, wenn die Shop DB auf INNODB läuft und umgekehrt. Auch hier gilt: Nur soviel Memory zuweisen, wie es überhaupt sinnvoll nutzbar ist.

 

Bei uns laufen alle Shops auf INNODB. MySQL führt jedoch datenbankintern auch gewisse Tabellen in MyISAM. Darum bekommt dieser Bereich auch Buffer, aber nur wenig. Der grosse Teil geht in den INNODB Buffer Pool.

 

Und zum Query Cache

Der Query Cache ist ein Speicherbereich, in welchem Queries und deren Resultate abgelegt sind. Wird eine Abfrage durchgeführt und das Resultat ist im Query Cache, dann holt die Datenbank das Resultat auch dort her, ohne die Query tatsächlich auszuführen. Der Query Cache unterliegt einem Expirationsprozess (Zeit, Änderungen an der DB). Da sollte man zusehen, dass die Effizienz im Bereich von 70% oder mehr liegt.

 

Wenn man den Query Cache ganz optimal nutzt, dann schaut man auch, dass das Shopsystem "gleichartige" Querys exakt identisch absetzt. Der Query Cache unterscheidet z.B. die zwei folgenden Abfragen:

 

select id_customer, lastname from ps_customer;

SELECT lastname, id_customer from ps_customer;

 

Leider ist PrestaShop in dieser Hinsicht nicht optimal. Die Queries kommen mal so und mal anders daher, auch wenn die Abfragen im Grundsatz genau dasselbe machen.

[Claudiocool]

Hey Freunde,

 

langsam macht mir das Thema Angst, ich versteh da langsam nur noch bahnhof

[Scully]

So schlimm ist das nicht. Aber: Datenbanken optimiert man selten on the fly in 5 Minuten. Es ist ein steter Prozess. Einen Parametern ändern und zwei Tage schauen, wie sich das auf die Performance auswirkt.

 

Wenn gut: belassen und nächsten Parameter prüfen

Wenn nicht gut: zurück auf die letzte Einstellung

 

Wenn man die Config ändert (ob dynamisch oder durch neues Conf-File), dann sieht man die Auswirkung von Änderungen leider eben erst nach ein paar Tagen des regulären Betriebs.

[Claudiocool]

Ich habe gestern mal gestöbert und bin da an das Wort "indexierung" geraten, wie sieht das mit den Presta-Datenbanken aus, gibt es hier auch Optimierungspotenzial?

[Scully]

Nein, eigentlich nicht. Wenn nicht irgendwie "aus Versehen" Indexe verloren gegangen sind, gibt es da kaum Potential. Die Leute, die sich bei PS um das Datenbank-Layout kümmern, verstehen ihren Job ganz gut.

 

Eher wäre es die Art der Queries, die PrestaShop generiert zu bemängeln. Oft macht man wunderbare SQL-Statements welche in einem Wisch komplexe Resultate liefern und dabei mal schnell so 20 Tabellen oder mehr mit einbeziehen. Oftmals schneller wäre, erst ein "provisorisches" Resultat z.B. nur mit Produkte-IDs zu selektieren und dann den Join auf die anderen Tabellen loszulassen. D.h. 2 oder ggf. 3 Queries sind eben oftmals schneller als ein einziges riesiges Query.

 

Warum?

MySQL macht bei komplexen Queries oft Full Table Scans. D.h. ganze und umfangreiche Tabelleninhalte werden geladen. Das Zwischenresultat wird dann auf Disk gespeichert und in einem nächsten Schritt von dort wieder gelesen. Kleinere Datenelemente werden jedoch im Memory behalten. Darum können mehrere kleiner Queries oft schneller sein.

Aber: Alles in allem ist in V 1.6 hier schon vieles verbessert. Bei 1.5. war das noch nicht so, speziell nicht bei der Auflösung von vielen Unterkategorien.

Edited July 8, 2017 by Scully (see edit history)

[Claudiocool]

Ohje.... dies macht es jetzt für mich nicht gerade motivierender, da Hand anzulegen...

 

Im Moment wäge ich ab, ob es das Risiko, den DB-Server plattzumachen, wert ist

[Scully]

Solange Du Parameter - Werte dynamisch in einem Script an die DB sendest, ist das Risiko sehr klein.

Läuft es dann nicht oder schlecht, startest Du entweder die DB oder den Server neu und alles ist beim Alten.

 

Das Aufwändige an Datenbankoptimierung ist schlicht, dass es Zeit benötigt. Wie oben erwähnt, brauchen wir eine minimale Laufzeit von 2 oder 3 Tagen, um aus der Datenbank auch wirklich realistische statistische Werte abzuleiten. Und mein Credo ist bei solchen Dingen:

Immer nur einen Parameter auf einmal ändern. Sonst weiss ich am Ende nicht, welcher Wert die Resultate positiv oder negativ beeinfluss hat.

 

Und das es sehr viele Parameter gibt, ist eine solche Optimierung nicht in einem Tag zu bewerkstelligen.

Edited July 9, 2017 by Scully (see edit history)