Benchmark: MyISAM vs. InnoDB

Da ich bei Club3k für den Datenbankserver zuständig bin hat es mich schon seit längerem interessiert was denn nun die beste MySQL Engine im Zusammenspiel mit Helma ist. Nachdem es aber bisher nur Vermutungen und keine Benchmarks gegeben hat habe ich ein paar Wochen geopfert um diese Wissenslücke zu stopfen.

Für alle die es kurz mögen: InnoDB ist bis zu 10% schneller

Bisher läuft bei club3k ein FreeBSD 6.2 mit einer MyISAM Konfiguration und libthr. Meine Benchmarks haben ergeben, dass FreeBSD 7 mit InnoDB ca 25% mehr Performance bringen dürfte. Jetzt heißts nur mehr warten bis FreeBSD 7 production ready ist.

Datenbank

Die Daten stammen von einem Snapshot der club3k.at Datenbank vom 5. September 2007. Natürlich sind alle Userdaten anonymisiert. Die Datenbank enthält 26 Tabellen mit insgesamt 4.85 Millionen Zeilen die rund 800MB belegen. Alle Keys zusammen haben eine Größe von ca 230MB.

Diese Datenbank wurde anschließend auf meiner Testhardware zwei mal importiert einmal mit MyISAM Tabellen und einmal mit InnoDB.

Queries

Die Queries wurden ebenfalls am 5. September 2007 um 19 Uhr mit dem MySQL General Querylog aufgezeichnet. Es sind also echte Queries die von der Applikation und Helma im Live Betrieb unter Last erzeugt wurden.

Aus diesem Querylog wurden anschließend alle SELECTS gefiltert und auf 2 Benchmarks wie folgt aufgeteilt.

bench1 Die ersten 50.000 SELECT Queries direkt nach dem Start der Applikation mit einem leeren Helma Object Cache.

bench2 50.000 SELECT Queries nach ca 20min Laufzeit der Applikation. Man kann davon ausgehen, dass der Object Cache zu diesem Zeitpunkt vollständig gefüllt ist.

Hardware

AMD Athlon 64 X2 3800+ EE
2GB Kingston ValueRAM PC2-6400U
Asus M2A-VM 690G

2x 320GB Raid 1 (gmirror)

Software

FreeBSD 6.2-STABLE AMD64

FreeBSD flachzange.bluelife.at 6.2-STABLE FreeBSD 6.2-STABLE #0: Wed Jul 18 20:35:48 CEST 2007
root@flachzange.bluelife.at:/usr/obj/usr/src/sys/SMP  amd64

FreeBSD 7-current AMD64

FreeBSD flachzange.bluelife.at 7.0-CURRENT FreeBSD 7.0-CURRENT #0: Tue Sep 25 19:30:04 CEST 2007
root@flachzange.bluelife.at:/usr/obj/usr/src/sys/GENERIC  amd64

Kernel Modifikationen

ohne WITNESS
ohne INVARIANTS
ohne KDB, DDB, GDB

MySQL 5.0.45
mysqlslap Ver 1.0 Distrib 5.1.21-beta, for portbld-freebsd6.2 (amd64)

http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

mytop 1.6 zur Plausibilitätskontrolle

http://jeremy.zawodny.com/mysql/mytop/

Optimierungen

/etc/make.conf # MySQL optimized BUILD_OPTIMIZED=yes BUILD_STATIC=yes PTHREAD_LIBS=-lthr

Unter FreeBSD 6.2 wird derzeit noch die langsamere libpthread eingesetzt. Die sollte man unbedingt durch libthr ersetzen denn die Performance ist sonst einfach grauenhaft. Bei ein paar kleineren Tests erreichte libpthread nur ca 30-50% der Performance von libthr. Für FreeBSD 7-current ist das nicht notwendig denn dort wird libthr bereits standardmäßig verwendet.

Nachdem ich schon beim Testen war habe ich auch ein paar Optimierungen getestet und BUILD_STATIC bringt noch einmal ein paar Prozent mehr Performance aber macht nicht den großen Unterschied wie viele behaupten.

my.cnf (Basis ist my-huge.cnf)

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384MB
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /storage/database/innodb/
innodb_data_file_path = ibdata1:2G
innodb_log_group_home_dir = /var/db/mysql/
innodb_log_arch_dir = /var/db/mysql/

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

Testaufbau

Es wurde darauf geachtet, dass die Datenbanken vollständig im RAM liegen und keine nennenswerten Plattenzugriffe stattfinden. Unter FreeBSD eignet sich gstat sehr gut um das zu kontrollieren. Bei allen Tests wurde eine Key Effizienz von annähernd 100% erreicht.

Jeder Durchlauf wurde mit zwei, vier, acht und sechzehn simulierten Clients jeweils 3 mal wiederholt. Die Durchläufe mit 2 Clients wurden nicht weiter beachtet und waren nur dazu da um die Caches aufzuwärmen. Vor jedem Durchlauf wurde die Datenbank außerdem neu gestartet um möglichst gleiche Bedingungen zu schaffen.

mysqlslap -u slappy --host=flachzange -q benchX.sql --concurrency="2,4,8,16" --iterations=3

Testläufe

Alle Werte sind gerundet auf Queries pro Sekunde und gemittelt aus 3 vollständigen Durchläufen bei einer maximalen Streuung von weniger als 1%.

BENCH1

Die ersten 50.000 SELECT Queries direkt nach dem Start der Applikation mit einem leeren Helma Object Cache.

MyISAM	InnoDB	Konfiguration
1273	1306	4 Clients, FreeBSD 6.2, libthr
1407	1444	4 Clients, FreeBSD 7-current, SCHED_4BSD
1411	1434	4 Clients, FreeBSD 7-current, SCHED_ULE

MyISAM	InnoDB	Konfiguration
1274	1290	8 Clients, FreeBSD 6.2, libthr
1429	1448	8 Clients, FreeBSD 7-current, SCHED_4BSD
1435	1456	8 Clients, FreeBSD 7-current, SCHED_ULE

MyISAM	InnoDB	Konfiguration
1223	1262	16 Clients, FreeBSD 6.2, libthr
1428	1434	16 Clients, FreeBSD 7-current, SCHED_4BSD
1412	1454	16 Clients, FreeBSD 7-current, SCHED_ULE

BENCH2

50.000 SELECT Queries nach ca 20min Laufzeit der Applikation. Man kann davon ausgehen, dass der Object Cache zu diesem Zeitpunkt vollständig gefüllt ist.

MyISAM	InnoDB	Konfiguration
1440	1599	4 Clients, FreeBSD 6.2, libthr
1568	1781	4 Clients, FreeBSD 7-current, SCHED_4BSD
1579	1754	4 Clients, FreeBSD 7-current, SCHED_ULE

MyISAM	InnoDB	Konfiguration
1438	1581	8 Clients, FreeBSD 6.2, libthr
1589	1792	8 Clients, FreeBSD 7-current, SCHED_4BSD
1598	1772	8 Clients, FreeBSD 7-current, SCHED_ULE

MyISAM	InnoDB	Konfiguration
1372	1496	16 Clients, FreeBSD 6.2, libthr
1592	1775	16 Clients, FreeBSD 7-current, SCHED_4BSD
1553	1763	16 Clients, FreeBSD 7-current, SCHED_ULE

Diagramme

img/articles/bench1_62.png img/articles/bench1_70_4bsd.png img/articles/bench1_70_ule.png img/articles/bench2_62.png img/articles/bench2_70_4bsd.png img/articles/bench2_70_ule.png img/articles/bench2_reference.png

Fazit

Dieser Benchmark ist kein allgemein gültiger MyISAM vs. InnoDB Vergleich sondern speziell auf die Queries und Arbeitsweise von Helma zugeschnitten. Helma greift dabei immer über den Primary Key auf die Tabellen zu und will entweder einzelne Zeilen oder den ganzen Key auslesen.

Aus Zeitgründen habe ich meine Tests auf SELECTS beschränkt und den 4 prozentigen Anteil an DELETE und UPDATE Statements nicht berücksichtigt.

Wie erwartet ist FreeBSD 7.0 um ca 15% schneller als FreeBSD 6.2 und laut diversen Benchmarks von Kris Kennaway in etwa gleichauf mit Linux 2.6. Wenn man dennoch einen FreeBSD 6.2 MySQL Datenbankserver laufen hat dann sollte man auf jeden Fall libthr verwenden und libpthread meiden. Auf meinem 2 Kern System ist zwischen den Schedulern noch kein Unterschied erkennbar das sollte sich aber ab 4 Kernen zugunsten von SCHED_ULE ändern.

Nun zur wichtigsten Erkenntnis. InnoDB ist um bis zu 10% schneller als MyISAM. Das ist auf mehrere Faktoren zurückzuführen die michi in seinen Seminarnotizen sehr gut zusammengefasst hat. Bei den zwei Benchmarks BENCH1 und BENCH2 die jeweils 50k Queries umfassen ist die Zusammenstellung der Queries scheinbar ein wenig unterschiedlich denn bei BENCH1 war kein nennenswerter Unterschied erkennbar aber bei BENCH2 ist InnoDB um 10% schneller und BENCH2 läuft auf der identischen Konfiguration ca 15% schneller als BENCH1 durch.

Comments

no comments

About me

Categories

Networking

Friends