SQL InnoDB vs. MyISAM

webfreak

New member
Hallo Freunde,

ich wollte meine Datenbank auf "InnoDB", vor allem wegen der Transaktionen umstellen. Nun habe ich in einem Versuchsaufbau festgestellt, dass ich mit dem neuen Tabellenformat erheblich längere Abfragen als bei MyISAM habe.
Für 10.000 Inserts braucht InnoDB ewige 10sek. - MyISAM gibt sich hingegen mit 0,6sek. zufrieden.

Mein Experiment habe ich wie folge aufgebaut:

Tabelle
Code:
id    | INT | PRIMARY KEY
value | INT
Insert
PHP:
for ($i = 1; $i < 1000; ++$i) {
    mysql_query("INSERT INTO test (value) VALUES ($i);");
}
Könnt ihr das bestätigen oder habt ihr ähnliche Erfahrungen gemacht?
 

GrafZahl

Member of Honour
zur performance ausnutzung von InnoDB bitte autocommit beachten...


Code:
<script language="php">

$con = mysql_connect('localhost','***','***') or die( mysql_error());

mysql_select_db('test')or die( mysql_error());

$time1=microtime(true);
mysql_query('SET autocommit=0;');
for ($i = 0; $i < 10000; ++$i) {
    mysql_query("INSERT INTO innodbtest (value) VALUES ($i),($i),($i),($i),($i),($i),($i),($i),($i),($i);");
}  
mysql_query('COMMIT');
$time2=microtime(true);
for ($i = 0; $i < 10000; ++$i) {
    mysql_query("INSERT INTO myisamtest (value) VALUES ($i),($i),($i),($i),($i),($i),($i),($i),($i),($i);");
}  
$time3=microtime(true);

$diff1=$time2-$time1;
$diff2=$time3-$time2;

echo "InnoDB:$diff1   MyISAM:$diff2";

</script>
InnoDB:1.39869499207 MyISAM:1.53029108047

ohne autocommit ist das eine transaktion die 100 000 zeilen einfügt ... mit autocommit sind es 10 000 Transaktionen die je 10 zeilen einfügen ... ein kleiner aber feiner unterschied ...
 

enkore

New member
GrafZahl hats ganz richtig gesagt, standardmäßig ist der Autocommitmodus an, der aus jedem SQL-Statement ein extra Commit macht. Ein Commit involviert aber einiges an Overhead, weil die Tabelle gesperrt wird, die Daten ins Journal geschrieben werden, das Journal in die DB geschrieben wird, das Journal wieder geleert wird und dann die Tabelle wieder entsperrt wird. Das dauert einfach... daher für Bulkops Autocommit aus und von Hand machen.
 

webfreak

New member
Dankeschön euch beiden! :)

Ich habe da auch nochmal eine grundsätzliche Verständnisfrage zu den Transaktionen. In einem neuen Versuchsaufbau, lasse ich in einer Schleife 150.000 Datensätze in die DB schreiben.

PHP:
mysql_query('SET AUTOCOMMIT=0;');

for ($i = 0; $i < 150000; ++$i) {
// 150.000 Einträge
mysql_query("INSERT INTO test (value) VALUES ($i);");
}  

mysql_query('COMMIT;');
Das dauert bei mir etwa 10 Sekunden in der das Script im Browser läd - was jetzt keine Rolle spielen soll, da es das ganze nur experimentell veranschaulichen soll. Wenn ich jetzt nach etwa 2 Sekunden den Ladevorgang abbreche und in der DB nachschaue, sind die Einträge noch nicht vorhanden. Erst nach der ursprünglich errechneten Zeit von 10 Sekunden sind alle 150.000 Datensätze in der DB einsehbar.

Bedeutet dieses "transaktionssicher" also, dass die Daten in jedem Fall und vollständig in die Datenbank geschrieben werden, auch wenn das Script meinetwegen nur 0,1 Sekunden eingelesen wurde?

In einem 2. Versuch habe ich vor die Queries noch eine Schleife eingebettet welche 40Mio. Mal durchlaufen wird um zu schauen, wie sich das Script verhält wenn die SQL-Befehle noch nicht eingelesen worden sind. Ergebnis: die Datensätze werden nicht in die DB geschrieben.
PHP:
for ($i = 0; $i < 40000000; ++$i) {
echo "Test"';
}

mysql_query('SET AUTOCOMMIT=0;');

for ($i = 0; $i < 150000; ++$i) {
// 150.000 Einträge
mysql_query("INSERT INTO test (value) VALUES ($i);");
}  

mysql_query('COMMIT;');
Anders verhält es sich, wenn die Schleife an das Ende vom Script gesetzt wird - die Datensätze werden wieder vollständig in die Datenbank übernommen:
PHP:
mysql_query('SET AUTOCOMMIT=0;');

for ($i = 0; $i < 150000; ++$i) {
// 150.000 Einträge
mysql_query("INSERT INTO test (value) VALUES ($i);");
}  

mysql_query('COMMIT;');

for ($i = 0; $i < 40000000; ++$i) {
echo "Test"';
}
Dies würde wiederum bedeuten, dass die Transaktion nur sicher ausgeführt werden kann, wenn sie mittels PHP eingelesen wurde (?)
 
Zuletzt bearbeitet:

GrafZahl

Member of Honour
die transaktionssicherheit bedeutet:

alle änderungen von beginn der transaktion bis zum commit werden quasi atomar ...

wenn dein script abläuft, und 100 millionen inserts macht, und vor erreichen des commit abbricht, ist keines dieser inserts geschehen ... (auto-rollback)

wenn dein script die 100 millionen inserts macht, das commit erreicht, und deine DB mit dem transaktions journal auf einem schnellen speicher liegt, die daten datei aber auf einem extrem langsammen speicher, würde dein php erst die rückmeldung bekommen (also die zeile mit dem commit abschließen) wenn alles geschrieben wurde ... bricht das script ab bevor alles geschrieben wurde (aber das commit bereits erreicht war) bleibt die transaktion bestehen und wird noch komplett von der DB umgesetzt ...

wenn du allerdings bei 150 000 inserts dieser art 10 sekunden auf die bestätigung nach dem commit warten musst, spricht das dafür das da etwas nicht stimmt ...

poste mal deine mysql server variablen mit bezug auf innodb ...
(show variables where variable_name like 'innodb%')
 

webfreak

New member
Code:
mysql> show variables where variable_name like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 1073741824             |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)
 

GrafZahl

Member of Honour
das verhältnis zwischen "innodb_buffer_pool_size" und "innodb_log_file_size" ist nicht unbedingt ideal (logfile um den faktor 50 zu klein) ... "innodb_log_buffer_size" könnte auch etwas größer sein (ich würde es mal mit so 4-8 mb versuchen)

wieviel ram hat das system? ist das ein reiner db server? was läuft da sonst noch so? wie schnell sind die platten?
 

webfreak

New member
CPU: 3.000 MHz
RAM: 1.024 MB

Da es nur ein vServer ist, kann ich dir leider nicht sagen wie schnell die Platten sind ;)
 

GrafZahl

Member of Honour
in dem fall dreh mal innodb_buffer_pool_size runter auf so 256 MB stell die log file größe auf 64MB und den log buffer auf 4 MB ...

bei den logfiles musst du darauf achten, dass die nach der änderung und vor dem start des servers auch dateimäßig entfernt werden ... sonst werden die nicht in neuer größe angelegt ... nach dem server start prüfen ob die innodb logfiles die richtige größe haben
 

webfreak

New member
So richtig was scheint es nicht zu ändern.

Ich habe es mal online auf einem ganz normalen Webspace getestet, dort dauern die 150.000 Inserts ca. 7 Sekunden. Auf meinem vServer fast 12-13 Sekunden. An PHP liegt es nicht, da ist der vServer im Direktvergleich schneller. Weiss auch nicht was ich da noch konfigurieren soll :(
 
Oben