Umgang mit sehr großen Datenmengen mit mysql
Sorry für den langen post!
Ich habe eine Datenbank mit ~30 Tabellen (InnoDB engine). Nur zwei dieser Tabellen, nämlich, "Transaktion" und "shift" sind sehr groß (die ersten 1,5 Millionen Zeilen und shift hat 23k Zeilen). Jetzt funktioniert alles einwandfrei und ich habe keine problem mit der aktuellen Größe der Datenbank.
Jedoch, wir haben eine ähnliche Datenbank (gleiche Datentypen, design ,..) aber sehr viel größer, z.B., die "Transaktionen" - Tabelle wird über 1 Milliarden Datensätze (über 2,3 Mio Transaktion pro Tag), und wir überlegen, wie wir umgehen sollten mit einer solchen Menge von Daten in MySQL? (es ist beides, Lesen und schreiben intensiv). Ich lese eine Menge posten, um zu sehen, wenn Mysql (und insbesondere InnoDB engine) durchführen können, auch mit Milliarden von Datensätzen, aber dennoch habe ich ein paar Fragen. Einige dieser Verwandte Beiträge, die ich gelesen habe sind die folgenden:
- Kann MySQL vernünftig ausführen von Abfragen auf Milliarden von Zeilen?
- Ist InnoDB (MySQL 5.5.8) die richtige Wahl für multi-Milliarden-Reihen?
- Am besten die Daten speichern, die für Milliarden von Zeilen
- Wie groß darf eine MySQL-Datenbank bekommen, bevor die Leistung sinkt
- Warum MySQL sein könnte, langsam, mit großen Tabellen?
- Kann Mysql Griff-Tabellen, die halten etwa 300 Millionen Datensätze?
Was ich verstanden hab, so weit zu verbessern, die Leistung für sehr große Tabellen:
- (bei innoDB-Tabellen, das ist bei mir der Fall) die Erhöhung der
innodb_buffer_pool_size
(z.B., bis zu 80% des RAM).
Auch fand ich einige andere MySQL-performance-Tuning-Einstellungen hier
percona blog - mit den richtigen Indizes auf der Tabelle (mit BESCHREIBEN, auf Abfragen)
- die Partitionierung der Tabelle
- MySQL-Splitter oder clustering
Hier sind meine Fragen/Verwirrungen:
-
Zur Partitionierung, ich habe einige Zweifel, ob wir es nutzen oder nicht. Auf der einen Seite viele Menschen, die es vorgeschlagen, um die Leistung zu verbessern, wenn die Tabelle sehr groß ist. Auf der anderen Seite, ich habe viele Beiträge gelesen, die sagen, es nicht in die query-performance verbessert und es macht nicht Abfragen schneller ausgeführt werden können (z.B. hier und hier). Auch Las ich in MySQL Reference Manual, dass InnoDB foreign keys und MySQL Partitionierung nicht kompatibel sind (wir haben foreign-keys).
-
In Bezug auf Indizes, jetzt Sie gut, aber soweit ich verstanden habe, für sehr große Tabellen, Indizierung restriktiver ist (wie Kevin Bedell erwähnt in seiner Antwort,hier). Auch, Indizes beschleunigen liest, während langsamer schreiben (insert/update). Also, für die neue ähnliche Projekte, wir haben in diesem großen DB, sollten wir zuerst einfügen/laden der Daten und erstellen Sie Indizes? (um die Geschwindigkeit der Beilage)
-
Wenn wir nicht partitionieren für unseren großen Esstisch ("Transaktionen" - Tabelle), was ist eine alternative option, um die performance zu verbessern? (außer MySQl variable Einstellungen wie
innodb_buffer_pool_size
). Sollten wir die Verwendung von Mysql Cluster? (wir haben auch viele joins)
BEARBEITEN
Dies ist die show create table
- Anweisung für unseren größten Tabelle mit dem Namen "Transaktion":
CREATE TABLE `transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`terminal_transaction_id` int(11) NOT NULL,
`fuel_terminal_id` int(11) NOT NULL,
`fuel_terminal_serial` int(11) NOT NULL,
`xboard_id` int(11) NOT NULL,
`gas_station_id` int(11) NOT NULL,
`operator_id` text NOT NULL,
`shift_id` int(11) NOT NULL,
`xboard_total_counter` int(11) NOT NULL,
`fuel_type` int(11) NOT NULL,
`start_fuel_time` int(11) NOT NULL,
`end_fuel_time` int(11) DEFAULT NULL,
`preset_amount` int(11) NOT NULL,
`actual_amount` int(11) DEFAULT NULL,
`fuel_cost` int(11) DEFAULT NULL,
`payment_cost` int(11) DEFAULT NULL,
`purchase_type` int(11) NOT NULL,
`payment_ref_id` text,
`unit_fuel_price` int(11) NOT NULL,
`fuel_status_id` int(11) DEFAULT NULL,
`fuel_mode_id` int(11) NOT NULL,
`payment_result` int(11) NOT NULL,
`card_pan` text,
`state` int(11) DEFAULT NULL,
`totalizer` int(11) NOT NULL DEFAULT '0',
`shift_start_time` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE,
KEY `start_fuel_time_idx` (`start_fuel_time`),
KEY `fuel_terminal_idx` (`fuel_terminal_id`),
KEY `xboard_idx` (`xboard_id`),
KEY `gas_station_id` (`gas_station_id`) USING BTREE,
KEY `purchase_type` (`purchase_type`) USING BTREE,
KEY `shift_start_time` (`shift_start_time`) USING BTREE,
KEY `fuel_type` (`fuel_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Vielen Dank für Ihre Zeit,
- Hehe -- "langen posten" Erträge "lange Antwort".
Du musst angemeldet sein, um einen Kommentar abzugeben.
Kann MySQL vernünftig ausführen von Abfragen auf Milliarden von Zeilen? -- MySQL kann 'Griff', die Milliarden von Zeilen. "Angemessen" ist, hängt auf den Abfragen; sehen wir Sie.
Ist InnoDB (MySQL 5.5.8) die richtige Wahl für multi-Milliarden-Reihen? -- 5.7 hat einige Verbesserungen, aber 5.5 ist ziemlich gut, trotz des seins
fast 68 Jahre alt, und kurz davor, nicht mehr unterstützt wird.Beste datenspeicher für Milliarden von Zeilen-Wenn du meinst, 'Motor', dann InnoDB.
Wie groß darf eine MySQL-Datenbank bekommen, bevor die Leistung sinkt-Wieder, das hängt von der Abfragen. Ich kann Ihnen zeigen, einen 1K-Zeilen-Tabelle, die Kernschmelze; ich habe mit Milliarden-Zeile Tabellen, hum entlang.
Warum MySQL sein könnte, langsam, mit großen Tabellen? -- Spektrum-scans führen zu I/O, was ist der langsame Teil.
Können Mysql-Griff-Tabellen, die halten etwa 300 Millionen Datensätze? - wieder ja. Die Grenze ist irgendwo bei Billionen Zeilen.
(bei innoDB-Tabellen, das ist bei mir der Fall) die Erhöhung der innodb_buffer_pool_size (z.B., bis zu 80% des RAM). Auch fand ich einige andere MySQL-performance-Tuning-Einstellungen hier im percona-blog -- ja
richtigen Indizes auf der Tabelle (mit BESCHREIBEN, auf Abfragen) -- gut, lassen Sie uns sehen, wie Sie. Es gibt viele Fehler, die gemacht werden können in diesem kritische Bereich.
Partitionierung der Tabelle -- "Partitionieren ist kein Allheilmittel!" Ich Harfe auf, die in mein blog
MySQL-Splitter -- dies ist Aktuell DIY
MySQL clustering -- Derzeit die beste Antwort ist einige Galera-basierte option (PXC, MariaDB 10, DIY w/Oracle). Oracle "Group Replication" ist eine praktikable Anwärter.
Partitionierung nicht unterstützt
FOREIGN KEY
oder "global"UNIQUE
.UUIDs, auf der Skala, von der Sie reden, wird nicht nur das system verlangsamen, tatsächlich aber töten Sie ihn. Typ-1-UUIDs möglicherweise einen workaround.
Einfügen und index-build-Geschwindigkeit-Es gibt zu viele Varianten zu geben, eine einzige Antwort. Lassen Sie uns sehen Sie Ihre vorläufige
CREATE TABLE
und wie Sie beabsichtigen, einspeisen der Daten in.Viele joins -- "Normalisieren, aber nicht zu normalisieren." Insbesondere nicht normalisieren datetimes oder Schwimmern oder anderen "permanent" Werte.
Bauen, Zusammenfassung Tabellen
2,3 Mio Transaktion pro Tag -- Wenn das 2.3 M fügt (30/Sek), dann gibt es nicht viel von einem performance-problem. Wenn Sie komplexer werden, dann ist RAID, SSD, Batchverarbeitung, etc., notwendig sein.
Umgang mit solchen Datenmengen -- Wenn die meisten Aktivität ist mit der "letzten" Zeilen ist, dann sind die buffer_pool wird schön 'cache' die Aktivität, wodurch die I/O. Wenn die Aktivität "random", dann MySQL (oder jemand anderes) wird I/O-Probleme.
Schrumpfen die Datentypen hilft in einer Tabelle wie dem ihrigen. Ich bezweifle, dass, wenn Sie benötigen 4 bytes angeben
fuel_type
. Es sind mehrere 1-byte-Ansätze.SHOW CREATE TABLE
, text vonSELECT
undUPDATE
Aussagen (auch wenn unter VORBEHALT). Diese werden zeigen, (1) was Indizes, die Sie derzeit haben, und (2) welche Indizes die Sie benötigen.GROUP BY
? Mehr Tipps zur Verfügung.show create table
auf meine Frage. Dies ist eine web-Anwendung, die im Zusammenhang mit der Erdöl-Transaktionen einige Tankstellen in meiner Stadt.Beim sammeln von Milliarden von Zeilen, ist es besser (wenn möglich) zu konsolidieren, zu verarbeiten, zusammenzufassen, was auch immer, die Daten vor speichern. Halten Sie die raw-Daten in einer Datei, wenn Sie denken, Sie brauchen, um wieder zu es.
Tun, die beseitigt die meisten Ihrer Fragen und Anliegen, plus beschleunigen die Bearbeitung.