MySQL unter hoher Last und T3

Hallo,
hat jemand Tipps wie man die MySQL Performance speziell an Typo3 als
CMS anpassen kann.

Folgendes Verhalten lässt sich beobachten, wenn viele User auf der
Seite sind und ein Datensatz aktualisiert wird, dann erzeugt der erste
Clientzugriff einen Refresh der Seite und der sperrt die ganze Tabelle.
In der Folge kaskadieren andere User die Anzahl der Anfragen hoch, es
gibt einen haufen Slow Queries und irgendwann löst sich der Spuk
wieder auf.

Das andere ist, das teilweise sehr sehr viele User kurzfristig
zugreifen und laut MySQL Statistik >4000 Anfragen/s abgearbeitet (nach
Restart). Das geht dann zwar runter, bleibt aber teilweise eine ganze
Zeit bei ca. 2000 Anfragen/s stehen. Normal wären hier bei anderen T3
Webs so um die 100, in diesem Fall wohl eher 300.

Großes Drama, der Apache staut dann auch hoch, bis die Webserver unter
Last sind, da der DB Server nicht mehr liefert.

Die Systemumgebung mag etwas ungewöhnlich sein, der DB Server ist
Standalone
4x Xeon mit 16GB RAM, es findet sich wenig Info dazu, was dort
angemessene Settings wären.

Von den Default-Einstellungen abweichend sind folgende Settings:
MySQL Init Skript mit ulimit -HSn 65535 und die Stop Time hochgesetzt,
da die Buffer ewig zum flushen brauchen.

my.cnf
skip-innodb
skip-bdb
skip-external-locking

max_connections =3D 2000
max_connect_errors =3D 16M

connect_timeout =3D 10
wait_timeout =3D 20

table_cache =3D 24K
tmp_table_size =3D 448M

thread_cache_size =3D 1024
thread_concurrency =3D 16

query_cache_limit =3D 36M
query_cache_size =3D 1G
query_cache_type =3D 1
query_prealloc_size =3D 16384
query_alloc_block_size =3D 16384
query_cache_wlock_invalidate =3D on

key_buffer =3D 1G

max_allowed_packet =3D 32M

sort_buffer_size =3D 3M
read_buffer_size =3D 1M
read_rnd_buffer_size =3D 8M

myisam_sort_buffer_size =3D 64M
log-slow-queries =3D /var/lib/mysql/mysql-slow.log
long_query_time =3D 30

Hat einer eine Idee, wo es sich lohnt dran zu drehen ?

Danke & viele Grüße
ff

Re: MySQL unter hoher Last und T3

fred.feinbein@googlemail.com wrote:

> hat jemand Tipps wie man die MySQL Performance speziell an Typo3 als
> CMS anpassen kann.

Der erste Tip wäre wohl, um Typo3 einen großen Bogen zu machen.
Das Ding saugt Universen durch Nanotubes.

> Folgendes Verhalten lässt sich beobachten, wenn viele User auf der
> Seite sind und ein Datensatz aktualisiert wird, dann erzeugt der erste
> Clientzugriff einen Refresh der Seite und der sperrt die ganze Tabelle.
> In der Folge kaskadieren andere User die Anzahl der Anfragen hoch, es
> gibt einen haufen Slow Queries und irgendwann löst sich der Spuk
> wieder auf.

Typisches Locking-Phänomen. Würde man optimieren, indem man

a) Queries so weit optimiert, daß sie schnell genug laufen um keine
Staus zu verursachen

b) Daten, die häufig gelesen werden und Daten die häufig geschrieben
werden, voneinander trennt.

c) einen Tabellentyp benutzt, der weniger LOCKs erzeugt (das wäre dann
InnoDB)

d) in großen Szenarien trennt man reine Lese-Server per Replikation vom
Schreib-Server ab

> Das andere ist, das teilweise sehr sehr viele User kurzfristig
> zugreifen und laut MySQL Statistik >4000 Anfragen/s abgearbeitet (nach
> Restart). Das geht dann zwar runter, bleibt aber teilweise eine ganze
> Zeit bei ca. 2000 Anfragen/s stehen. Normal wären hier bei anderen T3
> Webs so um die 100, in diesem Fall wohl eher 300.

Sind das echte User-Aktivitäten, oder füllt da Typo3 bloß mal wieder
irgendwelche Caches?

> Großes Drama, der Apache staut dann auch hoch, bis die Webserver unter
> Last sind, da der DB Server nicht mehr liefert.

Das ist das zu erwartende Verhalten.

> Die Systemumgebung mag etwas ungewöhnlich sein, der DB Server ist
> Standalone
> 4x Xeon mit 16GB RAM, es findet sich wenig Info dazu, was dort
> angemessene Settings wären.

Ein guter Ausgangspunkt wäre die mitgelieferte my-huge.cnf. Und dann
natürliche die Empfehlungen aus dem Handbuch.


> Von den Default-Einstellungen abweichend sind folgende Settings:

> max_connections = 2000

Wahrscheinlich zu hoch. Aber was solls.

> table_cache = 24K

Soviele Tabellen hast du nicht. Nein.
Hast du spaßeshalber mal in das MySQL-Errorlog geschaut? Ich bin
mir sicher, daß mysqld einige dieser Resource-Limits gleich wieder
zurücksetzt, weil er die nicht bekommt.

> max_connect_errors = 16M

LOL.

> tmp_table_size = 448M

Sicher, daß du das brauchst?

> sort_buffer_size = 3M

Wäre hier vermutlich besser aufgehoben.

> query_cache_size = 1G
> query_cache_limit = 36M
> query_cache_type = 1

Wenn der Query-Cache was bringt (messen!) könnte man den bei 16GB RAM
ruhig noch etwas größer machen.

> key_buffer = 1G

Mit 16GB RAM und (vermutlich?) alles MyISAM-Tabellen *muß* man das
größer machen. Hoffentlich sind deine XEONs schon EM64T-tauglich und
du fährst ein 64-Bit Linux. Sonst ist hier bei 2GB Schluß. Angemessen
wären eher 8GB.


XL

Re: MySQL unter hoher Last und T3

Hi Axel,

Axel Schwenke wrote:
> Der erste Tip wäre wohl, um Typo3 einen großen Bogen zu machen.
> Das Ding saugt Universen durch Nanotubes.

leider nicht möglich...

> Typisches Locking-Phänomen. Würde man optimieren, indem man
>
> a) Queries so weit optimiert, daß sie schnell genug laufen um keine
> Staus zu verursachen

Dazu müsste man in T3 eingreifen, was zuviel nach sich ziehen
würde...

> b) Daten, die häufig gelesen werden und Daten die häufig geschrieben
> werden, voneinander trennt.

Replikation ?

> c) einen Tabellentyp benutzt, der weniger LOCKs erzeugt (das wäre dann
> InnoDB)

Kann man das probieren aus MyISAM innodbs zu machen ?

> d) in großen Szenarien trennt man reine Lese-Server per Replikation vom
> Schreib-Server ab

Das ist ja die Krux, dass die Seiten im Cache von den Clients /
Abrufenden erzeugt werden.
Variante wäre natürlich das Caching auszuschalten und auf reine CPU
Perfromance zu gehen.

> > Das andere ist, das teilweise sehr sehr viele User kurzfristig
> > zugreifen und laut MySQL Statistik >4000 Anfragen/s abgearbeitet (nach

> Sind das echte User-Aktivitäten, oder füllt da Typo3 bloß mal wieder
> irgendwelche Caches?

Ich fürchte die sind echt, Gegenteiliges lies nicht feststellen.

> Ein guter Ausgangspunkt wäre die mitgelieferte my-huge.cnf. Und dann
> natürliche die Empfehlungen aus dem Handbuch.

Die war dort die Basis

> > max_connections =3D 2000
>
> Wahrscheinlich zu hoch. Aber was solls.
>
> > table_cache =3D 24K
>
> Soviele Tabellen hast du nicht. Nein.
> Hast du spaßeshalber mal in das MySQL-Errorlog geschaut? Ich bin
> mir sicher, daß mysqld einige dieser Resource-Limits gleich wieder
> zurücksetzt, weil er die nicht bekommt.

Das ist richtig, war nur ein Versuch...
Die Werte werden aber nicht zurückgesetzt.

> > max_connect_errors =3D 16M
>
> LOL.

Uralt Standardsetting, ich habe es einmal erlebt, dass MySQL den
Webserver aussperrte weil es irgend wie >10 Fehler durch
Netzwerkprobleme gab...

> > tmp_table_size =3D 448M
>
> Sicher, daß du das brauchst?

Kleiner glaube an den phpMyAdmin Output

> > sort_buffer_size =3D 3M
>
> Wäre hier vermutlich besser aufgehoben.

Okay, testen ich...

> > query_cache_size =3D 1G
> > query_cache_limit =3D 36M
> > query_cache_type =3D 1
>
> Wenn der Query-Cache was bringt (messen!) könnte man den bei 16GB RAM
> ruhig noch etwas größer machen.

Qcache_free_blocks 72 k
Qcache_free_memory 249 M
Qcache_hits 3 M
Qcache_inserts 2 M
Qcache_lowmem_prunes 8 k
Qcache_not_cached 48 k
Qcache_queries_in_cache 149 k

> > key_buffer =3D 1G
>
> Mit 16GB RAM und (vermutlich?) alles MyISAM-Tabellen *muß* man das
> größer machen. Hoffentlich sind deine XEONs schon EM64T-tauglich und
> du fährst ein 64-Bit Linux. Sonst ist hier bei 2GB Schluß. Angemessen
> wären eher 8GB.

Red Hat 4 AS 64Bit...

Hilft das irgendwie beim Locking ?

Derzeit probiere ich pconnect auf den Webservern um zumindest die
Clientzahl stabil zuhalten...

Any other Ideas ?

Danke
ff

Re: MySQL unter hoher Last und T3

"fred.feinbein@googlemail.com" wrote:
> Axel Schwenke wrote:
>
>> Typisches Locking-Phänomen. Würde man optimieren, indem man
>>
>> b) Daten, die häufig gelesen werden und Daten die häufig geschrieben
>> werden, voneinander trennt.
>
> Replikation ?

Z.B.

Die Alternative wäre, am Datenmodell zu schrauben. Wirst du nicht wollen.

>> c) einen Tabellentyp benutzt, der weniger LOCKs erzeugt (das wäre dann
>> InnoDB)
>
> Kann man das probieren aus MyISAM innodbs zu machen ?

Sicher. Dafür mußt du InnoDB konfigurieren, MySQL restarten und dann
kannst du die Daten per ALTER TABLE migrieren.

>> d) in großen Szenarien trennt man reine Lese-Server per Replikation vom
>> Schreib-Server ab
>
> Das ist ja die Krux, dass die Seiten im Cache von den Clients /
> Abrufenden erzeugt werden.
> Variante wäre natürlich das Caching auszuschalten und auf reine CPU
> Perfromance zu gehen.

Muß ich das jetzt verstehen? Auch wenn die Clients separat cachen, hat
man trotzdem viel mehr Lese- als Schreibzugriffe, auch bei CMSen. Daher
bezieht die Replikation ihr Potential zur Performanceverbesserung.

Aber auch damit wirst du nicht um Änderungen an T3 herum kommen. Immer-
hin mußt du ja mehrere Datenbank-Verbindungen managen statt zur einer.

>> > tmp_table_size = 448M
>>
>> Sicher, daß du das brauchst?
>
> Kleiner glaube an den phpMyAdmin Output

???

SHOW STATUS LIKE 'Created_tmp%_tables';

Erst wenn nennenswert tmp_tables auf der Platte erzeugt werden, mußt du
das Limit für in-memory tmp_tables erhöhen.

>> > sort_buffer_size = 3M
>>
>> Wäre hier vermutlich besser aufgehoben.
>
> Okay, testen ich...

Vorher wäre es sinnvoll, das slow log auf Queries zu checken, die nicht
in-memory sortieren können.

>> > query_cache_size = 1G
>> > query_cache_limit = 36M
>> > query_cache_type = 1
>>
>> Wenn der Query-Cache was bringt (messen!) könnte man den bei 16GB RAM
>> ruhig noch etwas größer machen.
>
> Qcache_free_blocks 72 k
> Qcache_free_memory 249 M
> Qcache_hits 3 M
> Qcache_inserts 2 M
> Qcache_lowmem_prunes 8 k
> Qcache_not_cached 48 k
> Qcache_queries_in_cache 149 k

Sieht nicht überragend aus. Jedes in den Cache geschriebene Ergebnis
wird im Schnitt nur 1.5-mal gelesen. Immerhin scheint der Cache groß
genug zu sein. Hast du mal SHOW STATUS LIKE 'Com_select' zum Vergleich?

Ganz allgemein hilft ein gutes Monitoring der Datenbank unheimlich,
wenn man Bottlenecks finden will. Interessante Fragen wären:

a) was ist der Query-Mix (Lesen zu Schreiben, beim Schreiben INSERT zu
UPDATE zu DELETE)?

b) konzentriert sich Last auf bestimmte Tabellen?

c) konzentrieren sich LOCKs auf bestimmte Tabellen?

d) gibt es notorisch langsame Queries?

Korrelieren irgendwelche Lastspitzen? Korreliert eine langsame
Datenbank mit heftiger I/O-Aktivität? etc. pp.

>> > key_buffer = 1G
>>
>> Mit 16GB RAM und (vermutlich?) alles MyISAM-Tabellen *muß* man das
>> größer machen. Hoffentlich sind deine XEONs schon EM64T-tauglich und
>> du fährst ein 64-Bit Linux. Sonst ist hier bei 2GB Schluß. Angemessen
>> wären eher 8GB.
>
> Red Hat 4 AS 64Bit...

Dann mach den key_buffer größer. Addiere die Größe aller .MYI Files.
Wenn du genug RAM hast, mach den key_buffer so groß.

> Hilft das irgendwie beim Locking ?

Caches helfen nicht gegen Locking. Aber sie machen Queries schneller.
Auch INSERTs. Das verringert die Gefahr, daß dir LOCKs auf die Füße
fallen.

> Derzeit probiere ich pconnect auf den Webservern um zumindest die
> Clientzahl stabil zuhalten...

*Das* solltest du lassen. Es bringt nicht nur nichts, sondern es macht
u.U. neue Probleme.

http://groups.google.com/group/comp.databases.mysql/tree/bro wse_frm/thread/4ae68befe1b488e7/7d3eb52f6130b15f?rnum=1&hl=e n&_done=%2Fgroup%2Fcomp.databases.mysql%2Fbrowse_frm%2Fthrea d%2F4ae68befe1b488e7%2Fe843f0b9e59ad710%3Flnk%3Dst%26q%3D%26 rnum%3D1%26hl%3Den%26#doc_e843f0b9e59ad710


XL

Re: MySQL unter hoher Last und T3

Axel Schwenke wrote:
> "fred.feinbein@googlemail.com" wrote:
> > Axel Schwenke wrote:

> Aber auch damit wirst du nicht um Änderungen an T3 herum kommen. Immer-
> hin mußt du ja mehrere Datenbank-Verbindungen managen statt zur einer.

Gut, das ist klar...
Favorisiert für Replikation ist wohl folgendes Setup:
Dateisystem per NFS, wenn das nicht hält, Replikation per rsync (was
nicht wirklich taugt, da Zeitversatz).
DB Replikation und dann T3 auf den einzelnen Servern nur per localhost,
also nicht wirklich was zum Anpassen...

> SHOW STATUS LIKE 'Created_tmp%_tables';
>
> Erst wenn nennenswert tmp_tables auf der Platte erzeugt werden, mußt du
> das Limit für in-memory tmp_tables erhöhen.

Created_tmp_disk_tables 544
Created_tmp_tables 1157

> Vorher wäre es sinnvoll, das slow log auf Queries zu checken, die nicht
> in-memory sortieren können.

Hmm, wie stelle ich das genau fest, das Verhalten ist da etwas
chaotisch ?

> >> Wenn der Query-Cache was bringt (messen!) könnte man den bei 16GB RAM
> >> ruhig noch etwas größer machen.
> >
> > Qcache_free_blocks 72 k
> > Qcache_free_memory 249 M
> > Qcache_hits 3 M
> > Qcache_inserts 2 M
> > Qcache_lowmem_prunes 8 k
> > Qcache_not_cached 48 k
> > Qcache_queries_in_cache 149 k
>
> Sieht nicht überragend aus. Jedes in den Cache geschriebene Ergebnis
> wird im Schnitt nur 1.5-mal gelesen. Immerhin scheint der Cache groß
> genug zu sein. Hast du mal SHOW STATUS LIKE 'Com_select' zum Vergleich?

Okay nach dem Test mit den anderen Daten (Siehe unten):
Qcache_hits 4077207
Qcache_inserts 3876495
Com_select 3904024

Wenn ich Dich recht verstehe, quasi 1:1 query wird reingeschrieben und
nur 1x ausgelesen, also bringt nichts ?

> Ganz allgemein hilft ein gutes Monitoring der Datenbank unheimlich,
> wenn man Bottlenecks finden will. Interessante Fragen wären:
>
> a) was ist der Query-Mix (Lesen zu Schreiben, beim Schreiben INSERT zu
> UPDATE zu DELETE)?

Com_insert 241169
Com_update 350551
Com_delete 565260

> b) konzentriert sich Last auf bestimmte Tabellen?

Ja, auf die T3 cache, session, page und tt_content (Suche ist derzeit
inaktiv)

> c) konzentrieren sich LOCKs auf bestimmte Tabellen?

Selbige...

> d) gibt es notorisch langsame Queries?

Nein, leider nicht, ich habe Stichproben gemacht und kriege die
Sitaution so nicht nachgebaut.
Bei einem anderen Web gab es so einen Fall mit einer osCommerce
Datenbank die für die Produktempfehlung alle gekauften Produkte 2x
über Kreuz kombinierte, das war wenigstens nachvollziehbar.

> Korrelieren irgendwelche Lastspitzen? Korreliert eine langsame
> Datenbank mit heftiger I/O-Aktivität? etc. pp.

Ich kriege immer nur die Hardcore Situation mit, wenn die Webserver
Alarm schlagen und dann bleibt meist nur ein kurzer Restart der DB.

> >> > key_buffer =3D 1G
> >>
> >> Mit 16GB RAM und (vermutlich?) alles MyISAM-Tabellen *muß* man das
> >> größer machen. Hoffentlich sind deine XEONs schon EM64T-tauglich u=
nd
> >> du fährst ein 64-Bit Linux. Sonst ist hier bei 2GB Schluß. Angemes=
sen
> >> wären eher 8GB.
> >
> > Red Hat 4 AS 64Bit...
>
> Dann mach den key_buffer größer. Addiere die Größe aller .MYI Fil=
es.
> Wenn du genug RAM hast, mach den key_buffer so groß.

Da sind wir jetzt bei einem Punkt den ich vergaß, momentan habe ich
EINE T3 Web DB komplett isoliert auf dem Server, d.h. die .MYI Files
zusammen haben gerade mal 60 MB (Gesamt: 577MB). Allerdings greifen 3
Webserver auf die DB zu.

> > Derzeit probiere ich pconnect auf den Webservern um zumindest die
> > Clientzahl stabil zuhalten...
>
> *Das* solltest du lassen. Es bringt nicht nur nichts, sondern es macht
> u.U. neue Probleme.

Bedingt durch die Umgebung mit einer Datenbank sollte sich das momentan
handeln lassen, aber danke für die weiteren Ausführungen. Ich wollte
nur für's erste verhindern, dass die Webserver irre viel Connections
zur DB aufbauen und damit die anderen Webs stören.

Also wenn ich Deine Ausführungen verstehe, so wäre es einen Versuch
wert die häufigst benutzten Tabellen in innodbs zu wandeln ? Und wenn
das nicht klappt, muss man an die Verteilung ran ?

Mit folgender Config und bisher keinen Alarm auf dem Webserver habe ich
bislang 18 Slow Queries (>30s)...
SELECT uid FROM pages WHERE uid =3D ...
(öhm, scheint mehr ein Check zu sein, der satte 98 Sekunden dauert)
2x UPDATE tt_content SET hidden ..., timestamp ... WHERE uid=3D...
4x SELECT hash, conetnt, tsatmp FROM fe_session_data WHERE hash=3D ...
LIMIT 0,1
10x SELECT md5hash, imagewidth, imageheight FROM cache_imagesizes WHERE
md5filename =3D ...
SELECT * FROM tt_content WHERE PID=3D ... AND ctype =3D ... LIMIT 0,1
(öhm, handgemachter Export, der lässt sich optimieren)

---
max_connections =3D 2000
connect_timeout =3D 10
wait_timeout =3D 10
table_cache =3D 6K
tmp_table_size =3D 128M
thread_cache_size =3D 1024
thread_concurrency =3D 16
query_cache_limit =3D 48M
query_cache_size =3D 2G
query_cache_type =3D 1
query_prealloc_size =3D 16384
query_alloc_block_size =3D 16384
query_cache_wlock_invalidate =3D on
key_buffer =3D 3G
max_allowed_packet =3D 32M
sort_buffer_size =3D 256M
read_buffer_size =3D 1M
read_rnd_buffer_size =3D 8M
myisam_sort_buffer_size =3D 64M

Wenn der Keybuffer sich tatsächlich nur nach den MYI Files richtet ist
das natürlich Unsinn, ditto für den Cache wenn der so ineffektiv ist.

Wenn ich mir die innodb Parameter angucke gibt es da wohl nicht soviel
zum Anpassen ?

Vielen Dank für die Hilfe und die Anregungen, vielleicht sollten wir
den Thread in den Mailbereich verlagern ?

Obwohl vmtl. viele daran interessiert wären, eine "optimale" T3 Konfig
hinzubekommen ;)

ff

Re: MySQL unter hoher Last und T3

"fred.feinbein@googlemail.com" wrote:
> Axel Schwenke wrote:
>
>> Aber auch damit wirst du nicht um Änderungen an T3 herum kommen. Immer-
>> hin mußt du ja mehrere Datenbank-Verbindungen managen statt zur einer.
>
> Gut, das ist klar...

Hmm. Da bin ich mir gar nicht so sicher. Replikation heißt in diesem
Kontext: alle Schreibzugriffe deiner Applikation gehen auf den Master.
Alle Lesezugriffe, bei denen die Daten unbedingt und absolut frisch
sein müssen, gehen auch auf den Master. Alle anderen Lesezugriffe gehen
auf einen Slave. Im Klartext: die Anwendung (bei dir: T3) muß ihre
Queries entsprechend qualifizieren und auf mehrere (mindestens: 2)
Datenbankverbindungen verteilen.

> Favorisiert für Replikation ist wohl folgendes Setup:
> Dateisystem per NFS, wenn das nicht hält, Replikation per rsync (was
> nicht wirklich taugt, da Zeitversatz).
> DB Replikation und dann T3 auf den einzelnen Servern nur per localhost,

Irgendwie redest du nicht von der gleichen Replikation wie ich:
http://dev.mysql.com/doc/refman/5.0/en/replication.html

Vergiß NFS. Das ist grottenlahm. Außerdem kannst du nicht mit mehreren
mysqld's auf die gleichen Datenfiles zugreifen (oder die Files an
mysqld vorbei per rsync verändern) - das schrottet deine Daten.

>> SHOW STATUS LIKE 'Created_tmp%_tables';
>>
>> Erst wenn nennenswert tmp_tables auf der Platte erzeugt werden, mußt du
>> das Limit für in-memory tmp_tables erhöhen.
>
> Created_tmp_disk_tables 544
> Created_tmp_tables 1157

Tja, scheint so als würden durchaus ein paar temporäre Tabellen an-
gelegt. Wäre mal interessant, herauszubekommen, wodurch. Auf jeden Fall
solltest du das Limit erstmal erhöhen. Am besten verdoppeln und mal
einen Tag lang beobachten. Sobald weniger als 10% (IMMV) der temp.
Tables auf der Platte landen, bauchst du nicht weiter zu erhöhen.

>> Vorher wäre es sinnvoll, das slow log auf Queries zu checken, die nicht
>> in-memory sortieren können.
>
> Hmm, wie stelle ich das genau fest, das Verhalten ist da etwas
> chaotisch ?

Erst mal stellst du slow-query-limit auf einen sinnvollen Wert (ich
habe schon Applikationen gesehen, wo 1 Sekunde zu lang war und man
eigentlich gar kein vernünftiges Limit setzen konnte). Auf jeden Fall
sind 30 sec viel zu lang - da hat mancher Webbrowser einen kürzeren
Timeout. Irgendwas zwischen 5 und 10 Sekunden wäre wohl angemessen.

Dann läßt du mal über einen normalen Tag ein slow-log schreiben und
schaust dir nachher die am häufigsten vertretenen Queries mit EXPLAIN
an. Hellhörig solltest du immer dann werden, wenn keine Indizes benutzt
werden und/oder "using filesort" vorkommt.

> Qcache_hits 4077207
> Qcache_inserts 3876495
> Com_select 3904024
>
> Wenn ich Dich recht verstehe, quasi 1:1 query wird reingeschrieben und
> nur 1x ausgelesen, also bringt nichts ?

Bringt zumindest nicht viel (was mich eigentlich wundert). Hier werden
ziemlich genau so viele SELECTs aus dem Cache beantwortet, wie nicht.
Aber da der Query-Cache sehr leichtgewichtig ist (dafür aber auch mal
Einträge invalidiert, die noch OK wären) kannst du den ruhig anlassen.

>> Ganz allgemein hilft ein gutes Monitoring der Datenbank unheimlich,
>> wenn man Bottlenecks finden will. Interessante Fragen wären:
>>
>> a) was ist der Query-Mix (Lesen zu Schreiben, beim Schreiben INSERT zu
>> UPDATE zu DELETE)?
>
> Com_insert 241169
> Com_update 350551
> Com_delete 565260

Also ca. 3 Mio SELECTs zu 1 Mio INS/UPD/DEL. Das ist recht viel
Schreiblast. Allerdings vermute ich, daß da etliche Nonsense-Schreib-
zugriffe a'la "Seite XYZ wurde angeschaut" dabei sind. Solltest du
versuchen, abzuschalten. Bei einem meiner vorigen Brötchengeber hat
ironischerweise das Bannersystem die größte Last auf der Datenbank
erzeugt. Es hat kein Geld eingespielt und Nutzer dürften sich über
Banner auch seltenst freuen.

>> b) konzentriert sich Last auf bestimmte Tabellen?
>
> Ja, auf die T3 cache, session, page und tt_content (Suche ist derzeit
> inaktiv)

Tja, die T3 Cache Tabelle wäre ein prima Kandidat, um jedem Webserver
seine eigene zu geben. Die Session-Tabelle dito, das setzt aber voraus,
daß dein Loadbalancer nach Sessions verteilen kann.

>> c) konzentrieren sich LOCKs auf bestimmte Tabellen?
>
> Selbige...

Bei der Session-Tabelle verwundert das nicht. Beim Cache wird es wohl
auf die Einstellung (in T3) ankommen. Wenn ein Cache was bringen soll,
muß man schon mindestens 10x soviel Lesen wie Schreiben. Ansonsten
frißt die Verwaltung des Caches die Vorteile wieder auf.

>> d) gibt es notorisch langsame Queries?
>
> Nein, leider nicht, ich habe Stichproben gemacht und kriege die
> Sitaution so nicht nachgebaut.

Wenn du nur Queries über 30 Sekunden Dauer loggst, ist klar daß du die
Probleme nicht siehst.

>> Dann mach den key_buffer größer. Addiere die Größe aller .MYI Files.
>> Wenn du genug RAM hast, mach den key_buffer so groß.
>
> Da sind wir jetzt bei einem Punkt den ich vergaß, momentan habe ich
> EINE T3 Web DB komplett isoliert auf dem Server, d.h. die .MYI Files
> zusammen haben gerade mal 60 MB (Gesamt: 577MB). Allerdings greifen 3
> Webserver auf die DB zu.

Mal langsam: du hast einen Server mit 16GB RAM und da drauf läuft ein
MySQL, dessen gesamte Datenfiles gerade mal 577MB groß sind? Das läuft
ja komplett aus dem RAM. Da darf *gar* nichts langsam sein.

Vermutlich ein drastisch kaputtes Datenmodell.

> Mit folgender Config und bisher keinen Alarm auf dem Webserver habe ich
> bislang 18 Slow Queries (>30s)...

> SELECT uid FROM pages WHERE uid = ...
> (öhm, scheint mehr ein Check zu sein, der satte 98 Sekunden dauert)

Ist da ein Index auf uid? Und wäre nicht SELECT COUNT(*) WHERE uid=...
eine viel sinnvollere Query (die uid weiß der Aufrufer ja bereits, also
dürfte ihn als einziges die Anzahl Treffer interessieren...)

> 2x UPDATE tt_content SET hidden ..., timestamp ... WHERE uid=...
> 4x SELECT hash, conetnt, tsatmp FROM fe_session_data WHERE hash= ...
> LIMIT 0,1
> 10x SELECT md5hash, imagewidth, imageheight FROM cache_imagesizes WHERE
> md5filename = ...
> SELECT * FROM tt_content WHERE PID= ... AND ctype = ... LIMIT 0,1

Sind hier passende Indizes vorhanden? Was sagt EXPLAIN zu diesen
Queries?

> Wenn ich mir die innodb Parameter angucke gibt es da wohl nicht soviel
> zum Anpassen ?

Im wesentlichen stellst du die Größe des Page-Caches ein. Da InnoDB
auch Daten cached, darf der ruhig auch größer sein. Das steht aber
*alles* auch im Handbuch.

> Vielen Dank für die Hilfe und die Anregungen, vielleicht sollten wir
> den Thread in den Mailbereich verlagern ?

Nein. Das ist hier schon richtig.

> Obwohl vmtl. viele daran interessiert wären, eine "optimale" T3 Konfig
> hinzubekommen ;)

Ich hoffe nicht. Vielleicht lassen sich zukünftige T3 Anwender eher
davon abhalten, T3 überhaupt zu verwenden. Ich habe da einmal näher
reingeschaut, weil ein Freund das verwendet und mir ist spontan übel
geworden. Seitdem habe ich mir geschworen, T3 nur noch gegen Geld
(quasi Schmerzensgeld) anzufassen.


XL

Re: MySQL unter hoher Last und T3

Axel Schwenke wrote:
> Mal langsam: du hast einen Server mit 16GB RAM und da drauf läuft ein
> MySQL, dessen gesamte Datenfiles gerade mal 577MB groß sind? Das läuft
> ja komplett aus dem RAM. Da darf *gar* nichts langsam sein.
>
> Vermutlich ein drastisch kaputtes Datenmodell.

Hmm, sehen wir mal davon ab, wie dramatisch bewertest Du die restliche
Systemumgebung ?
Vorher war das 1 Web + 1 DB Server mit Debian, 1 angepasster Apache 1.3
und ein auf dem System kompiliertes MySQL 4.0.x mit T3 3.5. Dort lief
eigentlich alles gut und nur sehr selten gab es mal eine Lastspitze auf
dem Webserver, die zeitlich aber immer mit irgendwas bekannten in
Einklang zu bringen war.

Dann wurde gewechselt auf mehr Server um das auch zu beenden und auch
das neue T3 4.x zu nehmen. Zum Einsatz kommen die Standardpakete von
Red Hat Apache 2 + MySQL 4.1.x (derzeit 20).

Ich habe jetzt innodb am Start und das Slow Query Log runter auf 3
Sekunden, bin gespannt was im Laufe des Tages so aufläuft...

Viele Grüße
ff

Re: MySQL unter hoher Last und T3

"fred.feinbein@googlemail.com" wrote:
> Axel Schwenke wrote:

>> Mal langsam: du hast einen Server mit 16GB RAM und da drauf läuft ein
>> MySQL, dessen gesamte Datenfiles gerade mal 577MB groß sind? Das läuft
>> ja komplett aus dem RAM. Da darf *gar* nichts langsam sein.
>>
>> Vermutlich ein drastisch kaputtes Datenmodell.
>
> Hmm, sehen wir mal davon ab, wie dramatisch bewertest Du die restliche
> Systemumgebung ?
> Vorher war das 1 Web + 1 DB Server mit Debian, 1 angepasster Apache 1.3
> und ein auf dem System kompiliertes MySQL 4.0.x mit T3 3.5. Dort lief
> eigentlich alles gut und nur sehr selten gab es mal eine Lastspitze auf
> dem Webserver, die zeitlich aber immer mit irgendwas bekannten in
> Einklang zu bringen war.
>
> Dann wurde gewechselt auf mehr Server um das auch zu beenden und auch
> das neue T3 4.x zu nehmen. Zum Einsatz kommen die Standardpakete von
> Red Hat Apache 2 + MySQL 4.1.x (derzeit 20).

In meiner Zeit als Sysadmin habe ich mir immer die Zeit genommen,
meine Brot-und-Butter Applikationen (Apache, PHP, MySQL) selber zu
kompilieren, um so perfekt an die Aufgabe und Umgebung angepaßte
Binaries zu erhalten. Das kann durchaus mal Faktor 1.5 bis 2 ausmachen.
Besonderes Augenmerk würde ich hier auf PHP und MySQL legen.

Lange Zeit war auch die Anbindung von PHP an Apache2 problematisch.
Mittlerweile dürfte das besser sein, allerdings würde ich mich nicht
trauen, ein multithreaded-MPM in Verbindng mit PHP zu verwenden.

> Ich habe jetzt innodb am Start und das Slow Query Log runter auf 3
> Sekunden, bin gespannt was im Laufe des Tages so aufläuft...

Dann noch ein *ganz* wichtiger Optimierungstip: schreib ein
innodb_flush_log_at_trx_commit=2 in deine my.cnf. Hintergrund: deine
Applikation benutzt keine Transaktionen, MySQL läuft mit AUTO_COMMIT
=> jeder Schreibzugriff enthält ein implizites COMMIT. In der
Defaulteinstellung flusht InnoDB das Transaktionlog bei jedem COMMIT
auf die Platte, was deinen Durchsatz auf 100-150 Schreibzugriffe pro
Sekunde beschränkt. Mit obiger Einstellung schreibt InnoDB das Log nur
noch in die betriebssystemseitigen Buffer und überläßt dem Betriebs-
system das Flushen.

Ich hoffe mal, du hast innodb_buffer_pool_size schon sinnvoll gesetzt?


XL

Re: MySQL unter hoher Last und T3

Axel Schwenke wrote:

> Dann noch ein *ganz* wichtiger Optimierungstip: schreib ein
> innodb_flush_log_at_trx_commit=3D2 in deine my.cnf. Hintergrund: deine

Erledigt ;)

> Ich hoffe mal, du hast innodb_buffer_pool_size schon sinnvoll gesetzt?

Vorsichtig 1GB vergeben...

Bis jetzt gibt es 2 Queries...
Über Nacht war es der hier 14x ca. 4 - 8 Sekunden:
UPDATE pages SET SYS_LASTCHANGED=3D'1156647923'
WHERE uid=3D16235;
fällt eher in die Kategorie überflüssig, allerdings ist auf der
Seite ein Votingergebnis, also macht es schon Sinn das zu aktualisieren
damit man mit der Cache Tabelle gegenprüfen kann.

und jetzt neu mit 54 Sekunden:
EXPLAIN SELECT *
FROM tt_content
WHERE tt_content.pid
IN ( 12387 )
AND colPos =3D2
AND sys_language_uid
IN ( 0 )
AND tt_content.deleted =3D0
AND tt_content.t3ver_state !=3D1
AND tt_content.hidden =3D0
AND (
tt_content.starttime <=3D1156679238
)
AND (
tt_content.endtime =3D0
OR tt_content.endtime >1156679238
)
AND (
tt_content.fe_group =3D ''
OR tt_content.fe_group =3D '0'
OR (
tt_content.fe_group LIKE '%,0,%'
OR tt_content.fe_group LIKE '0,%'
OR tt_content.fe_group LIKE '%,0'
OR tt_content.fe_group =3D '0'
)
OR (
tt_content.fe_group LIKE '%,-1,%'
OR tt_content.fe_group LIKE '-1,%'
OR tt_content.fe_group LIKE '%,-1'
OR tt_content.fe_group =3D '-1'
)
)
ORDER BY sorting;

ergibt

id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE tt_content ref parent,deleted,hidden parent 4 const 2 Using
where; Using filesort

also mit filesort wohl ein Killer, bei Indizes über
uid, pid, deleted, hidden, sorting

Grüße
ff

Re: MySQL unter hoher Last und T3

fred.feinbein@googlemail.com wrote:
> AND (
> tt_content.fe_group = ''
> OR tt_content.fe_group = '0'
> OR (
> tt_content.fe_group LIKE '%,0,%'
> OR tt_content.fe_group LIKE '0,%'
> OR tt_content.fe_group LIKE '%,0'
> OR tt_content.fe_group = '0'
> )
> OR (
> tt_content.fe_group LIKE '%,-1,%'
> OR tt_content.fe_group LIKE '-1,%'
> OR tt_content.fe_group LIKE '%,-1'
> OR tt_content.fe_group = '-1'
> )
> )

Ach du große, große Scheiße.

Richtiges Datenmodell und ein LEFT JOIN waren wohl zu kompliziert.

Grüße
Kai


--
This signature is left as an exercise for the reader.

Re: MySQL unter hoher Last und T3

"fred.feinbein@googlemail.com" wrote:
>
> Axel Schwenke wrote:
>
>> Dann noch ein *ganz* wichtiger Optimierungstip: schreib ein
>> innodb_flush_log_at_trx_commit=2 in deine my.cnf. Hintergrund: deine
>
> Erledigt ;)

Gut.

> jetzt neu mit 54 Sekunden:
> EXPLAIN SELECT *
> FROM tt_content
> WHERE tt_content.pid
> IN ( 12387 )
> AND colPos =2
> AND sys_language_uid
> IN ( 0 )
> AND tt_content.deleted =0
> AND tt_content.t3ver_state !=1
> AND tt_content.hidden =0
> AND (
> tt_content.starttime <=1156679238
> )
> AND (
> tt_content.endtime =0
> OR tt_content.endtime >1156679238
> )
> AND (
> tt_content.fe_group = ''
> OR tt_content.fe_group = '0'
> OR (
> tt_content.fe_group LIKE '%,0,%'
> OR tt_content.fe_group LIKE '0,%'
> OR tt_content.fe_group LIKE '%,0'
> OR tt_content.fe_group = '0'
> )
> OR (
> tt_content.fe_group LIKE '%,-1,%'
> OR tt_content.fe_group LIKE '-1,%'
> OR tt_content.fe_group LIKE '%,-1'
> OR tt_content.fe_group = '-1'
> )
> )
> ORDER BY sorting;



> ergibt
>
> id select_type table type possible_keys key key_len ref rows
> Extra
> 1 SIMPLE tt_content ref parent,deleted,hidden parent 4 const 2 Using
> where; Using filesort
>
> also mit filesort wohl ein Killer, bei Indizes über
> uid, pid, deleted, hidden, sorting

Am Sortieren wirds wohl eher nicht liegen. Der Optimizer schätzt ja,
daß nur 2 rows übrig bleiben. Allerdings kann MySQL vor 5.0 noch keine
Indizes für OR verwenden. Und niemals für LIKE '%...'

Grausliches Datenmodell eben. Und entsprechend grausliche Queries.


XL

Re: MySQL unter hoher Last und T3

Hallo,
kleiner Zwischenstatus,
seitdem die DB komplett auf innodb läuft sind die Table_locks_waited
auf 0.
Allerdings haben sich trotzdem gut 9K Queries gesammelt und ich habe
erst am WE Zeit ein kleines Auswertungstool anzupassen...

Aber bei der letzten Lastspitze ist mir folgendes krasses Verhältnis
aufgefallen...

bei ca. 1100 Anfragen/s

steht bei Connections:
max. concurrent connections 851
Failed attempts 0 0.00 0.00%
Aborted 21 k 106.91 k 114.60%
Total 18 k 93.29 k 100.00%

Liegt das jetzt an der tatsächlichen Last, oder platt in den Raum
gesagt...
Gibt es Tool, welches HTTP Abfragen abschickt, die Verbindung cancelt,
nicht vom Apache geloggt wird, aber dadurch die DB Anfragen hochtreibt
?

Andere Gründe ?

Grüße
ff

Re: MySQL unter hoher Last und T3

"fred.feinbein@googlemail.com" wrote:
> Hallo,
> kleiner Zwischenstatus,
> seitdem die DB komplett auf innodb läuft sind die Table_locks_waited
> auf 0.
> Allerdings haben sich trotzdem gut 9K Queries gesammelt und ich habe
> erst am WE Zeit ein kleines Auswertungstool anzupassen...

Du kennst das 'mysqldumpslow' Tool, das mit MySQL kommt?

> Aber bei der letzten Lastspitze ist mir folgendes krasses Verhältnis
> aufgefallen...
>
> bei ca. 1100 Anfragen/s
>
> steht bei Connections:
> max. concurrent connections 851
> Failed attempts 0 0.00 0.00%
> Aborted 21 k 106.91 k 114.60%
> Total 18 k 93.29 k 100.00%

Das steht wo? kannst du das mal im Kontext mit SHOW STATUS zeigen?
Außerdem laufen die Zähler auch manchmal über, so daß gerade bei einem
stark belasteten Server der Zähler für 'connections' gerade einen
niedrigeren Wert zeigt, als 'connect errors'. Ersterer ist dann halt
schon ein (oder mehr) mal übergelaufen.


XL