Felix Riesterer: Extreme Performance-Unterschiede zwischen MariaDB und MySQL?

Beitrag lesen

Lieber Christian,

ich habe den Cache auf 0 gesetzt und sehe, dass dann ebenfalls 100 Teilschritte entstehen. Sie dauern aber Microsekunden, anstatt Millisekunden. Das mag daran liegen, dass auf meinem Testsystem der Apache und der MariaDB-Server auf derselben Maschine liegen, auf dem Webspace aber nicht.

Ein EXPLAIN sowie die Datenstrukturen sollten bei so einer Frage nicht fehlen.

Das sieht bei beiden sehr ähnlich aus:

MariaDB

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alt_exams ALL PRIMARY NULL NULL NULL 18 Using where; Using temporary
1 PRIMARY alt_bookings ref PRIMARY,alt_exam,student PRIMARY 4 school_2019_2020.alt_exams.alt_exam 9 Using where
1 PRIMARY students eq_ref PRIMARY,person PRIMARY 4 school_2019_2020.alt_bookings.student 1
1 PRIMARY people eq_ref PRIMARY PRIMARY 4 school_2019_2020.students.person 1
6 SUBQUERY users index person person 6 NULL 78 Using index
6 SUBQUERY staff eq_ref PRIMARY,shorthand PRIMARY 4 school_2019_2020.users.person 1 Using where
6 SUBQUERY lesson_teachers ref PRIMARY,lesson_id,teacher teacher 62 school_2019_2020.staff.shorthand 80 Using where; Using index
6 SUBQUERY lessons eq_ref PRIMARY PRIMARY 4 school_2019_2020.lesson_teachers.lesson_id 1
7 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index
4 DEPENDENT SUBQUERY lessons ref PRIMARY,schedule,course_name course_name 602 school_2019_2020.alt_bookings.course_name 10 Using index condition; Using where
4 DEPENDENT SUBQUERY lesson_teachers ref PRIMARY,lesson_id PRIMARY 4 school_2019_2020.lessons.lesson_id 1 Using index
5 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index
2 DEPENDENT SUBQUERY courses eq_ref PRIMARY,schedule,course_name PRIMARY 605 func,school_2019_2020.alt_bookings.course_name 1 Using where; Using temporary
3 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index

MySQL

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alt_exams NULL ALL PRIMARY NULL NULL NULL 18 33.33 Using where; Using temporary
1 PRIMARY alt_bookings NULL ref PRIMARY,alt_exam,student PRIMARY 4 school.alt_exams.alt_exam 23 100.00 Using where
1 PRIMARY students NULL eq_ref PRIMARY,person PRIMARY 4 school.alt_bookings.student 1 100.00 NULL
1 PRIMARY people NULL eq_ref PRIMARY PRIMARY 4 school.students.person 1 100.00 NULL
6 DEPENDENT SUBQUERY users NULL index person person 4 NULL 78 100.00 Using index
6 DEPENDENT SUBQUERY staff NULL eq_ref PRIMARY,shorthand PRIMARY 4 school.users.person 1 100.00 Using where
6 DEPENDENT SUBQUERY lesson_teachers NULL ref PRIMARY,lesson_id,teacher teacher 62 school.staff.shorthand 160 100.00 Using where; Using index
6 DEPENDENT SUBQUERY lessons NULL eq_ref PRIMARY PRIMARY 4 school.lesson_teachers.lesson_id 1 100.00 NULL
7 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)
4 DEPENDENT SUBQUERY lessons NULL ref PRIMARY,schedule,course_name course_name 602 school.alt_bookings.course_name 21 100.00 Using index condition; Using where
4 DEPENDENT SUBQUERY lesson_teachers NULL ref PRIMARY,lesson_id lesson_id 4 school.lessons.lesson_id 1 100.00 Using index
5 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)
2 DEPENDENT SUBQUERY courses NULL ref PRIMARY,subject,schedule,course_name PRIMARY 3 func 559 10.00 Using where; Using temporary
3 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)

Hier werden drei temporäre Tabellen erstellt. Außerdem wird das Ergebnis sehr fragmentiert übertragen (siehe die vielen executing und sending-Einträge).

Ich kann es noch nicht besser, sonst würde ich meine Anfrage sicherlich günstiger formulieren.

Ist der MySQL-Server auf der gleichen Maschine?

Offensichtlich nicht. Es handelt sich um ein shared webhosting Paket.

Ist Netzwerk ein Flaschenhals?

Das kann ich nicht beurteilen.

Wie groß ist das Resultset?

Weniger als 500 Zeilen.

Wie sieht die Speicher-Auslastung auf der DB-Maschine aus? Prüfe auch, ob der Tablespace für temporäre Tabellen auf einer drehenden Platte ist oder auf einer SSD.

Das ist in einem Rechenzentrum... k.A.

Drei correlating subqueries – ist vielleicht auch nicht so optimal. Das könnte schon auch ein Grund für die temporären Tabellen sein. Kannst du das vielleicht umformulieren?

Wenn ich wüsste, wie man das macht! Momentan tendiere ich dazu, die Abfrage allgemeiner zu halten und die Zuordnungen per PHP-Script zu leisten.

Liebe Grüße

Felix Riesterer