MyISAM gegenüber InnoDB

Question

Ich arbeite an einem Projekt, bei dem viel in Datenbanken geschrieben wird, würde ich sagen ( 70% Beilagen und 30% Lesestoff ). Dieses Verhältnis würde auch Aktualisierungen einschließen, die ich als ein Lesen und ein Schreiben betrachte. Die Lesevorgänge können unsauber sein (z. B. benötige ich keine 100%ig genauen Informationen zum Zeitpunkt des Lesens).
Die betreffende Aufgabe wird über 1 Million Datenbanktransaktionen pro Stunde umfassen.

Ich habe eine Reihe von Sachen auf dem Web über die Unterschiede zwischen MyISAM und InnoDB gelesen, und MyISAM scheint wie die offensichtliche Wahl zu mir für die bestimmte Datenbank/Tabellen, die ich für diese Aufgabe verwenden werden. Nach dem, was ich zu lesen scheine, ist InnoDB gut, wenn Transaktionen benötigt werden, da Sperren auf Zeilenebene unterstützt wird.

Hat jemand Erfahrung mit dieser Art von Belastung (oder höher)? Ist MyISAM der richtige Weg?

Answer 1

Bei einer Last mit mehr Schreib- und Lesevorgängen profitieren Sie von InnoDB. Da InnoDB eher Row-Locking als Table-Locking bietet, kann Ihr SELECT s können nicht nur untereinander, sondern auch mit vielen anderen INSERT s. Wenn Sie jedoch nicht vorhaben, SQL-Transaktionen zu verwenden, setzen Sie den InnoDB-Commit-Flush auf 2 ( innodb_flush_log_at_trx_commit ). Dadurch erhalten Sie eine Menge Leistung zurück, die Sie sonst beim Verschieben von Tabellen von MyISAM nach InnoDB verlieren würden.

Ziehen Sie auch eine Replikation in Betracht. Dadurch erhalten Sie eine gewisse Leseskalierung, und da Sie angegeben haben, dass Ihre Reads nicht aktuell sein müssen, können Sie die Replikation ein wenig zurückfallen lassen. Achten Sie nur darauf, dass die Replikation auch bei starkem Datenverkehr den Rückstand aufholen kann, sonst wird sie immer im Rückstand sein und nie aufholen können. Wenn Sie diesen Weg gehen, muss ich jedoch stark empfehlen Sie, das Lesen von den Slaves und die Verwaltung der Replikationsverzögerung auf Ihren Datenbank-Handler zu übertragen. Es ist sehr viel einfacher, wenn der Anwendungscode nichts davon weiß.

Und schließlich sollten Sie sich der unterschiedlichen Belastung der Tische bewusst sein. Sie werden nicht für alle Tabellen das gleiche Lese-/Schreibverhältnis haben. Einige kleinere Tabellen mit nahezu 100 % Lesezugriffen können es sich leisten, MyISAM zu verwenden. Wenn Sie einige Tabellen haben, die zu fast 100 % geschrieben werden, können Sie von MyISAM profitieren. INSERT DELAYED aber das wird nur in MyISAM unterstützt (die DELAYED Klausel wird bei einer InnoDB-Tabelle ignoriert).

Aber Benchmark, um sicher zu sein.

Answer 2

Als Ergänzung zu den zahlreichen Antworten, die hier zu den mechanischen Unterschieden zwischen den beiden Motoren gegeben wurden, stelle ich eine empirische Studie zum Geschwindigkeitsvergleich vor.

In Bezug auf die reine Geschwindigkeit ist MyISAM nicht immer schneller als InnoDB, aber meiner Erfahrung nach ist es in reinen READ-Arbeitsumgebungen in der Regel um den Faktor 2,0-2,5 schneller. Natürlich ist dies nicht für alle Umgebungen geeignet - wie andere geschrieben haben, fehlen MyISAM Dinge wie Transaktionen und Fremdschlüssel.

Ich habe ein wenig Benchmarking unten getan - ich habe Python für Schleifen und die timeit Bibliothek für Timing-Vergleiche verwendet. Aus Interesse habe ich auch die Speicher-Engine einbezogen, die die beste Leistung auf der ganzen Linie bietet, obwohl sie nur für kleinere Tabellen geeignet ist (man trifft ständig auf The table 'tbl' is full wenn Sie das MySQL-Speicherlimit überschreiten). Die vier Arten von Select, die ich betrachte, sind:

1. Vanille SELECTs
2. zählt
3. bedingte SELECTs
4. indizierte und nicht-indizierte Subselects

Zunächst habe ich drei Tabellen mit folgendem SQL erstellt

CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

mit "MyISAM" anstelle von "InnoDB" und "memory" in der zweiten und dritten Tabelle.

1) Vanille wählt aus

Abfrage: SELECT * FROM tbl WHERE index_col = xx

Ergebnis: zeichnen

Die Geschwindigkeit ist im Großen und Ganzen die gleiche und hängt erwartungsgemäß linear von der Anzahl der auszuwählenden Spalten ab. InnoDB scheint leicht schneller als MyISAM, aber das ist wirklich marginal.

Code:

import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

2) Zählt

Abfrage: SELECT count(*) FROM tbl

Ergebnis: MyISAM gewinnt

Hier zeigt sich ein großer Unterschied zwischen MyISAM und InnoDB - MyISAM (und der Speicher) verfolgt die Anzahl der Datensätze in der Tabelle, daher ist diese Transaktion schnell und O(1). Die Zeit, die InnoDB zum Zählen benötigt, steigt in dem von mir untersuchten Bereich superlinear mit der Tabellengröße an. Ich vermute, dass viele der in der Praxis beobachteten Geschwindigkeitssteigerungen bei MyISAM-Abfragen auf ähnliche Effekte zurückzuführen sind.

Code:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

3) Bedingte Auswahlen

Abfrage: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Ergebnis: MyISAM gewinnt

Hier schneiden MyISAM und Speicher ungefähr gleich gut ab und schlagen InnoDB bei größeren Tabellen um etwa 50 %. Dies ist die Art von Abfrage, bei der die Vorteile von MyISAM am größten zu sein scheinen.

Code:

myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

4) Unter-Auswahlen

Ergebnis: InnoDB gewinnt

Für diese Abfrage habe ich einen zusätzlichen Satz von Tabellen für die Unterauswahl erstellt. Jede besteht einfach aus zwei BIGINT-Spalten, eine mit einem Primärschlüsselindex und eine ohne Index. Aufgrund der großen Tabellengröße habe ich die Speicher-Engine nicht getestet. Der SQL-Tabellenerstellungsbefehl lautete

CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

wobei auch hier in der zweiten Tabelle "MyISAM" durch "InnoDB" ersetzt wird.

In dieser Abfrage belasse ich die Größe der Auswahltabelle bei 1000000 und variiere stattdessen die Größe der unterselektierten Spalten.

Hier gewinnt die InnoDB leicht. Nachdem wir eine vernünftige Tabellengröße erreicht haben, skalieren beide Engines linear mit der Größe der Unterauswahl. Der Index beschleunigt den MyISAM-Befehl, hat aber interessanterweise kaum Auswirkungen auf die InnoDB-Geschwindigkeit. subSelect.png

Code:

myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )

    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

Ich denke, die Botschaft, die man aus all dem mitnehmen kann, ist, dass man, wenn man wirklich Wenn Sie sich Sorgen um die Geschwindigkeit machen, müssen Sie die Abfragen, die Sie durchführen, einem Benchmarking unterziehen, anstatt Vermutungen darüber anzustellen, welche Maschine besser geeignet ist.

Answer 3

Etwas abseits des Themas, aber zu Dokumentationszwecken und der Vollständigkeit halber möchte ich Folgendes hinzufügen.

Im Allgemeinen wird die Verwendung von InnoDB zu einer wesentlich WENIGER komplexen Anwendung führen, die wahrscheinlich auch fehlerfreier ist. Da Sie die gesamte referentielle Integrität (Foreign Key-Constraints) in das Datenmodell aufnehmen können, benötigen Sie nicht annähernd so viel Anwendungscode wie bei MyISAM.

Jedes Mal, wenn Sie einen Datensatz einfügen, löschen oder ersetzen, MÜSSEN Sie die Beziehungen überprüfen und pflegen. Wenn Sie z. B. einen übergeordneten Datensatz löschen, sollten auch alle untergeordneten Datensätze gelöscht werden. Wenn Sie beispielsweise in einem einfachen Blogsystem einen Blogpost-Datensatz löschen, müssen Sie auch die Kommentar-Datensätze, die Likes usw. löschen. In InnoDB wird dies automatisch von der Datenbank-Engine erledigt (wenn Sie die Einschränkungen im Modell angegeben haben) und erfordert keinen Anwendungscode. Bei MyISAM muss dies in der Anwendung kodiert werden, was bei Web-Servern sehr schwierig ist. Web-Server sind von Natur aus sehr nebenläufig/parallel und da diese Aktionen atomar sein sollten und MyISAM keine echten Transaktionen unterstützt, ist die Verwendung von MyISAM für Web-Server riskant/fehleranfällig.

Auch in den meisten allgemeinen Fällen wird InnoDB viel besser abschneiden, und zwar aus mehreren Gründen, unter anderem weil es in der Lage ist, Sperren auf Datensatzebene im Gegensatz zu Sperren auf Tabellenebene zu verwenden. Nicht nur in einer Situation, in der häufiger geschrieben als gelesen wird, sondern auch in Situationen mit komplexen Joins auf großen Datenbeständen. Allein durch die Verwendung von InnoDB-Tabellen anstelle von MyISAM-Tabellen für sehr große Joins (die mehrere Minuten dauern) konnten wir eine dreifache Leistungssteigerung feststellen.

Ich würde sagen, dass InnoDB (unter Verwendung eines 3NF-Datenmodells mit referenzieller Integrität) im Allgemeinen die Standardwahl bei der Verwendung von MySQL sein sollte. MyISAM sollte nur in sehr speziellen Fällen verwendet werden. Es wird höchstwahrscheinlich weniger leisten und zu einer größeren und fehleranfälligeren Anwendung führen.

Dies vorausgeschickt. Datenmodellierung ist eine Kunst, die man unter Webdesignern/Programmierern nur selten findet. Nichts für ungut, aber das erklärt, warum MyISAM so häufig verwendet wird.

Answer 4

InnoDB bietet:

ACID transactions
row-level locking
foreign key constraints
automatic crash recovery
table compression (read/write)
spatial data types (no spatial indexes)

In InnoDB können alle Daten in einer Zeile mit Ausnahme von TEXT und BLOB maximal 8.000 Byte belegen. Für InnoDB ist keine Volltextindizierung verfügbar. In InnoDB werden die COUNT(*)s (wenn WHERE, GROUP BY oder JOIN nicht verwendet wird) langsamer ausgeführt als in MyISAM, da die Zeilenzahl nicht intern gespeichert wird. InnoDB speichert sowohl Daten als auch Indizes in einer Datei. InnoDB verwendet einen Pufferpool, um sowohl Daten als auch Indizes zwischenzuspeichern.

MyISAM bietet:

fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
full text indexing
smaller disk footprint
very high table compression (read only)
spatial data types and indexes (R-tree)

MyISAM hat Sperren auf Tabellenebene, aber keine Sperren auf Zeilenebene. Keine Transaktionen. Keine automatische Wiederherstellung nach einem Absturz, aber es bietet eine Funktion zur Reparatur von Tabellen. Keine Fremdschlüssel-Beschränkungen. MyISAM-Tabellen haben im Vergleich zu InnoDB-Tabellen im Allgemeinen eine kompaktere Größe auf der Festplatte. MyISAM-Tabellen können bei Bedarf durch Komprimierung mit myisampack weiter stark verkleinert werden, sind dann aber schreibgeschützt. MyISAM speichert Indizes in einer Datei und Daten in einer anderen. MyISAM verwendet Schlüsselpuffer für die Zwischenspeicherung von Indizes und überlässt die Verwaltung der Datenzwischenspeicherung dem Betriebssystem.

Insgesamt würde ich InnoDB für die meisten Zwecke und MyISAM nur für spezielle Anwendungen empfehlen. InnoDB ist jetzt die Standard-Engine in neuen MySQL-Versionen.

Answer 5

Wenn Sie MyISAM verwenden, werden Sie nicht a Transaktionen pro Stunde, es sei denn, Sie betrachten jede DML-Anweisung als eine Transaktion (die im Falle eines Absturzes ohnehin nicht dauerhaft oder atomar ist).

Daher denke ich, dass Sie InnoDB verwenden müssen.

300 Transaktionen pro Sekunde sind eine ganze Menge. Wenn Sie unbedingt wollen, dass diese Transaktionen auch bei einem Stromausfall Bestand haben, sollten Sie sicherstellen, dass Ihr E/A-Subsystem so viele Schreibvorgänge pro Sekunde problemlos bewältigen kann. Sie benötigen mindestens einen RAID-Controller mit batteriegepuffertem Cache.

Wenn Sie eine kleine Haltbarkeit Hit nehmen können, könnten Sie InnoDB mit innodb_flush_log_at_trx_commit auf 0 oder 2 gesetzt (siehe Docs für Details), können Sie Leistung verbessern.

Es gibt eine Reihe von Patches von Google und anderen, die die Gleichzeitigkeit erhöhen können - diese können von Interesse sein, wenn Sie ohne diese Patches keine ausreichende Leistung erhalten.