5.5 ist GA!

Auch ich will mir die Freiheit nehmen über die MySQL 5.5 zu berichten. Die neuen Features, welche meine Neugierde weckten, werden in einem anderen Blog zusammengefasst.
Bereits in den Blogs hier und hier verglich ich PBXT vs InnoDB(Plugin 1.0.13) und MySQL  5.5.8 selbst kommt mit dem Plugin 1.1.8.
Was liegt näher, als die Gelegenheit zu nutzen und  ein paar Tests auf MySQL 5.5zu fahren?

Für den Vergleich wurde wieder die unten stehende Tabelle mit 300.000.000 Rows genommen.

 CREATE TABLE `rein` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id2` int(11) NOT NULL,
  `id3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id2` (`id2`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Wie im letzten Post zu PBXT vs InnoDB. Werden SELECTs und UPDATES ausgeführt. Einmal über eine kleine Teilmenge (hot), sprich der ersten Millionen Einträge der Tabelle und ein einmal über die ganze Tabelle (uniform). Eine SELECT-Testreihe besteht aus 100.000.000 Statements und eine UPDATE-Testreihe aus 100.000 Statements. Im Gegensatz zu den Tests der letzten Blogs, laufen alle Zugriffe über den PK.

Start the fire

Ich habe es mir einfach gemacht und über die aktuelle 5.1.53 die 5.5.8 installiert. Die Einträge, welche zu 5.1.x Zeiten noch, dafür sorgten, dass das interne InnoDB ignoriert wird und das InnoDB-Plugin geladen sind zu entfernen. Schmerzlich musste ich auch feststellen, dass SHOW INNODB STATUS nicht mehr funktioniert. Jetzt wurde durchgegriffen es hat SHOW ENGINE INNODB STATUS zu lernen:)

Es gab immer zwei Durchläufe und der letzte (mit heißem Cache) wurde gewertet.

SELECT id3 from rein where ID=@id

hot:


uniform:

Spontan keinen Grund auf 5.5 zu wechseln.

Wie sieht es für UPDATEs aus:

UPDATE rein SET id3=round(rand()*10000) where ID=@id

hot:

uniform:


Autsch das tut weh. So gesehen gibt es gar keinen Grund auf 5.5 wechseln zu wollen.
Nun gibt es noch die Variable innodb_buffer_pool_instances. Da ich mir noch die neuen Möglichkeiten aneignen muss, wurden dieTests auch mal mit innod_buffer_pool_instancs 4,8,16 durchgeführt. Der Default von innodb_buffer_pool_instances ist bei 1.
(Das Kürzel b4 gibt an wieviel BufferPoolInstanzen konfiguriert sind. b4->4, b8->, b16->16)

SELECT

---

hot:

uniform:

Keine großen Sprünge. 5.5 hat ab hoher concurrency die Nase vorne. Mehrere Bufferpoolinstancen sind hier wohl förderlich. Trotzalledem ist der gößtmögliche Durchsatz bei allen mit einer concurrency von 64. Nach einer Ablösung von MySQL 5.1 sieht es beim maximalen Durchsatz nicht aus.

UPDATE

---

hot:

uniform:

Das ist mal eine Hausnummer.
Sprich bei diesem Workload und Konfiguration ist innodb_buffer_pool_instances ein MUST. Sonst wird der Aufstieg zu 5.5 ein Abstieg *Schenkelklopfer*.
Zumindest kann ich mir gut Vorstellen, dass die Suche nach dem richtigen Wert für innodb_buffer_pool_instances ähnlich turbulent wird, wie schon die Suche nach dem richtigen Wert für innodb_thread_concurrency:) 

---

#v+ innodb-setting
innodb_buffer_pool_size = 20G
innodb_flush_log_at_trx_commit = 0
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_file_per_table
innodb_file_format=Barracuda
# innodb_buffer_pool_instances from 1(default),4,8,16
# und nur bei 5.5
innodb_buffer_pool_instances=4
#v-

2xCPUx4Cores Intel(R) Xeon(R) X5550@2.67GHz
96GBRam

Viel Spaß
Erkan

Dieser Post führt den vorherigen Artikel über PBXT weiter.
Diesmal werden mit unterschiedlicher Concurrency SELECTs auf die - 300.000.000 Rows enthaltene - Tabelle gelassen. Zur Erinnerung:

 CREATE TABLE `rein` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id2` int(11) NOT NULL,
  `id3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id2` (`id2`)
) ENGINE=PBXT DEFAULT CHARSET=latin1

Zur Ausgangstabelle:

Eine Info, die beim vorherigen Post hätte erwähnt werden müssen ist die Größe der Tabelle.

PBXT:
1.2G    rein-5.xtr
7.6G    rein.xtd
9.8G    rein.xti

InnoDB:
22G     rein.ibd

Während der PK der Tabelle wohlweislich aus 300.000.000 unterschiedlichen Werten besteht. ist dies beim scondary Index `id2` nicht der Fall. Dieser hat Werte aus dem Bereich [1,1.000.000] angenommen. So ist zu erwarten, dass jeder Wert 300 mal angenommen wird. Dies ist für alle SELECTs, welche via `id2` gehen, wichtig zu wissen!;)

Da die Datenmenge zu groß ist, habe ich mich entschieden die Daten via Boxplots darzustellen. Diese Tests dienen nur sich dem Thema PBXT(InnoDB) zu nähern. Tests haben im Allgemeinen zu viele Freiheitsgrade um mehr als das zu liefern. Aber dies sollte jedem Bekannt sein.

SELECT

Die SELECT-Tests bestanden aus 100x1.000 Abfragen. Unterschieden wurde zwischen hot und uniform. Während bei hot ein Definitionsbereich von [1,10.000] existierte (für die WHERE-Bedingung), ging es bei uniform über die ganze Tabelle [1,300.000.000]

---

SELECT id3 from rein where ID=@id

Ich will mir nicht die Freiheit nehmen, die Daten zu interpretieren. Bei diesem Test war ich überrascht, dass InnoDB nicht gnadenlos gewann, da `id3` ja immer im selben Block wie der PK ist.

---

SELECT id3 from rein where ID=@id

PBXT schlägt sich erschreckend gut. Schön ist an der Graphik zu sehen wie der Durchsatz für beide Engines einer Glockenkurve gleich verläuft. Sollte dies mehr mit dem Server als mit den Engines zu tun haben, dann freue ich mich schon auf Tests mit der MySQL 5.5 :)

---

SELECT count(id3) from rein where id2=@id

Das InnoDB via secondary Index schlechter als PK abschneidet ist plausibel. Der Unterschied doch frappant. 

---

SELECT count(id3) from rein where id2=@id

Wie zu erwarten geht der Durchsatz - alsbald auf alle 300.000.000 Rows zugegriffen wird etwas runter. Eine Erklärung für das Verhalten von könnte sein, dass PBXT gerade mit einigen Hintergrundjobs beschäftigt war. Andere Testreihen (auf einem anderen Rechner und anderer Version) hatten bei 128 keinen Drop im Durchsatz.

---

Ich für meinen Teil war/bin überrascht, wie gut sich PBXT schlug. Gerade bei SELECTs via PK hätte ich erwartet, dass InnoDB hier eindeutig die Nase vorne hat.


Viel Spaß
Erkan

PBXT ist eine weitere vielversprechende StorageEngine für MySQL. Eine richtige Einführung soll erst in fernerer Zukunft folgen, doch soll uns das nicht davon abhalten diese StorageEngine schon mal zu testen.
Was bietet sich mehr an, als PBXT mit dem gegenwärtigen Platzhirschen InnoDB zu vergleichen?!
Zum Testen wurde die MySQL 5.1.53 verwendet. PBXT fand in der Version 1.5.02 Beta Verwendung. Ich verwendete die Beta, da ich in der 1.0.11-7 einen Bug vermutete.

Doch vorweg einiges, was ich spontan gerne noch an Features bei PBXT sehen würde:

An das InnoDB-Plugin gewohnt, kann man schnell einiges vermissen,  was es bei PBXT nicht gibt. So wäre etwas der Art PBXT_TRX, PBXT_LOCKS etc. (in Analogie zu InnoDB) hielfreich. Auch wäre es schön zu wissen was die Hintergrundthreads von PBXT gerade machen. Ein Backuptool zum binären Sichern (aka xtrabackup/Innodb Hot Backup), wie auch die Möglichkeit Daten zu kompremieren (aka InnoDB-Barracuda) wären schön zu haben. Ein Äquivalent zu innodb_force_recovery habe ich mir auch zwei mal gewünscht.

Tests

Von PBXT habe ich quasi keine Ahnung. Hier gilt es noch viele Tests zu fahren. Bei diesen Tests sollte nicht jedem Commit ein fsync() folgen, da ich eher die StorageEngines als die Festplatten testen wollte.
Folgende (PBXT/InnoDB)Config wurde verwendet.

• InnoDB-Plugin:
  innodb_buffer_pool_size = 20G
  innodb_flush_log_at_trx_commit = 2
  innodb_log_file_size = 512M
  innodb_log_files_in_group = 3
  innodb_file_per_table
  
• PBXT:
  pbxt_trx_log_buffer_size=8M
  pbxt_index_cache_size=8G
  pbxt_data_log_threshold=256M
  pbxt_record_cache_size=12G
  pbxt_trx_log_cache_size=1G
  pbxt_flush_log_at_trx_commit=0
  pbxt_trx_log_threshold=128M
  pbxt_data_file_grow_size=50M
  pbxt_row_file_grow_size=10M
  pbxt_log_buffer_size=512M
  pbxt_data_log_cache_size=1G
  pbxt_checkpoint_frequency=64M
  pbxt_index_dirty_threshold=60
  pbxt_sweeper_priority=2
  

Autoincrement

InnoDB liebt Tabellen mit einen auto_increment PK. So hatte sich beim ersten Test PBXT in einem Bereich zu schlagen, in dem InnoDB sehr gut ist.
Mit mysqlslap wurden über 8 parallele Verbindungen (mit 8 Verbindungen hatte ich bis dato immer den besten Durchsatz auf der Maschine) 300.000.000 Zeilen in eine einfache Tabelle zu schreiben (autocommit=ON).

 CREATE TABLE `rein` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id2` int(11) NOT NULL,
  `id3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id2` (`id2`)
) ENGINE=PBXT DEFAULT CHARSET=latin1

Dies wurde - um auch Daten für die Grafik zu haben - in 300 x 1.000.000 Inserts gesplittet. So beschreibt folgende Grafik wieviel Sekunden pro 1.000.000 Inserts benötigt wurden. Sprich: Kleinere Werte sind besser.



Die ersten paar Millionen Rows laufen für PBXT zwar wunderbar. Aber dann sackt PBXT förmlich ein. Während InnoDB nahezu konstant durchmarschirt.
Trägt man auf der x-Achse die Inserts/Sekunde bekommt man sehr schnell auch ein Gefühl für die Performance.


















Von "kleinen" Tabellen abgesehen, gewinnt hier PBXT kein Land. Unangenehm sind die starken Schwankungen bei PBXT. Ob das an den Hintergrundthreads lag und wie diese die Last verarbeiten, kann ich nicht (hoffentlich noch nicht) sagen. Auffällig war allerdings, dass PBXT das RAID10 stärker zum Anschlag brachte (war fast durchgängig bei einer %util von 100%) als das InnoDB-Plugin. Mit knapp über 16000 Inserts pro Sekunde ist PBXT auch nicht wirklich langsam, aber über 60000 Inserts pro Sekunde bei InnoDB ist schon entscheidend mehr.

UUID

Doch wie sieht es mit einem Workload aus, bei der InnoDB eben nicht so schön skaliert? Nun wird als PK UUID() verwendet und zudem noch ein Text-Column.

CREATE TABLE `reinUUID` (
  `uid` char(32) NOT NULL,
  `id` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `mail` text,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[UPDATE: Oli Sennhauser wies mich darauf hin, dass  innodb_flush_log_at_trx_commit = 2 nicht die beste Konfiguration für InnoDB ist. Daher wurde der Test für beide auf UUID-PK gehende Tests wiederholt. Wobei sowohl für innodb_flush_log_at_trx_commit, alsauch für pbxt_flush_log_at_trx_commit.
i := Innodb, p:=pbxt die folgende Zahl gibt jeweils den Wert der Variable an.]



Hier ist ziemlich eindeutig zu sehen, wie InnoDB sich quasi verabschiedet. Nach 16000000 Rows brach ich den Versuch dann schlicht und ergeifend ab.
PBXT hatte - im Vergleich zu InnoDB - keinerlei Probleme mit diesem Workload.
Um noch ein Gefühl für die Performance zu geben, auch hier die selben Daten anders dargestellt.

















 
An dieser Grafik erkennt man gut, das auch PBXT langsamer wird. Im Vergleich zu InnoDB ist bei diesem Workload und dieser Config klar PBXT überlegen und eine interessante Wahl.


Beim nächsten mal werden UPDATEs und SELECTs auf PK und secondary Indizes verglichen. Stay tuned:)