Detaillierte Erläuterung der MySQL-Puffer- und Cache-Einstellungen

Relationales MySQL-Datenbankverwaltungssystem

MySQL ist ein kleines relationales Open-Source-Datenbankverwaltungssystem, das von der schwedischen Firma MySQL AB entwickelt wurde. MySQL wird häufig auf kleinen und mittelgroßen Websites im Internet verwendet. Aufgrund der geringen Größe, der hohen Geschwindigkeit, der niedrigen Gesamtbetriebskosten und insbesondere der Eigenschaften von Open Source wählen viele kleine und mittlere Websites MySQL als Website-Datenbank, um die Gesamtbetriebskosten der Website zu senken.

Dieser Artikel erklärt Ihnen hauptsächlich die beiden wichtigeren Parameterpufferung und Cache-Einstellungen im MySQL-Optimierungsprozess.

Die optimierbaren MySQL-Einstellungen können auf den gesamten MySQL-Prozess oder auf einzelne Client-Sitzungen angewendet werden.

Serverseitige Einstellungen

Jede Tabelle kann als Datei auf der Festplatte dargestellt werden, die zuerst geöffnet und dann gelesen werden muss. Um das Lesen von Daten aus Dateien zu beschleunigen, speichert mysqld diese geöffneten Dateien bis zu einer maximalen Anzahl zwischen, die durch table_cache in /etc/mysqld.conf angegeben wird. Listing 4 zeigt eine Möglichkeit, Aktivitäten im Zusammenhang mit dem Öffnen einer Tabelle anzuzeigen.

Listing 4. Anzeige der Aktivität geöffneter Tabellen

mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 5000 |
| Opened_tables | 195  |
+---------------+-------+
2 rows in set (0.00 sec)

Listing 4 zeigt, dass derzeit 5.000 Tabellen geöffnet sind und 195 Tabellen geöffnet werden müssen, da keine verfügbaren Dateien im Cache vorhanden sind . Deskriptor (da die Statistiken zuvor gelöscht wurden, dürfen in den 5.000 offenen Tabellen nur 195 offene Datensätze vorhanden sein). Wenn Opened_tables bei der erneuten Ausführung des Befehls SHOW STATUS schnell ansteigt, deutet dies darauf hin, dass die Cache-Trefferquote unzureichend ist. Wenn Open_tables viel kleiner als die table_cache-Einstellung ist, ist der Wert zu groß (aber Platz zum Wachsen zu haben ist nie eine schlechte Sache). Verwenden Sie beispielsweise table_cache =5000, um den Cache der Tabelle anzupassen.

Ähnlich wie der Tabellencache gibt es auch einen Cache für Threads. mysqld generiert nach Bedarf Threads, wenn Verbindungen empfangen werden. Auf einem ausgelasteten Server, auf dem sich Verbindungen schnell ändern, kann das Zwischenspeichern von Threads für die spätere Verwendung die anfängliche Verbindung beschleunigen.

Listing 5 zeigt, wie Sie feststellen können, ob genügend Threads zwischengespeichert sind.

Listing 5. Thread-Nutzungsstatistiken anzeigen

mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name   | Value |
+-------------------+--------+
| Threads_cached  | 27   |
| Threads_connected | 15   |
| Threads_created  | 838610 |
| Threads_running  | 3   |
+-------------------+--------+
4 rows in set (0.00 sec)

Der wichtige Wert hier ist Threads_created. Dieser Wert wird jedes Mal erhöht, wenn mysqld einen neuen Thread erstellen muss. Wenn diese Zahl bei der Ausführung aufeinanderfolgender SHOW STATUS-Befehle schnell ansteigt, sollten Sie versuchen, den Thread-Cache zu vergrößern. Um dies zu erreichen, können Sie beispielsweise thread_cache = 40 in my.cnf verwenden.

Der Schlüsselwortpuffer enthält den Indexblock der MyISAM-Tabelle. Im Idealfall sollten Anfragen für diese Blöcke aus dem Speicher und nicht von der Festplatte kommen. Listing 6 zeigt, wie man ermittelt, wie viele Blöcke von der Festplatte und wie viele aus dem Speicher gelesen wurden.

Liste 6. Bestimmen Sie die Keyword-Effizienz

mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name   | Value   |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads     | 98247   |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads stellt die Anzahl der Anfragen dar, die auf die Festplatte gelangen, und Key_read_requests ist die Gesamtzahl. Die Anzahl der Leseanfragen, die auf der Festplatte eintreffen, geteilt durch die Gesamtzahl der Leseanfragen, ergibt das Miss-Verhältnis – in diesem Fall kommen auf 1.000 Anfragen etwa 0,6 Fehler im Speicher. Wenn die Anzahl der Festplattentreffer 1 pro 1.000 Anfragen übersteigt, sollten Sie eine Erhöhung des Schlüsselwortpuffers in Betracht ziehen. Mit „key_buffer =384M“ wird der Puffer beispielsweise auf 384 MB festgelegt.

Temporäre Tabellen können in komplexeren Abfragen verwendet werden, bei denen Daten vor der weiteren Verarbeitung in einer temporären Tabelle gespeichert werden müssen (z. B. eine GROUPBY-Klausel). Erstellen Sie die temporäre Tabelle idealerweise im Speicher. Wenn die temporäre Tabelle jedoch zu groß wird, muss sie auf die Festplatte geschrieben werden. Listing 7 enthält Statistiken zur Erstellung temporärer Tabellen.

Listing 7. Bestimmen der Verwendung temporärer Tabellen

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files    | 2   |
| Created_tmp_tables   | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Jede Verwendung einer temporären Tabelle erhöht Created_tmp_tables; festplattenbasierte Tabellen erhöhen auch Created_tmp_disk_tables. Für dieses Verhältnis gibt es keine strengen Regeln, da es von der jeweiligen Abfrage abhängt. Wenn Sie Created_tmp_disk_tables im Laufe der Zeit beobachten, sehen Sie das Verhältnis der erstellten Festplattentabellen und können die Effizienz Ihres Setups bestimmen. Sowohl tmp_table_size als auch max_heap_table_size steuern die maximale Größe temporärer Tabellen. Stellen Sie daher sicher, dass beide Werte in my.cnf festgelegt sind.

Einstellungen pro Sitzung

Die folgenden Einstellungen gelten spezifisch für jede Sitzung. Seien Sie beim Festlegen dieser Zahlen sehr vorsichtig, da diese Optionen multipliziert mit der Anzahl der möglicherweise vorhandenen Verbindungen eine große Speichermenge darstellen! Sie können diese Zahlen innerhalb einer Sitzung über Code ändern oder diese Einstellungen in my.cnf für alle Sitzungen ändern.

Wenn MySQL sortieren muss, weist es beim Lesen von Daten von der Festplatte einen Sortierpuffer zum Speichern der Datenzeilen zu. Wenn die zu sortierenden Daten zu groß sind, müssen die Daten in einer temporären Datei auf der Festplatte gespeichert und erneut sortiert werden. Wenn die Statusvariable sort_merge_passes groß ist, weist dies auf Festplattenaktivität hin. Listing 8 enthält einige sortierungsbezogene Statuszählerinformationen.

Listing 8. Sortierstatistiken anzeigen

mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| Sort_merge_passes | 1    |
| Sort_range    | 79192  |
| Sort_rows     | 2066532 |
| Sort_scan     | 44006  |
+-------------------+---------+
4 rows in set (0.00 sec)

如果 sort_merge_passes 很大，就表示需要注意sort_buffer_size。例如，sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如清单 9 所示。

清单 9. 确定表扫描比率

mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select  | 318243 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name     | Value   |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next /Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过4000，就应该查看 read_buffer_size，例如read_buffer_size = 4M。如果这个数字超过了8M，就应该与开发人员讨论一下对这些查询进行调优了！

查看数据库缓存配置情况

mysql> SHOW VARIABLES LIKE ‘%query_cache%';
+——————————+———+
| Variable_name | Value |
+——————————+———+
| have_query_cache | YES | –查询缓存是否可用
| query_cache_limit | 1048576 | –可缓存具体查询结果的最大值
| query_cache_min_res_unit | 4096 |
| query_cache_size | 599040 | –查询缓存的大小
| query_cache_type | ON | –阻止或是支持查询缓存
| query_cache_wlock_invalidate | OFF |
+——————————+———+

配置方法：

在MYSQL的配置文件my.ini或my.cnf中找到如下内容：

# Query cache is used to cache SELECT results and later returnthem

# without actual executing the same query once again. Having thequery

# cache enabled may result in significant speed improvements, ifyour

# have a lot of identical queries and rarely changing tables.See the

# "Qcache_lowmem_prunes" status variable to check if the currentvalue

# is high enough for your load.

# Note: In case your tables change very often or if your queriesare

# textually different every time, the query cache may result ina

# slowdown instead of a performance improvement.

query_cache_size=0

以上信息是默认配置，其注释意思是说，MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。

具体配置方法：

1． 将query_cache_size设置为具体的大小，具体大小是多少取决于查询的实际情况，但最好设置为1024的倍数，参考值32M。

2． 增加一行：query_cache_type=1

query_cache_type参数用于控制缓存的类型，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。但是这种情况下query_cache_size设置的大小系统是否要为其分配呢，这个问题有待于测试？

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

OK，配置完后的部分文件如下：

query_cache_size=128M

query_cache_type=1

保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：

mysql> show variables like '%query_cache%';

+——————————+———–+

| Variable_name      |Value  |

+——————————+———–+

| have_query_cache     |YES   |

| query_cache_limit     |1048576  |

| query_cache_min_res_unit  |4096   |

| query_cache_size     | 134217728|

| query_cache_type     |ON    |

| query_cache_wlock_invalidate | OFF   |

+——————————+———–+

6 rows in set (0.00 sec)

主要看query_cache_size和query_cache_type的值是否跟我们设的一致：

这里query_cache_size的值是134217728，我们设置的是128M，实际是一样的，只是单位不同，可以自己换算下：134217728 = 128*1024*1024。

query_cache_type设置为1，显示为ON，这个前面已经说过了。

总之，看到上边的显示表示设置正确，但是在实际的查询中是否能够缓存查询，还需要手动测试下，我们可以通过show statuslike '%Qcache%';语句来测试，现在我们开启了查询缓存功能，在执行查询前，我们先看看相关参数的值：

mysql> show status like '%Qcache%';

+————————-+———–+

| Variable_name    |Value  |

+————————-+———–+

| Qcache_free_blocks   |1    |

| Qcache_free_memory   | 134208800|

| Qcache_hits     |0    |

以上就是mysql缓冲和缓存设置详解的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！