MySQL: Artikelserie von Isotopp

MySQL, PostgreSQL, SQLite
User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL: Artikelserie von Isotopp

Post by isotopp » 2006-02-11 13:02

Ein MySQL Datenbankserver hat ein Datadir. Das Kommando

Code: Select all

root@localhost [(none)]> show variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
sagt uns, wo das liegt. In datadir wird für jede mit "CREATE DATABASE" angelegte Datenbank ein Verzeichnis angelegt, und für jede mit CREATE TABLE in dieser Datenbank vorhandene Tabelle eine *.frm-Datei. Dies gilt für alle Tabellentypen, die MySQL verwendet, sogar für MEMORY-Tables. Die frm-Datei beschreibt die Definition der Tabelle, also welche Spalten und Indices vorhanden sein sollen.

Code: Select all

root@localhost [(none)]> create database rootforum;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use rootforum
Database changed
root@localhost [rootforum]> create table t ( id serial ) engine=memory;
Query OK, 0 rows affected (0.03 sec)

root@localhost [rootforum]>
[1]+  Stopped                 mysql -u root -p
h743107:/var/lib/mysql # l rootforum/
insgesamt 24
drwx------  2 mysql mysql 4096 2006-02-11 11:27 ./
drwxr-xr-x  5 mysql mysql 4096 2006-02-11 11:26 ../
-rw-rw----  1 mysql mysql   65 2006-02-11 11:26 db.opt
-rw-rw----  1 mysql mysql 8556 2006-02-11 11:27 t.frm
Die db.opt sind übrigens die beim Anlegen der Datenbank verwendeten Datenbankoptionen, also der DEFAULT CHARACTER SET und die DEFAULT COLLATION.

Für eine MyISAM-Tabelle werden neben der frm-Datei auch noch eine MYD- und eine MYI-Datei angelegt. Die MYD-Datei enthält die Daten, die MYI-Datei die Indices. Mit Hilfe der frm-Datei und den Daten aus der MYD-Datei kann die MYI-Datei jederzeit rekonstruiert werden, aber dies kann bei vielen Daten sehr lange dauern. Mit Hilfe der Tabellendefinition aus "mysqldump --no-data" kann die frm-Datei jederzeit rekonstruiert werden, aber das kann umständlich werden. Wenn die MYD-Datei beschädigt ist, gehen Daten verloren.

Code: Select all

h743107:/var/lib/mysql # fg
mysql -u root -p

root@localhost [rootforum]> alter table t engine=myisam;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [rootforum]>
[1]+  Stopped                 mysql -u root -p
h743107:/var/lib/mysql # l rootforum/
insgesamt 28
drwx------  2 mysql mysql 4096 2006-02-11 11:28 ./
drwxr-xr-x  5 mysql mysql 4096 2006-02-11 11:26 ../
-rw-rw----  1 mysql mysql   65 2006-02-11 11:26 db.opt
-rw-rw----  1 mysql mysql 8556 2006-02-11 11:28 t.frm
-rw-rw----  1 mysql mysql    0 2006-02-11 11:28 t.MYD
-rw-rw----  1 mysql mysql 1024 2006-02-11 11:28 t.MYI
Eine MYD-Datei besteht aus Records - je nach Definition der Tabelle haben sie eine feste Länge oder eine variable Länge: Solange VARCHAR, TEXT und BLOB nicht in einer Tabellendefinition vorkommen, haben die Records feste Längen, ansonsten eine variable Länge. Mit SHOW TABLE STATUS LIKE "name" kann man das prüfen:

Code: Select all

root@localhost [rootforum]> show table status like "t"G
*************************** 1. row ***************************
           Name: t
         Engine: MyISAM
        Version: 9
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 38654705663
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-02-11 11:28:56
    Update_time: 2006-02-11 11:28:56
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
(Indem man ein Kommano im MySQL-Kommandozeilenclient nicht mit ";" oder "g", sondern mit "G" abschließt, bekommt man eine vertikale Ausgabe der Daten und dies ist in manchen Fällen übersichtlicher)

Wir sehen hier, daß das Row_Format der Tabelle t fixed ist. Das bedeutet, daß die Datensätze in der MYD-Datei eine feste Länge haben. MySQL kann auf die Datensätze hier mit Datensatznummern zugreifen, statt mit Byte-Offsets zu arbeiten. Eine Datensatznummer oder ein Byte-Offset können bis zu 32 Bit unsigned groß sein - daraus ergibt sich eine Max_data_length von 4 GB für Row_Format: dynamic und von 4 GB * Datensatzgröße bei Row_format: Fixed. Ist das nicht ausreichend, muß man durch Angabe der Tabellenoptionen avg_row_length und max_rows dafür sorgen, daß MySQL z.B. intern 8 Byte Zahlen zur Adressierung verwendet - dies ist aber nur empfohlen, wenn notwendig, weil es den Server generell ein wenig langsamer macht.

An den Datenbankdateien von MySQL sollte man generell nicht zu Fuß herumfummeln, solange der Datenbankserver läuft (Manche Leute trauen sich das nach einem FLUSH TABLES WITH READ LOCK zu, aber generell ist es besser, ein /etc/init.d/mysql stop zu machen, bevor man was in datadir anfasst).

Für eine Datensicherung ist es bei einer Default-Konfiguration ausreichend, datadir und die my.cnf zu sichern.
Last edited by isotopp on 2008-04-11 19:58, edited 1 time in total.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 2: Daten und SHOW TABLE STATUS

Post by isotopp » 2008-04-11 19:56

Für die folgenden Beispiele wird eine breitere Tabelle gebraucht, und es müssen auch einige Testdaten vorhanden sein. Daher hier einmal eine Tabellendefinition und ein Generatorscript für Testdaten:

Code: Select all

root@localhost [rootforum]> drop table t;
Query OK, 0 rows affected (0.00 sec)

root@localhost [rootforum]> create table t ( id bigint unsigned not null, d char(12) not null, e char(25) not null, i integer unsigned not null );
Query OK, 0 rows affected (0.22 sec)
root@localhost [rootforum]>
[1]+  Stopped                 mysql -u root -p
h743107:~ # cat gendata.pl
#! /usr/bin/perl -w

my $limit = 20000000; # 20 Mio

for (my $i=0; $i<$limit; $i++) {
  $str1 = "";
  for (my $j=0; $j<12; $j++) {
    $str1 .= chr(97+26*rand());
  }

  $str2 = "";
  for (my $j=0; $j<25; $j++) {
    $str2 .= chr(97+26*rand());
  }
  $x = int(rand()*2**31);
  printf qq("$i","$str1","$str2","$x"n);
}
h743107:~ # ./gendata.pl > /tmp/data.csv
h743107:~ # head -10 /tmp/data.csv
"0","uvhgnbjklavf","fnwnwuwphwjcnuufzzbdjswpt","466412497"
"1","sfnyjtjcljyr","puccvmiqgujqetgkwmwzdjefs","940962493"
"2","fbdjnejvhkar","wwpfrdpasbztljviehzojgwyf","1417468689"
"3","lqwgrvcmygqz","aazxhpetwqlkdwuaxwvhmhtsl","1393448064"
"4","tvfucewatbhp","zayvtprykglnynesavekdnpwr","1565437858"
"5","kgiyuxheibsa","bipgdpnvetowphowepuerediy","944905110"
"6","qpgyhtohyfut","uxajpmyegbgaoexdyxnfsetpg","323176798"
"7","fdpzsnkfvird","mpkqpcfgelhtxnszqremviuzk","1486742441"
"8","awvwzzthakbe","slgfbakiuoggaulehscqxvoct","143366617"
"9","drqdiqggqfos","hoigxngyrgwebyavcgugiixnm","1264096349"
h743107:~ # wc -l /tmp/data.csv
20000000 /tmp/data.csv
h743107:~ # ls -lh /tmp/data.csv
-rw-r--r--  1 root root 1,3G 2006-02-11 12:00 /tmp/data.csv
h743107:~ # fg
mysql -u root -p        (wd: /var/lib/mysql)
root@localhost [rootforum]> load data infile "/tmp/data.csv" into table t fields terminated by "," enclosed by '"';
Query OK, 20000000 rows affected (1 min 45.35 sec)
Records: 20000000  Deleted: 0  Skipped: 0  Warnings: 0
root@localhost [rootforum]>
[1]+  Stopped                 mysql -u root -p
h743107:~ # ll /var/lib/mysql/rootforum/
insgesamt 977552
drwx------  2 mysql mysql       4096 2006-02-11 11:48 .
drwxr-xr-x  5 mysql mysql       4096 2006-02-11 11:52 ..
-rw-rw----  1 mysql mysql         65 2006-02-11 11:26 db.opt
-rw-rw----  1 mysql mysql       8628 2006-02-11 11:48 t.frm
-rw-rw----  1 mysql mysql 1000000000 2006-02-11 12:04 t.MYD
-rw-rw----  1 mysql mysql       1024 2006-02-11 12:04 t.MYI


Wir erzeugen hier eine Tabelle mit einem UNSIGNED BIGINT "id" (8 Byte), zwei CHAR Spalten d und e von 12 und 25 Zeichen Breite und einer Spalte i INTEGER UNSIGNED. Alle Werte sind, wie sich das gehört, NOT NULL.

Das Generatorscript füllt diese Spalten mit aufsteigenden Werten für id, zufälligen Texten für d und e und mit Zufallszahlen für i. Es braucht auf meinem MR2 gibt 20 Minuten, um eine 1.3 GB große Loaderdatei mit 20 Mio Datensätzen zu erzeugen. Der Load erfolgt dann mit dem relativ schnellen LOAD DATA INFILE Kommando. Da keine Indices mitzuführen sind, geht der Load mit 105 Sekunden (190000 Rows/sec, 12380 KB/sec) über die Bühne. Das ist etwa 1/3 der Bruttogeschwindkeit der Platte, und daher relativ akzeptabel.

Die resultierende MYD-Datei verbraucht 50 Byte/Record und ist Fixed, sodaß 100 Mio Bytes (954 MB) für Datenfile verwendet werden. Ein Index wird nicht erzeugt, weil keiner definiert ist, daher ist die MYI-Datei minimal groß.

Die Daten sind drin:

Code: Select all

h743107:~ # fg
mysql -u root -p

root@localhost [rootforum]> show warnings;
Empty set (0.00 sec)

root@localhost [rootforum]> select count(*) from t;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (0.01 sec)

root@localhost [rootforum]> show table status like "t"G
*************************** 1. row ***************************
           Name: t
         Engine: MyISAM
        Version: 9
     Row_format: Fixed
           Rows: 20000000
 Avg_row_length: 50
    Data_length: 1000000000
Max_data_length: 214748364799
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-02-11 11:48:23
    Update_time: 2006-02-11 12:04:41
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


Die Data_length bei SHOW TABLE STATUS gibt also die Größe der MYD-Datei an, die Index_length ist die Größe der MYI-Datei. Data_free sagt, daß in der MYD-Datei keine Lücken existieren.

Das ist sehr wichtig, wenn in eine MyISAM-Tabelle mit INSERT Daten eingefügt werden: Neue Werte werden nun am Ende der MYD-Datei angefügt, und zwar ohne Locking - SELECT können also auf diese Tabelle ausgeführt werden, ohne sich mit den INSERT um den Zugriff zu prügeln - aber das funktioniert nur, solange Data_free 0 ist.

In Fixed-Tabellen ist es recht leicht, Data_free auf 0 zu bekommen - da alle Records gleich groß sind, wird das irgendwann einmal sicher der Fall sein, wenn Daten eingefügt werden und man kann mit der Avg_row_length und dem Wert aus Data_free leicht ausrechnen, wann das der Fall sein wird. OPTIMIZE TABLE schließt die Lücken auch, kann aber sehr lange dauern, und sobald das erste Mal aus der Tabellen mittenraus gelöscht wird, ist das gute OPTIMIZE wieder beim Teufel.

Mit MyISAM MERGE-Tables oder mit MySQL 5.1 DATA PARTITIONS kann man Tabellen bauen, aus denen sich Daten löschen lassen ohne Lücken aufzureißen. Data_free bleibt dann immer schon 0 und Concurrent_insert kann durchgeführt werden.

Code: Select all

root@localhost [rootforum]> show variables like "concurrent%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | ON    |
+-------------------+-------+
1 row in set (0.00 sec)


Man kann sich dies in der [mysqld]-Sektion seiner /etc/my.cnf leicht konfigurieren (concurrent_insert = 1).

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 3: Select und der Query Cache

Post by isotopp » 2008-04-11 19:57

Suchen dauern auf unserer Tabelle sehr lange.

Code: Select all

root@localhost [rootforum]> select * from t where i = 1163012190;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999993 | vrciabekkgcb | ykdewonxucqpwtdvzgnschyaw | 1163012190 |
+----------+--------------+---------------------------+------------+
1 row in set (21.81 sec)

root@localhost [rootforum]> select * from t where i = 944905110;
+----+--------------+---------------------------+-----------+
| id | d            | e                         | i         |
+----+--------------+---------------------------+-----------+
|  5 | kgiyuxheibsa | bipgdpnvetowphowepuerediy | 944905110 |
+----+--------------+---------------------------+-----------+
1 row in set (19.94 sec)

root@localhost [rootforum]>
[1]+  Stopped                 mysql -u root -p
h743107:~ # free -m
             total       used       free     shared    buffers     cached
Mem:          1010        996         13          0          7        781
-/+ buffers/cache:        208        802
Swap:         3074          3       3071
h743107:~ # fg
mysql -u root -p


An diesem Beispiel können wir sehen, daß eine Suche auf unseren Daten immer so um die 20 Sekunden lang dauert. Dabei spielt es keine Rolle, ob die zu suchenden Daten am Anfang oder am Ende der Tabelle stehen, weil wir ja mit dem SELECT-Kommando nach allen Vorkommen des gesuchten Wertes graben müssen - MySQL muß also durch die ganze Tabelle laufen und alle 20 Millionen Werte abprüfen.

Die zweite Query ist auch nicht viel schneller als die erste Query, weil die zu durchsuchenden Daten größer sind, als der dem System zur Verfügung stehende Buffer Cache (Spalte "cached" bei "free -m").

Wenn wir an diese Query ein "LIMIT 1" anfügen, bekommen wir ein variables Zeitverhalten: Werte am Anfang der Tabelle werden schnell gefunden, während eine Suche nach Werten am Ende der Tabelle sehr lange dauert:

Code: Select all

root@localhost [rootforum]> SELECT * from t where i = 944905110 limit 1;
+----+--------------+---------------------------+-----------+
| id | d            | e                         | i         |
+----+--------------+---------------------------+-----------+
|  5 | kgiyuxheibsa | bipgdpnvetowphowepuerediy | 944905110 |
+----+--------------+---------------------------+-----------+
1 row in set (0.00 sec)

root@localhost [rootforum]> SELECT * from t where i = 1163012190 limit 1;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999993 | vrciabekkgcb | ykdewonxucqpwtdvzgnschyaw | 1163012190 |
+----------+--------------+---------------------------+------------+
1 row in set (20.97 sec)


Führt man eine bereits einmal durchgeführte Query noch einmal aus, bekommt man die Antwort sehr, sehr schnell, denn MySQL hat einen Query Cache, der einmal gestellte Queries wiedererkennen kann und dann einfach noch einmal den alten Result-Set abspielt statt die Query tatsächlich noch einmal auszuführen. Dabei kommt es auf die Schreibweise an. Schon das unterschiedliche "S" bei den folgenden beiden Queries sorgt dafür, daß MySQL die Query nicht mehr als "alt" erkennt, sondern neu ausführen muß.

Code: Select all

root@localhost [rootforum]> select * from t where i = 1163012190;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999993 | vrciabekkgcb | ykdewonxucqpwtdvzgnschyaw | 1163012190 |
+----------+--------------+---------------------------+------------+
1 row in set (0.00 sec)

root@localhost [rootforum]> Select * from t where i = 1163012190;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999993 | vrciabekkgcb | ykdewonxucqpwtdvzgnschyaw | 1163012190 |
+----------+--------------+---------------------------+------------+
1 row in set (20.64 sec)


Auch ein INSERT in eine Tabelle kann natürlich den Result-Set einer Query verändern. Daher muß eine Query aus dem Query Cache gelöscht werden, wenn ihre Base Table verändert wird. Der Query Cache kann auch mit dem Kommando FLUSH QUERY CACHE absichtlich gelöscht werden.

Code: Select all

root@localhost [rootforum]> insert into t values ( 20000001, "keks", "keks", 3);
Query OK, 1 row affected (0.06 sec)

root@localhost [rootforum]> select * from t where i = 1163012190;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999993 | vrciabekkgcb | ykdewonxucqpwtdvzgnschyaw | 1163012190 |
+----------+--------------+---------------------------+------------+
1 row in set (19.77 sec)


Diese Query geht nicht in den Query Cache:

Code: Select all

root@localhost [rootforum]> flush query cache;
Query OK, 0 rows affected (0.00 sec)

root@localhost [rootforum]> select sql_no_cache * from t where id = 1000;
+------+--------------+---------------------------+-----------+
| id   | d            | e                         | i         |
+------+--------------+---------------------------+-----------+
| 1000 | tgnqufoiygkr | knkosawojeknkcmsodwdnorkd | 295436531 |
+------+--------------+---------------------------+-----------+
1 row in set (20.12 sec)

root@localhost [rootforum]> select sql_no_cache * from t where id = 1000;
+------+--------------+---------------------------+-----------+
| id   | d            | e                         | i         |
+------+--------------+---------------------------+-----------+
| 1000 | tgnqufoiygkr | knkosawojeknkcmsodwdnorkd | 295436531 |
+------+--------------+---------------------------+-----------+
1 row in set (20.28 sec)


Die Konfiguration des Query Cache erfolgt über Variables in der [mysqld]-Sektion der /etc/my.cnf und kann wie folgt ausgegeben werden:

Code: Select all

root@localhost [rootforum]> show variables like "query_cache%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)


Das query_cache_limit bestimmt, wie groß der Result Set einer Query maximal sein kann, damit er in den Query Cache Eingang findet. Das Cachen von sehr großen Result Sets würden den Query Cache fluten und andere, wichtigere Queries aus dem Cache drängen. Speicher wird dabei in Blöcken von query_cache_min_res_unit Bytes verbraucht - auch eine einzeilige Query verbraucht also 4 KB im Cache, wenn man dies nicht verkleinert. Das ist vor allen Dingen dann lohnend, wenn sehr viele kleine Result Sets zu verwalten sind.

Die Query_Cache_Size legt fest, wieviel RAM wir für den Query Cache reservieren wollen. Die Größe des Query_Cache muß man ein wenig variieren, und dabei die Ergebnisse mit SHOW STATUS kontrollieren.

Der Query_Cache_Type kann 0 (OFF), 1 (ON) oder 2 (ON DEMAND) sein. Ist er auf 0, werden alle anderen Parameter ignoriert und der Cache ist aus. Ist er auf 1, werden alle Queries, für die dies möglich ist, in den Cache gelegt - Queries mit PREPARE statt SELECT, Queries, die Funktionsaufrufe enthalten, die NOT DETERMINISTIC sind (rand(), now() und so weiter), und Queries, die mit SQL_NO_CACHE markiert sind, können nicht in den Cache. Ist er auf 2, werden Queries nicht in den Cache gelegt, außer sie sind ausdrücklich mit SQL_CACHE markiert.

Wenn auf einer Tabelle durch eine schreibende Operation ein Write Lock gelegt wird, können Queries aus dem Query Cache dennoch beantwortet werden. Will man dies nicht, will man also durch ein passendes LOCK TABLES sicher alle Reader anhalten, dann muß man query_cache_wlock_invalidate auf 1 (ON) stellen.

Code: Select all

root@localhost [rootforum]> show status like "qc%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33538984 |
| Qcache_hits             | 6        |
| Qcache_inserts          | 11       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 9        |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 14       |
+-------------------------+----------+
8 rows in set (0.00 sec)

root@localhost [rootforum]> show status like "com_select";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 21    |
+---------------+-------+
1 row in set (0.00 sec)


Der Status des Query Cache kann mit "SHOW STATUS LIKE 'qc%'" abgefragt werden. Der Query Cache hier ist noch fast ganz frei: In den SHOW VARIABLES waren 33554432 Byte konfiguriert und hier sind noch 33538984 Byte frei. Der freie Speicher ist nicht fragmentiert, sondern liegt an einem Stück vor. Der Cache hat dabei 6 Qcache_hits gehabt, dem stehen 21 Select-Kommandos entgegen, die nicht gecached worden sind (ein Select zählt ENTWEDER qcache_hits ODER com_cache hoch).

Die Resultate von 11 Queries sind im Qcache abgelegt worden, und der Cache ist bisher noch niemals wegen Ã?berlauf gepruned (also teilweise gelöscht worden). 9 Queries konnten nicht gecached werden, aus welchen Gründen auch immer, 6 Queries liegen derzeit im Cache.

Wenn man wissen will, ob der eigene Query Cache groß genug ist, sollte man im Abstand von einigen Stunden einmal die Qcache_lowmem_prunes ansehen, und überprüfen, ob hochzählen. Wenn dies passiert, ist der Query Cache übergelaufen. Qcache_free_memory und Qcache_free_blocks sollte man ebenfalls im Auge behalten: Wenn der fre_blocks counter groß wird, ist der Query Cache fragmentiert und man muß unter Umständen mit einer größeren Blockgröße experimentieren. Es kann sinnvoll sein, die com_select, qcache_hits, qcache_inserts, qcache_lowmem_prunes und qcache_free_memory sowie qcache_free_blocks minütlich auszulesen und in ein RRD zu plotten.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 4: Indexing

Post by isotopp » 2008-04-11 19:58

Wie wir in den vorangegangenen Beispielen gesehen haben, reicht es nicht aus, einer Datenbank zu sagen, welche Daten sie zu suchen hat. Wir müssen ihr auch noch Hilfen geben, mit denen sie in der Lage ist, diese Aufgabe schnell zu erfüllen. Eine sehr wichtige solche Hilfe ist ein Index.

Eine MYD-Datei wird, wenn sie keine Lücken hat, durch Anfügen von neuen Datensätzen verlängert. Die natürliche Reihenfolge der Datensätze in einer MYD-Datei ohne Löschen ist also chronologisch. Wenn in der MYD-Datei auch gelöscht wird, werden die Dinge komplizierter, denn MySQL wird versuchen, die Lücken aufzufüllen. Die Reihenfolge der Datensätze ist dann zufällig.

Das gleicht einer Bücherei, bei der neu gekaufte Bücher einfach hinten in die freien Regale gestellt werden, und wenn Lücken vorhanden sind, diese aufgefüllt werden.

Ein Index ist eine Struktur, die uns erlaubt, bestimmte Suchen schneller durchzuführen. Büchereien haben zum Beispiel oft einen Titelkatalog oder einen Autorenkatalog. Ein Autorenkatalog entsteht, indem wir die Autoren aller Bücher in der Bücherei zusammen mit dem Regalstandort des Buches auf Notizkarten aufschreiben und diese Karten dann alphabetisch nach Autoren sortieren.

In einem sortierten Datenbestand kann man nämlich sehr schnell suchen. Wenn auf den Autorenkarten keine Reiter "a", "b", "c", ... vorhanden sind, kann man "Suchen durch Halbieren": Ein Buch von "Jeremy Zawodny" findet man, indem man den die Autorenliste halbiert und dann prüft, ob der Autor in der ersten oder zweiten Hälfte der Bücher zu finden ist. Diese Hälfte halbiert man dann wieder, und so weiter und so weiter.

Für zwei Bücher kann man so mit einer Operation entscheiden, welches von beiden Büchern das gesuchte ist. Für vier Bücher braucht man eine Operation, um dies auf den Fall mit zwei Büchern zurück zu führen, also zwei Operationen insgesamt. Für acht Bücher kann man dies mit einer Operation auf den Fall mit vier Büchern reduzieren.

Allgemein braucht man für n Bücher dann also log(2,n) Operationen, um ein Buch eines bestimmten Autors zu finden.

Code: Select all

Bücher   Suchoperationen
     2   1 (2^[b]1[/b] = 2)
     4   2 (2^[b]2[/b] = 4)
     8   3 (2^[b]3[/b] = 8)

     n   e = log(2, n) (2^e = n)


Aus einer Bibliothek mit 20 Millionen Büchern kann man also mit weniger als 24-25 Operationen die Bücher von Jeremey Zawodny finden, wenn man einen nach Autorennamen sortierten Katalog zur Verfügung hat (Man bekommt so eine Liste der Standorte, und muß dann zu den auf den Karten notierten Standorten laufen, um die Bücher tatsächlich abzuholen).

Code: Select all

root@localhost [(none)]> select log(20000000)/log(2) as ops;
+-----------------+
| ops             |
+-----------------+
| 24.253496664212 |
+-----------------+
1 row in set (0.00 sec)


Das Beispiel macht Gebrauch von der Beobachtung, daß man mit einer beliebigen Logarithmusfunktion jede andere Logarithmusfunktion berechnen kann, indem man einfach log(b, n) in log(n)/log(b) umformt. MySQL stellt mit den Funktionen exp() und log() die Exponentialfunktionen und Logarithmusfunktionen zur Basis e = 2.718281828459 zur Verfügung, sodaß log(20000000)/log(2) hier den log(2, 20000000) bestimmt.

Tatsächlich befinden sich auf den Karten in einer Bibliothek aber Reiter, sodaß man gleich unter "Z" nachschauen kann, also bei gleichverteilten Autorennamen schon mit dem ersten Schritt nur noch ein Sechsundzwanzigstel aller Bücher durchsuchen muß statt die Hälfte. Wäre nun auf allen Z-Karten "Za", "Zb", und so weiter notiert, würde auch der zweite Schritt nur sechsundzwanzigsteln, und so weiter. Man bräuchte dann bei 20 Millionen Büchern nur noch 5-6 Schritte, um alle Bücher von Jeremy Zawodny zu finden.

Code: Select all

root@localhost [(none)]> select log(20000000)/log(26);
+-----------------------+
| log(20000000)/log(26) |
+-----------------------+
|       5.1598357001807 |
+-----------------------+
1 row in set (0.00 sec)



Nun sind die Namen von Buchautoren aber nicht gleichverteilt. Stattdessen könnte man aber auch wie bei einem Lexikon vorgehen und in regelmäßigen Abständen, etwa für jeweils eine Million Autoren, Proben nehmen: Band 1 von A-E, Band 2 von F-L und so weiter. Innerhalb der Million würde man dann für jeweils einen 100.000er Block vermerken A-Be, Bf-Ca, und so weiter, und auf den weiteren Ebenen genauso. Dann hätte man gleich große Blöcke, und wüßte für jeden Block, welche Buchstabenintervalle darin enthalten sind.

Beim Einfügen von Werten muß man jedoch unter Umständen Werte aus dem 1. 100.000er-Block in den zweiten Verschieben und so weiter: Kommt Arnold dazu, muß Bertram unter Umständen in den 2. Block und die Blockgrenzen sind nun A-Bd, Be-Ca.

So einen Index bezeichnet man als balancierten Baum (Btree), und das ist genau das, was MySQL verwendet. Die Schiebeoperationen nennt man Rebalancing des Baumes, und die will man gerne vermeiden.

Eine MYI-Datei besteht also aus Blöcken von key_cache_block_size (normal 1024) Byte Größe. Ein Key Cache Block enthält was immer wir indizieren wollen und Zeiger in die MYD-Datei. Das sind entweder Byte-Offsets (Row_format: dynamic) oder Datensatznummern (Row_format: fixed). Wenn wir also eine INTEGER-Spalte indizieren, braucht jeder Eintrag im Index 4 Byte für die zu indizierende Spalte plus 4 Byte Zeiger in die Daten. In einen 1024-Block passen so also 1024/8 = 2^10/2^3 = 2^(10-3) = 2^7 = 128 Einträge.

Durch Laden dieses Blocks können wir also die Suche im Index nicht "durch Halbieren" oder "durch sechsundzwanzigsteln" durchführen, sondern wir reduzieren die zu durchsuchende Datenmenge in jedem Schritt auf ein Hundertachtundzwanzigstel. Oder würden dies tun, wenn wir die Indexblöcke alle immer ganz voll machen würden.

Das tun wir aber nicht, weil wir dann bei jedem Einfügen von Daten rebalancieren müßten. Stattdessen machen wir die Blöcke nur zu 2/3 voll. Dadurch 128teln wir nicht, sondern 84teln nur, aber das macht fast nichts aus. 20 Millionen Datensätze durchsuchen wir so mit maximal 4 Operationen.

Code: Select all

root@localhost [(none)]> select 128*0.66;
+----------+
| 128*0.66 |
+----------+
|    84.48 |
+----------+
1 row in set (0.00 sec)

root@localhost [(none)]> select log(20000000)/log(84.48);
+--------------------------+
| log(20000000)/log(84.48) |
+--------------------------+
|          3.7892903582541 |
+--------------------------+
1 row in set (0.00 sec)


Einen "sortierten Autorenkatalog" nennt man in einer Datenbank einen "Index auf eine Spalte". In unserer Beispieltabelle haben wir die Spalten id, d, e und i. Wir wollen einen Index auf die Spalte i (INTEGER, 4 Byte) legen, also braucht ein Indexeintrag 8 Byte (4 Byte für das Integer und 4 Byte für den Datenzeiger). Für 20 Millionen Einträge rechnen wir also mit einer ca. 230 MB großen MYI-Datei.

Die Rechnung geht so: 20 Mio Einträge zu 8 Byte sind 152 MB, aber wir machen die Blöcke nur zu ca. 2/3 voll, also müssen wir das ganze durch 2/3 teilen:

Code: Select all

root@localhost [rootforum]> select 20000000*8/1024/1024/(2/3)G
*************************** 1. row ***************************
20000000*8/1024/1024/(2/3): 228.881836166382
1 row in set (0.00 sec)


Den Index erzeugen wir mit dem Kommando CREATE INDEX. Das Kommando geht dann die Spalte i durch, notiert die Werte und für jeden Wert die Datensatznummer, in der der Wert zu finden ist. Die Werte müssen sortiert werden, und dazu braucht MySQL Platz. Der Puffer für diesen Platz kann online definiert werden, und sollte sehr groß gewählt werden (bei uns: Mindestens 230 MB groß, wie wir eben vorgerechnet haben). Der Name des Puffers ist myisam_sort_buffer_size.

Im folgenden Beispiel verwenden wir Sessionvariablen, hier die Variable @old_buffer, um den alten Wert für myisam_sort_buffer_size zu speichern. Wir definieren dann für unsere Verbindung zur Datenbank und nur für diese einen größeren Wert für myisam_sort_buffer_size, und erzeugen den Index mit CREATE INDEX. Danach setzen wir myisam_sort_buffer_size wieder auf den alten Wert zurück.

Wir verändern nicht @@global.myisam_sort_buffer_size, weil das ein großes Risiko wäre: Jedes CREATE INDEX, ALTER TABLE ... ADD INDEX oder REPAIR TABLE würde dann den großen Puffer verwenden und bis zu 300 MB Speicher verlangen.

Code: Select all

root@localhost [rootforum]> select @@session.myisam_sort_buffer_size;
+-----------------------------------+
| @@session.myisam_sort_buffer_size |
+-----------------------------------+
|                          31457280 |
+-----------------------------------+
1 row in set (0.04 sec)

root@localhost [rootforum]> set @old_buffer=@@session.myisam_sort_buffer_size;
Query OK, 0 rows affected (0.00 sec)

root@localhost [rootforum]> set @@session.myisam_sort_buffer_size = 300*1024*1024;
Query OK, 0 rows affected (0.02 sec)

root@localhost [rootforum]> create index i on t(i);
Query OK, 20000000 rows affected (4 min 33.35 sec)
Records: 20000000  Duplicates: 0  Warnings: 0

root@localhost [rootforum]> set @@session.myisam_sort_buffer_size = @old_buffer;
Query OK, 0 rows affected (0.00 sec)


Da diese Aktion auf meinem Rechner über 4 Minuten dauert, kann ich MySQL beim Erzeugen des Index zusehen:

Code: Select all

root@localhost [(none)]> show processlistG
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: rootforum
Command: Query
   Time: 48
  State: copy to tmp table
   Info: create index i on t(i)
...


Das will ich genauer beobachten:

Code: Select all

root@linux:/var/lib/mysql/data/rootforum # ls -l
total 1493641
-rw-rw----  1 mysql mysql  527958016 Feb 12 17:15 #sql-26a5_5.MYD
-rw-rw----  1 mysql mysql       1024 Feb 12 17:14 #sql-26a5_5.MYI
-rw-rw----  1 mysql mysql       8628 Feb 12 17:14 #sql-26a5_5.frm
drwx------  2 mysql mysql        240 Feb 12 17:14 ./
drwxr-xr-x  6 mysql mysql        440 Feb 12 17:06 ../
-rw-rw----  1 mysql mysql         61 Feb 12 17:01 db.opt
-rw-rw----  1 mysql mysql 1000000000 Feb 12 17:07 t.MYD
-rw-rw----  1 mysql mysql       1024 Feb 12 17:07 t.MYI
-rw-rw----  1 mysql mysql       8628 Feb 12 17:02 t.frm


Die Prozeßgröße ist dabei eher klein:

Code: Select all

root@linux:/var/lib/mysql/data/rootforum # ps axuwww| grep mysql[d]
mysql     9893  2.2  1.2 351420 13152 pts/1    Sl   15:13   2:41 /usr/local/mysql-max-5.0.18-linux-i686-glibc23/bin/mysqld ...


Der mysqld hat hier also das Potential, bis zu 351420 KB zu belegen, da er aber für diese Ã?bung frisch gestartet wurde, belegt er tatsächlich nur 13152 KB Speicher.

Nach dem Abschluß der Kopieroperation ist die #sql-26a5_5.MYD genau so groß wie die t.MYD, und die eigentliche Sortieroperation kann beginnen. Der mysqld wächst um die vereinbarte Puffergröße (genauer: nur um die benötigte Größe und das nur schrittweise).

Code: Select all

root@localhost [(none)]> show processlistG
*************************** 1. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: rootforum
Command: Query
   Time: 124
  State: Repair by sorting
   Info: create index i on t(i)


Dies ist die Anzeige von SHOW PROCESSLIST, während sortiert wird und der myisam_sort_buffer_size tatsächlich benutzt wird. Sieht man sich den Prozeß in der Unix-Prozeßliste an, kann man sehen, daß auch Speicher belegt wird:

Code: Select all

root@linux:/var/lib/mysql/data/rootforum # !ps
ps axuwww| grep mysql[d]
mysql     9893  3.5 27.6 624744 286388 pts/1   Sl   15:13   4:24 /usr/local/mysql-max-5.0.18-linux-i686-glibc23/bin/mysqld ...


Und der Gewinn? Operationen wie "select * from t where i = 193306751" sind jetzt sehr schnell durchzuführen, und zwar unabhängig davon, ob der Wert am Anfang oder am Ende der Tabelle gefunden wird.

Code: Select all

root@localhost [rootforum]> flush query cache;
Query OK, 0 rows affected (0.03 sec)

root@localhost [rootforum]> select * from t where i = 193306751;
+----+--------------+---------------------------+-----------+
| id | d            | e                         | i         |
+----+--------------+---------------------------+-----------+
|  9 | jilrmbmbujaw | vegmospiyoenpnovopofogiof | 193306751 |
+----+--------------+---------------------------+-----------+
1 row in set (0.00 sec)

root@localhost [rootforum]> select * from t where i = 1830603510;
+----------+--------------+---------------------------+------------+
| id       | d            | e                         | i          |
+----------+--------------+---------------------------+------------+
| 19999990 | hctjfsosiobb | zyxhrbyvbtsrnpwjijtgqrfcb | 1830603510 |
+----------+--------------+---------------------------+------------+
1 row in set (0.03 sec)

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 5: Locking

Post by isotopp » 2008-04-11 19:59

Key Buffer

In dem letzten Artikel dieser Serie haben wir gesehen, wie MySQL einen Index anlegt und was dies bedeutet. MySQL at einen Puffer, mit dem ein Index ganz oder teilweise im RAM gehalten werden kann. Dies ist der Key Buffer, und er ist eine sehr zentrale Konfigurationsvariable, die in der [mysqld]-Sektion der my.cnf gesetzt wird.

Code: Select all

[mysqld]
key_buffer = 100M


Dies stellt einen Key Buffer von bis zu 100 MB für den mysqld zur Verfügung. Mit den STATUS-Variablen key_read_requests und key_reads kann man dann leicht feststellen, ob dieser Puffer groß genug ist: key_read_requests sollte sehr viel größer als key_reads sein, und zwar zwischen 100 und 1000 mal so groß. MySQL hält dann den MyISAM-Index im RAM, und kann so Daten auf der Platte sehr schnell finden.

Man kann bis zu 20% des Hauptspeichers, den man MySQL zur Verfügung stellen will, in den Key Buffer investieren (auf einer 1 GB Maschine, bei der man 500 MB für MySQL und 500 MB für Apache aufwenden will, kann man den Key Buffer also 100 MB groß machen). Der Key Buffer sollte so groß sein, wie die Summe aller verwendeten Indices ist (und wir haben ja schon gesehen, wie man die berechnen oder nachsehen kann). Je nach Hauptspeicher und Indexgröße ist das Dilemma nun da, und man muß auf der einen oder anderen Seite Abstriche machen.

Das nennt der Sysadmin dann "Optimierungsaufgabe" und der Betriebswirtschaftler hat auch ein Wort dafür: "Investitionsbedarf". Man sieht deutlich die verschiedenen Herangehensweisen der beiden Disziplinen an Probkleme.

Locks und Locking

Wie wir in den vergangenen Ausgaben dieser Serie auch gesehen haben, kann es Datenbankoperationen geben, die vergleichsweise lange dauern. Es kann sein, daß eine solche Operation während dieser Zeit ein LOCK auf einer Tabelle erzeugt. MyISAM kennt zwei Arten von Locks und sie gelten immer für die gesamte Tabelle - MyISAM kann nicht Bereiche einer Tabelle oder gar einzelne Zeilen locken.

Ein SHARED LOCK oder READ LOCK erlaubt dem Lockinhaber den Lesezugriff auf eine Tabelle, und erlaubt gleichzeitig weitere Lesezugriffe durch andere Threads. Schreibende Threads werden jedoch ausgesperrt und müssen warten. Ein EXCLUSIVE LOCK oder WRITE LOCK erlaubt dem Lockinhaber und nur diesem den Schreibzugriff auf die Tabelle. Alle anderen Threads, egal ob sie lesen oder schreiben wollen, müssen warten.

Ein Thread, der wartet, sieht so aus:

Code: Select all

root@localhost [rootforum]> show processlistG
*************************** 1. row ***************************
     Id: 7
   User: root
   Host: localhost
     db: rootforum
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: rootforum
Command: Query
   Time: 422
  State: copy to tmp table
   Info: alter table t add index (i)
*************************** 3. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: rootforum
Command: Query
   Time: 205
  State: Locked
   Info: insert into t values (20000001+1, "keks", "keks", 17)
3 rows in set (0.00 sec)


Hier ist der Thread mit der ID 9 im State: Locked, denn er versucht auf die Tabelle t zu schreiben. t wird jedoch gerade vom Thread 8 bearbeitet, der die Tabelle dazu mit einem Lock belegt hat. Das INSERT-Kommando hängt nun an seinem Prompt und kommt nicht zurück. Erst nachdem das ALTER TABLE durchgelaufen ist, kann das INSERT-Kommando ausgeführt werden. Wenn man so eine Ausgabe ("Locked") in seinem eigenen SHOW PROCESSLIST sieht, womöglich für mehr als ein Kommando in Folge, dann ist es quasi schon zu spät - der MySQL-Server braucht dringend eine tunende Hand.

Hier noch einmal die Ausführungszeit:

Code: Select all

root@localhost [rootforum]> insert into t values (20000001+1, "keks", "keks", 17);
Query OK, 1 row affected (8 min 9.49 sec)


Ein Blick ins Slow-Query-Log bringt dann noch mehr Information. Dort wird für jede lange dauernde Query nämlich neben der absoluten Query_time auch noch die Lock_time mit aufgeführt. Ist diese nicht 0, weiß man, daß der Server ein Problem mit seinen Locks hat.

Das ist eine sehr unangenehme Situation, die man unbedingt vermeiden möchte. Denn wenn man sich mit der Performance von Servern auseinandersetzt, dann gibt es im Wesentlichen zwei Ziele, auf die man einen Server optimieren kann. Eines dieser Ziele ist Durchsatz, also die Anzahl der Anfragen pro Sekunde, die ein Server zu Ende bringt, und das andere ist Latenz, also die Dauer, die eine einzelne Query dauern darf. Beides sind sehr unterschiedliche Ziele, und man muß beim Performancetuning seine Segel sehr sorgfältig setzen, je nachdem, welches Optimierungsziel vorne steht.

Warteschlangen, Telefonzellen und Hockeyschläger

Bei Optimierung auf Antwortzeit, Latenz, begegnet einem dabei in der Regel die "Hockey Stick Curve" aus der Warteschlangentheorie. Sie sieht in etwa so aus:

Code: Select all

latency
^
|               |
|               |
|               |
|              |
|            _|
|         __-
|____-----
+-----------X-------> load


Warum diese Kurve so aussieht, wie sie aussieht, kann man leicht mit einem kleinen PHP-Script testen. Das gezeigte PHP-Script simuliert eine Telefonzelle, an der mit 20%iger Wahrscheinlichkeit pro Minute Leute auftauchen, die Gespräche führen wollen, die zwischen einer und fünf Minuten lang sind. Läßt man das Script mehrfach mit einem CLI-PHP laufen, und dreht dabei $prob langsam von 20% auf 22%, 25% und dann 30% hoch, sieht man sehr schön, wie sich die Warteschlangen im System immer weiter aufbauen.

Warum ist das so? Telefonzellen (und auch Datenbanken) können nicht vorarbeiten. Idlezeit verstreicht ungenutzt - nur Zeit, in der die Warteschlange nicht leer ist, ist sinnvoll genutzte Zeit.

Code: Select all

#! /usr/bin/php5 -q
<?php
  $prob   = 20; ## Wahrscheinlichkeit für neuen Kunden in Prozent
  $maxlen = 5;  ## Gesprächsdauer in Minuten (1-$maxlen)

  $link = 0;

  function connect() {
    global $link;

    $link = mysql_connect("localhost", "root", "1wjsnh");
    if ($link === false)
      die("Argh");

    mysql_select_db("rootforum") or die("cannot select db");
    doquery("DROP TABLE IF EXISTS telefon");
    doquery("CREATE TABLE telefon ( id serial, dauer int )");
  }

  function doquery($cmd) {
    mysql_query($cmd) or die("cannot do query $cmd");
  }

  function getvalues($cmd) {
    $res = mysql_query($cmd);
    if ($res === false)
      die("Cannot query $cmd");

    $r = mysql_fetch_assoc($res);
    return $r;
  }

  function getvalue($cmd, $name) {
    $r = getvalues($cmd);
    return $r[$name];
  }

  connect();
  $minute = 0;
  $call_remaining = 0;
  $id = 0;

  while ($minute < 1000) {
    $minute += 1;
    echo "Processing minute $minute: ";

    # Wie lang ist die Schlange?
    $r = getvalues("select count(dauer) as len, sum(dauer) as wait from telefon", "len");
    $len = $r['len'];
    $wait = $r['wait'];
    echo "(Schlange $len, Wait $wait) ";

    # Neuer Kunde stellt sich an
    if (rand(1,100) < $prob) {
      $call_len = rand(1,$maxlen);
      echo "(New customer $call_len min.) ";

      doquery("insert into telefon ( dauer ) values ( $call_len )");
    }

    # Zelle leer:
    if ($call_remaining == 0) {
      # Wartet ein Kunde?
      $r = getvalues("select id, dauer from telefon order by id limit 1");
      if ($r === false) {
        # Kein Kunde da.
        echo "(queue empty) ";
      } else {
        # Kunde da, abholen und processing
        $call_remaining = $r['dauer'];
        $id = $r['id'];
        echo "(customer $id: $call_remaining) ";

        # Kunde aus der Schlange nehmen
        doquery("delete from telefon where id = $id");
      }
    } else {
      # Telefonieren...
      $call_remaining -= 1;
      echo "(customer $id: $call_remaining) ";
    }
    echo "n";
  }
?>


Tabellen, Locks und Deadlocks

In MyISAM braucht man Tabellen in der Regel nicht selber zu locken: Jedes Kommando ist logisch atomar. Es lockt die benötigten Tabellen mit dem passenden Lock, führt seine Operation durch und unlockt die Tabellen am Ende des Kommandos wieder.

Manchmal will man aber mehr als ein Kommando auf einer Tabelle durchführen und will dabei sicherstellen, daß sich der Inhalt der Tabelle nicht verändert, während man die Operation durchführt. Der häufigste Fall ist das Auslesen und Verändern eines Zählers. Um einen Zähler einfach nur hochzuzählen kann man ja einfach

Code: Select all

update counter set val = val + 1 where id = <wert>


verwenden. Aber will man den Zähler auch auslesen, dann braucht man so etwas wie

Code: Select all

LOCK TABLES counter WRITE
SELECT val into @v from counter where id = <wert>
UPDATE counter SET val = @v+1 WHERE id = <wert>
UNLOCK TABLES
SELECT @v


Nur so ist sichergestellt, daß das Hochzählen auch atomar und ohne Unterbrechung durch andere Zähloperationen erfolgt.

Wann immer man mehrere Tabellen mit einem Lock belegt, können Deadlocks entstehen. Man stelle sich vor, zwei Threads würden gleichzeitigt die Statements "LOCK TABLES a, b WRITE" und "LOCK TABLES b, a WRITE" ausführen. Thread 1 würde die Tabelle a locken, während Thread 2 jetzt die Tabelle b lockt. 1 will nun b locken, aber das geht nicht, weil 2 dieses Lock schon hält. Zugleich will 2 jetzt a locken, aber das geht auch nicht - beide Threads würden ewig darauf warten, daß der jeweils andere die Locks aufgibt.

Ein gängiger Weg zur Vermeidung von Deadlocks ist, Ressourcen grundsätzlich nur einmal und nur atomar zu vergeben - MySQL hat mit LOCK TABLES diesen Weg gewählt. Ein Thread kann also nur "LOCK TABLES a,b WRITE" ausführen, nicht etwa "LOCK TABLES a WRITE" und später "LOCK TABLES b WRITE" dazunehmen, um zusätzlich zu a noch b dazu zu nehmen. Außerdem kann "LOCK TABLES" nicht unterbrochen werden - LOCK TABLES a,b WRITE sammelt also alle Locks für a und b gleichzeitig ein.

Eine andere Methode Deadlocks zu vermeiden besteht darin, die Ressourcen, die zu vergeben sind, anzuordnen und alle Threads zu zwingen, Ressourcen in Reihenfolge zu bestellen. "LOCK TABLES a, b WRITE" und "LOCK TABLES b, a WRITE" würden so intern beide zu demselben Kommando "LOCK TABLES a,b WRITE" werden, und dann kann es schon gar nicht mehr zum Deadlock kommen (denn der zweite Thread würde a gelockt vorfinden, bevor er überhaupt seine Finger auf b legen kann). So hat MySQL die oben dargestellte Atomizität intern implementiert.

Ein LOCK auf Tabellen kann mit UNLOCK TABLES aufgehoben werden.

Ein LOCK auf Tabellen sollte immer nur so kurz wie irgend möglich gehalten werden. Wer mit den Werten für die Telefonatsdauer und die Neukundenwahrscheinlichkeit im Telefonzellensimulations-PHP ein wenig gespielt hat, der hat möglicherweise jetzt eine Vorstellung davon, warum das so ist.

Locks haben Prioritäten

In MySQL hat ein WRITE-Lock eine höhere Priorität als ein READ-Lock und entsprechend ein INSERT/UPDATE/DELETE eine höhere Priorität als ein SELECT. Wenn also auf eine Tabelle viele schreibende Statements durchgeführt werden, dann werden die SELECT-Statements entsprechend zurück gedrängt. Daher ist es sehr wichtig, daß schreibende Statements schnell durchgeführt werden, damit sie ihre Locks nur für möglichst kurze Zeit halten. Ein schreibendes Statement kann dann schnell durchgeführt werden, wenn es eine möglichst einfache WHERE-Clause hat, die einen Index verwenden kann (z.B. ein UPDATE gegen eine Zeile, die über ihren PRIMARY KEY bestimmt wird).

Oft kann es sinnvoll sein, schreibende Statements zu batchen. Dazu kann man zum Beispiel die extended INSERT-Syntax verwenden ("insert into <name> ( col1, col2, ...) values (val1, val2, ...), (val1, val2, ...)"), mit der man mehr als eine Zeile auf einmal in die Datenbank einfügen kann. Diese Syntax steht für UPDATE nicht zur Verfügung, kann aber zum Beispiel mit "REPLACE INTO" verwendet werden. Ein einzelnes Statement kann max_allowed_packet Bytes lang sein, und wenn extended INSERT-Syntax verwendet wird, verwendet MySQL intern einen Puffer von bulk_insert_buffer_size, um diese Operation zu beschleunigen. Dieser Puffer sollte zur Insert-Größe passend gewählt werden.

Bei extended INSERT-Syntax werden Updates der Indices einer Tabelle gepuffert und der Baum nicht nach jedem Insert rebalanciert, sondern nur einmal am Ende aller Inserts dieses Blocks. Hat man mehr als ein solches INSERT-Statement, kann man diese außerdem noch in ein LOCK TABLES wickeln, damit es noch schneller geht:

Code: Select all

LOCK TABLES t WRITE;
INSERT INTO t (a,b) VALUES (1, 2), (3, 4);
INSERT INTO t (a,b) VALUES (5,6);
UNLOCK TABLES;


Hier wird der Key Buffer nur einmal, beim UNLOCK TABLES, aktualisiert und auf die Platte geschrieben.

Wer sehr mutig ist, und ein MySQL 5.x hat, startet seinen MySQL-Server mit der Konfigurationsvariable "delayed_key_writes = ON" oder gar "delayed_key_writes = ALL" in der [mysqld]-Sektion der my.cnf. Steht diese Variable auf 1, kann man eine Tabelle beim CREATE TABLE mit der Option DELAYED_KEY_WRITE spezifizieren. Der Key-Buffer für solche Tabellen wird dann nicht mehr so oft auf die Platte geschrieben (bei delayed_key_writes = 2 wird dies für alle Tabellen unabhängig von den CREATE-Optionen gemacht). Dies kann dazu führen, daß bei einem Servercrash die Indices (aber nicht die Daten) beschädigt sind und neu erzeugt werden müssen - das macht häßlich lange Recovery-Zeiten. Dafür werden Index-Updates immer so schnell ausgeführt, als wären sie gebatched und in LOCK TABLES eingewickelt.

Manchmal will man ein einzelnes SELECT haben, das nicht von INSERT-Statements verdrängt werden kann. Das kann man mit "SELECT HIGH_PRIORITY ..." erreichen: Es macht das Select nicht schneller, sorgt aber dafür, daß es nicht von immer wieder eintreffenden INSERT-Statements nach hinten gedrückt werden kann.

Anders herum hat man manchmal ein INSERT, das keine SELECT-Statements zur Seite drücken soll. Das kann man mit "INSERT LOW_PRIORITY" erreichen: Es macht das Insert nicht langsamer, sorgt aber dafür, daß das INSERT keine SELECT-Statements zur Seite drückt.

INSERT DELAYED

Etwas ganz anderes ist INSERT DELAYED: INSERT DELAYED kommt in jedem Fall sofort zurück. Die Daten, die dem Insert übergeben werden, sind dabei noch lange nicht geschrieben und tatsächlich kann es sein, daß sie niemals geschrieben werden, denn MySQL schiebt die Daten nur in einen internen Puffer, der irgendwann einmal abgearbeitet wird und in die Tabelle geschoben wird.

Dabei gelten eine ganze Reihe von Einschränkungen, die in http://dev.mysql.com/doc/refman/5.0/en/ ... layed.html im Detail beschrieben sind. Wie auch immer: INSERT DELAYED drückt Daten in einen Insert-Puffer und kommt dann immer sofort mit Erfolgt zurück, während der Puffer irgendwann mal von einem Delayed-Insert-Thread gelesen wird und von diesem mit niederer Priorität in die Tabellen geschoben wird. Genau genommen kann SHOW STATUS einem zeigen, daß es delayed_insert_threads viele solcher Threads gibt, die derzeit not_flushed_delayed_rows an Datenzeilen ausstehen haben (von insgesamt delayed_writes vielen Zeilen, die bisher geschrieben worden sind).

In den Konfigurationsvariablen läßt sich einstellen, wie INSERT DELAYED arbeitet: Alle delayed_insert_limit Zeilen läßt ein Delay-Insert-Thread eventuell anstehenden Select-Statements den Vortritt. Ebenso gibt ein solcher Thread sein Lock auf, wenn sein Puffer leer ist und beendet sich delayed_insert_timeout Sekunden später, wenn er keine neue Arbeit bekommt. Kommen andererseits sehr viele INSERT DELAYED-Zeilen in die Warteschlange, so setzt delayed_queue_size der Anzahl der Zeilen im Puffer eine obere Grenze, damit der Speicher nicht überläuft.

Reminder: Data_free = 0 und Concurrent_inserts

Wir erinnern uns an die Diskussion von SHOW TABLE STATUS und das Feld Data_free dort. Wenn Data_free = 0 ist, und für den Server Concurrent_inserts eingeschaltet sind, dann können auf der Tabelle Inserts und Selects gleichzeitig durchgeführt werden. Insert-Locks können dann nicht entstehen und so können auch keine Selects an die Wand gedrückt werden.

MERGE-Tables oder MySQL 5.1 Partitions können sehr, sehr hilfreich sein, wenn man Tabellen so gestalten will, daß Data_free immer 0 ist und man sich OPTIMIZE TABLE nicht leisten kann.

Wie steht es mit meinem Locking?

Mit Hilfe von SHOW STATUS sieht man seine Table_locks_immediate und Table_locks_waiting. Wenn mehr als 1% (manche Leute sagen 3%) der Table Locks waiting sind, dann hat der Server ein definitives Locking-Problem. Wie ernst das ist, kann man besser beurteilen, wenn man Questions/Uptime (Queries pro Sekunde, qps) berechnet und wenn man (qcache_hits+com_select)/(com_delete+com_insert+com_replace+com_update) (QL/DML-Ratio, "update-heavyness") des Servers kennt.

Wenn mehr als eine Query pro Sekunde wartend ist (1% Table_locks_waiting bei 100 qps oder mehr), dann lohnt es sich, sich mit locklösenden Maßnahmen zu befassen. Locklösende Maßnahmen sind:

- DML schneller machen
- weniger DML pro Sekunde erzeugen
- keine Table Locks mehr verwenden -> InnoDB einsetzen

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 6: Was les ich denn nun mal?

Post by isotopp » 2008-04-11 20:00

krusty007 wrote:gilt das ganze auch für Mysql Version 4.1?


http://dev.mysql.com/doc/refman/4.1/en/binary-log.html.

[edit]Sag mal kannst du mir ein wirklich gutes Mysql Buch empfehlen, welches nicht nur auf Syntax eingeht sonder sich auch mit der Konfiguration des Servers auseinandersetzt? Der Preis ist zweitrangig.


http://www.amazon.de/exec/obidos/ASIN/0672326329/
MySQL Certification Study Guide (4.1, veraltet)

http://www.amazon.de/exec/obidos/ASIN/0672328127/
MySQL Certification Study Guide (5.0, aktuell)

Die Bücher bereiten Dich auch das Bestehen der Prüfungen der MySQL Certification vor.

http://www.amazon.de/exec/obidos/ASIN/0596003064
High Performance MySQL, kein Buch für blutige Anfänger, basierend auf 4.0 und 4.1, vieles auch auf 5.0 anwendbar.



Joe Celko ist langjähriges Mitglied im SQL Standard Komitee. Seine Bücher zum Thema will man gelesen und verstanden haben.

http://www.amazon.de/exec/obidos/ASIN/1558605762
Joe Celko's SQL for Smarties: Advanced SQL Programming
Eine Tour durch das wie und warum von SQL, nicht MySQL spezifisch. Das ist kein Howto, und bezieht sich nicht auf ein konkretes Produkt, sondern auf den Standard.

http://www.amazon.de/exec/obidos/ASIN/0120887975
Joe Celko's SQL for Smarties: SQL Programming Style
Wie man sein SQL schreiben sollte, damit andere nicht kotzen müssen. Dies ist ein Howto über Formatting und Formulierung.

http://www.amazon.de/exec/obidos/ASIN/1558609202
Joe Celko's SQL for Smarties: Trees and Hierarchies in SQL

Dieses Buch kann man nicht lesen, man muß es studieren - hier findet man relativ heftigen Stoff, der sich mit dem Problem der Darstellung von Bäumen in SQL beschäftigt und liefert mindestens drei verschiedene Methoden, Bäume auf SQL zu mappen und wie man zwischen diesen Darstellungen konvertiert. Das Buch setzt sehr sichere und vollständige Beherrschung von SQL und Stored Procedures voraus. Das Buch bezieht sich nicht auf ein konkretes Produkt und ist kein Howto.


http://www.amazon.de/exec/obidos/ASIN/3826606191
Datenbanken: Konzepte und Sprachen

Die meisten Lehrbücher zu Datenbankenvorlesungen saugen tote Hamster durch Strohhalme. Die Bücher von Andreas Heuer und Gunter Saake haben den (Daten-) Hamster bzw. Biber nur auf dem Cover, der Inhalt saugt überhaupt nicht.

Dies ist ein Lehrbuch für eine Univorlesung, und geht über existierende gängige Systeme hinaus bis hin zu objektrelationalen Systemen, zu SQL-99 und anderen Dingen, die man in Produkten noch nicht unbedingt findet.

Dies ist ein Lehrbuch für eine Univorlesung. Es setzt ein funktionierendes Gehirn voraus, und erfordert Bereitschaft zum selbstständigen Denken und Umsetzen. Es ist ein ziemlich gutes Lehrbuch, es ist aber für Leute nicht geeignet, die sich beim Lesen des Wortes Algebra überlegen ob sie nicht doch noch lieber eine Runde CS zocken sollten.

http://www.amazon.de/exec/obidos/ASIN/3826607155
Als Taschenbuch im Kompaktformat

http://www.amazon.de/exec/obidos/ASIN/3826614380
Datenbanken: Implementierungstechniken

Wie man sich seinen Datenbankserver mal eben selber schreibt.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 7: Niemand will Backup, alle wollen Restore.

Post by isotopp » 2008-04-11 20:00

Mein Freund Heinz ist Vertriebler, und manchmal sagen Vertriebler überraschend schlaue Dinge. Heinz zum Beispiel sagt sehr gerne "Niemand will Backup. Alle wollen Restore!" Er meint damit, daß ein Backup nicht nur ein lästiges Costcenter in der IT ist, sondern daß es außerdem keinen Mehrwert "an sich" darstellt. Der Mehrwert liegt nicht in der Datensicherung, sondern in der Wiederherstellung der Daten.

Es ist sehr wichtig, dies im Kopf zu behalten, wenn man sein Backup plant: Es geht nicht wirklich um Datensicherung, sondern es geht um einen Plan für die Recovery von verlorenen Daten. Das beinhaltet nicht nur Ã?berlegungen wie man welche Daten wiederherstellt, sondern auch, wie lange die Betriebsunterbrechung denn wohl dauern wird und ob das akzeptabel ist. Ã?berlegungen zum Restore enden nämlich (im Gegensatz zu Ã?berlegungen zum Backup) oft auch in HA-Konzepten oder organisatorischen und personellen Problemstellungen.

Ein guter Plan

Zwei Ã?berlegungen gehen jeder Backup -- Entschuldigung! -- Recoveryplanung voran. Der erste Gedanke ist: Was brauche ich wirklich um für den Kunden "operational" auszusehen? Wir bezeichnen diese Daten und die Systeme, die sie halten, als "P-Daten" und "P-Systeme" ("P" steht für "Production"). Diese Daten müssen organisatorisch und technisch eine Einheit sein in der Form, daß sie möglichst schnell wieder verfügbar sind. Es handelt sich dabei meistens um OLTP-Daten (Online Transaction Processing) aktueller Natur.

In einem Webshop sind diese Daten zum Beispiel der Katalog und Bestand, die notwendig sind, damit der Kunde auswählen kann, und die noch nicht bearbeiteten Orders und unbezahlte Rechnungen, die für den Versand und das Inkasso notwendig sind.

In einem Monitoring-System sind dies die aktuellen Zustände aller Systeme, und die offenen Tickets und Probleme.

Historische Daten, die nicht für die Produktion notwendig sind, sondern lediglich produktionsunterstützend verwendet werden, brauchen nicht so schnell wieder online zu sein. Meist handelt es sich um historische Daten, aus denen Statistiken für den Vertrieb, den Endkunden oder andere Nutzer generiert werden. Dies sind "PS-Daten" und "PS-Systeme" (Production Support).

In einem Webshop sind dies die Orderlogs mit erfüllten Ordern und alten Rechnungen, und die daraus generierten (und regenerierbaren) Statistikdaten.

In einem Monitoring-System wären dies die historischen Daten über Systemzustände und Performance.

Diese Daten sind wichtig, aber nicht essentiell für das Funktionieren des Kerngeschäftes.

Indem man diese Daten sinnvoll trennt, kann man Prozeduren entwickeln, die es einem erlauben, die Kerndaten von den "Nice to have"-Daten zu trennen und so bei großen Datenbeständen relativ schnell wieder zu recovern.


Konfiguration mitsichern

MySQL speichert seine Daten alle im Datadir. Legt man dort (oder eine Ebene höher) auch seine Konfiguration ab, kann man durch ein Sichern von Datadir (bzw. der Ebene höher) einen Komplettabzug aller seiner logischen Datenbankschemata bekommen.

Damit meine Datenbank auch mit der my.cnf in meinem Datadir startet und NUR mit dieser Konfiguration läuft, verwende ich ein Startscript wie dieses hier:

Code: Select all

linux:/export/data/rootforum # cat start
#! /bin/bash --

MYSQL_CMD_DIR=/usr/local/mysql-max-5.0.18-linux-i686-glibc23
MYSQL_DIR=/export/data/rootforum
MYSQL_UNIX_PORT=$MYSQL_DIR/mysql.sock
MYSQL_TCP_PORT=3340
export MYSQL_UNIX_PORT MYSQL_TCP_PORT MYSQL_DIR

cd $MYSQL_CMD_DIR
$MYSQL_CMD_DIR/bin/mysqld_safe --defaults-file=$MYSQL_DIR/my.cnf --datadir=$MYSQL_DIR/data &


Dieses Script startet den mysql_safe mit dem gegebenen Datadir und erzwingt dabei die Benutzung des korrekten Konfigurationsfiles. Es erzwingt auch die Verwendung eines alternativen Ports und Unix Domain Sockets, da ich auf meinem System eine ganze Reihe von MySQL-Instanzen laufen habe.

Das Stopscript dazu findet einfach das PID-File in $MYSQL_DIR/data/*pid, und sendet ein "kill -TERM" an diesen Prozeß. MySQL schreibt dann seine Puffer geordnet auf Disk und beendet sich. Man kann verifizieren, ob MySQL sich beendet hat, indem man wartet bis das PID-File verschwunden ist.

Code: Select all

#! /bin/bash --

MYSQL_DIR=/export/data/rootforum
MYSQL_PID=$MYSQL_DIR/data/*pid

# No Pid, no process - we are done
[ ! -f $MYSQL_PID ] && exit 0

# No Pid in file, no process, we are done
pid=$(cat $MYSQL_DIR/data/*pid)
if [ -z "$pid" ]
then
  rm $MYSQL_PID
  exit 0
fi

# Stop server
kill -TERM $pid

# Wait for server stop
while [ -f $MYSQL_PID ]
do
  sleep 1
done


Das Binlog

In Datadir befinden sich normalerweise die Unterverzeichnisse, die die logischen Datenbanken enthalten, die eigentlichen Daten, die von den einzelnen Storage-Engines in Dateien abgelegt werden, die Engine-spezifisch sind und die verschiedenen Logfiles. Für eine Datensicherung ist es vor allen Dingen wichtig, daß das Binlog angeschaltet ist. Das ist am einfachsten zu erreichen, indem man in seiner my.cnf-Datei einen Eintrag für das Binlog macht. Trägt man nur einen Schalter "log-bin" ein, wird das Binlog mit <hostname>-bin.xxxxxx erzeugt, wobei xxxxxx eine laufende Nummer ist. Es ist besser, dem Binlog gleich einen festen Namen zu geben, der sich nicht ändert, wenn sich der Hostname der Maschine ändert.

Außerdem kann man die Option sync_binlog=1 setzen, die bewirkt, daß das Binlog nach jedem Kommando bzw. jeder Transaktion auch auf Disk geflushed wird. Das erzeugt ein wenig mehr Disk-Aktivität beim Schreiben des Binlog, stellt aber sicher, daß keine Kommandos am Ende des Binlogs fehlen, wenn man einen Crash erlebt.

Code: Select all

[mysqld]
log-bin=linux-bin
sync_binlog=1


Nachdem man dies konfiguriert und den Server einmal angehalten und neu gestartet hat, um die Konfiguration zu aktivieren, bekommt man eine Reihe von Binlog-Dateien und eine Indexdatei, die ein Verzeichnis dieser Dateien ist.

Code: Select all

linux:/export/data/rootforum/data # ls -l linux-bin*
-rw-rw----  1 mysql mysql 1034 Feb 13 23:07 linux-bin.000001
-rw-rw----  1 mysql mysql  421 Feb 14 09:06 linux-bin.000002
-rw-rw----  1 mysql mysql 1940 Feb 14 12:28 linux-bin.000003
-rw-rw----  1 mysql mysql  117 Feb 14 14:13 linux-bin.000004
-rw-rw----  1 mysql mysql 9666 Feb 20 13:40 linux-bin.000005
-rw-rw----  1 mysql mysql  650 Feb 20 15:29 linux-bin.000006
-rw-rw----  1 mysql mysql   98 Mar  1 10:33 linux-bin.000007
-rw-rw----  1 mysql mysql  133 Mar  1 10:33 linux-bin.index
linux:/export/data/rootforum/data # cat linux-bin.index
./linux-bin.000001
./linux-bin.000002
./linux-bin.000003
./linux-bin.000004
./linux-bin.000005
./linux-bin.000006
./linux-bin.000007


Die Binlog-Dateien enthalten alle Anweisungen, die Daten in Datadir verändern können in der richtigen Reihenfolge. Mit dem Programm mysqlbinlog kann man sie sich ansehen. Neben den eigentlichen Queries findet man dann auch noch weitere Anweisungen, die das Umfeld für diese Queries vorbereiten und einige Kommentare, die es einem erlauben, Queries desselben Threads zu identifizieren und zu isolieren. Außerdem finden sich im Binlog die Resultcodes und Ausführungszeiten für diese Statements als Kommentar.

Ein Beispiel:

Code: Select all

linux:/export/data/rootforum # cat mysqlbinlog-3340
#! /bin/bash --

MYSQL_CMD_DIR=/usr/local/mysql-max-5.0.18-linux-i686-glibc23
MYSQL_DIR=/export/data/rootforum

$MYSQL_CMD_DIR/bin/mysqlbinlog --defaults-file=$MYSQL_DIR/my.cnf $@
linux:/export/data/rootforum # ./mysqlbinlog-3340 data/linux-bin.000006
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#060220 15:10:37 server id 3340  end_log_pos 98         Start: binlog v 4, server v 5.0.18-max-log created 060220 15:10:37 at startup
ROLLBACK;
# at 98
#060220 15:11:43 server id 3340  end_log_pos 179        Query   thread_id=2    exec_time=0      error_code=0
SET TIMESTAMP=1140444703;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
drop database boom;


Das Script mysqlbinlog-3340 ist mein Wrapper für mysqlbinlog, das einen Zeiger auf die passende Konfigurationsdatei und das zu verwendende Binary enthält. Auf diese Weise spare ich etwas Tipparbeit.

Der Aufruf "mysqlbinlog <name der Binlogdatei>" gibt dann den Inhalt dieser Datei als SQL-Source aus. Man kann diese Ausgabe in ein "mysql -u root -p" umleiten und so die Anweisungen erneut ausführen lassen.

Das eigentliche SQL sieht ein wenig komisch aus, ist aber gültig. Kommentare der Form /*! */ schließen dabei Statements ein, die nur ab einer bestimmten Version von MySQL verstanden werden sollen. Die Versionsnummer wird dabei als xyyzz gegeben, etwa mit x=4, yy=00 und zz=19. Das erste Kommando wird also nur dann gelesen, wenn der Dump von einem MySQL 4.0.19 oder neuer verarbeitet wird.

Eine Operation wird immer von einem Kommentar eingeleitet, der die Binlog-Position ("at 98") des Eintrags nennt, und besteht aus einer Reihe von vorbereitenden Kommandos und dann dem eigentlichen Kommando. Die vorbereitenden Kommandos sind immer "use <datenbankname>", wenn notwendig und eine Reihe von "SET"-Statements, darunter auch "SET TIMESTAMP", der die Ausführungszeit des Kommandos als Unix-Timestamp enthält. "SET TIMESTAMP" bewirkt, daß Funktionen wie NOW() bei der Ausführung aus dem Binlog nicht mehr die aktuelle Zeit zurückliefern, sondern die mit "SET TIMESTAMP" gesetzt Zeit. Dabei besteht zur Zeit ein wesentliches Problem mit MySQL 5.0 und der Funktion SYSDATE() - man sollte sich in MySQL 5.0 besser auf NOW() verlassen.

Code: Select all

root@localhost [(none)]> SET TIMESTAMP=1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> SELECT NOW(), SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 1970-01-01 01:00:01 | 2006-03-01 10:55:44 |
+---------------------+---------------------+
1 row in set (0.33 sec)


Point in Time Recovery mit dem Binlog

Man kann das Binlog nutzen, um nach einem Crash eine Point-in-Time Recovery zu fahren. Zu diesem Zweck benötigt man ein Vollbackup, und alle Binlogs, die seit diesem Zeitpunkt angefallen sind. Damit die Binlogs eine bessere Ã?berlebenschance haben, wenn das System hard crashen sollte, legt man sie meistens auf einem anderen Dateisystem ab als das eigentliche Datadir. Angenommen, man installiert seine Datenbanken alle nach /export, dann würde man seine Partionierung so haben wollen:

Code: Select all

linux:/export/data/rootforum # df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/system-root
          reiserfs    8.0G  6.2G  1.9G  77% /
tmpfs        tmpfs    506M     0  506M   0% /dev/shm
/dev/sda5     ext2     99M  8.9M   85M  10% /boot
/dev/mapper/system-home
          reiserfs    8.1G  6.6G  1.6G  81% /home
/dev/mapper/system-data
          reiserfs    600G  520G   83G  87% /export/data
/dev/mapper/system-log
          reiserfs    600G  5.2G  594G  99% /export/log
linux:/export/data/rootforum # grep log-bin my.cnf
log-bin=/export/log/rootforum/linux-bin


Angenommen man macht eine Vollsicherung von /export/data/rootforum am Montag, und sichert dann jeden Tag die Inhalte von /export/log/rootforum als inkrementelles Backup weg, dann kann man die Datenbank wie folgt rekonstruieren:

1. Falls nur /export/data verloren ist, rekonstruiert man /export/data vom Montag aus der Vollsicherung und spielt dann die Binlogs von Montag bis Freitag mit mysqlbinlog wieder ab, indem man sie in einen mysql-Commandline-Client reinpiped. Da das Binlog kontinuierlich aufgezeichnet wird, kann man so bis zum Zeitpunkt des Crashes voran rollen.

Natürlich muß man das Binlog genau ab dem Zeitpunkt abspielen, an dem das Vollbackup endet. Daher ist es wichtig, daß das Vollbackup als Snapshop erzeugt wird, also genau einen Point in der Zeit des Datenbankservers darstellt. Man muß außerdem wissen, welchen Namen dieser Punkt als Binlog-Position ausgedrückt hat. Es gibt verschiedene Möglichkeiten, dies zu erreichen, die weiter unten im einzelnen diskutiert werden.

2. Falls /export/data und /export/log verloren sind, muß man beide Verzeichnisse wieder herstellen und kann nur bis zum Zeitpunkt des letzten inkrementellen Backups wieder voran rollen. Daher wäre es schön, wenn man das Binlog nicht nur auf einer lokalen Platte sichern könnte, sondern auch noch einen Mechanismus hätte, der das Binlog "live" absaugt und woanders abspeichert.

Dieser Mechanismus ist genau ein Replication Slave. Einen Slave setzt man auf, indem man ein Vollbackup des Masters auf einem anderen Rechner einspielt, und als Datenbankserver startet. Auf dem Master erlaubt man dem Slave jetzt das Login mit dem Recht das Binlog abzusaugen:

Code: Select all

root@master [rootforum]> CREATE USER "slave" IDENTIFIED BY "pukcab";

root@master [rootforum]> GRANT REPLICATION SLAVE ON *.* TO "slave";


Dem Slave teilt man mit, wo sich der Master befindet und wie man sich dort einloggen kann. Außerdem muß der Slave natürlich wissen, auf welchem Stand er gerade ist.

Code: Select all

root@slave [rootforum]> CHANGE MASTER TO MASTER_HOST="master", 
    -> MASTER_PORT=3306,
    -> MASTER_USER="slave",
    -> MASTER_PASSWORD="pukcab";
root@slave [rootforum]> CHANGE MASTER TO MASTER_LOG="linux-bin.000001",
    -> MASTER_POS=98;


Die Binlogs ab File 1, Position 98 müssen auf dem Master nun noch bereitstehen. Der Slave wird sich nach einem "START SLAVE IO_THREAD" auf dem Master einloggen und damit beginnen, diese Files zu sich runterzuladen und sie im Slave abzuspeichern. Nach einem "START SLAVE SQL_THREAD" wird er außerdem damit beginnen, sie auszuführen. Mit "SHOW SLAVE STATUS" kann man den Slave dabei beobachten, wie er die Binlogs vom Master absaugt und lokal abspielt.

Mit dem Slave hat man also nicht nur einen Binlog-Sauger gebaut, sondern der Slave spielt die Binlogs auch gleich ab, sodaß man zur Recovery einfach nur das Datadir vom Slave nehmen und auf den Master transportieren muß. Das ist die schnellste und bequemste Methode der Recovery.

Physikalisches Vollbackup erstellen

Ein binäres Vollbackup ist einfach ein Abzug von Datadir, in unserem Beispiel also /export/data/rootforum. Dabei muß lediglich sichergestellt sein, daß die Datenbank ihre Finger aus dem Datadir fernhält während das Backup läuft. Am einfachsten kann man das erreichen, indem man die Datenbank vor dem Backup runterfährt und nach dem Backup wieder startet. Nur so kann man sicher erreichen, daß auch alle Daten auf der Platte und konsistent sind, während das Backup gemacht wird.

Wird lediglich MyISAM verwendet und kommt InnoDB nicht zum Einsatz, kann man statt des Runterfahrens auch "FLUSH TABLES WITH READ LOCK" machen - die Verbindung mit diesem Kommando muß aber während des ganzen Backup stehen bleiben, denn das "UNLOCK TABLES" wird automatisch durchgeführt, wenn die Verbindung des "FLUSH TABLES WITH READ LOCK"-Prozesses beendet wird. "FLUSH TABLES" wirkt aber nicht auf InnoDB, sodaß man bei Verwendung von InnoDB sowieso die Datenbank runterfahren muß.

Damit die Downtime der Datenbank möglichst klein ist, verwendet man am besten einen Volume Manager mit Snapshots. Das Backup kann dann so ausgeführt werden:

Code: Select all

#! /bin/sh --

SNAPSIZE=4G
pass=geheim

cd /export/data/rootforum

# Optional: Neues Binlog anfangen
./mysql-3340 -u root -p$pass 'flush logs'

# Snap database
./stop
name=$(tail -1 /export/log/rootforum/linux-bin.index)
size=$(stat -c '%s' $name)
echo "binlog position $name $size" > /export/data/rootforum/data/restore.info
lvcreate -s -L $SNAPSIZE -n snap /dev/system/data
./start

# Backup snapshot

mount /dev/system/snap /export/snap
dobackup /export/snap/rootforum
umount /export/snap
lvchange -a n /dev/system/snap
lvremove /dev/system/snap

# Optional: Binlog kürzen
./mysql-3340 -u root -p$pass -e "purge master logs to '$name'"


Das Script hält erzeugt erst einmal ein neues Binlog, indem es ein "FLUSH LOGS"-Kommando absetzt.

Danach hält es die Datenbank mit dem Stopscript kurz an. Sobald das der Fall ist, kann die zum Backup gehörende Binlog-Position bestimmt werden und im Backup abgespeichert werden. Die zum Backup passende Binlog-Position bestimmt man dabei am einfachsten, indem man den Namen und die Größe der letzten im Index stehenden Binlog-Datei aufzeichnet. Das Binlog ist gerade neu erzeugt worden, wird also relativ klein sein.

Mit Hilfe von Linux LVM wird nun ein Snapshot des Dateisystems erzeugt. Das geht sehr schnell, und braucht fast keine Zeit. Die Datenbank kann dann neu gestartet werden und ist weiter operabel.

Das eigentliche Backup wird dann erzeugt, indem der Snapshot gemountet wird, und das eigentliche Backupscript dobackup aufgerufen wird, um das Backup auf Band zu schreiben. Danach wird der Snapshot abgemeldet und zerstört.

Sobald dies alles durch ist, ist sichergestellt, daß wir ein neues Vollbackup haben und alle Binlogs, die älter sind als das Backup können mit einem "PURGE MASTER LOGS"-Kommando weggeworfen werden.

Logisches Vollbackup erstellen

Manche Leute bevorzugen statt eines physikalischen Vollbackups der Datenbankdateien ein logisches Backup, also das Backup von SQL-Statements, die den Inhalt der Datenbank neu erzeugen können. Auch das ist mit MySQL möglich, und zwar, indem man "mysqldump" verwendet.

Mit der Option "--master-data" wird dabei ein "CHANGE MASTER" Statement erzeugt, das die Binlog-Position aufzeichnet, die zum Backup paßt. Setzt man "--master-data=2" wird dasselbe Statement auch generiert, aber als Kommentar.

Code: Select all

linux:/export/data/rootforum # ./mysqldump-3340 --master-data rootforum | head -24
-- MySQL dump 10.10
--
-- Host: localhost    Database: rootforum
-- ------------------------------------------------------
-- Server version       5.0.18-max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='linux-bin.000007', MASTER_LOG_POS=879;


Damit das Backup konsistent ist, müssen außerdem die Optionen "--single-transaction" (wenn ausschließlich InnoDB verwendet wird) oder "--lock-all-tables" (geht für alle Tabellentypen, blockiert aber die Datenbank für die Dauer des Dumps) verwendet werden. Die beiden Optionen schließen sich gegenseitig aus. Die Option "--all-databases" sichert dann alle Datenbanken. Damit Trigger und Stored Functions/Stored Procedures auch mit gesichert werden, müssen außerdem die Optionen "--triggers" und "--routines" mit angegeben werden.

Verwendet man "--lock-all-tables", kann man außerdem zusätzlich noch "--flush-logs" und "--delete-master-logs" mitgeben, um vor dem Backup ein "FLUSH LOGS"-Kommando laufen zu lassen und nach dem Backup die Binlogs, die älter als das Backup sind, zu löschen.

Das komplette Kommando kann also so aussehen:

Code: Select all

linux:/export/data/rootforum # cat ./mysqldump-3340
#! /bin/bash --

MYSQL_CMD_DIR=/usr/local/mysql-max-5.0.18-linux-i686-glibc23
MYSQL_DIR=/export/data/rootforum

$MYSQL_CMD_DIR/bin/mysqldump --defaults-file=$MYSQL_DIR/my.cnf "$@"
linux:/export/data/rootforum # ./mysqldump-3340 -u root --master-data --lock-all-tables --triggers --routines --all-databases --flush-logs --delete-master-logs | gzip -9 > /export/backups/backup.sql.gz


Inkrementelle Backups erstellen

Eine inkrementelle Sicherung besteht dann lediglich in einer Sicherung der Binlogs im Logdir. Insbesondere wenn sync_binlogs eingeschaltet sind, ist es also ausreichend, einfach /export/data/rootforum auf Tape zu ziehen - auch bei laufender Datenbank.

Man muß sich überlegen, wie oft man Vollbackups ziehen will. Vollbackups mit dieser Methode blockieren die Datenbank oder erfordern ein kurzes Herunterfahren des Datenbankservers. Inkrementelle Backups können bei laufendem Server gezogen werden. Mit einem Vollbackup kann man jedoch schnell recovern, sind außerdem noch Binlogs abzuspielen, kann die Recovery je nach Menge der Binlogs länger dauern. Je nach Ã?nderungsrate kann es also notwendig werden, öfter Vollbackups zu ziehen.

Backup und Replikation

Eine andere Möglichkeit besteht darin, wie oben angesprochen einen Replication Slave aufzusetzen und die Sicherung des Master-Servers gar nicht mehr durchzuführen. Das Backup auf Tape erfolgt dann durch Runterfahren des Slave, und gemütlicher Sicherung des Slave auf Band. Startet man den Slave neu, zieht er sich die Binlogs vom Master, die während der Downtime angefallen sind, nachträglich runter und fängt an, diese abzuarbeiten. Der Master wird von dieser Aktion niemals in seiner Arbeit unterbrochen oder seiner Leistung beeinträchtigt.

Recovery besteht darin, den Slave runterzufahren und das Datadir des Slave auf den Master zu schieben. Dort muß man dann noch die "master.info"-Datei des Slave löschen und man hat einen neuen Master.

Die Pflege des Master besteht darin, per Cron alle Binlogs zu löschen, die so alt sind, daß der Slave sie garantiert nicht mehr braucht. Man kann im Cron also ein "PURGE MASTER LOGS BEFORE NOW() - INTERVAL 7 DAY" oder so per Script einmal pro Tag abfahren.

Backup mit Replikation ist also bequemer, unterbrechungsfrei und die Recovery ist schneller.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

MySQL 8: Von Storage und Datenbanken

Post by isotopp » 2008-04-11 20:00

Hardware für Datenbankserver

Ein MySQL-Server verwaltet Daten in einer oder mehreren Dateien im Datadir des Servers. Entweder liegen die Daten wie bei MyISAM unsortiert in einer MYD-Datei vor, und eine MYI-Indexdatei enthält Zeiger auf die Datensätze, oder die Daten liegen wie bei InnoDB in einem oder mehreren Tablespace-Files in Primary Key Order sortiert vor, und weitere Indices enthalten Wert-Primary Key-Paare als Zeiger auf die Daten.

In jedem Fall wird der Datenbankserver beim Einfügen von Daten eine Menge von kleinen Schreibzugriffen zum Schreiben der Daten und zum aktualisieren der Indices machen müssen, und beim Lesen entweder die Datendatei von vorne nach hinten ohne Indexunterstützung in großen Schritten durchscannen, oder eine Reihe von kleinen Lesezugriffen auf den Index vornehmen, um dann eine Reihe von weiteren kleinen, ungeordneten Lesezugriffen auf die Daten vorzunehmen.


Ein paar Messungen an Festplatten

Diese Lesezugriffe gehen auf eine Festplatte. Eine Festplatte besteht aus einem Stapel von starren Scheiben, die je nach Modell der Festplatte 4000 bis 20000 mal pro Minute rotieren, und in die ein "Kamm" von Leseköpfen hineingreift, um die Daten von den Scheiben zu lesen. Der Lesekopfkamm muß dazu über der passenden Spur positioniert werden, ein Vorgang, der je nach Modell und zurückzulegender Strecke zwischen 2ms und 20ms dauern kann.

Aus der Beschreibung der Lese- und Schreibzugriffe weiter oben geht schon hervor, daß ein Datenbankserver in den meisten Fällen nicht linear ("durchlesend") auf die Daten einer Festplatte zugreift, sondern in den Daten- und Indexdateien hin- und herspringt. Eine Festplatte in einem Datenbankserver wird also eine ganze Reihe von Kopfbewegungen ("seeks") und kurzen Lese- und Schreibzugriffen sehen.

Der begrenzende Faktor bei Disksubsystemen in Datenbanken ist also in der Regel nicht die Datentransferrate in MB/sec, sondern vielmehr die Anzahl der Seeks pro Sekunde, die die Platte durchführen kann. Der limitierende Faktor ist hier die mittlere Zugriffszeit, die bei zufälligen Lesezugriffen der bestimmende Faktor ist. Wenn eine Festplatte also eine mittlere Zugriffszeit von 8ms hat, kann man von ihr 125 Seeks pro Sekunde erwarten.

Von http://www.iozone.org kann man das gleichnamige Tool herunterladen. Ein kleiner Test mit einer Laptop-Festplatte demonstriert, worum es geht:

Code: Select all

linux:/export/data # /opt/iozone/bin/iozone -+A1 -i2 -s 1024m -r 4k -o -O


Dieses Kommando wird in /export/data eine Datei von "-s 1024m", also einem Gigabyte Größe anlegen. Wegen der Option "-+A1" wird iozone dem OS mit einem madvise()-Call Random-I/O für diese Datei signalisieren. Nach dem Anlegen der Datei wird ein -i2 Random-I/O-Test für diese Datei gemacht. Dabei wird wegen "-r 4k" eine Blockgröße von 4k gewählt. Alle Zugriffe erfolgen wegen "-o" mit O_SYNC, sodaß kein Write-Caching möglich ist, und die Ausgaben wegen "-O" in Operations pro Sekunde.

Zunächst legt iozone die Datei durch lineares Schreiben in 4KB-Blöcken an. Man kann dem Prozeß mit "strace" leicht dabei zusehen:

Code: Select all

write(3, "276276276276276276276276\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
write(3, "276276276276276276276276\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096


In dieser Zeit sehen wir

Code: Select all

linux:~ # iostat -x 1 5
...
avg-cpu:  %user   %nice %system %iowait   %idle
           0.98    0.00   22.55   76.47    0.00

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda          0.00 1752.94  0.00 1100.00    0.00 22823.53     0.00 11411.76    20.75     0.90    0.82   0.77  85.10


Das System ist also ausgelastet, wenn auch meistenteils mit iowait (Warten auf Eintreffen von Daten von der Platte). Wir sehen 1753 Write Requests pro Sekunde, die zu 1100 Writes pro Sekunde auf die Platte werden. Die resultierende Schreibrate ist 11411KB/sec, also 11 MB/sec. Für synchrones Schreiben in 4 KB Blöcken auf eine Laptop-Platte ist das ordentlich. Die Platte ist nicht einmal voll ausgelastet, sondern nur zu 85% busy, ein Schreibzugriff dauert nur 0.77ms, und da die Write-Queue im Schnitt 0.9 Requests lang ist, kommen auf diese Zeit noch mal 0.82ms Wait-Time oben drauf. Nach 1.59ms sind die Daten also auf der Platte.

Beim Lesen sieht es dann so aus:

Code: Select all

avg-cpu:  %user   %nice %system %iowait   %idle
           4.04    0.00    9.09   86.87    0.00

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda          2.02  15.15 416.16  2.02 53527.27  258.59 26763.64   129.29   128.62     4.09    9.29   2.43 101.41


Von der Platte kommen also 416 lineare Reads pro Sekunde, was in einem Datenstrom von 26 MB/sec zu sehen ist. Da die Platte nicht mit noatime gemountet ist, und ein Journaling-Filesystem verwendet wird, sehen wir auch während der reinen Reads ein wenig Schreibaktivität. Ein "mount -o remount,noatime /export/data" während des laufenden Tests behebt das schnell (und verbessert die Zahlen noch einmal).

Soweit Linear-I/O.

Später wechselt der Test von linearem Schreiben auf Random-I/O. Wir können das mit strace leicht überprüfen:

Code: Select all

_llseek(3, 701083648, [701083648], SEEK_SET) = 0
read(3, "276276276276276276276276\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
_llseek(3, 707031040, [707031040], SEEK_SET) = 0
read(3, "276276276276276276276276\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096


Und hier die iostat-Daten der Platte:

Code: Select all

avg-cpu:  %user   %nice %system %iowait   %idle
           7.07    0.00    2.02   90.91    0.00

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda          0.00   0.00 123.23  0.00  985.86    0.00   492.93     0.00     8.00     0.99    8.07   8.03  98.99 8


Die Platte identifiziert sich als "Hitachi HTS72108", laut Datenblatt mit einer Average Seek Time von 8ms. Das kann man dann auch sehr schön an der Service Time von 8.03ms sehen. Dazu kommt dann noch eine Wait-Time pro Request für weitere 8ms, da die Queue in der Regel einen Request tief ist. Die Request-Size ist 8 Blocks a 512 Byte, also die bestellten 4 KB. Und so bekommen wir 123.23 Read-Requests pro Sekunde von der Platte, was in einer Datenrate von nicht ganz 500 KB/sec resultiert.

Schnelle Datenbankzugriffe

Wie man sieht, ist der Schlüssel zur Datenbankperformance, nicht auf die Platte zuzugreifen: Datenbanken brauchen mehr RAM. Wenn dann noch Geld über ist, sollte man es in RAM investieren. Die Zugriffszeiten von RAM liegen im Bereich von ns, das 10E-9 Sekunden. Die Zugriffszeiten von Platten liegen im Bereich von ms, das sind 10E-3 Sekunden. RAM ist also eine Million mal schneller als Platten.

Wenn man doch auf Plattenspeicher zugreifen muß, dann sollte dieser so viele Spindeln wie möglich haben und diese so schnell als möglich rotieren. Es ist wichtiger viele Spindeln zu haben als schnell drehende Platten zu haben. Ein guter Plattenspeicher an einer Datenbank besteht also aus einem RAID mit möglichst vielen kleinen Platten.

RAID und Datenbanken

Der Trick beim Lesen und Schreiben auf Platten in RAIDs besteht darin, die Anzahl von physikalischen Zugriffen auf die Platten klein zu halten und wenn möglich über möglichst viele Platten zu verteilen.

Disk Concatenation ist die Aneinanderreihung von mehreren physikalischen Platten zu einer größeren logischen Platte: Die physikalische Platte 1 hat Blöcke von 0 bis n, die physikalische Platte 2 hat Blöcke von 0 bis m, und die logischen Blöcke 0..n werden auf Platte 1 gemapped, die logischen Blöcke n+1..n+m werden auf Platte 2 gemapped. Für die Leistung bringt dies bei linearem Zugriff gar nichts, denn zunächst wird auf eine, dann auf die andere Platte geschrieben, aber es ist immer nur eine Platte zur Zeit aktiv. Da die Plattenblöcke auch nicht gut durchmischt sind, ist auch beim Datenbankbetrieb die Wahrscheinlichkeit gegeben, daß eine Platte mehr Last als die andere sieht und die Performance ist nicht höher als die Performance einer einzelnen Platte.


RAID-0 ("Striping") ist das Verschränken von mehreren gleich großen physikalischen Platten, sodaß die Zugriffe auf alle Platten gleichmäßig verteilt werden. Platte 1 hat die Blöcke 0..n, Platte 2 ebenfalls von 0..n, und bei Zugriffen auf den logischen Block x wird die Platte (x%2)-1 an der Position int(x/2) angesprochen, oder mit anderen Worten: Alle Zugriffe auf gerade Blöcke gehen auf die eine Platte, alle Zugriffe auf ungerade Blöcke auf die andere. In der Realität erfolgt die Verteilung in der Regel nicht auf Blockebene, sondern auf der Ebene von mehreren Blöcken, Chunks, von einstellbarer Größe. Bei einer Chunksize von 4 KB gehen also die ersten 4 KB auf die Platte 0, die nächsten 4 KB auf die Platte 1 und so weiter.

RAID-0 mit hinreichend kleiner Chunksize lastet also n Platten beim Lesen und Schreiben gleichmäßig aus, und die Lese- und Schreibrate des Arrays ist die aggregierte Lese- und Schreibrate der einzelnen Platten. Das gilt auch für Random-I/O. Mit einem Array von 10 Platten kann man also 10 mal so viele Seeks pro Sekunde erwarten wie von 10 einzelnen Platten dieser Sorte. Darum will man viele kleine Platten, nicht wenig große.

Leider ist ein RAID-0 nicht verfügbar, wenn auch nur eine einzelne Platte in dem Array nicht verfügbar ist. Die Ausfallwahrscheinlichkeiten der einzelnen Platten im Array addieren sich also: Die Ausfallwahrscheinlichkeit eines RAID-0 mit 10 Platten ist 10 mal höher als die Ausfallwahrscheinlichkeit einer einzelnen Platte.


RAID-1 ("Mirroring") ist das Spiegeln zweier Platten, sodaß von jedem logischen Plattenblock x eine Kopie an der Position x aus [0..n] auf Platte 1 und Platte 2 existiert. Schreiben auf solches RAID-1 kann synchron-seriell, synchron-parallel und asynchron-seriell erfolgen.

Bei synchron-seriellem Schreiben wird der Block x zunächst auf Platte 1 geschrieben. Wenn diese Platte Vollzug meldet, wird er auf Platte 2 geschrieben. Wenn auch diese Platte Vollzug meldet, wird der nächste Block zum Schreiben angenommen. Dadurch ist sichergestellt, daß von jedem Plattenblock zu jedem beliebigen Zeitpunkt eine konsistente Version entweder von vor oder nach einem Schreibzugriff existiert, selbst dann, wenn eine Platte mitten im Schreiben eines Blocks stirbt. Die Schreibrate ist hier halb so hoch wie bei einer einzelnen Platte, da effektiv jeder Block zweimal geschrieben wird und diese Schreibvorgänge serialisiert werden.

Bei synchron-parallelem Schreiben wird der Block x zugleich auf die Platten 1 und 2 geschrieben, und erst wenn beide Platten Vollzug melden wird der nächste Block zum Schreiben angenommen. Das ist schneller: Es ist genauso schnell wie das Schreiben auf einer einzelnen Platte in einer einzelnen Kopie, da hier immer zwei Schreibvorgänge parallel ausgeführt werden. Doch da nicht von jedem Block zu jedem Zeitpunkt eine unberührte Kopie existiert, kann es sein, daß in einigen seltenen Fällen zum Zeitpunkt des Crashes keine konsistenze Kopie eines Datenblockes irgendwo im System existiert.

Bei asynchron-seriellem Schreiben wird der Block x zunächst auf Platte 1 geschrieben. Wenn diese Platte Vollzug meldet, wird er auf Platte 2 geschrieben. Zugleich wird jedoch schon der nächste Block an Position y auf Platte 1 geschrieben - diese braucht ja nicht auf Platte 2 zu warten. Danach wird y auch auf Platte 2 geschrieben, während auf Platte 1 schon Block z bearbeitet wird. Die Schreibrate ist hier fast genau so hoch wie bei synchron-parallelem Schreiben, dennoch existiert von jedem Block zu jedem Zeitpunkt eine konsistente Kopie entweder vor oder nach der Änderung.

Beim Lesen können die Datenblöcke von allen Platten parallel gelesen werden. RAID-1 ist beim Lesen also zweimal so schnell wie eine einzelne Platte.

Fällt eine Platte aus, degeneriert ein RAID-1 zu einer einzelnen Platte, schreibt also genauso schnell wie immer und liest mit der Geschwindigkeit einer einzelnen Platte (also halb so schnell wie normal).


RAID-0+1 und RAID-1+0 ("RAID-10") sind eine Kombination von RAID-1 und RAID-0. RAID-0+1 ist ein RAID-1 Mirror aus zwei RAID-0 Stripes, RAID-10 ist ein RAID-0 Stripe aus RAID-1 Mirrors. RAID-10 ist die bevorzugte Konstruktion: Wenn in einem RAID-0+1 eine Platte ausfällt, ist einer der beiden RAID-0 Stripes komplett weg, und der Ausfall einer zweiten Platte killt das ganze Array. Dagegen ist bei einem RAID-10 der Ausfall einer zweiten Platte unkritisch, es sei denn, es ist genau die Platte die der ersten ausgefallenen Platte gegenüber liegt.


RAID-4 und RAID-5 sind zwei sehr ähnliche Verfahren, die weniger Overhead als RAID-1 haben, aber ganz anderes Performanceverhalten zeigen. In RAID-4 werden n Disks (wir nehmen im Beispiel einmal 3 Disks an) und eine Paritydisk zusammengefaßt. Die Blöcke (eigentlich wieder Chunks) x, y und z werden auf die Platten 1, 2 und 3 geschreiben, und auf der Platte 4 wird p = (x xor y xor z) gespeichert.

Fällt irgendeine Disk aus, kann man mit Hilfe der Parity-Information den Inhalt des fehlenden Chunks rekonstruieren. Angenommen Platte 2 stirbt. Kennt man p, x und z, kann man y = (p xor x xor z) = (x xor y xor z xor x xor z) berechnen: x xor x = 0 und z xor z = 0 heben sich weg, sodaß das fehlende y zurückbleibt.

Beim Schreiben muß man zwischen zwei Fällen unterscheiden. Im allgemeinen Fall ("short write") soll irgendetwas Kleines geändert werden, etwa x in x'. Die Änderung läuft nun so ab:

Code: Select all

1. Read x.
2. Read p = (x xor y xor z).
3. Calculate p' = p xor x xor x' = (x' xor y xor z).
4. Write x.
5. Write p.


Aus einem logischen "Write x'" werden also physikalisch zwei Lese- und zwei Schreibzugriffe, oder ein Overhead von 400%.

Werden alle Chunks in einer Raid-Line überschrieben, ist eine Optimierung möglich ("long write"). Zum Schreiben von x', y' und z' reicht es aus, das folgende zu tun:

Code: Select all

1. Write x'.
2. Write y'.
3. Write z'.
4. Calculate p' = (x' xor y' xor z').
5. Write p'.


Aus drei logischen Writes werden also vier physikalische Writes, oder ein Overhead von ((n+1)/n)*100%.

Bei RAID-4 zieht außerdem jeder Write auf irgendeine der n Disks außerdem ein Write auf die Parity-Disk nach sich. Bei RAID-5 verteilt man die Daten der Parity Disk daher auf alle anderen Disks: Für die erste RAID-Line ist die Platte 4 Parity Disk, für die nächste RAID-Line steht p auf Platte 3, danach auf Platte 2 und so weiter. Dadurch verteilen sich die Parity Writes auf alle Platten und das RAID wird schneller.

Während RAID-5 für schreibende Fileserver also okay ist, weil dort bei geeignet kleiner Chunk-Size entsprechend viele Long Writes generiert werden, machen schreibende Datenbankserver fast immer Short Writes, und das ist tödlich langsam. Das geht so weit, daß ich bei einem Kunden mit einem "/opt/iozone/bin/iozone -+A1 -i2 -s 1024m -r 4k -o -O" auf meine einzelne SATA-Laptop-Festplatte ein 2x (5+1) + 3 Hotspare RAID-5 aus Ultra-320 SCSI Festplatten überholt habe. Ohne Mühe - mein Benchmark war durch, während das Kundenarray noch das Testfile angelegt hat.

Beim Lesen ist ein RAID-5 mit n+1 Disks so schnell wie ein RAID-0 mit n Disks, solange das Array nicht degraded ist, also alle Platten verfügbar sind. Wenn das Array degraded ist, wird jedes Read auf den fehlenden Chunk zu einem Read auf alle anderen Chunks in dieser RAID-Line, da der fehlende Chunk y ja durch das XOR aller anderen Chunks simuliert werden muß. Es ist daher sehr wichtig, daß die defekte Platte in einem RAID-5 so schnell als möglich ersetzt wird. Daher sollte man ein RAID-5 immer mit Hot Spare Disks einrichten, sodaß beim Ausfall einer Platte die Hotspare automatisch nachrückt und das Array sofort regenerieren kann.


Wir halten als Kaufempfehlung fest:

Code: Select all

0. Kauft mehr RAM. 
   Baut 64 Bit-Kisten, damit Ihr das RAM auch ansprechen könnt, wenn es mehr als 4 GB sind.
1. Viele Spindeln sind besser als große Platten.
2. RAID-10 ist besser als RAID-01
3. Für Datenbanken, die nicht sehr, sehr read-heavy sind, ist RAID-5 ungeeignet.
4. RAID-5 niemals ohne Hot Spares.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Weitere Texte zu MySQL

Post by isotopp » 2008-04-11 20:01

http://mysqldump.azundris.com/archives/ ... f-LVM.html

This is a quick tour of LVM and a demonstration how it is superior to static partitions. Basically, LVM provides you with a way to create dynamic partitions - you will be able to grow and shrink partitions on demand, move them between disks and snapshot them for backup, all while the filesystem and database on top of it are active and busy.

The LVM tour in this blog post has been created on a vmware instance with a Suse 10.0 Professional installation which I am using to show a combination of RAID and LVM configuration examples. The vmware has a bit of memory, a network card, a boot disk with a text only Suse 10 installation and 8 small simulated SCSI disks besides the boot disk to demonstrate stuff.

...

http://blog.koehntopp.de/archives/1360- ... erger.html

Seit MySQL 4.1 existiert in MySQL Support für Zeichensätze und Sortierreihenfolgen. Das bedeutet im Wesentlichen, daß an jedem CHAR, VARCHAR und TEXT in der Datenbank und an jeder Stringkonstante dranklebt, in welcher Codierung der String vorliegt und mit welcher Sortierreihenfolge der String beim Sortieren und Vergleichen zu behandeln ist.

Vokabeln

Aber zuerst einmal ein bischen Vokabular:

Ein Zeichensatz ist eine Sammlung von Symbolen, die verwendet werden dürfen. Zeichen in einem Zeichensatz haben Namen wie "Copyright Symbol" oder "Lowercase O-Umlaut".

Eine Codierung ist eine Definition, in welcher Bytefolge ein Symbol zu codieren ist. In Latin1-Codierung zum Beispiel ist ein Lowercase O-Umlaut Symbol als 0xF6 codiert, während es in UTF8 als 0xC3B6 codiert wird. In MySQL wird eine Codierung fälschlicherweise als Charset bezeichnet.

Ein Font ist eine Definition, wie ein bestimmtes Symbol auszusehen hat. Ein Lowercase O-Umlaut kann zum Beispiel als ö oder als ö dargestellt werden.

Eine Collation definiert eine Vergleichsfunktion für einen Zeichensatz. Eine binäre Collation definiert die Vergleichsfunktion für zwei Zeichen über die Binärwerte ihrer Codierung, sortiert die Umlaute und andere Sonderzeichen also ziemlich durcheinander und recht willkürlich weit oberhalb der normalen Zeichen. Die Default-Collations für alle Zeichensätze in MySQL sind "CI" (case-insensitive) - das ist ungewöhnlich für SQL und unterscheidet MySQL von fast allen anderen SQL-Produkten. Zum Glück ist das nur ein Default und kann leicht geändert werden.

...

http://blog.koehntopp.de/archives/1424- ... lagen.html

Markus fragt:
"Durch ... wechseln wir gerade unseren Root-Server. Dort habe ich bereits das Gentoo-Basissystem am fluppen. Bei der Umstellung möchte ich gleich von MySQL 4 auf 5 wechseln.

Dabei stellt sich die Frage, ob ich das System nicht gleich von latin1 auf utf-8 umstellen soll. Sollte ich lieber bei latin1 bleiben und alles so migrieren oder doch den großen Wurf zu utf-8 wagen?"

Die Frage habe ich ihm schon beantwortet, aber versprochen, das Thema noch einmal im Blog "in groß" durchzudeklinieren.

...

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: MySQL: Artikelserie von Isotopp

Post by isotopp » 2010-02-05 12:49