Frage Beste MySQL-Cache-Einstellungen für 8 GB RAM dedizierter MySQL-Server, der nur InnoDB verwendet (5-GB-Datenbank)


Ich bin ein ziemlich großer Anfänger, wenn es darum geht, MySQL für die Leistung einzurichten. Ehrlich gesagt mache ich mir keine Sorgen um die Feinabstimmung, um die letzte Leistung von MySQL zu nutzen, aber ich weiß, dass das wichtigste, das beste Ergebnis zu erzielen, das richtige Einrichten von Caches / Buffern ist.

Ich habe versucht, die Dinge einfach zu halten, indem ich nur InnoDB als Speicher-Engine verwende. Und ich habe einen dedizierten Server für MySQL. Es hat 8 GB RAM, wie sollte ich das zuweisen, um die Leistung zu maximieren? Ich würde gerne in der Lage sein, meine gesamte Datenbank in den Speicher für die beste Leistung zu passen. Die Datenbank ist ungefähr 5 GB. Ist das möglich?

Wie viel Speicher sollte ich dem Abfragecache zuweisen? Wie viel zum InnoDB-Pufferpool? Wie viel für den Rest des Computers (d. H. Nicht MySQL-bezogene Prozesse)? Usw.

Da ich MyISAM nicht verwende, muss ich nicht wirklich viel Speicher in den Schlüssel-Cache einbauen?


19
2017-10-15 19:21


Ursprung




Antworten:


Das ist schwer, ohne viel über die Datenbank selbst zu wissen. Es gibt ein paar Werkzeuge, die Sie beachten sollten;

Über das Speichern der gesamten Datenbank im Speicher; Alle Abfragen, die Änderungen an der Datenbank vornehmen, bleiben geöffnet, bis der Schreibvorgang auf dem Datenträger ausgeführt wird. Die einzige Sache, die vermeiden kann, dass die Platte ein Engpass ist, ist ein Plattencontroller mit einem Schreibcache.

Ich würde mit den folgenden Änderungen von den Standardeinstellungen beginnen:

key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

innodb_buffer_pool_size = 4G 

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve

Dann würde ich sehen, wie die Dinge laufen, und verschiedene Dinge ausprobieren, unter anderem auf der Grundlage der oben genannten Werkzeuge. Ich würde auch sicherstellen, Trends mit einem Monitoring-Tool, wie z Munin oder Kakteen, um zu sehen, mit welcher Art von Arbeitslast ich tatsächlich zu tun habe. Ich persönlich habe viel Erfahrung mit den MySQL-Plugins von Munin.


24
2017-10-15 19:47



Danke, ich gebe dir eine Chance und schau, ob ich etwas Neues lernen kann. - billmalarky
@billmalarky Willkommen bei Serverfehler! :) Denken Sie daran, die Frage zu korrigieren oder zu markieren, wenn Sie mit der Antwort, die Sie erhalten haben, zufrieden sind. - Kvisle
+1 für die Empfehlung von mysqltuner.pl !!! - RolandoMySQLDBA
@Kvisle Danke für die Begrüßung! Ich bin schon seit einiger Zeit mit stackoverflow beschäftigt (naja ... ein paar Monate später ...), aber ich bin neu bei Serverfault. Ich hosste mit voxel.net und ihr promanaged Hosting (IE-System-Support) ist wirklich großartig, aber ich dachte mir, dass es angemessen wäre, auch nach draußen zu schauen, also klang ich nicht wie ein komplettes Werkzeug, wenn ich mit ihnen darüber spreche. - billmalarky
@ Kvisle Auch, ich werde eine richtige Antwort markieren, aber ich möchte, dass dies ein wenig schmoren, um ein gutes Brainstorming zu bekommen. - billmalarky


IMHO solltest du mitgehen können

innodb_buffer_pool_size=5G

Das wären 62,5% RAM mit genügend RAM für das Server OS plus Speicher für DB Connections

@ kvisle empfohlen mit mysqltuner.pl. Dieses Skript eignet sich hervorragend zum Beurteilen der Menge an RAM, die join_buffer_size, sort_buffer_size, read_buffer_size und read_rnd_buffer_size zugewiesen werden soll. Diese 4 zusammengesetzten Puffer werden mit max_connections multipliziert. Diese Antwort wird statischen Puffern hinzugefügt (innodb_buffer_pool_size + key_buffer_size). Die kombinierten Summen werden gemeldet. Wenn diese kombinierte Summe 80% des RAM übersteigt, müssen Sie diese Puffergrößen verringern. mysqltuner.pl wird in dieser Hinsicht sehr hilfreich sein.

Da alle Ihre Daten InnoDB sind, können Sie key_buffer_size (Schlüssel-Cache-Puffer für MyISAM-Indizes) sehr niedrig machen (ich empfehle 64M).

Hier ist ein Beitrag, den ich im DBA StackExchange gemacht habe, um eine empfohlene Größe von innodb_buffer_pool_size zu berechnen.

UPDATE 2011-10-15 19:55 EDT

Wenn Sie wissen, dass Sie 5 GB Daten haben, dann war meine erste Empfehlung in Ordnung. Allerdings habe ich vergessen, eine Sache hinzuzufügen:

[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M

Die Protokolldateigröße muss 25% des InnoDB-Pufferpools betragen

UPDATE 2011-10-16 13:36 EDT

Die 25% -Regel basiert ausschließlich auf der Verwendung von zwei Protokolldateien. Während es möglich ist, mehrere innodb-Protokolldateien zu verwenden, funktionieren zwei normalerweise am besten.

Andere haben mit 25% ausgedrückt

Bei aller Fairness hat jedoch jemand aus der ursprünglichen InnoBase Oy-Firma geäußert, dass er die 25% -Regel wegen des größeren InnoDB-Pufferpools nicht verwendet.

Natürlich kann die 25% -Regel nicht funktionieren, wenn große Mengen an RAM vorhanden sind. Tatsächlich ist die größte innodb_log_file_size, die nur 2 Protokolldateien verwenden darf, 2047M, da die Gesamtgröße der Protokolldatei weniger als 4G (4096M) betragen muss.

CASE IN POINT: Einer meiner Auftraggeber hat einen DB-Server mit 192 GB RAM. Es gibt keine Möglichkeit, 48G-Protokolldateien zu erstellen. Ich verwende einfach die maximale Dateigröße für eine innodb-Protokolldatei 2047M. @ Kvisles Kommentar zu meiner Antwort gibt einfach einen Link, der besagt, dass Sie sich nicht auf zwei Protokolldateien beschränken müssen. Wenn Sie über N Protokolldateien verfügen, können sie 4G nicht zusammenführen. Meine 25% Regel ist nur in einer perfekten Welt (DB Server mit 8GB oder weniger).


9
2017-10-15 21:23



Danke, ich werde definitiv mysqltuner.pl benutzen. Allerdings muss ich fragen, ob dieses Skript noch korrekt ist, wenn meine Datenbank gerade leer ist? Ich habe alle Tabellen und Schemas korrekt eingerichtet, aber keine eigentlichen Daten. Ich habe die 5GB als Beispielgröße verwendet, die ich auf meinem aktuellen dedizierten Server verwenden würde (das heißt, sobald die Datenbank größer wird, werde ich wahrscheinlich zu einem besseren dedizierten Server wechseln). Derzeit ist die Datenbank leer (es ist eine brandneue Site, die nicht gestartet wurde), aber ich möchte mysql anpassen, um die Skalierung vor dem Start gut zu handhaben. - billmalarky
Meine Antwort aktualisiert !!! - RolandoMySQLDBA
Ich möchte den "Muss 25% des InnoDB Pufferpools" kommentieren. Weil es nicht 100% wahr ist. Lesen dev.mysql.com/doc/refman/5.0/de/... für eine ausführlichere Antwort. In Bezug auf die Leistung hilft es sehr, die Größe ein wenig von der Standardgröße zu erhöhen, aber Sie müssen sie nicht maximieren. - Kvisle
Rolando, danke für die Hilfe. Ich habe diesen Thread als Referenz gespeichert. Außerdem verwende ich dieses mysqltuner-Skript jetzt. - billmalarky