Datenbank Tuning
|
Baustelle: Dieser Artikel ist eine Baustelle. Das heißt, jemand hat sich dieses Artikels angenommen und überarbeitet ihn gerade. |
Ich zeige euch hier ein paar Tipps für das Tuning der Datenbanken.
Hardware
Bei der Hardware kann man generell sagen das es bei Datenbankservern generell nie genug gibt, jedoch ist RAM und und I/O sehr sehr wichtig. Idealerweise sollte die Datenbank komplett in den RAM passen; wenn diese nicht reinpasst, muss die Datenbank auf die langsameren Festplatten zu greifen. Wo wir auf das nächste Thema kommen, die Festplattenperformance ist sehr wichtig. Ich würde euch Festplatten mit 15000U/min empfehlen und diese diese in ein RAID10 packen (mind 4Festplatten dafür erforderlich). In einem RAID10 erreicht man die höchste I/O Performance, Schreib- und Leserate. Die Performance von HDD, auch mit RAID10, kann noch durch Einsatz von SSDs um einiges vervielfacht werden, eine SSD schafft die I/O-Performance von einem RAID10.
Dateisystem
In vielen Tutorials findet man XFS als ideales Datenbankdateisystem, jedoch sind viele Tutorials etwas älter und nicht auf dem aktuellen Stand, mit dem kernel 3.2 sind viele positive Änderungen in XFS aber auch in ext4 eingegangen. Mittlerweile würde ich sagen das XFS und ext4 sich ähnlich sind, aber XFS hat vorteile in den Mountoptionen bzw beim RAID-Tuning. Alle Dateisysteme auf denen die Datenbak läuft sollten mindestens mit der Mountoption noatime gestartet werden. Bei ext4 ist es zu empfehlen wenn man einen RAID-Controller nutzt mit Batterie die Mountoptionen data=writeback und nobarrier noch etwas Performancezuwachs. Zusätzlich kann man etwas mit den Schedulern von Linux etwas herumspielen, bei SSDs könnte sich evtl der NOOP-Scheduler als Vorteil erweisen und bei normalen Platten DEADLINE, während des Betriebes kann man es wie folgt ändern:
root@debian:~# echo deadline > /sys/block/sda/queue/scheduler
um es dauerhaft zu ändern muss in GRUB2 die Datei /etc/default/grub um folgende Zeile erweitert werden:
GRUB_CMDLINE_LINUX="elevator=noop"
Speichertuning
Der Kernel hat sehr viele Einstellungsmöglichkeiten was den Speicher betrifft, für Datenbanken gibt es insgesamt 3 wichtige Parameter, die man anpassen sollte, die 3 sind folgende (alle Dateien befinden sich in /proc/sys/vm/):
| swappiness | Um so kleiner der Wert, desto unwahrscheinlicher ist es, dass der Kernel in den Swap auslagert, 100= lagere soviel wie möglich aus, 1= lagere nur dann aus wenn es absolut nötig ist | als Startwert ist 40 ein guter Anfang |
| dirty_ratio | wenn die %-Zahl (des Gesamt-RAMs) von einem Prozess erreicht worden ist, wird der Cache geleert und auf die Festplatte geschrieben | kann man i.d.R. auf 10 stellen |
| dirty_background_ratio | wenn die %-Zahl (des Gesamt-RAMs) vom Kernel erreicht worden ist, wird der Cache geleert und auf die Festplatte geschrieben | kann man i.d.R. auf 5 stellen |
Statt _ratio kann man auch _bytes nutzen, wenn man _bytes nutzt, wird dieser Wert genommen und nicht _ratio. Dies macht man vor allem dann, wenn man sehr viel RAM hat und 1% immer noch zuviel ist. Alle Werte kann man auch Persistent in der /etc/sysctl.conf einstellen:
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
vm.swappiness = 40
MySQL tunen
MySQL hat sehr viele Stellschrauben um es zu tunen, was einen Neuling schon erschlagen kann. Ein erster Schritt zum Tuning ist das Script tuningprimer.sh. Es wurde direkt von einem mysql Programmierer geschrieben, man sollte dabei die Datenbank mind 48h laufen lassen haben und sie sollte weitestgehend schon normale Last gefahren haben sonst ergibt das Script verfälschte Ergebnisse.
Eine der wichtigeren Parameter sind folgende:
| Schlüssel | Erklärung | Anfangseinstellung |
|---|---|---|
| innodb_buffer_pool_size | sagt wie viel Hauptspeicher MySQL zum Puffern von Operationen nutzen darf. | als Faustregel gilt ca 80% des Arbeitsspeichers |
| innodb_log_file_size | bevor mysql Transaktionen in die Tabellendateien auf der Festplatte schreibt, werden diese in das Innodb-Logfile geschrieben, es ist somit eine Art Puffer <- es zeigt nur Wirkung wenn die Datenbank heruntergefahren wird und das alte Log-File gelöscht wird | sollte als Wert 512-1024MB liegen |
| max_connections | Wieviele Verbindungen (und damit auch Threads) sind generell erlaubt | dieser Wert sollte nicht zu viel, aber auch nicht zu wenig sein |
| table_cache | Der Table-Cache häufiger gebrauchten Tabellen | ein guter Startwert ist 512 |
| query_cache_size | wieviel Speicher wird für die Ergebniss von Abfragen genutzt | |
| key_buffer_size | Der Speicher, der für die referenziellen Keys reserviert wird, dieser Wert gilt pro Connection | ein guter Startwert ist 128M |
| sort_buffer_size | Der Speicher, der für die Sortierung reserviert wird. Dieser Wert gilt pro Connection | für den Anfang 16MB |
Wie man sehen kann hat mysql viele Einstellungsmöglichkeiten, möchte man alle aktuellen Einstellungen sehen (auch die die man noch vornehmen kann bzw deren Defaulteinstellung) einfach folgendes eintippen:
root@debian:~# mysqld --verbose --help
Hier mal eine Beispielconfig mit 24GB RAM:
[mysqld]
# paths
datadir = /var/lib/mysql/data
tmpdir = /tmp/mysqltmp
# network
connect_timeout = 60
wait_timeout = 28800
max_connections = 2048
max_allowed_packet = 64M
max_connect_errors = 1000
# limits
tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 512
# logs
log_error = /var/log/mysql/mysql-error.log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 20
# innodb
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:128M;ibdata2:128M:autoextend:max:4096M
innodb_file_per_table = 1
innodb_status_file = 1
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 14G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_flush_log_at_trx_commit = 2
innodb_support_xa = 0
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
# experimental
innodb_stats_update_need_lock = 0
# other stuff
event_scheduler = 1
query_cache_type = 0
PostgreSQL tunen
PostgreSQL arbeitet grundlegend anders als MySQL, PostgreSQL nutzt shared memory, um den aktuellen sharedmemory wert auszulesen folgenden Befehl eintragen:
ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 32768
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1
Um diesen Wert auf 4GB im laufenden Betrieb zu setzen reicht ein
root@debian:~# echo 4294967296 > /proc/sys/kernel/shmmax
Diese Einstellung geht jedoch bei einem Neustart verloren um es dauerthaft ein zu stellen muss man es dem Kernel mitgeben, einfach in der /etc/sysctl.conf die Zeile kernel.shmmax=4294967296 hinzufügen. Es kann evtl auch sein das eine Anpassung von shmall sein muss, wenn das der Fall ist steht folgendes in der Log-Datei:
FATAL: could not create shared memory segment: No space left on device
Wenn das der Fall ist sollte man shmall erstmal an den selben Wert wie shmmax anpassen.
Für PostgreSQL gibt es ein Tool das erstmal gute Startwerte in die Config-Dateien schreibt, es heisst pgtune, man führt es einfach aus mit den Optionen -i und -o kann man die Input-config-datei und die neue Config-Datei angeben.
Die wichtigsten Parameter für die config-Datei sind die folgenden:
| Schlüssel | Erklärung | Anfangseinstellung |
|---|---|---|
| shared_buffers | sagt wie viel Hauptspeicher PostgreSQL zum Puffern von Operationen nutzen darf | 25-50% des Hauptspeichers |
| effective_cache_size | sagt dem Query-Planer von PostgreSQL wieviel Speicher er für eine Indexerstellung nutzen kann | 50-75% des Hauptspeichers |
| checkpoint_segments | ein Segment ist 16MB gross, es sagt aus wieviele WAL-Segmente existieren dürfen bevor PostgreSQL prüft ob auch wirklich alles was in den WAL-Segmenten steht auch in der Datenbank selbst steht, falls nicht wird hinein geschrieben | 10-256 |
| checkpoint_completion_targets | PostgreSQL versucht schon vorher alle WAL-Segmente in die DB zu schreiben, der Wert 0.5 versucht PostgreSQL alle Operationen in der ersten Hälfte vor dem nächsten Checkpoint ab zu fertigen | 0.7-0.9 |
| work_mem | gibt an wieviel Speicher er pro Verbindung zum bearbeiten nutzen kann. Ist der Wert zu klein eingestellt, schreibt PostgreSQL das alles in einer Datei auf der Festplatte, dies sollte man beobachten und vermeiden, ein zu großer Wert belegt unnötig viel Arbeitsspeicher! Man sollte hier nur klein anfangen. | Standard ist 8MB wenn zu klein auf 12MB gehen und dann langsam in 2MB schritten erhöhen |
| synchronous_commit | bei off wird nicht auf das Beenden des Schreibens gewartet. Datenverluste sind möglich, aber keine beschädigte Datenbank | Standard=on, möglich ist noch off (bis zu 2,5fache Leistung) |
| fsync | aktiviert bzw deaktivert das synchrone schreiben, bei deaktivieren kannes zu Datenverluste führen | Standard= on, möglich ist noch off (bis zu 6fache Leistung, aber nicht zu empfehlen!!) |