MySQL Connections buendeln

Hallo zusammen,

ich habe hier einen etwas seltsamen MySQL Anwendungsfall. Und zwar muessen
viele Prozesse mit kurzer TTL von vielen Rechnern aus jeweils kurz eine
Connection zu einem MySQL Server aufmachen, ein oder zwei selects
durchfuehren und danach die Connection wieder schliessen.

Diese vielen Verbindungsauf- und abbauten, zusammen mit der hohen Gesamtzahl
der Connections, die das anscheinend existierende 4k Limit weit
uebersteigen wuerde, lassen mich nach einer Moeglichkeit suchen, die
Connections ausserhalb der Prozesse persistent zu halten und
wiederzubenutzen.
Dabei fiel mein Blick auf MySQL Proxy:

http://dev.mysql.com/tech-resources/articles/proxy-gettingst arted.html

Nun meine Frage, hat jemand von Euch das schon mal benutzt? Kann der Proxy
die Connection zur DB offen halten, auch wenn lokal ständig Connections
auf- und abgebaut werden?

Oder gibt es ein anderes Tool um das zu erreichen?

Vielen Dank und Gruesse...

Stephan



--
Freedom isn't lost in one big step when the storm-troopers
show up at your door. It is lost in little pieces, each
so small that they tend to be ignored.
Richard B. Johnson

Re: MySQL Connections buendeln

Stephan Menzel wrote:
>
> ich habe hier einen etwas seltsamen MySQL Anwendungsfall. Und zwar muessen
> viele Prozesse mit kurzer TTL von vielen Rechnern aus jeweils kurz eine
> Connection zu einem MySQL Server aufmachen, ein oder zwei selects
> durchfuehren und danach die Connection wieder schliessen.

Lokales Netzwerk, oder ist da eine Engstelle dazwischen?

> Diese vielen Verbindungsauf- und abbauten, zusammen mit der hohen Gesamtzahl
> der Connections, die das anscheinend existierende 4k Limit weit
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
häh?

> uebersteigen wuerde, lassen mich nach einer Moeglichkeit suchen, die
> Connections ausserhalb der Prozesse persistent zu halten und
> wiederzubenutzen.

> Dabei fiel mein Blick auf MySQL Proxy:

AFAIK kann der das derzeit nicht.

> Oder gibt es ein anderes Tool um das zu erreichen?

SQL Relay kann wohl auch connection pooling


Aber weder TCP-Connections noch der MySQL-Handshake sind sonderlich
teuer (von allfälligen Ausnahmen mal abgesehen).

Das max_connections Limit im MySQL Server hängt an
a) Hauptspeicher. Derzeit belegt jede offene Client-Connection einen
Thread und damit Speicherplatz für Stack und lokale Variablen.
b) File Handles. Jede Client-Connection verbraucht ein Filehandle für
den TCP-Socket.

Auf geeigneter Hardware kannst du dieses Limit also richtig hoch
setzen.

XL

Re: MySQL Connections buendeln

Hallo,

Axel Schwenke wrote:
> Lokales Netzwerk, oder ist da eine Engstelle dazwischen?

Das Netzwerk ist nicht der Flaschenhals.

>> Dabei fiel mein Blick auf MySQL Proxy:
>
> AFAIK kann der das derzeit nicht.

Sah ich auch so. Ich hab' mir heute selbst ein kleines Tool geschrieben
welches das kann. Das Problem war also aus der Welt und...

> Aber weder TCP-Connections noch der MySQL-Handshake sind sonderlich
> teuer (von allfälligen Ausnahmen mal abgesehen).

.... das hab' ich auch festgestellt. Die Connections schienen zwar seitens
der DB stark begrenzt, aber wenn man das kompensiert ist immer noch ein
derbes Performanceloch zu sehen.
Ich versuch's mal zu umreissen:

Die Kiste auf welcher der Server derzeit laeuft hat 16 CPUs und ca. 20G RAM.
Die Daten sind in zwei simplen Tabellen organisiert und sowohl selects als
auch inserts sehr einfach. Explain zeigte keine sichtbaren Ansatzpunkte.

Die Daten selbst belegen geschaetzte 6 GB. Selbst unter konservativen
Gesichtspunkten sollten sie also komplett im RAM vorgehalten werden
koennen. Aber genau das passiert anscheinend nicht. Die CPUs sind alles in
allem nicht besonders ausgelastet (20% max) aber die Platten-IO ist selbst
nach laengerer Laufzeit am Limit. Lesend hauptsaechlich. Load etwa 11. Wenn
dieser Stand erreicht ist, macht sie die Graetsche. Selbst einfache delete
oder insert oder auch selects brauchen dann ueber 25 Sekunden, was
indiskutabel ist.
Die derzeitige Engine ist InnoDB.
Nun sind bei den selects sehr viele no datas dabei. Ich schaetze also, dass
die DB schon versucht, viel im RAM zu halten (der Server zeigt etwa 5.4GB
virtuell) aber im no data Fall trotzdem auf der Platte schaut, ob die Daten
vielleicht doch da sind. Ergibt das irgend einen Sinn?

Und kann ich MySQL irgendwie dazu zwingen, die Daten komplett in den RAM zu
lesen und auf PlattenIO ausser im schreibenden Falle ganz zu verzichten?

> Das max_connections Limit im MySQL Server hängt an
> a) Hauptspeicher. Derzeit belegt jede offene Client-Connection einen
> Thread und damit Speicherplatz für Stack und lokale Variablen.
> b) File Handles. Jede Client-Connection verbraucht ein Filehandle für
> den TCP-Socket.
>
> Auf geeigneter Hardware kannst du dieses Limit also richtig hoch
> setzen.

Lt. des Datenbankadmins meines geringsten Misstrauens gibt es eine
Obergrenze von 4k fuer diesen Wert. Ist das nicht korrekt? Und wenn nein,
wie hoch kann ich die sinnvollerweise auf obiger HW haben?

Mit meinem externen Pooling sind zwar die Connections persistent und kein
Problem mehr, aber ich wuerde ganz gerne wissen wie eine sinnvolle Grenze
aussaehe. Ich muss mit ca. 200 Kisten an das Ding ran und theoretisch
wuerde es mir schon gefallen, mindestens 30 Connections pro Kiste zu
poolen. Macht mit ein bissl Puffer 8000 erlaubte Connections.

Wenn ich kompletten Bullshit rede, seht es mir bitte nach, ich hab' bisher
keine Erfahrung mit MySQL.

Vielen Dank und Gruesse...

Stephan


--
Freedom isn't lost in one big step when the storm-troopers
show up at your door. It is lost in little pieces, each
so small that they tend to be ignored.
Richard B. Johnson

Re: MySQL Connections buendeln

Stephan Menzel schrieb:
> Hallo,
>
> Axel Schwenke wrote:
>> Lokales Netzwerk, oder ist da eine Engstelle dazwischen?
>
> Das Netzwerk ist nicht der Flaschenhals.
>
>>> Dabei fiel mein Blick auf MySQL Proxy:
>> AFAIK kann der das derzeit nicht.
>
> Sah ich auch so. Ich hab' mir heute selbst ein kleines Tool geschrieben
> welches das kann. Das Problem war also aus der Welt und...
>
>> Aber weder TCP-Connections noch der MySQL-Handshake sind sonderlich
>> teuer (von allfälligen Ausnahmen mal abgesehen).
>
> ... das hab' ich auch festgestellt. Die Connections schienen zwar seitens
> der DB stark begrenzt, aber wenn man das kompensiert ist immer noch ein
> derbes Performanceloch zu sehen.
> Ich versuch's mal zu umreissen:
>
> Die Kiste auf welcher der Server derzeit laeuft hat 16 CPUs und ca. 20G RAM.
> Die Daten sind in zwei simplen Tabellen organisiert und sowohl selects als
> auch inserts sehr einfach. Explain zeigte keine sichtbaren Ansatzpunkte.
>
> Die Daten selbst belegen geschaetzte 6 GB. Selbst unter konservativen
> Gesichtspunkten sollten sie also komplett im RAM vorgehalten werden
> koennen. Aber genau das passiert anscheinend nicht. Die CPUs sind alles in
> allem nicht besonders ausgelastet (20% max) aber die Platten-IO ist selbst
> nach laengerer Laufzeit am Limit. Lesend hauptsaechlich. Load etwa 11. Wenn
> dieser Stand erreicht ist, macht sie die Graetsche. Selbst einfache delete
> oder insert oder auch selects brauchen dann ueber 25 Sekunden, was
> indiskutabel ist.


Das ist wirklich indiskutabel. Ein insert dauert normalerweise fast gar
nicht. Du sprichst von 6 GB Daten. Zzgl Indizes? Wieviele? Welche Art
von Queries? Hast du deine Config schon optimiert? Was sagt das
slow-query-log?

Ich würde die 200 Clients nicht parallel auf die Datenbank zugreifen
lassen. Ich würde die Datenbank hinter einer Zugriffsschicht kapseln.
Dann kann man dort mit weniger Connections arbeiten.

Wieso überhaupt 30 Connections pro Client? Sollen die parallel zugreifen
können? Und dann von 200 Clients? Womöglich mehr als 2000 parallel
Zugriffe? Eine so hohe Parallelität kann auch das Serverbetriebssystem
stark bremsen. Threadswitching ist bei 8000 Threads nicht zu
vernachlässigen.

Soweit meine kurzen Gedanken ...
Grüße

Dirk

Re: MySQL Connections buendeln

Dirk Brosowski wrote:

> Das ist wirklich indiskutabel. Ein insert dauert normalerweise fast gar
> nicht. Du sprichst von 6 GB Daten. Zzgl Indizes?

Nun, ich hab' das gerade nochmal ueber das Admininterface von MySQL
angeschaut und da ist von deutlich hoeheren Werten die Rede. Eine der
Tabellen wird dort mit 17G angegeben und eine mit 3.5G.
Also schon mehr als meine gestrige Aussage.
Ein Index liegt jeweils über einem BIGINT Feld welches aber nicht uniq ist,
sondern theoretisch bis zu 10000 Zeilen umfassen kann.

> Wieviele?

1

> Welche Art von Queries? Hast du deine Config schon optimiert? Was sagt das
> slow-query-log?

Die queries sind einfache selects, allerdings mit einem LIKE darin, der
dafuer sorgt, dass tatsaechlich immer alle Zeilen angeschaut werden
muessen.

Slow query log zeigt die ueblichen Queries wo halt viele dort drin stehen
wenn ich die Last langsam aufdrehe.

> Ich würde die 200 Clients nicht parallel auf die Datenbank zugreifen
> lassen. Ich würde die Datenbank hinter einer Zugriffsschicht kapseln.
> Dann kann man dort mit weniger Connections arbeiten.

Das waere zu unperformant ueber's Netz. Die 200 Clients sind in dem fall 200
Rechner und die koennen sich kaum was teilen.

> Wieso überhaupt 30 Connections pro Client?

Das war nur ein geschaetzter Startwert, aber den kann ich beliebig aendern.
Ich denke, ich werd' das mal reduzieren um die Load lieber auf die Clients
zu verteilen.

> Sollen die parallel zugreifen können?

Ja.

> Und dann von 200 Clients?

Ja.

> Womöglich mehr als 2000 parallel Zugriffe? Eine so hohe Parallelität
> kann auch das Serverbetriebssystem stark bremsen.
> Threadswitching ist bei 8000 Threads nicht zu
> vernachlässigen.

Schon, aber da muss ich eben eine Balance finden. Ich hab' jetzt erst mal
ordentlich virtuellen RAM draufgelegt und den Query Cache reduziert (der
ist in dem Szenario IMHO nutzlos). Bis jetzt sieht's besser aus und ich
drehe mal weiter auf.

> Soweit meine kurzen Gedanken ...

Vielen Dank und Gruesse...

Stephan

--
Freedom isn't lost in one big step when the storm-troopers
show up at your door. It is lost in little pieces, each
so small that they tend to be ignored.
Richard B. Johnson

Re: MySQL Connections buendeln

Stephan Menzel wrote:
>
> Die Kiste auf welcher der Server derzeit laeuft hat 16 CPUs und ca. 20G RAM.
> Die Daten sind in zwei simplen Tabellen organisiert und sowohl selects als
> auch inserts sehr einfach. Explain zeigte keine sichtbaren Ansatzpunkte.
>
> Die Daten selbst belegen geschaetzte 6 GB. Selbst unter konservativen
> Gesichtspunkten sollten sie also komplett im RAM vorgehalten werden
> koennen. Aber genau das passiert anscheinend nicht.

InnoDB fädelt *jeglichen* I/O durch den Page Buffer. Wie groß ist der?
Wenn du sonst nichts auf der Maschine machst, laß 1-2GB frei und
konfiguriere den Rest als InnoDB Buffer Pool.

Ansonsten gibt dir SHOW INNODB STATUS einen ganz guten Überblick, was
da gerade abgeht.

> Die CPUs sind alles in
> allem nicht besonders ausgelastet (20% max) aber die Platten-IO ist selbst
> nach laengerer Laufzeit am Limit. Lesend hauptsaechlich. Load etwa 11.

InnoDB hat auch ein internes Limit, wieviele Threads es nutzt. Default
dürfte 8 sein. Wenn du damit eine Load von 11 erreichst, ist das schon
gut. Ist wohl noch etwas Overhead aus dem SQL-Layer und I/O threads.

> Wenn
> dieser Stand erreicht ist, macht sie die Graetsche. Selbst einfache delete
> oder insert oder auch selects brauchen dann ueber 25 Sekunden, was
> indiskutabel ist.

Wenn I/O saturiert ist, wird es schnell ganz langsam. Da kann man sich
auch jegliche Diskussion um die Kosten des Thread-Switchings sparen.

> Nun sind bei den selects sehr viele no datas dabei. Ich schaetze also, dass
> die DB schon versucht, viel im RAM zu halten (der Server zeigt etwa 5.4GB
> virtuell) aber im no data Fall trotzdem auf der Platte schaut, ob die Daten
> vielleicht doch da sind. Ergibt das irgend einen Sinn?

Nein. Kein Sinn ;)

> Und kann ich MySQL irgendwie dazu zwingen, die Daten komplett in den RAM zu
> lesen und auf PlattenIO ausser im schreibenden Falle ganz zu verzichten?

Wenn du genug RAM für die verschiedenen Buffer konfigurierst, macht es
das von selber.

>> Das max_connections Limit im MySQL Server hängt an
>> a) Hauptspeicher. Derzeit belegt jede offene Client-Connection einen
>> Thread und damit Speicherplatz für Stack und lokale Variablen.
>> b) File Handles. Jede Client-Connection verbraucht ein Filehandle für
>> den TCP-Socket.
>>
>> Auf geeigneter Hardware kannst du dieses Limit also richtig hoch
>> setzen.
>
> Lt. des Datenbankadmins meines geringsten Misstrauens gibt es eine
> Obergrenze von 4k fuer diesen Wert. Ist das nicht korrekt? Und wenn nein,
> wie hoch kann ich die sinnvollerweise auf obiger HW haben?

Das ist nicht korrekt. Aber das ist auch die falsche Schraube, um dran
zu drehen. So lange der experimentelle Thread-Pool Patch für MySQL
nicht raus ist, bedeutet jede Connection einen weiteren Thread.
Spätestens mit vierstellig Threads siehts dann düster aus in Sachen
Skalierbarkeit. Pooling, oder noch besser eine Zwischenschicht mit
extra Caching sind dann die bessere Lösung.


XL

Re: MySQL Connections buendeln

Hi,

Axel Schwenke wrote:
> InnoDB fädelt *jeglichen* I/O durch den Page Buffer. Wie groß ist der?
> Wenn du sonst nichts auf der Maschine machst, laß 1-2GB frei und
> konfiguriere den Rest als InnoDB Buffer Pool.

Zu klein. Ich bin selbst auch zu diesem Schluss gekommen und hab' den Puffer
jetzt von 5,irgendwas auf 17G erhoeht, was die Platten-IO fast komplett
beseitigt hat. Jetzt scheint's zu klappen mit der IO. Danke.

> InnoDB hat auch ein internes Limit, wieviele Threads es nutzt. Default
> dürfte 8 sein. Wenn du damit eine Load von 11 erreichst, ist das schon
> gut. Ist wohl noch etwas Overhead aus dem SQL-Layer und I/O threads.

Jetzt tut sich in der Tat bei weiter hochgedrehter Last die CPU als neuer
Flaschenhals auf. Von den insgesamt 18.6G die mir TOP als virt anzeigt sind
14 reserviert, tendenz immer leichter steigend. Die 16 Cores sind aber
mittlerweile alle so bei 50% Auslastung, was eine theoretische Load von
derzeit 15-20 macht. Die ersten Queries werden aber schon wieder zu
langsam.

> Wenn I/O saturiert ist, wird es schnell ganz langsam. Da kann man sich
> auch jegliche Diskussion um die Kosten des Thread-Switchings sparen.

Indeed.

> Wenn du genug RAM für die verschiedenen Buffer konfigurierst, macht es
> das von selber.

check. Das sieht mir sehr danach aus. iostat ist jetzt schoen anzusehen.

>> Lt. des Datenbankadmins meines geringsten Misstrauens gibt es eine
>> Obergrenze von 4k fuer diesen Wert. Ist das nicht korrekt? Und wenn nein,
>> wie hoch kann ich die sinnvollerweise auf obiger HW haben?
>
> Das ist nicht korrekt. Aber das ist auch die falsche Schraube, um dran
> zu drehen. So lange der experimentelle Thread-Pool Patch für MySQL
> nicht raus ist, bedeutet jede Connection einen weiteren Thread.

Wow..... Tatsaechlich? Jede Conn ein Thread? Dabei sind doch Threadpools
mittlerweile usus. Ist doch kein Hexenwerk. Das haette ich nicht erwartet.
Zum Glueck hab' ich die Connections jetzt persistent.

Jetzt muss ich nur noch sehen, wie ich die CPU in den Griff bekomme.
Erstaunlicherweise laeuft sie mit Query Cache doch noch einen Zacken
besser. Anscheinend sind doch nicht so viele Queries uniq wie erwartet.

Vielen Dank fuer die Tips und Gruesse...

Stephan


--
Freedom isn't lost in one big step when the storm-troopers
show up at your door. It is lost in little pieces, each
so small that they tend to be ignored.
Richard B. Johnson