MYSQL: Hilfe benötigt für schnell wachsende Tabelle und sinkende Geschwindigkeit (4 Millionen Zeilen)

Question

Ich habe mit einem rasant wachsenden Tisch zu tun, der sich mit zunehmender Geschwindigkeit ausdehnt (derzeit 4 Mio. Zeilen, 300.000 Einfügungen pro Tag). Ich hoffe, ich kann hier einige Ideen und Ratschläge bekommen, um mein Setup zu verbessern und das letzte bisschen aus meiner Box herauszuholen, bevor es meine Website in naher Zukunft lahmlegt.

Das Setup:

    Intel i7 720 
    8GB RAM
    2x750GB SATA RAID 0
    CentOS
    MySQL 5.5.10
    Node.js + node-lib_mysql-client

Die Tabellendefinition:

CREATE TABLE IF NOT EXISTS `canvas` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`x1` int(11) NOT NULL,
`y1` int(11) NOT NULL,
`x2` int(11) NOT NULL,
`y2` int(11) NOT NULL,
`c` int(4) unsigned NOT NULL,
`s` int(3) unsigned NOT NULL,
`m` bigint(20) unsigned NOT NULL,
`r` varchar(32) NOT NULL,
PRIMARY KEY (`id`,`x1`,`y1`) KEY_BLOCK_SIZE=1024,
KEY `x1` (`x1`,`y1`) KEY_BLOCK_SIZE=1024,
KEY `x2` (`x2`,`y2`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY HASH ( (
(
x1 MOD 10000
)
) + y1 MOD 10000)
PARTITIONS 10 */ AUTO_INCREMENT=13168904 ;

Die Abfrage:

SELECT x1, y1, x2, y2, s, c, r, m FROM canvas
WHERE 1 AND ((
 x1 >= 0
 AND x1 <= 400
 AND y1 >= 0
 AND y1 <= 400
 ) OR ( 
 x2 >= 0
 AND x2 <= 400
 AND y2 >= 0
 AND y2 <= 400
 ) )
  ORDER BY id desc

Das ist die einzige Abfrage, die ich ausführe, abgesehen davon, dass sich die Werte für x1, y1, x2 und y2 pro Abfrage ändern. Es handelt sich um eine 2D-Leinwand und jede Zeile stellt eine Linie auf der Leinwand dar. Es ist auch wichtig zu wissen, dass der maximale Bereich, der für ein Feld ausgewählt wird, nie größer als 1200 (Pixel) ist. Vor ein paar Wochen habe ich auf MySQL 5.5.10 aktualisiert und angefangen, Partitionen zu verwenden. Der 'x1 % 10000' Hash war mein erster, unbewusster Ansatz, um in das Partitionsthema einzutauchen. Es hat mir bereits einen anständigen Geschwindigkeitsschub bei SELECTs gegeben, aber ich bin sicher, dass noch Raum für Optimierungen besteht.

Ach ja, und bevor Sie fragen... Ich bin mir bewusst, dass ich eine MyISAM-Tabelle verwende. Ein Freund von mir schlug innoDB vor, aber ich habe es bereits ausprobiert und das Ergebnis war eine zweimal größere Tabelle und ein großer Einbruch in der SELECT-Performance. Ich brauche keine schicken Transaktionen und so Zeug... alles, was ich brauche, ist die bestmögliche SELECT-Performance und eine anständige Leistung bei INSERTs.

Was würden Sie ändern? Könnte ich vielleicht meine Indizes irgendwie optimieren? Macht mein Partitions-Setup überhaupt Sinn? Sollte ich vielleicht die Anzahl der Partitiondateien erhöhen?

Alle Vorschläge sind willkommen... Ich habe auch mit einem Freund über eine lokale Replikation in eine Memory-Tabelle diskutiert, aber ich bin sicher, es ist nur eine Frage der Zeit, bis die Tabellengröße meinen RAM übersteigt und ein Auslagern auf eine Festplatte ist eine ziemlich hässliche Sache.

Wenn Sie über mein Problem nachdenken, denken Sie bitte daran, dass es sich rasant und unvorhersehbar ausbreitet. Falls es aus irgendeinem Grund irgendwo viral geht, erwarte ich mehr als 1 Million Einfügungen pro Tag zu sehen.

Vielen Dank fürs Lesen und Nachdenken darüber. :)

EDIT: Das angeforderte EXPLAIN-Ergebnis

select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
SIMPLE  canvas  index_merge     x1,x2   x1,x2   8,8     NULL    133532  Using sort_union(x1,x2); Using where; Using fileso...

EDIT2: Die angeforderte my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Die Deaktivierung von symbolischen Links wird empfohlen, um verschiedene Sicherheitsrisiken zu verhindern
symbolic-links=0

innodb_buffer_pool_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
innodb_file_format = Barracuda

query_cache_type = 1
query_cache_size = 100M

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Die innoDB-Werte sind für meinen innoDB-Versuch... ich glaube, sie sind nicht mehr notwendig. Der Server betreibt auch 4 andere Websites, die jedoch eher klein sind und nicht wirklich erwähnenswert. Ich werde dieses Projekt sowieso bald auf eine dedizierte Box umziehen. Ihre Ideen können radikal sein - ich habe keine Angst vor Experimenten.

EDIT3 - BENCHMARKS MIT INDEXEN

Ok Leute... ich habe einige Benchmarks mit verschiedenen Indizes durchgeführt und die Ergebnisse sind bisher ziemlich gut. Für diesen Benchmark habe ich alle Zeilen innerhalb eines Box von 2000x2000 Pixeln ausgewählt.

SELECT SQL_NO_CACHE x1, y1, x2, y2, s, c FROM canvas_test WHERE 1 AND (( x1 ZWISCHEN -6728 UND -4328 UND y1 ZWISCHEN -6040 UND -4440 ) ODER (  x2 ZWISCHEN -6728 UND -4328 UND y2 ZWISCHEN -6040 UND -4440 ) )  ORDER BY id aufsteigend

Unter Verwendung der oben von mir geposteten Tabellen- / Indexdefinition betrug die durchschnittliche Abfragezeit: 1740ms

Dann habe ich alle Indizes, außer dem Primärschlüssel, gelöscht -> 1900ms

einen Index für x1 hinzugefügt -> 1800ms

einen Index für y1 hinzugefügt -> 1700ms

einen Index für x2 hinzugefügt -> 1500ms

einen Index für y2 hinzugefügt -> 900ms!

Bisher ziemlich erstaunlich... aus irgendeinem Grund dachte ich, dass es sinnvoll wäre, kombinierte Indizes für x1/y1 und x2/y2 zu erstellen, aber tatsächlich sieht es so aus, als ob ich falsch lag.

EXPLAIN gibt jetzt dies zurück:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  canvas_test     index_merge     x1,y1,x2,y2     y1,y2   4,4     NULL    263998  Using sort_union(y1,y2); Using where; Using fileso..

Jetzt frage ich mich, warum es y1/y2 als Schlüssel verwendet und nicht alle vier?

Trotzdem bin ich immer noch auf der Suche nach weiteren Ideen und Ratschlägen, insbesondere bezüglich Partitionen und ordentlicher Hashfunktionen.

Answer 1

Zuerst würde ich das SELECT wie folgt ändern:

SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE 
  x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400 OR
  x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
ORDER BY id desc

Und stellen Sie sicher, dass ein Index auf diesen Ausdruck vorhanden ist:

CREATE INDEX canvas400 ON canvas(
  x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400 OR
  x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
)

Answer 2

1. Wie viel Speicher verwendet Ihr Server derzeit?
2. Handelt es sich um die einzige Datenbank/Tabelle auf dem Server?
3. Verwenden Sie ausschließlich MyISAM?

Die Verwendung von MyISAM ist in Ordnung, solange Sie Ihre Zeilen nicht aktualisieren. Wenn Sie eine Zeile in einer MyISAM-Tabelle aktualisieren, sperrt MySQL die gesamte Tabelle und blockiert die Ausführung von SELECTs und INSERTs, bis das UPDATE abgeschlossen ist. UPDATE hat Vorrang vor SELECT, daher warten Ihre SELECTs, wenn viele UPDATEs ausgeführt werden, bis sie alle abgeschlossen sind, bevor sie Zeilen zurückgeben.

Wenn das für Sie in Ordnung ist, gehen Sie zur Konfiguration Ihres Servers über. Wie sieht Ihre my.cnf-Datei aus? Sie möchten diese Datei optimieren, um den Speicher für Indizes zu maximieren. Wenn diese SELECTs langsam werden, liegt das daran, dass Ihre Tabellenindizes nicht in den Speicher passen. Wenn MySQL Ihre Tabellenindizes nicht in den Speicher passen kann, muss es auf die Festplatte gehen und einen Tabellen-Scan durchführen, um Ihre Daten abzurufen. Dies wird die Leistung beeinträchtigen.

BEARBEITEN 18.05.2011 21:30 EST

Nachdem ich Ihre my.cnf-Datei betrachtet habe, stelle ich fest, dass überhaupt keine MyISAM-Optimierungen vorhanden sind. Ihr Ausgangspunkt wird die Variable key_buffer_size sein. Diese Variable wird in der Regel auf etwa 25% bis 50% des insgesamt verfügbaren Speichers auf Ihrem System eingestellt. Ihr System verfügt über 8 GB verfügbaren Speicher, also würde ich sagen, dass etwa 3 GB ein minimaler Ausgangspunkt sind. Sie können jedoch schätzen, wie viel Sie benötigen, und es bei Bedarf optimieren, wenn Sie wissen, dass Sie die anderen Variablen auf dem System kontrollieren können.

Was Sie tun sollten, ist zum mysql-Datenspeicherordner zu wechseln (normalerweise /var/lib/mysql), wo sich alle Ihre Daten befinden. Eine schnelle Möglichkeit, herauszufinden, wie viele Indexdaten Sie haben, ist:

 sudo du -hc `find . -type f -name "*.MYI"

Dieser Befehl betrachtet die Größe all Ihrer MyISAM-Indexdateien und zeigt Ihnen deren Gesamtgröße an. Wenn Sie genug Speicher haben, möchten Sie sicherstellen, dass Ihr key_buffer_size in Ihrer my.cnf GRÖSSER ist als die Gesamtgröße all Ihrer MYI-Dateien. Dies stellt sicher, dass Ihre MyISAM-Indizes im Speicher liegen, sodass MySQL nicht auf die Festplatte zugreifen muss, um die Indexdaten abzurufen.

Eine kurze Anmerkung: Erhöhen Sie Ihren key_buffer_size nicht willkürlich. Dies ist nur ein Bereich von MySQL, der Speicher benötigt. Es gibt andere bewegliche Teile, bei denen Sie den Speicherverbrauch ausbalancieren müssen. MySQL-Verbindungen verbrauchen Speicher, und verschiedene Tabellen-Engines verwenden unterschiedliche Speicherpools für ihre Indizes, und MySQL verwendet andere Speicherbereiche für unterschiedliche Zwecke. Wenn Ihnen der Speicher ausgeht, weil Sie den key_buffer_size zu groß eingestellt haben, könnte Ihr Server mit dem Seitenwechsel beginnen (die Verwendung von virtuellem Speicher, was die Leistung noch MEHR beeinträchtigen würde) oder schlimmer noch, abstürzen. Beginnen Sie mit kleineren Werten, wenn Sie unsicher sind, überprüfen Sie Ihren Speicherverbrauch und erhöhen Sie ihn, bis Sie mit der Leistung zufrieden sind und Ihr Server nicht abstürzt.

Answer 3

Denken Sie daran, dass MySQL pro Abfrage nur einen Index pro Tabelle verwendet. Ihre SELECT-Abfrage wird nicht in der Lage sein, beide Indizes in derselben Abfrage zu verwenden - sie wird einen oder den anderen verwenden. Möglicherweise ist es effizienter, zwei SELECT-Abfragen mit UNION zu verknüpfen, damit jede den entsprechenden Index verwenden kann, z.B:

SELECT x1, y1, x2, y2, s, c, r, m FROM canvas
WHERE 
 x1 >= 0
 AND x1 <= 400
 AND y1 >= 0
 AND y1 <= 400
UNION
SELECT x1, y1, x2, y2, s, c, r, m FROM canvas
WHERE
 x2 >= 0
 AND x2 <= 400
 AND y2 >= 0
 AND y2 <= 400
;

oder Sie könnten, wie einer der anderen Antworten vorgeschlagen hat, BETWEEN verwenden, z.B:

SELECT x1, y1, x2, y2, s, c, r, m FROM canvas
WHERE x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400
UNION
SELECT x1, y1, x2, y2, s, c, r, m FROM canvas
WHERE x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
;

Es ist schon eine Weile her, seit ich ein UNION benutzt habe, daher bin ich mir nicht sicher, wo Sie Ihre ORDER BY-Klausel platzieren würden, aber Sie können damit experimentieren.

Wie einer der anderen Antworten erwähnt hat, verwenden Sie EXPLAIN, um zu sehen, wie viele Zeilen MySQL berücksichtigen muss, um die Abfragen zu erfüllen.

Es könnte auch sinnvoll sein, sich einen RTREE-Index anzusehen, obwohl ich selbst damit nicht experimentiert habe.

Answer 4

Welche Art von Geschwindigkeiten erzielst du? Da du keine relationalen Daten benötigst, solltest du in Betracht ziehen, deine Daten nach Redis zu verschieben. Es sollte problemlos +100k Einfügungen oder Lesevorgänge pro Sekunde auf deiner Maschine durchführen.