query_cache... mit Festplatte statt RAM

Kann man eigentlich auch den Query Cache auch auf die Festplatte schreiben
statt in den Speicher?
Dann natürlich bei wiederkehrenden Queries die Ergebnisse von dort lesen.
Bei großen Fulltext Indizes werden nämlich viele Gigabyte-Ram verbraucht.

Weiß jemand was alles auch im "Leerlauf" ins RAM abgelegt wird.
Nur schon das Anlegen einer großen Tabelle ( ca. 1 GB mit 3 Mio. Daten ),
ohne daß dort Abfragen gemacht werden, frißt mir den Speicher weg.
Liegt das vielleicht auch Index?

Gibt es zu diesem Thema was im Internet? Hab leider nicht gefunden.

Danke
PG

Re: query_cache... mit Festplatte statt RAM

"PG" wrote:
>
> Kann man eigentlich auch den Query Cache auch auf die Festplatte schreiben
> statt in den Speicher?

Das wäre reichlich sinnlos. Ein Cache muß vor allen Dingen *schnell*
sein. Das ist er nur im RAM. Außerdem sind die Daten im Query-Cache
höchst volatil. Wenn du z.B. 1000 Queries (bzw. deren Results) für
Tabelle `foo` im Cache hast und es kommt ein UPDATE für irgendeine
Zeile in `foo`, dann müssen alle diese 1000 Einträge aus dem Cache
geworfen werden. Stell dir mal vor, was das für eine I/O-Last erzeugen
würde, wenn der Cache auf der Platte läge.

Oberste Prämisse des Query-Caches ist nicht "möglichst viele Queries
aus dem Cache beantworten", sondern "immer korrekte Resultate liefern".
Wenn du einen Cache haben willst, der möglichst selten die Datenbank
anfaßt (dafür aber womöglich auch mal veraltete Daten liefert) dann
mußt du das in der Applikation machen.

> Bei großen Fulltext Indizes werden nämlich viele Gigabyte-Ram verbraucht.

Was hat das mit dem Query-Cache zu tun?

> Weiß jemand was alles auch im "Leerlauf" ins RAM abgelegt wird.
> Nur schon das Anlegen einer großen Tabelle ( ca. 1 GB mit 3 Mio. Daten ),
> ohne daß dort Abfragen gemacht werden, frißt mir den Speicher weg.

Was meinst du mit "frißt mir den Speicher weg"? Der Speicher wird
*benutzt*. Wenn du 1GB Daten in die Datenbank prügelst und dabei RAM
leer ist, werden die Daten zwar auf die Platte geschrieben, bleiben
aber zusätzlich *auch* im RAM. So spart man sich den Lesezugriff auf
die Platte, wenn bald danach jemand etwas von diesen Daten lesen will.

Andererseits wird diese Kopie im RAM auch sehr bereitwillig verworfen,
wenn der RAM für was wichtigeres gebraucht wird. Ganz allgemein
versucht MySQL (genauso wie das Betriebssystem) die am häufigsten
gebrauchten Daten im RAM zu halten. Eine typische Strategie ist dabei
"least recently used" (kurz: LRU). Google mal danach.


XL

Re: query_cache... mit Festplatte statt RAM

"Axel Schwenke" schrieb im Newsbeitrag
news:8toovd.lov.ln@idefix.xl.local...
> "PG" wrote:
>>

Erstmal vielen Dank für die hilfreichen Infos. Toll.

>> Bei großen Fulltext Indizes werden nämlich viele Gigabyte-Ram verbraucht.
>
> Was hat das mit dem Query-Cache zu tun?

Nur die Tatsache, daß ich eine weitere Tabelle mit 1 GB Daten mit
Fulltxt-Index auf dem Rechner habe, hat zu einem deutlichen
Performanceverlustgeführt. Also ohne daß diese Tabelle in die Queriesetc.
eingebunden ist.
Die Queries auf die anderen Tabellen wurden alle langsamer.
Der Speicher war voll, das System fing an zu Swapen.
Irgendeinen Zusammenhang muss es geben, daß nach dem Leeren der ungenutzten
1GB Tabelle wieder alles ok war.

Wierden die Queris eigentlich noch woanders gesichert.
Bei ausgeschaltetem query_cache ist die zweite ( gleiche ) Abfrage nähmlich
auch deutlich schneller als die allererste.

PG

Re: query_cache... mit Festplatte statt RAM

"PG" wrote:
> "Axel Schwenke" schrieb im Newsbeitrag

>> Was hat das mit dem Query-Cache zu tun?
>
> Nur die Tatsache, daß ich eine weitere Tabelle mit 1 GB Daten mit
> Fulltxt-Index auf dem Rechner habe, hat zu einem deutlichen
> Performanceverlustgeführt. Also ohne daß diese Tabelle in die Queriesetc.
> eingebunden ist.

Das kann nicht sein. Eventuell geht die Performance kurzfristig zurück,
wenn Caches mit den Daten besagter Tabelle "zweckentfremded" werden.
Aber das sollte sich nach kürzester Zeit wieder einpegeln. Eine inakti-
ve Tabelle belegt Platz auf der Festplatte, sonst nix.

> Die Queries auf die anderen Tabellen wurden alle langsamer.
> Der Speicher war voll, das System fing an zu Swapen.

*Dann* hast du die diversen Caches und Buffer von MySQL zu groß einge-
stellt. Auf einem nur-MyISAM System sollte der key_buffer nicht größer
als 50% des RAMs gesetzt werden. Das Betriebssystem braucht auch ein
bisschen RAM, um Datenseiten zu cachen.

Wahrscheinlich brauchst du einfach nur mehr RAM.

> Wierden die Queris eigentlich noch woanders gesichert.

Nein.

> Bei ausgeschaltetem query_cache ist die zweite ( gleiche ) Abfrage nähmlich
> auch deutlich schneller als die allererste.

Das ist implizites Caching. Einerseits das Caching von Index-Seiten im
key_buffer, andererseits das allgegenwärtige Caching des Betriebssystems.


XL

Re: query_cache... mit Festplatte statt RAM

>> "Axel Schwenke" schrieb im Newsbeitrag
>
>> Die Queries auf die anderen Tabellen wurden alle langsamer.
>> Der Speicher war voll, das System fing an zu Swapen.
>
> *Dann* hast du die diversen Caches und Buffer von MySQL zu groß einge-
> stellt. Auf einem nur-MyISAM System sollte der key_buffer nicht größer
> als 50% des RAMs gesetzt werden. Das Betriebssystem braucht auch ein
> bisschen RAM, um Datenseiten zu cachen.
> Wahrscheinlich brauchst du einfach nur mehr RAM.

Vielen Dank nochmal. Du hast wirklich Ahnung.

Wahrscheinlich hast Du recht. Ich hoffte, es gibt Wege das zu umgehen.
Bei Dual Xeon 2.7, 6GB RAM und 5 GB mysql-Daten (10 Mio Einträge, MyISAM)
ist das die aktuelle Einstellung, die ganz gut funktioniert.

[mysqld]
.....
key_buffer = 1022M
max_allowed_packet = 3M
table_cache = 2096
sort_buffer_size = 6M
read_buffer_size = 6M
myisam_sort_buffer_size = 192M
thread_cache = 24
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Mit drei weiteren Tabellen à 1 GB schien das System insgesamt überfordert.

Vielleicht ist es sinnvoll einzelne Tabellen nicht so groß zu machen und
eher mehrer über UNION zu verbinden?
Das ist zumindest bei den Updates ( delete und insert ) schneller.

Mit Deinen Tipps weiß ich jetzt jedenfalls besser nach welchen Infos ich
weiter suchen soll. Danke.

Re: query_cache... mit Festplatte statt RAM

"PG" wrote:
>>> "Axel Schwenke" schrieb im Newsbeitrag
>>
>>> Die Queries auf die anderen Tabellen wurden alle langsamer.
>>> Der Speicher war voll, das System fing an zu Swapen.
>>
>> *Dann* hast du die diversen Caches und Buffer von MySQL zu groß einge-
>> stellt. Auf einem nur-MyISAM System sollte der key_buffer nicht größer
>> als 50% des RAMs gesetzt werden. Das Betriebssystem braucht auch ein
>> bisschen RAM, um Datenseiten zu cachen.
>
> Wahrscheinlich hast Du recht. Ich hoffte, es gibt Wege das zu umgehen.
> Bei Dual Xeon 2.7, 6GB RAM und 5 GB mysql-Daten (10 Mio Einträge, MyISAM)

*Was* ist 5GB groß? Alle Tabellen-Files zusammen? Die Daten (*.MYD),
die Indizes (*.MYI)?

Die Größe einer Tabelle (egal ob Megabytes oder Anzahl Rows) ist bei
korrekt gesetzten Indizes eher nebensächlich. Allerdings ist es der
Performance sehr zuträglich, wenn die aktiven Indizes komplett in den
key_buffer passen.

Mit Volltext-Indizes wird das schwieriger, die sind im Vergleich zu den
"normalen" BTree-Indizes ziemlich groß.

> [mysqld]
> ....
> key_buffer = 1022M

Das ist bei 6GB RAM noch recht konservativ. Wenn deine Maschine bei
dieser Einstellung zu swappen anfängt, müssen noch andere Speicher-
fresser drauf laufen. Java?

Auf einer reinen Datenbankmaschine (MyISAM-only) mit 6GB RAM würde ich
den key_buffer auf 2GB setzen. Das ist auf 32-bittigen Betriebssystemen
ohnehin das Maximum. Größere Werte können sinnvoll sein, vor allem wenn
viel geschrieben wird.

> Vielleicht ist es sinnvoll einzelne Tabellen nicht so groß zu machen und
> eher mehrer über UNION zu verbinden?

Kaum. Die Lese-Performance leidet. Schreiben sollte allerdings etwas
schneller sein. UNION und MERGE tables bieten sich an, wenn man z.B.
zeitlich getrennte Daten bearbeitet und für jeden Zeitraum eine eigene
Tabelle verwenden will.


XL

Re: query_cache... mit Festplatte statt RAM

Am Dienstag, den 21.03.2006, 12:43 +0100 schrieb Axel Schwenke:

> Das wäre reichlich sinnlos. Ein Cache muß vor allen Dingen *schnell*
> sein. Das ist er nur im RAM.=20

Nein. Ein gecachter Resultset ist aus verschiedenen Gründen erheblich
schneller als das ausführen der Query, auch wenn er auf der Platte
liegt.=20

> Außerdem sind die Daten im Query-Cache
> höchst volatil. Wenn du z.B. 1000 Queries (bzw. deren Results) für
> Tabelle `foo` im Cache hast und es kommt ein UPDATE für irgendeine
> Zeile in `foo`, dann müssen alle diese 1000 Einträge aus dem Cache
> geworfen werden. Stell dir mal vor, was das für eine I/O-Last erzeugen
> würde, wenn der Cache auf der Platte läge.

Es reicht, die Einträge als ungültig zu markieren. Da kann auch im RAM
passieren.

Gruß,
Joachim

Re: query_cache... mit Festplatte statt RAM

Joachim Zobel wrote:
> Am Dienstag, den 21.03.2006, 12:43 +0100 schrieb Axel Schwenke:
^^^^^^^^^^
Wow! Du mußt gigantische Newslaufzeiten haben, wenn du auf ein Posting
von vor sieben Wochen antwortest.

>> Das wäre reichlich sinnlos. Ein Cache muß vor allen Dingen *schnell*
>> sein. Das ist er nur im RAM.
>
> Nein. Ein gecachter Resultset ist aus verschiedenen Gründen erheblich
> schneller als das ausführen der Query, auch wenn er auf der Platte
> liegt.

Ein simples SELECT * WHERE PK='foo' dürfte ungecachet etwa genauso
schnell laufen wie es aus einem plattenbasierten Cache kommt.
Vermutlich ist es sogar schneller, weil das Suchmuster kürzer ist
('foo' vs. die ganze Query). Da der Cache aber bei *jedem* SELECT
befragt wird, würde obige Query mit Cache gleich mal doppelt so
lange brauchen. Absolut intolerabel. Wenn der Cache nicht garantiert
mindestens 10 mal so schnell ist wie *jede* Query, ist er witzlos.


XL

Re: query_cache... mit Festplatte statt RAM

Am Sonntag, den 13.05.2007, 21:56 +0200 schrieb Axel Schwenke:
> Joachim Zobel wrote:
> > Am Dienstag, den 21.03.2006, 12:43 +0100 schrieb Axel Schwenke:
> ^^^^^^^^^^
> Wow! Du mußt gigantische Newslaufzeiten haben, wenn du auf ein Posting
> von vor sieben Wochen antwortest.

Versehentlich falschrum sortiert und nicht genau hingeguckt.

> Da der Cache aber bei *jedem* SELECT
> befragt wird, würde obige Query mit Cache gleich mal doppelt so
> lange brauchen. Absolut intolerabel. Wenn der Cache nicht garantiert
> mindestens 10 mal so schnell ist wie *jede* Query, ist er witzlos.

Oh, der Query Cache arbeitet nicht kostenbasiert. Das ist doch bei
Beispielen wie Deiner einfachen Abfrage sogar im Hauptspeicher
zweifelhaft, spätestens wenn Index und Tabelle gecacht sind.=20

Gruß,
Joachim

Re: query_cache... mit Festplatte statt RAM

Joachim Zobel wrote:
> Am Sonntag, den 13.05.2007, 21:56 +0200 schrieb Axel Schwenke:
>
>> Da der Cache aber bei *jedem* SELECT
>> befragt wird, würde obige Query mit Cache gleich mal doppelt so
>> lange brauchen. Absolut intolerabel. Wenn der Cache nicht garantiert
>> mindestens 10 mal so schnell ist wie *jede* Query, ist er witzlos.
>
> Oh, der Query Cache arbeitet nicht kostenbasiert.

Ein *kostenbasierter* Cache? Erzähl mehr!

Nochmal: damit ein Cache was bringt, muß er schnell sein. Um schnell
zu sein, muß er einfach sein. Und einfach ist der Query-Cache:

- er erkennt nur identische Query-Strings

- er funktioniert nicht für Prepared Statements

- er führt für jedes Statement im Cache eine Liste der Tabellen von
denen das Ergebnis anhängt und invalidiert den Cache-Eintrag, sobald
auch nur eine der Tabellen geändert wird

manche dieser Einschränkungen sind echt lästig oder schränken die
Wirksamkeit des Caches ein. Aber ein komplizierterer Cache wäre
vermutlich auch langsamer und damit wirkungslos.


XL

Re: query_cache... mit Festplatte statt RAM

On 13 Mai, 13:01, Joachim Zobel wrote:
> Nein. Ein gecachter Resultset ist aus verschiedenen Gründen erheblich
> schneller als das ausführen der Query, auch wenn er auf der Platte
> liegt.

Je nach Anwendung macht es mehr Sinn, die fertigen Daten zu speichern
- bei ASP.NET wäre das das DataGridView. Hat auch den Vorteil, dass
bei einer Trennung von Anwendung und Datenbankserver auf verschiedenen
Rechnern der Speicher jedesmal optimal ausgeschöpft werden kann ;-)

Gruß, Dirk

Re: query_cache... mit Festplatte statt RAM

Am Sonntag, den 13.05.2007, 23:03 +0200 schrieb Axel Schwenke:
> Joachim Zobel wrote:
> > Oh, der Query Cache arbeitet nicht kostenbasiert.
>=20
> Ein *kostenbasierter* Cache? Erzähl mehr!

Das ist naheliegend. Wenn entschieden wird, ob der Resultset gecacht
wird, sind die Kosten für die Erstellung (CPU, Disk IO) mitprotokolliert
worden und bekannt. Ab einem (evtl. grössenabhängigen Schwellenwert)
wird gecacht.

Gruß,
Joachim