Der MySQL Query-Cache

Geschrieben von Frank (Level 3 - Junior Administrator)

Erstellt am 13.11.2003, um 08:45:42 Uhr, Permanent-ID: 820

Dieser Beitrag wurde bisher 50224 mal aufgerufen und gilt als gelöst.

Melden Sie sich mit Ihrem Nicknamen an, um diesen Beitrag zu bewerten!

0 NeutralDruckenE-Mail an FreundeBeobachten

Kurz gesagt: Der MySQL Query-Cache beschleunigt in den meißten Fällen die Abfragen. Er ist ab der Version 4 von MYSQL enthalten.

In dem Query-Cache werden Querys und deren Ergebnismengen gespeichert. Wenn das gleiche Statement mehrmals benutzt wird und die betroffenen Datensätze sich nicht verändert haben, wird das Ergebnis aus dem Cache geliefert. Bei einem kleinen Cache kann es allerdings sehr schnell vorkommen, dass ältere Statements überschrieben werden.
In Szenarien, in denen häufig auf Bestandsdaten zugegriffen wird, kann dieser Cache enmorme Geschwindigkeitssteigerungen bewirken. Besonders bei komplexen Statements kann der Query-Cache sehr wirkungsvoll sein, da das zeitaufwendige Parsen der Statements entfällt. Bei Bewegungsdaten, dürften die Geschwindigkeitssteigerungen ehere gering sein.

Die Cache-Parameter:

Um mit den Cache-Parametern herumspielen zu können, müssen Sie sich mit 'root'-Rechten einloggen. Zunächst identifizieren Sie die eingestellten Optionen der MySQL Datenbank. Sie können dazu einfach 'show variables' eingeben. Allerdings erhalten Sie dabei eine Riesenliste, und müssten die interessanten Werte mühsam heraussuchen. Einfacher geht es mit dem Befehl:
mysql> show variables like '%cache%';

Das Ergebins sieht bei den meißten dann so aus:

query_cache_limit     | 1048576    |
query_cache_size      | 0          |
query_cache_type      | ON         01.
query_cache_limit     | 1048576    | 
02.
query_cache_size      | 0          | 
03.
query_cache_type      | ON         

Als default query_cache_size Wert steht dort 0. Ihr Cache ist also noch nicht eingeschaltet.

Mit dem Wert 'query_cache_size' reservieren Sie in MySQL einen bestimmten Speicherbereich, um einmal beantwortete 'select'-Anfragen temporär zwischenzuspeichern. Das bringt ab der zweiten Abfrage ganz massiv schnellere Resultate, weil die Informationen direkt aus dem Speicher kommen.

Schalten Sie den Cache nun in der Laufzeit ein (die Größe ist hier in Byte angegeben, kann aber auch in MB angegeben werden z.B. 100M):

mysql> set global query_cache_size = 10000000;01.
mysql> set global query_cache_size = 10000000;

Zum Testen nun den Befehl:

mysql> show variables like 'query_cache_size';01.
mysql> show variables like 'query_cache_size';

Die query_cache_size steht jetzt auf etwa 9999360 Bytes. Die Differenz von einigen Bytes sollte Sie nicht weiter beunruhigen, letztlich braucht die Verwaltung auch noch einige Bytes.

Mit dem Befehl:

mysql> FLUSH QUERY CACHE; 01.
mysql> FLUSH QUERY CACHE; 

initialisieren Sie den Cache auf Ihre aktuellen Daten. Der Befehl bewirkt eine defragmentation des Caches und kann jederzeit zur Optimierung ausgeführt werden.

Mit dem Befehl:

mysql> RESET QUERY CACHE;01.
mysql> RESET QUERY CACHE;

löschen Sie alle gespeicherten Cache Ergebnisse aus den Query-Cache.

In Ihrer Laufzeitumgebung ist der Query-Cache nun erfolgreich eingeschaltet. Fehlt nur noch der permanente Eintrag in die my.cnf, damit die Einstellung nach einem Neustart der MySQL auch weiterhin bestehen bleiben.
Tragen sie dazu in die MySQL Konfigdatei: "/etc/my.cnf" die Zeile: "set-variable = query_cache_size=1000000" ein. Überprüfen Sie den Eintrag danach wieder mit dem "show variables like 'query_cache_size';" Befehl. Fertig.

Man kann die Effiezienz des Query-Cache auch sehr leicht bewerten. Mit dem Befehl:

mysql> SHOW STATUS; 01.
mysql> SHOW STATUS; 

bekommen sie alle Laufzeit-Informationen zum Query-Cache angezeigt: (hier ein Beispiel unseres gerade frisch aktivierten Query-Cache)

Qcache queries in cache   2  
Qcache inserts   3888  
Qcache hits   1797  
Qcache lowmem prunes   3376  
Qcache not cached   185  
Qcache free memory   924784  
Qcache free blocks   9  
Qcache total blocks   24  
 01.
Qcache queries in cache   2   
02.
Qcache inserts   3888   
03.
Qcache hits   1797   
04.
Qcache lowmem prunes   3376   
05.
Qcache not cached   185   
06.
Qcache free memory   924784   
07.
Qcache free blocks   9   
08.
Qcache total blocks   24   
09.
 

Einen weiteren interessanten Artikel dazu finden Sie auch unter der IX Webseite: http://www.heise.de/ix/artikel/2003/02/04 ...

Die englische MySQL Dokumentation finden Sie unter dem Link: http://www.mysql.com/doc/en/Query_Cache_S ...

Viel Spaß noch beim Optimieren. Sie können uns und den Lesern ja mal Ihrer Erfahrungen über die Vor- oder Nachteile des Query-Caches schreiben. Auf Feedback sind wir immer neugierig. [FS]

Kommentar schreibenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

Diskussionsverlauf (5 Kommentare)

Thread-Ansicht

Frank schreibt am 24.02.2005 um 14:51:57 Uhr
Frank schreibt am 26.05.2007 um 12:33:35 Uhr
- bitverdreher schreibt am 21.06.2007 um 11:07:04 Uhr
  - bennos schreibt am 16.02.2008 um 16:25:46 Uhr
- meloki schreibt am 18.06.2008 um 18:32:11 Uhr

Frank schreibt am 24.02.2005, 14:51:57 Uhr

Kleine Ergänzung:

Mann kann den Query-Cache auch mit:

mysql>SHOW VARIABLES LIKE 'have_query_cache'01.
mysql>SHOW VARIABLES LIKE 'have_query_cache'

prüfen.

Um alle Ergebnisse anzuzeigen:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+01.
mysql> SHOW STATUS LIKE 'Qcache%'; 
02.
+-------------------------+--------+ 
03.
| Variable_name           | Value  | 
04.
+-------------------------+--------+ 
05.
| Qcache_free_blocks      | 36     | 
06.
| Qcache_free_memory      | 138488 | 
07.
| Qcache_hits             | 79570  | 
08.
| Qcache_inserts          | 27087  | 
09.
| Qcache_lowmem_prunes    | 3114   | 
10.
| Qcache_not_cached       | 22989  | 
11.
| Qcache_queries_in_cache | 415    | 
12.
| Qcache_total_blocks     | 912    | 
13.
+-------------------------+--------+

AntwortenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

Frank schreibt am 26.05.2007, 12:33:35 Uhr

Hi @all,

eine paar Änderungen:

Tragen sie dazu in die MySQL Konfigdatei: "/etc/my.cnf" die Zeile: "set-variable =
query_cache_size=1000000" ein.

seit der MySQL Version 5 kann man in der "/etc/my.cnf" das "set-variable =" weglassen.
Es reicht also:

query_cache_limit  = 16M 
query_cache_size  = 128M 
query_cache_type = 1 01.
query_cache_limit  = 16M  
02.
query_cache_size  = 128M  
03.
query_cache_type = 1 

Auch gibt es noch ein paar zusätzliche Parameter:

query_cache_limit  = max. Größe für einen einzelnen Query Aufruf, der in den Cache aufgenommen wird.
query_cache_size  = Wie viel Speicher der gesamte Query-Cache belegen darf
query_cache_type = Query-Cache ein (1) der (0) ausschalten (2 per SQL aktivieren (SQL_CACHE) /deaktivieren (SQL_NO_CACHE) )01.
query_cache_limit  = max. Größe für einen einzelnen Query Aufruf, der in den Cache aufgenommen wird. 
02.
query_cache_size  = Wie viel Speicher der gesamte Query-Cache belegen darf 
03.
query_cache_type = Query-Cache ein (1) der (0) ausschalten (2 per SQL aktivieren (SQL_CACHE) /deaktivieren (SQL_NO_CACHE) )

AntwortenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

bitverdreher schreibt am 21.06.2007, 11:07:04 Uhr

Hallo,

ich habe meine my.cnf folgendermaßen geändert.

query_cache_limit  = 16M 
query_cache_size  = 128M 
query_cache_type = 1 01.
query_cache_limit  = 16M  
02.
query_cache_size  = 128M  
03.
query_cache_type = 1 

Der Cache funktioniert soweit auch. Allerdings ist der Cache nach einem Neustart wieder leer. Ist das normal? Kann man das so konfigurieren, das der Cache erhalten bleibt?

Vielen Dank für das Tut und Viele Grüße
bitverdreher

AntwortenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

bennos schreibt am 16.02.2008, 16:25:46 Uhr

Ja das ist normal.

Der Query Cache liegt im RAM und ist logischerweise nach einem reboot leer.

als ergänzung kann ich immer empfehlen
http://www.mysqlperformanceblog.com

gute infos über performance einzelner STorage Engines, sowie benchmark und Standard Konfig von DB's.

bennos

AntwortenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

meloki schreibt am 18.06.2008, 18:32:11 Uhr

morgen Alle
ich habe ien problem im mein DB mysql, nach 1s nachdem ich mein DB neu gestartet habe , gibt schon ein paar tabellen in Qcache_not_cached=3. und nach 5 min ist es 38, obwohl ich die
Query_cache_type=1 ist.
in der DB habe ich 500.000 personen und 41 tabellen,Innodb engine
kann jemand mir helfen.

2GB RAM,Linux x64

[client]
#password	= your_password
port		= 3306
socket		= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking

key_buffer=256M

max_allowed_packet = 16M
sort_buffer_size =5M
table_cache = 4000

set-variable= max_connections=400
join_buffer_size=1M
key_buffer_size=64M

net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 1
character_set_server = utf8

query_cache_limit = 3M
query_cache_size = 50M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
query_cache_min_res_unit=9K
tmp_table_size=64M
low_priority_updates=1

server-id	= 1
innodb_data_file_path = ibdata1:1300M:autoextend:max:3500M

innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/

innodb_buffer_pool_size = 1300M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 5M

innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
wait_timeout=60
connect_timeout=10
interactive_timeout=120

thread_cache_size=256
thread_concurrency=2
thread_stack=128K

sql-mode=NO_BACKSLASH_ESCAPES


[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout01.
[client] 
02.
#password	= your_password 
03.
port		= 3306 
04.
socket		= /var/lib/mysql/mysql.sock 
05.
 
06.
# Here follows entries for some specific programs 
07.
 
08.
# The MySQL server 
09.
[mysqld] 
10.
port		= 3306 
11.
socket		= /var/lib/mysql/mysql.sock 
12.
skip-locking 
13.
 
14.
key_buffer=256M 
15.
 
16.
max_allowed_packet = 16M 
17.
sort_buffer_size =5M 
18.
table_cache = 4000 
19.
 
20.
set-variable= max_connections=400 
21.
join_buffer_size=1M 
22.
key_buffer_size=64M 
23.
 
24.
net_buffer_length = 8K 
25.
read_buffer_size = 512K 
26.
read_rnd_buffer_size = 512K 
27.
myisam_sort_buffer_size = 8M 
28.
lower_case_table_names = 1 
29.
character_set_server = utf8 
30.
 
31.
query_cache_limit = 3M 
32.
query_cache_size = 50M 
33.
query_cache_type = 1 
34.
query_prealloc_size = 163840 
35.
query_alloc_block_size = 32768 
36.
query_cache_min_res_unit=9K 
37.
tmp_table_size=64M 
38.
low_priority_updates=1 
39.
 
40.
server-id	= 1 
41.
innodb_data_file_path = ibdata1:1300M:autoextend:max:3500M 
42.
 
43.
innodb_log_group_home_dir = /var/lib/mysql/ 
44.
innodb_log_arch_dir = /var/lib/mysql/ 
45.
 
46.
innodb_buffer_pool_size = 1300M 
47.
 
48.
innodb_additional_mem_pool_size = 20M 
49.
 
50.
innodb_log_file_size = 5M 
51.
 
52.
innodb_flush_log_at_trx_commit = 1 
53.
innodb_lock_wait_timeout = 50 
54.
wait_timeout=60 
55.
connect_timeout=10 
56.
interactive_timeout=120 
57.
 
58.
thread_cache_size=256 
59.
thread_concurrency=2 
60.
thread_stack=128K 
61.
 
62.
sql-mode=NO_BACKSLASH_ESCAPES 
63.
 
64.
 
65.
[safe_mysqld] 
66.
err-log=/var/lib/mysql/mysqld.log 
67.
 
68.
[mysqldump] 
69.
quick 
70.
max_allowed_packet = 16M 
71.
 
72.
[mysql] 
73.
no-auto-rehash 
74.
 
75.
[isamchk] 
76.
key_buffer = 20M 
77.
sort_buffer_size = 20M 
78.
read_buffer = 2M 
79.
write_buffer = 2M 
80.
 
81.
[myisamchk] 
82.
key_buffer = 20M 
83.
sort_buffer_size = 20M 
84.
read_buffer = 2M 
85.
write_buffer = 2M 
86.
 
87.
[mysqlhotcopy] 
88.
interactive-timeout

AntwortenMit Zitat

Anmeldung erforderlich!

Bitte melden Sie sich erst mit Ihrem Nicknamen und Passwort an.

SSH und scp ...

Linux Software Raid 1 (md0) ...

Aktuelle Anleitungen

Mögliche Antworten oder verwandte Beiträge

Anleitungen zum Thema

Links zum Thema

Windows	Betriebssysteme	IT-Sicherheit & Recht	Entwicklung & Scripte
Linux & Unix	Netzwerke	Hardware Support	Multimedia
Apple MacOS	Internet & Intranet	Job & Weiterbildung	Off-Topic