MySQL: Performance Optimierung

greystone

Member
Hallo zusammen,

ich habe hier ein Typo3, dass ich gerne beschleunigen möchte.

Setup ist:
  • OS: Debian Buster
  • VM(Xen-HVM,RAID-10 SATA,8 GB RAM)
  • PHP 7.3(FastCGI)
  • Typo3 9.5.13
  • Apache(Momentan noch mit mpm_prefork)
  • MariaDB 10.3
Es ist ein Webauftritt, der keine kritischen Daten enthält.

Da gibt's ja so einige Möglichkeiten wo man drehen kann(Opcache, Webserver-Konfiguration, Datenbank, Storage).

mpm_prefork.conf

Code:
<IfModule mpm_prefork_module>
        StartServers             30
        MinSpareServers          30
        MaxSpareServers          60
        MaxRequestWorkers        150
        MaxConnectionsPerChild   0
</IfModule>
Die grössten "Bremsen" sind aktuell aber anscheinend die SQL-Abfragen. Ich habe mit iotop -o mir die Prozesse angeschaut, die I/O-verursachen. Auf einem leeren System rennen da bei Klick auf der Webseite mal direkt 5 mysql-Prozesse los und verursachen Plattenaktivität.

Deswegen habe ich folgende MySQL-Einstellungen vorgenommen:
  • den InnoDB-Buffer Pool so dimensioniert, dass die komplette DB reinpasst (1 GB)
  • Transaktionssicherheit ausgeschaltet(innodb_flush_log_at_trx_commit=0), da keine kritischen Daten im Spiel sind
  • InnoDB-Buffer Pool wird beim speichern gesichert und beim starten wieder geladen
  • Cache Aging vom InnoDB-Buffer Pool weitgehend(oder ganz, habe die Doku da nicht ganz verstanden) ausgeschaltet
Das sind die relevanten Einstellungen:

Code:
[mysqld]
# Namensaufloesung bei MySQL-Connects verhindern
skip-name-resolve

# Gesamtpoolgroesse fuer InnoDB auf 1000M setzen
innodb_buffer_pool_size = 1000M

# Logfilegroesse auf 16M setzen (mysqltuner empfiehlt 25% von benutzten Buffer-DATEN-Groesse, bei mir wären das 128 MB)
innodb_log_file_size = 16M

# Transaktionssicherheit ausschalten
innodb_flush_log_at_trx_commit=0

# Daten in dem Intervall von 30 Sekunden auf Platte schreiben
innodb_flush_log_at_timeout=30

# InnoDB-Pool beim beenden auf die Platte schreiben
innodb_buffer_pool_dump_at_shutdown=ON

# InnoDB-Pool beim starten wieder von der Platte laden(kein Cache-WarmUp notwendig)
innodb_buffer_pool_load_at_startup=ON

# Keine Elemente aus dem Cache entfernen
innodb_old_blocks_time=0

# Prozentsatz wie viel vom Cache für nicht "heiße" Daten(regelmässig in
# Verwendung befindliche Daten verwendet werden soll.)
innodb_old_blocks_pct=90
So sieht der Status der InnoDB-Variablen in MySQL derzeit aus:

Code:
mysql -e "SHOW STATUS LIKE '%innodb_buffer%';"
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        |                                                  |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 200207  9:34:01 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_load_incomplete    | OFF                                              |
| Innodb_buffer_pool_pages_data         | 8317                                             |
| Innodb_buffer_pool_bytes_data         | 136265728                                        |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 5380                                             |
| Innodb_buffer_pool_pages_free         | 57192                                            |
| Innodb_buffer_pool_pages_misc         | 27                                               |
| Innodb_buffer_pool_pages_total        | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 449019                                           |
| Innodb_buffer_pool_reads              | 7646                                             |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 40298                                            |
+---------------------------------------+--------------------------------------------------+
Die Webseiten-performance ist wie erwartet deutlich gestiegen. Ich merke auch, das bei den Browser-Requests der MySQL bei kürzlich aufgerufenen Seiten keine I/O mehr verursacht.

Interessanterweise bei Webseiten, die seit einer Stunde z. B. nicht mehr aufgerufen habe ich eine hohe Schreibaktivität direkt beim Webseitenaufruf. Das dürfte nach meinem Verständnis bei den gemachten Einstellungen von innodb_flush_log_at_trx_commit=0 bzw. innodb_flush_log_at_timeout=30 nicht sein, oder?

Nachtrag

Ich habe mir das jetzt nochmal mit vmstat angeschaut. Aktuell sieht es gut aus. Ich werde das später nochmal probieren:

Code:
# vmstat 1 100

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0      0 5641848  35980 767676    0    0   390    25  256  198  7  1 80  8  4
1  1      0 5639800  35980 767680    0    0     0    68  617  352  3  0 96  1  0
2  0      0 5629996  35980 767680    0    0     0    48 1739 1024 32  6 60  3  0
1  0      0 5633484  35980 767696    0    0     0     0 1879 1437 42  9 49  0  0
2  0      0 5630788  35980 767700    0    0     0     0 1978 1363 49  9 42  0  0
3  0      0 5622652  35988 767700    0    0     0    40 1924 1620 54 15 30  1  0
0  0      0 5634952  35988 767716    0    0     0     0 2109 1595 47 12 41  0  0
1  0      0 5635820  35992 767712    0    0   132    48 1326  869 28  6 64  2  0
2  0      0 5631392  35992 767848    0    0     0     0 1576 1091 27  5 67  0  0
3  1      0 5622500  36052 767960    0    0   120   344 5941 6284 47 10 39  4  0
1  0      0 5607528  36164 768696    0    0   636   308 8770 10276 34  9 47  9  0
1  0      0 5585116  36280 769748    0    0   952   416 3395 3493 23  9 59  9  0
1  0      0 5600812  36340 770200    0    0    12 10176 1765 1647 10  2 74 13  0
2  1      0 5609616  36396 770412    0    0    32  6552 2106 1587 16  5 50 30  0
0  0      0 5616100  36428 770396    0    0     0   168  735  523  1  1 94  4  0
0  0      0 5621364  36444 770412    0    0     0    32  557  410  0  0 98  2  0
0  0      0 5621404  36444 770412    0    0     0     0  418  320  0  0 100  0  0
0  0      0 5624336  36444 770412    0    0     0     0  475  337  0  0 100  0  0
0  0      0 5631968  36444 770412    0    0     0     0  596  417  0  0 100  0  0
0  0      0 5634828  36444 770416    0    0     0     0  583  361  0  0 100  0  0
0  0      0 5634828  36444 770416    0    0     0     0  576  339  0  0 100  0  0
0  0      0 5634972  36444 770420    0    0     0     4 1446 1144  4  3 93  0  0
0  0      0 5635300  36444 770416    0    0     0     0  616  363  0  0 100  0  0
0  0      0 5635300  36444 770416    0    0     0     0  501  328  0  0 100  0  0
0  0      0 5635300  36452 770408    0    0     0    52  514  324  0  0 99  1  0
0  0      0 5635568  36452 770416    0    0     0     0  671  417  0  0 100  0  0
0  0      0 5635616  36452 770416    0    0     0     0  556  331  0  0 100  0  0
0  0      0 5635616  36452 770416    0    0     0     0  518  320  0  0 100  0  0
0  0      0 5635648  36452 770416    0    0     0     0  573  358  1  0 99  0  0
0  0      0 5635396  36452 770416    0    0     0     0  638  388  0  0 100  0  0
0  0      0 5635396  36460 770416    0    0     0    16  605  390  0  0 99  1  0
0  0      0 5635364  36460 770416    0    0     0     0  499  315  0  0 100  0  0
0  0      0 5635504  36460 770416    0    0     0     0  574  336  0  0 100  0  0
0  0      0 5635584  36460 770416    0    0     0     0  655  390  0  0 100  0  0
0  0      0 5635584  36460 770416    0    0     0     0  465  308  0  0 100  0  0
0  0      0 5635616  36460 770416    0    0     0     0  547  331  0  0 100  0  0
1  0      0 5624108  36460 770416    0    0     0     0  719  401  5  1 94  0  0
2  2      0 5623224  36464 770420    0    0     0    24 1589 1505 28  7 64  0  0
2  0      0 5624636  36468 770424    0    0     0   212 2045 1537 43 10 46  1  0
2  0      0 5629160  36508 770428    0    0     0   540 1804 1300 34  9 56  2  0
2  0      0 5620712  36508 770432    0    0     0     0 1595 1262 49  9 42  0  0
3  0      0 5625760  36520 770876    0    0   540     0 2236 1530 42  8 48  2  0
3  0      0 5609756  36524 771188    0    0   212     0 2225 1636 51 12 35  2  0
1  0      0 5626716  36524 771588    0    0   492     0 1606 1135 30  8 60  3  0
0  0      0 5628860  36524 771700    0    0     0     0 1235  919 15  5 80  0  0
0  0      0 5628900  36532 771692    0    0     0    28  483  366  0  0 98  1  0
0  0      0 5628752  36532 771700    0    0     0     4 1034  992  1  2 96  0  0
0  0      0 5628776  36532 771704    0    0     0     0  436  329  0  0 100  0  0
0  0      0 5628744  36532 771704    0    0     0    96  423  327  0  0 98  2  0
0  0      0 5633152  36532 771704    0    0     0     0  520  308  0  0 100  0  0
0  0      0 5633208  36532 771704    0    0     0     0  430  320  0  0 100  0  0
0  0      0 5632372  36540 771704    0    0     0   148  881  744  1  1 98  1  0
0  0      0 5632372  36540 771704    0    0     0     0  431  308  0  0 100  0  0
1  0      0 5632632  36540 771704    0    0     0     0  472  355  0  0 100  0  0
1  0      0 5630236  36556 771688    0    0     0   168  679  445  7  1 90  3  0
1  0      0 5623060  36556 771704    0    0     0     0 1966 1354 31  8 61  0  0
2  0      0 5626200  36556 771704    0    0     0     0 1680 1198 39  8 53  0  0
2  0      0 5618404  36556 771712    0    0     0     4 2287 1598 49 14 37  0  0
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
4  0      0 5604000  36556 771720    0    0     0     0 1421  924 31  6 63  0  0
2  0      0 5622672  36564 771728    0    0     0    72 2224 1717 49 13 37  1  0
2  0      0 5625532  36564 771728    0    0     0     0 1219  996 39  7 55  0  0
3  0      0 5611600  36564 771736    0    0     0     0 2060 1491 51 11 38  0  0
2  0      0 5620188  36564 771740    0    0     0     0 2407 1701 45 10 45  0  0
1  0      0 5616768  36564 771748    0    0     0     0 1695 1348 51 10 39  0  0
1  0      0 5629820  36564 771748    0    0     0     0 1410 1121 27  7 66  0  0
0  0      0 5630996  36572 771748    0    0     0    28  592  444  2  1 97  0  0
0  0      0 5631028  36572 771748    0    0     0     0  343  258  0  0 100  0  0
0  0      0 5631092  36572 771748    0    0     0     0  428  334  0  0 100  0  0
0  0      0 5633024  36572 771748    0    0     0     0  435  309  0  0 99  0  0
0  0      0 5633296  36572 771748    0    0     0     0  527  367  0  0 100  0  0
0  0      0 5634520  36580 771748    0    0     0    24  457  331  0  0 99  1  0
0  0      0 5634520  36580 771748    0    0     0     0  417  318  0  0 100  0  0
0  0      0 5635276  36580 771748    0    0     0     0  557  369  0  0 100  0  0
0  0      0 5635244  36580 771748    0    0     0     0  432  355  0  0 100  0  0
0  0      0 5635276  36580 771748    0    0     0     0  460  344  0  0 100  0  0
0  0      0 5635340  36580 771748    0    0     0     0  479  357  0  0 100  0  0
0  0      0 5635308  36580 771748    0    0     0     0  413  313  0  0 100  0  0
0  0      0 5635308  36580 771748    0    0     0     0  416  325  0  0 100  0  0
0  0      0 5635308  36588 771740    0    0     0    20  423  333  0  0 97  3  0
1  0      0 5633132  36588 771748    0    0     0     0  660  404  7  1 92  0  0
2  0      0 5626132  36588 771760    0    0     0    16 2017 1395 33  8 59  0  0
3  0      0 5612600  36588 771760    0    0     0     4 2505 1973 57 14 29  0  0
2  0      0 5627068  36588 772216    0    0   332     0 2337 1564 44  9 46  1  0
0  0      0 5627576  36588 772472    0    0   328     0 1510 1047 19  6 73  2  0
0  0      0 5627600  36588 772472    0    0     0     0  425  321  0  0 100  0  0
0  1      0 5627688  36596 772464    0    0     0   216  463  335  0  0 96  4  0
0  0      0 5627792  36596 772472    0    0     0   112  475  364  0  0 99  1  0
0  0      0 5627744  36596 772472    0    0     0     0  441  338  0  0 100  0  0
0  0      0 5634260  36596 772472    0    0     0     0  483  357  0  0 99  0  0
0  0      0 5634368  36596 772472    0    0     0     0  463  326  0  0 100  0  0
0  0      0 5634588  36596 772472    0    0     0     0  459  335  0  0 100  0  0
0  0      0 5634556  36604 772464    0    0     0    52  463  338  0  0 99  1  0
0  0      0 5634524  36604 772472    0    0     0     0  422  278  0  0 100  0  0
0  0      0 5634596  36604 772472    0    0     0     0  495  369  1  0 99  0  0
0  0      0 5634336  36604 772472    0    0     0     0  467  350  0  0 100  0  0
0  0      0 5634336  36604 772472    0    0     0     0  416  324  0  0 100  0  0
1  0      0 5623576  36604 772472    0    0     0     0  505  337  5  1 95  0  0
2  0      0 5623744  36604 772472    0    0     0     0 1445 1183 37  6 57  0  0
2  0      0 5624004  36612 772480    0    0     0    28 1334 1151 42 10 47  1  0
0  0      0 5623276  36612 772480    0    0     0     0 1452 1172 23  7 70  0  0
Nachtrag

Ich habe jetzt nochmal die Situation geprüft. Lese-I/O vom MySQL-Server findet nicht mehr statt, auch wenn ich die Hälfte aller Webseiten aufrufe. Vereinzelt wird noch in MB-Größenordnungen etwas bei bestimmten Seiten geschrieben durch den MySQL, meistens aber nur sehr wenig(KB-Bereich). Also alles super.

Das ist der aktuelle MySQL-Status vom InnoDB-Pool:

Code:
MariaDB [(none)]> SHOW STATUS LIKE '%innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        |                                                  |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 200207 14:00:11 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_load_incomplete    | OFF                                              |
| Innodb_buffer_pool_pages_data         | 47317                                            |
| Innodb_buffer_pool_bytes_data         | 775241728                                        |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 220050                                           |
| Innodb_buffer_pool_pages_free         | 17947                                            |
| Innodb_buffer_pool_pages_misc         | 272                                              |
| Innodb_buffer_pool_pages_total        | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 2401                                             |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 82867070                                         |
| Innodb_buffer_pool_reads              | 24828                                            |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 1291394                                          |
+---------------------------------------+--------------------------------------------------+
19 rows in set (0.001 sec)
 
Last edited:
Top