Slow-Querys

[nanto]

Hallo

Wie schon im anderen Thema gesagt habe ich mal die Ruhe mich mit ein paar Dinge zu befassen.
Anfangen werde ich mal damit.

Beim Aufrufen der Media Galerie gibt es MySQL Slow-Querys.
Nach allem was ich bisher gegoogelt habe sollte wohl auf die entsprechenden Tabellen / Spalten ein Index gesetzt werden.

So wie ich das sehen geht das mit phpMyAdmin.
Aber richtig schlau bin ich noch nicht draus geworden.

Ich habe hier bei DF einen sogenannten CGI-Debugger.
Der spuckt da dann z.B. das aus:

Query:

SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
                    attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.width, data.height, data.thumbnail_width, data.thumbnail_height,
                    deletion_log.delete_date, deletion_log.delete_reason,
                    deletion_log.delete_user_id, deletion_log.delete_username,
                    IF(media_watch.user_id IS NULL, 0, 1) AS media_is_watched
            FROM xengallery_media AS media
               
                    LEFT JOIN xengallery_album AS album ON
                        (album.album_id = media.album_id)
                    LEFT JOIN xengallery_album_permission as albumviewperm ON
                        (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
                   
                    LEFT JOIN xengallery_category AS category ON
                        (category.category_id = media.category_id)
                    LEFT JOIN xf_user AS user ON
                        (user.user_id = media.user_id)
                    LEFT JOIN xf_user_profile AS user_profile ON
                        (user_profile.user_id = media.user_id)
                    LEFT JOIN xf_attachment AS attachment ON
                        (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
                    LEFT JOIN xf_attachment_data AS data ON
                        (data.data_id = attachment.data_id)
                    LEFT JOIN xf_deletion_log AS deletion_log ON
                        (deletion_log.content_type = 'xengallery_media' AND deletion_log.content_id = media.media_id)
                    LEFT JOIN xengallery_media_watch AS media_watch
                        ON (media_watch.media_id = media.media_id
                        AND media_watch.user_id = 1)
                WHERE (media.media_state IN ('visible','deleted')) AND (IF(media.album_id > 0, album.album_state IN('visible','deleted'), 1=1))
                ORDER BY media.media_date DESC, media.media_id DESC           
             LIMIT 9

Liegt das am fehlenden Index oder ist was anderes faul?
Und wenn ja welche Tabellen / Spalten sind hier betroffen?

Da steht ein Haufen Zeug drin...

 

[GeorgeS]

Hallo, nanto -


nicht immer ist ein Index wirklich gut - zuviele davon können auch ausbremsen.

Hilfreich ist der SQL Befehl EXPLAIN - vor die SELECT-Anweisung gesetzt, wird die Anweisung untersucht und erklärt, was der Server machen würde, um sie auszuführen. Steht dann am Ende so etwas wie hier:

+----+-------------+---------+------+---------------+------+---------+-------+-------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows  | Extra          |
+----+-------------+---------+------+---------------+------+---------+-------+-------+----------------+
|  1 | SIMPLE      | mydata  | ALL  | NULL  | NULL  | NULL | NULL    | 27822 | Using filesort         |
+----+-------------+---------+------+---------------+------+---------+-------+-------+----------------+

bedeutet das, dass MySQL die Daten sortieren muss, weil kein Index vorliegt. Jetzt stellt sich die Frage: wie häufig wird die Abfrage gebraucht? Ein Index ist ja nicht statisch, sondern wird immer dann aktualisiert, wenn sich was ändert: Erstellen und Löschen von Zeilen ändern den Index immer, wenn Änderungen an der Tabelle vorgenommen werden, wird der Index nur dann aktualisiert, wenn die betroffenen Felder verändert werden.

Legt man zu viele Index-Dateien über zu viele kritische (wiel häufig geänderte) Felder, kann das die Performance ebenfalls herunterziehen.

Geht es nur um die eine Abfrage, ist das eher unkritisch.


Soviel im Allgemeinen. Da in der SELECT-Anweisung viele LEFT JOIN drin sind, kann es auch dort erforderlich sein, dass Index-Dateien verwendet werden. Vielleicht führst Du mal den EXPLAIN durch, dann kann man sich das Ergebnis mal ansehen.

Und: wie sehen die Speicherzuweisungen in der /etc/mysql/my.cnf aus?

 

[nanto]

Gut, also zuviel Index ist auch nicht gut.

Habe jetzt mal in phpMyAdmin diese SQL Befehl ausgeführt. (Im Testboard natürlich.)

EXPLAIN SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
                    attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.width, data.height, data.thumbnail_width, data.thumbnail_height,
                    deletion_log.delete_date, deletion_log.delete_reason,
                    deletion_log.delete_user_id, deletion_log.delete_username,
                    IF(media_watch.user_id IS NULL, 0, 1) AS media_is_watched
            FROM xengallery_media AS media
              
                    LEFT JOIN xengallery_album AS album ON
                        (album.album_id = media.album_id)
                    LEFT JOIN xengallery_album_permission as albumviewperm ON
                        (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
                  
                    LEFT JOIN xengallery_category AS category ON
                        (category.category_id = media.category_id)
                    LEFT JOIN xf_user AS user ON
                        (user.user_id = media.user_id)
                    LEFT JOIN xf_user_profile AS user_profile ON
                        (user_profile.user_id = media.user_id)
                    LEFT JOIN xf_attachment AS attachment ON
                        (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
                    LEFT JOIN xf_attachment_data AS data ON
                        (data.data_id = attachment.data_id)
                    LEFT JOIN xf_deletion_log AS deletion_log ON
                        (deletion_log.content_type = 'xengallery_media' AND deletion_log.content_id = media.media_id)
                    LEFT JOIN xengallery_media_watch AS media_watch
                        ON (media_watch.media_id = media.media_id
                        AND media_watch.user_id = 1)
                WHERE (media.media_state IN ('visible','deleted')) AND (IF(media.album_id > 0, album.album_state IN('visible','deleted'), 1=1))
                ORDER BY media.media_date DESC, media.media_id DESC          
             LIMIT 9

Und das kam als Ergebnis:

 

[GeorgeS]

Hallo,


kurz vor dem Weg in die Muckibude ein paar Gedanken:

interessant ist nur die erste Tabelle, media, mit 12.847 Zeilen. Allerdings schlägt EXPLAIN hier keinen Schlüssel vor (die anderen Tabellen können über den definierten PRIMARY KEY abgefragt werden - ein PRIMARY KEY bedeutet, dass MySQL implizit einen entsprechenden Index angelegt hat). Du kannst theoretisch einen

CREATE INDEX mediaindex1 ON media (media_date, media_id)

durchführen und schauen, was passiert in Bezug auf die Performance.

Der Grund, warum kein Index verwendet wird, mag in den komplexen WHERE Abfragen liegen, die den Query-Optimizer quasi zwingen, jeden Satz aus media zu lesen, um zu entscheiden, ob der Datensatz Verwendung findet oder nicht. Durch den von mir vorgeschlagenen Index kann bereits in der gewünschten Reihenfolge gelesen werden, was einen kleinen Zeitvorteil bringen kann.

 

[nanto]

Besten Dank.
Für media_date und media_id gibt es schon ein Index.
Aber mit diesem Beispiel kann ich was anfangen.

 

[nanto]

Glaube da kommt was mit der 1.0.4
https://xenforo.com/community/threads/xfmg-1-0-2-cpu-intensive-queries-maybe-add-some-indexes.91034/