mysqldump und Query Cache

17 views
Skip to first unread message

Helmut Schneider

unread,
May 23, 2009, 7:55:09 AM5/23/09
to
Hi,

ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche Datenbanken
st�ndlich. Nun ist mir aufgefallen, dass jeder Aufruf auch z.B. den Query
Cache leert. Kann man das verhindern?

Danke und Gru�, Helmut

--
No Swen today, my love has gone away
My mailbox stands for lorn, a symbol of the dawn

Steffen Mosthaf

unread,
May 25, 2009, 5:22:48 AM5/25/09
to
Helmut Schneider schrieb:

> Hi,
>
> ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche
> Datenbanken st�ndlich. Nun ist mir aufgefallen, dass jeder Aufruf auch
> z.B. den Query Cache leert. Kann man das verhindern?
>
> Danke und Gru�, Helmut

Wie sieht dein Dump Befehl denn genau aus?
Laut Doku gibt es so etwas wie ein "--flush-logs", aber das der
Query-Cache geleert wird kann ich mir nicht vorstellen.

Gru�
Steffen

Helmut Schneider

unread,
May 25, 2009, 6:40:48 AM5/25/09
to
Steffen Mosthaf <steffen...@gmx.de> wrote:
> Helmut Schneider schrieb:
>> Hi,
>>
>> ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche
>> Datenbanken st�ndlich. Nun ist mir aufgefallen, dass jeder Aufruf auch
>> z.B. den Query Cache leert. Kann man das verhindern?
>>
>> Danke und Gru�, Helmut
>
> Wie sieht dein Dump Befehl denn genau aus?

mysqldump -F --lock-all-tables

> Laut Doku gibt es so etwas wie ein "--flush-logs", aber das der
> Query-Cache geleert wird kann ich mir nicht vorstellen.


Es scheint am "--lock-all-tables" zu liegen, hab ich das Handbuch
missverstanden? Ich wollte eigentlich nur Ver�nderungen w�hrend des Dumps
vermeiden.

# echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_queries_in_cache 122
# mysqldump -uuser -ppass --lock-all-tables my_data | gzip >
./my_data.sql.gz
# echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_queries_in_cache 1
#

Axel Schwenke

unread,
May 25, 2009, 6:57:57 AM5/25/09
to
"Helmut Schneider" <jump...@gmx.de> wrote:

> Steffen Mosthaf <steffen...@gmx.de> wrote:
>>>
>>> ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche
>>> Datenbanken st�ndlich.

Warum das denn? La� doch lieber ein binlog schreiben.

>>> Nun ist mir aufgefallen, dass jeder Aufruf auch
>>> z.B. den Query Cache leert. Kann man das verhindern?
>>

>> Wie sieht dein Dump Befehl denn genau aus?
>
> mysqldump -F --lock-all-tables
>

> Es scheint am "--lock-all-tables" zu liegen

Nein.

> # echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
> Variable_name Value
> Qcache_queries_in_cache 122
> # mysqldump -uuser -ppass --lock-all-tables my_data | gzip >
> ./my_data.sql.gz
> # echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
> Variable_name Value
> Qcache_queries_in_cache 1

Das ist kein Beleg daf�r da� der Query-Cache gel�scht wird. Welche
Version von MySQL (insbesondere mysqldump) ist das? Aktuelle Versionen
von mysqldump machen "SELECT /*!40001 SQL_NO_CACHE */ * FROM ..." um
den Inhalt von Tabellen auszulesen (und in den Dump zu schreiben).

Kann durchaus sein, da� �ltere Versionen das nicht machen. Dann steht
nach dem Dump der Inhalt des letzten SELECTs im Query-Cache.

Direkt �berpr�fen kannst du das, indem du das Query-Log anschaltest.
Dann kannst du sehen welche Queries mysqldump auf deinen Server
abschie�t.


XL

Helmut Schneider

unread,
May 25, 2009, 7:43:38 AM5/25/09
to
Axel Schwenke <axel.s...@gmx.de> wrote:
> "Helmut Schneider" <jump...@gmx.de> wrote:
>> Steffen Mosthaf <steffen...@gmx.de> wrote:
>>>>
>>>> ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche
>>>> Datenbanken st�ndlich.
>
> Warum das denn? La� doch lieber ein binlog schreiben.

Ich hab mich in das binlog Thema noch nicht vollst�ndig eingelesen. Aktiv
ist es schon, aber im Falle des Gaus w�sste ich spontan nicht, was genau ich
machen m�sste. Daher fahre ich derzeit 2-gleisig.

>>>> Nun ist mir aufgefallen, dass jeder Aufruf auch
>>>> z.B. den Query Cache leert. Kann man das verhindern?
>>>
>>> Wie sieht dein Dump Befehl denn genau aus?
>>
>> mysqldump -F --lock-all-tables
>>
>> Es scheint am "--lock-all-tables" zu liegen
>
> Nein.
>
>> # echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
>> Variable_name Value
>> Qcache_queries_in_cache 122
>> # mysqldump -uuser -ppass --lock-all-tables my_data | gzip >
>> ./my_data.sql.gz
>> # echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
>> Variable_name Value
>> Qcache_queries_in_cache 1
>
> Das ist kein Beleg daf�r da� der Query-Cache gel�scht wird.

[root@mysql ~]# echo "SHOW STATUS like '%qcache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_free_blocks 6
Qcache_free_memory 65008144
Qcache_hits 39988
Qcache_inserts 358624
Qcache_lowmem_prunes 0
Qcache_not_cached 16488
Qcache_queries_in_cache 591
Qcache_total_blocks 1226
[root@mysql ~]# mysqldump -uuser -ppass --lock-all-tables lcs > /dev/null
[root@mysql ~]# echo "SHOW STATUS like '%qcache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_free_blocks 1
Qcache_free_memory 67092792
Qcache_hits 40084
Qcache_inserts 358666
Qcache_lowmem_prunes 0
Qcache_not_cached 16507
Qcache_queries_in_cache 1
Qcache_total_blocks 4
[root@mysql ~]#

Das auch nicht?!

> Welche Version von MySQL (insbesondere mysqldump) ist das?

5.0.81

> Direkt �berpr�fen kannst du das, indem du das Query-Log anschaltest.

SELECT /*!40001 SQL_NO_CACHE */ * FROM ...

Noch was anderes, nachdem ich suchen k�nnte?

Bernd Hohmann

unread,
May 25, 2009, 8:28:03 AM5/25/09
to
Axel Schwenke schrieb:

>>>> ich hab immer[tm] mit mysqldump Backups gemacht, täglich, manche
>>>> Datenbanken stündlich.
>
> Warum das denn? Laß doch lieber ein binlog schreiben.

Weil mysqldump in kleinen Installationen idiotensicher ist?

Bernd

--
Visit http://www.nixwill.de and http://www.spammichvoll.de
jean....@nixwill.de & bernado....@spammichvoll.de

Axel Schwenke

unread,
May 25, 2009, 8:54:28 AM5/25/09
to
Bernd Hohmann <bernd.hohma...@freihaendler.com> wrote:
> Axel Schwenke schrieb:
>>>>> ich hab immer[tm] mit mysqldump Backups gemacht, t�glich, manche
>>>>> Datenbanken st�ndlich.
>>
>> Warum das denn? La� doch lieber ein binlog schreiben.

> Weil mysqldump in kleinen Installationen idiotensicher ist?

Daf�r lockt es Tabellen (im gegebenen Fall sogar *alle* Tabellen f�r
die *gesamte* Laufzeit von mysqldump). Ein Binlog macht das nicht.
Und du kannst aus dem Binlog jeden beliebigen Zwischenzustand - z.B.
genau vor dem fatalen "DELETE FROM kunden" wiederherstellen.


XL

Axel Schwenke

unread,
May 25, 2009, 8:51:28 AM5/25/09
to
"Helmut Schneider" <jump...@gmx.de> wrote:

> Axel Schwenke <axel.s...@gmx.de> wrote:
>>
>> La� doch lieber ein binlog schreiben.
>
> Ich hab mich in das binlog Thema noch nicht vollst�ndig eingelesen. Aktiv
> ist es schon, aber im Falle des Gaus w�sste ich spontan nicht, was genau ich
> machen m�sste. Daher fahre ich derzeit 2-gleisig.

http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

>> Das ist kein Beleg daf�r da� der Query-Cache gel�scht wird.
>
> [root@mysql ~]# echo "SHOW STATUS like '%qcache%'" | mysql -uuser -ppass
> Variable_name Value
> Qcache_free_blocks 6
> Qcache_free_memory 65008144
> Qcache_hits 39988
> Qcache_inserts 358624
> Qcache_lowmem_prunes 0
> Qcache_not_cached 16488
> Qcache_queries_in_cache 591
> Qcache_total_blocks 1226
> [root@mysql ~]# mysqldump -uuser -ppass --lock-all-tables lcs > /dev/null
> [root@mysql ~]# echo "SHOW STATUS like '%qcache%'" | mysql -uuser -ppass
> Variable_name Value
> Qcache_free_blocks 1
> Qcache_free_memory 67092792
> Qcache_hits 40084
> Qcache_inserts 358666
> Qcache_lowmem_prunes 0
> Qcache_not_cached 16507
> Qcache_queries_in_cache 1
> Qcache_total_blocks 4
> [root@mysql ~]#
>
> Das auch nicht?!

Nicht wirklich. Es zeigt, da� Ergebnisse aus dem Cache entfernt worden
sind. Das passiert aber auch so st�ndig: immer wenn eine Tabelle
ge�ndert wird (INSERT/UPDATE/DELETE) werden alle Ergebnisse, die mit
dieser Tabelle assoziiert sind, aus dem Cache geworfen.

>> Direkt �berpr�fen kannst du das, indem du das Query-Log anschaltest.
> SELECT /*!40001 SQL_NO_CACHE */ * FROM ...
> Noch was anderes, nachdem ich suchen k�nnte?

Du kannst alle Queries anschauen, die mysqldump macht. So lange da kein
FLUSH TABLES oder RESET QUERY CACHE dabei ist ...

Andererseits:

> Qcache_hits 40084
> Qcache_inserts 358666

weniger als 1/8 deiner SELECTs kann aus dem Cache beantwortet werden.
Das Verh�ltnis sollte eher anders herum sein.
IMHO kannst du den Cache ganz abschalten und MySQL das freiwerdende
RAM anderweitig gewinnbringender verwenden lassen.


XL

Helmut Schneider

unread,
May 25, 2009, 9:59:16 AM5/25/09
to
Axel Schwenke <axel.s...@gmx.de> wrote:
> "Helmut Schneider" <jump...@gmx.de> wrote:
>> Axel Schwenke <axel.s...@gmx.de> wrote:
>>>
>>> La� doch lieber ein binlog schreiben.
>>
>> Ich hab mich in das binlog Thema noch nicht vollst�ndig eingelesen. Aktiv
>> ist es schon, aber im Falle des Gaus w�sste ich spontan nicht, was
>> genau ich machen m�sste. Daher fahre ich derzeit 2-gleisig.
>
> http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

Kenn ich, Danke, hatte aber eben noch keine Zeit f�r.

Ich graphe mit Cacti, jede Stunde steht der Cache auf 0, wundersch�ner
S�gezahn. Lasse ich --lock-all-tables weg, bekomme ich nen stetigen Graph.
Zudem:

# echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_queries_in_cache 39
# mysqldump -uuser -ppass -F lcs | gzip > ./lcs.sql.gz
# echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_queries_in_cache 39
# mysqldump -uuser -ppass -F --lock-all-tables lcs | gzip > ./lcs.sql.gz
# echo "SHOW STATUS like '%queries_in_cache%'" | mysql -uuser -ppass
Variable_name Value
Qcache_queries_in_cache 1
#

> Du kannst alle Queries anschauen, die mysqldump macht. So lange da kein
> FLUSH TABLES oder RESET QUERY CACHE dabei ist ...
>
> Andererseits:
>
>> Qcache_hits 40084
>> Qcache_inserts 358666
>
> weniger als 1/8 deiner SELECTs kann aus dem Cache beantwortet werden.
> Das Verh�ltnis sollte eher anders herum sein.

M�glicherweise w�re die Ratio ja h�her, wenn der Cache nicht 1/h geleert
w�rde...

> IMHO kannst du den Cache ganz abschalten und MySQL das freiwerdende
> RAM anderweitig gewinnbringender verwenden lassen.

Die Kiste ist dediziert und langweilt sich. *Die* 64MB hab ich �brig.

Axel Schwenke

unread,
May 25, 2009, 10:55:43 AM5/25/09
to
"Helmut Schneider" <jump...@gmx.de> wrote:
> Axel Schwenke <axel.s...@gmx.de> wrote:
>
>> Du kannst alle Queries anschauen, die mysqldump macht. So lange da kein
>> FLUSH TABLES oder RESET QUERY CACHE dabei ist ...

So. Jetzt habe ich mal in mysqldump.c geschaut. Und was sehe ich:

if ((opt_lock_all_tables || opt_master_data) &&
do_flush_tables_read_lock(mysql))
goto err;

und dann:

static int do_flush_tables_read_lock(MYSQL *mysql_con)
{
/*
We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES
will wait but will not stall the whole mysqld, and when the long update is
done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So,
FLUSH TABLES is to lower the probability of a stage where both mysqldump
and most client connections are stalled. Of course, if a second long
update starts between the two FLUSHes, we have that bad stall.
*/
return
( mysql_query_with_error_report(mysql_con, 0, "FLUSH TABLES") ||
mysql_query_with_error_report(mysql_con, 0,
"FLUSH TABLES WITH READ LOCK") );
}

ein --lock-all-tables macht also FLUSH TABLES und das leert dann den
Query-Cache. Et voila!

Wenn du (wie du angedeutet hast) nur einzelne Datenbanken dumpst, dann
brauchst du --lock-all-tables auch gar nicht. --lock-tables (der Default)
leistet dann das gleiche. Es lockt alle Tabellen in der gleichen
Datenbank auf einmal und macht kein globales FLUSH TABLES.

>> Andererseits:
>>
>>> Qcache_hits 40084
>>> Qcache_inserts 358666
>>
>> weniger als 1/8 deiner SELECTs kann aus dem Cache beantwortet werden.

>> Das Verh锟絣tnis sollte eher anders herum sein.
>
> M锟絞licherweise w锟絩e die Ratio ja h锟絟er, wenn der Cache nicht 1/h geleert
> w锟絩de...

Unwahrscheinlich. Marginale Verbesserungen vielleicht.
Aber probier es ruhig aus.

>> IMHO kannst du den Cache ganz abschalten und MySQL das freiwerdende
>> RAM anderweitig gewinnbringender verwenden lassen.
>

> Die Kiste ist dediziert und langweilt sich. *Die* 64MB hab ich 锟絙rig.

Da sind noch andere Dinge zu ber锟絚ksichtigen. Wenn der Query-Cache
angeschaltet ist, wird jedes SELECT erstmal gehasht und der Hash im
Cache gesucht -> jedes SELECT das sowieso nicht im Cache gefunden
wird, wird dadurch langsamer.

Jedes DML-Statement, das auch nur eine Tabelle ver锟絥dert, ruft die
Cleanup-Routine des Query-Cache auf -> DML Statements werden langsamer.

Wenn der Query-Cache nicht wenigstens 30-50% Hitrate bringt, ist es
meist besser, ihn ganz abzuschalten.


XL

Helmut Schneider

unread,
May 26, 2009, 7:10:10 AM5/26/09
to

K�nnten die Jungens ruhig in die manpage packen...

> Wenn du (wie du angedeutet hast) nur einzelne Datenbanken dumpst, dann
> brauchst du --lock-all-tables auch gar nicht. --lock-tables (der Default)
> leistet dann das gleiche. Es lockt alle Tabellen in der gleichen
> Datenbank auf einmal und macht kein globales FLUSH TABLES.

OK.

>>> Andererseits:
>>>
>>>> Qcache_hits 40084
>>>> Qcache_inserts 358666
>>>
>>> weniger als 1/8 deiner SELECTs kann aus dem Cache beantwortet werden.

>>> Das Verh�ltnis sollte eher anders herum sein.
>>
>> M�glicherweise w�re die Ratio ja h�her, wenn der Cache nicht 1/h geleert
>> w�rde...


>
> Unwahrscheinlich. Marginale Verbesserungen vielleicht.
> Aber probier es ruhig aus.

Mache ich.

>>> IMHO kannst du den Cache ganz abschalten und MySQL das freiwerdende
>>> RAM anderweitig gewinnbringender verwenden lassen.
>>

>> Die Kiste ist dediziert und langweilt sich. *Die* 64MB hab ich �brig.
>
> Da sind noch andere Dinge zu ber�cksichtigen. Wenn der Query-Cache


> angeschaltet ist, wird jedes SELECT erstmal gehasht und der Hash im
> Cache gesucht -> jedes SELECT das sowieso nicht im Cache gefunden
> wird, wird dadurch langsamer.
>

> Jedes DML-Statement, das auch nur eine Tabelle ver�ndert, ruft die


> Cleanup-Routine des Query-Cache auf -> DML Statements werden langsamer.
>
> Wenn der Query-Cache nicht wenigstens 30-50% Hitrate bringt, ist es
> meist besser, ihn ganz abzuschalten.

Guter Tip!

Merci, Helmut

Reply all
Reply to author
Forward
0 new messages