KK10_15 MySQL Tuning
Werbung
Datenbanken 15 MySQL Tuning Karl Meier [email protected] 14.12.2010 Inhalt MySQL Architektur Index Query Performance … 14.12.2010 15 MySQL Tuning 2 1 Architektur Connection management, security SQL parsing, execution, caching, … MyISAM 14.12.2010 InnoDB MEMORY NDB 15 MySQL Tuning 3 Architektur Verschiedene RDBMS Dienste (Netzwerk, Client/Server). connection handling, authentification, security, … MySQL spezifische Dienste, Storage Engine übergreifend. query parsing, analysis, optimization, caching, built-in functions, … Storage Engines mit ihren Vor- und Nachteilen 14.12.2010 15 MySQL Tuning 4 2 Architektur 14.12.2010 15 MySQL Tuning 5 Architektur 14.12.2010 15 MySQL Tuning 6 3 Generell Einzelne Queries Tabellendesign nicht optimal Index Query zu aufwändig Abhilfe: Slow-Query-Log und Analyse mit EXPLAIN Ganze Datenbank Hardware Installations-“Fehler“ 14.12.2010 15 MySQL Tuning 7 Datenbankschema Normalisiert • Vorteilhaft für OLTP-Anwendungen –Transaktionen –viele Schreiboperationen • Minderung von Redundanz –Weniger Speicherverbrauch –Viele Joins (kann teuer werden) –Viele Indices 14.12.2010 15 MySQL Tuning 8 4 Datenbankschema Unnormalisiert DWH/OLAP, Berichtserstellung – Historisch korrekte Daten: • Nicht der aktuelle Preis interessiert, sondern der damalige Verkaufspreis • Nicht die aktuelle Kundenanschrift interessiert, sondern die PLZ der damaligen Lieferadresse – Keine Joins mehr: Übergang vom Seek zum Scan 14.12.2010 15 MySQL Tuning 9 Datentypen Vorsicht bei der Deklaration von Zahlenwerten Speicherplatzbelegung, Attribut UNSIGNED verwenden Vorsicht bei der Deklaration von Zeichenketten CHAR meist mit besserer Zugriffsgeschwindigkeit als VARCHAR ENUM als Alternative bei begrenzter Menge Vorsicht beim Speichern binärer Daten BLOB beeinträchtigt die Performance sehr stark Alternative: Link auf externe Datei 14.12.2010 15 MySQL Tuning 10 5 Deklaration Folgendes SQL Statement bietet Hilfe bei der Deklaration von Datentypen. SELECT spalte(n) FROM tabelle PROCEDURE ANALYSE(); Diese Prozedur liefert die minimalen und maximalen Werte sowie die Längen der Spalten zurück und ermittelt den optimalen Datentyp: ( Field_name, Min_value, Max_value, Min_length, Max_length, Empties_or_zeros, Nulls, Avg_value_or_avg_length, Std, Optimal_fieldtype ) 14.12.2010 15 MySQL Tuning 11 Storage Engines Locking Concurrency Overhead Engines Table locks gering gering MyISAM, MEMORY, MERGE Page locks mittel mittel BDB Row locks (MVCC) hoch hoch InnoDB Concurrency und Overhead können die Performance beeinflussen. MVCC: Multi-Version Concurrency Control 14.12.2010 15 MySQL Tuning 12 6 Multi-Version Concurrency Control MVCC genutzt von InnoDB, PostgreSQL und Oracle Versioning Zusatz zum Row Level Locking 2 versteckte Werte zu jedem Datensatz: creation ID deletion ID Die database version (bzw. system version) ist ein Zahlenwert, der bei jedem Transaktionsstart erhöht wird Resultat des MVCC ist, dass Read Queries niemals Tabellen, Pages oder Datensätze sperren. 14.12.2010 15 MySQL Tuning 13 Deadlocks Wenn mehrere Transationen Datensätze sperren, kann dies zu Deadlocks führen. mysql> show variables; +---------------------------------+------| Variable_name | Value +---------------------------------+------| … | … | innodb_lock_wait_timeout | 50 | … | … 14.12.2010 15 MySQL Tuning 14 7 Tabellenformate transaktionsorientiert: InnoDB nicht transaktionsorientiert: MyISAM, MEMORY Erstere sollten eingesetzt werden, wenn die Konsistenz der Datenbank auf keinen Fall gefährdet werden soll. Transaktionssichere Tabellen sind meist langsamer als das am häufigsten verwendete MyISAM Format. 14.12.2010 15 MySQL Tuning 15 Wahl der richtigen Engine Transaktionen? MERGE MEMORY Fremdschlüssel? Design Analyse MyISAM InnoDB Betrieb Implementation Spezialfunktionen? CSV 14.12.2010 Sicherheit? Backup? 15 MySQL Tuning NDB 16 8 Index Auch bei MySQL ist die Indizierung von Daten eine der einfachsten und effizientesten Massnahmen zur Steigerung der Performance. Die Praxis zeigt, dass etwa 90% aller Probleme bei MySQL wegen mangelhafter Indizes auftreten. Da jeder Index auf ein bestimmtes Datenfeld aber auch den Datenbestand erhöht, gilt es, eine optimale Balance zwischen Speicherbedarf und Performance zu finden. 14.12.2010 15 MySQL Tuning 17 Index Mehrspalten Index: • Die gesuchten Daten stehen im Index • Keine Seeks! • select a from t where b = ? • normaler Index wäre (b), • „Covering Index“ ist (b,a) 14.12.2010 15 MySQL Tuning 18 9 B-Tree Index – Default Index (ausser für MEMORY-Engine) – Beschleunigt “=” Zugriffe, Bereichsabfragen und Sortierung • Ein BTREE-Index auf (a, b, id) beschleunigt select select select select select select select id id id id id id id from from from from from from from table table table table table table table where where where where where where order 14.12.2010 a = a = a = a = a = a = by a ? ? ? ? ? ? and b order and b and b and b = ? by b between ? and ? > ? like ‘prefix%’ 15 MySQL Tuning 19 Indexoptimierung MySQL Typische Überindizierung: INDEX (A) INDEX (A,B) OPTIMIZE TABLE • um Indizes zusammenzuführen und zu sortieren ANALYZE TABLE • Statistiken für Optimizer • notwendig, wenn sich Datenverteilung ändert 14.12.2010 15 MySQL Tuning 20 10 SHOW TABLE STATUS mysql> show table status like "user" \G *************************** 1. row ******************** Name: user Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 8 Avg_row_length: 76 Data_length: 608 Max_data_length: 4294967295 Index_length: 2048 Data_free: 0 Auto_increment: NULL Create_time: 2004-10-23 09:51:29 Update_time: 2005-01-24 15:57:45 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 1 row in set (0.04 sec) 14.12.2010 15 MySQL Tuning 21 Indexauswahl Ein wichtiger Schritt zur Optimierung ist die Auswahl der Spalten zur Indexierung. Es gibt zwei Orte, welche sich für Indizes empfehlen: Spalten, in der WHERE Klausel referenziert Spalten, in JOIN Klauseln verwendet 14.12.2010 15 MySQL Tuning 22 11 Beispiele SELECT FROM WHERE AND ort telbuch vorname=`Martin` name=`Huber`; SELECT FROM LEFT JOIN ON WHERE AND 14.12.2010 # Kein Index # Index # Index telbuch.plz ort.name telbuch ort telbuch.plz=ort.plz vorname=`Martin` name=`Huber`; # Kein Index # Kein Index # Index ort.plz # Index # Index 15 MySQL Tuning 23 Abgrenzungen „Dann indexiere ich doch alle Spalten der WHERE und JOIN Klauseln!?“ - Stimmt eben nicht immer! MySQL benutzt den Index nur für `<`, `<=`, `=`, `>`, `>=`, BETWEEN, IN und LIKE LIKE funktioniert nur, wenn der erste Charakter keine Wildcard ( % oder _ ) ist. SELECT id FROM telbuch WHERE name LIKE `Hub%`; SELECT id FROM telbuch WHERE name LIKE `%ber`; 14.12.2010 15 MySQL Tuning 24 12 EXPLAIN Eine Frage bleibt, welches ist der effizienteste Index? MySQL bietet ein integriertes SQL Statement, um eben diese Frage zu klären. EXPLAIN SELECT * FROM t_ma WHERE vname=`Martin` AND name=`Huber` \G EXPLAIN EXTENDED 14.12.2010 *********** 1. row *********** id: 1 select_type: SIMPLE table: t_ma type: ref possible_keys: name,i_nv key: i_nv key_len: 71 ref: const,const rows: 1 Extra: Using where 1 row in set (0.00 sec) 15 MySQL Tuning 25 Nachteile Jeder Index belegt Platz auf der Festplatte Ist in der Regel kein grosses Problem, ausser, wenn alle Spalten in jeder möglich Kombination indexiert werden sollen. Schreiboperationen verlieren an Performance Bei DELETE, UPDATE und INSERT ändern nicht nur die Daten, sondern es müssen auch alle tangierten Indizes aktualisiert werden. 14.12.2010 15 MySQL Tuning 26 13 Ineffiziente Abfragen Query Tuning Zuerst werden immer Tuning Möglichkeiten gesucht, deren Effekt lokal und absehbar ist. Optimierung der SQL-Abfragen gehört in diese Kategorie. Hinweise zu nicht optimalen Queries liefern ein Query Analyzer, Traces oder I/O Messungen, welche auf unzählige Plattenzugriffe hinweisen. 14.12.2010 15 MySQL Tuning 27 Query Cache query_cache_type = ( 0 | 1 | 2 ) bzw. ( OFF | ON | DEMAND ) MySQL sucht das Resultat einer SELECT Abfrage zuerst im Cache bevor das Statement analysiert und ausgeführt wird. Dazu wird das Query gehasht und der Hashwert mit dem Inhalt des Caches verglichen. SELECT * FROM t_ma ≠ select * from t_ma SELECT SQL_NO_CACHE * FROM t_ma; SELECT SQL_CACHE * FROM t_ma; 14.12.2010 15 MySQL Tuning (1) (2) 28 14 Query Cache Wenn sich Daten relativ selten ändern SELECT Statements müssen exakt gleich sein SELECT Statements dürfen keine benutzerdefinierten Variablen enthalten SELECT Statements dürfen bestimmte Funktionen nicht beinhalten: RAND, NOW, CURDATE, CURTIME, … MySQL berücksichtigt nur SELECT Abfragen. Anfänglich musste SEL am Anfang stehen. Seit MySQL 5.0 funktioniert z.B. auch: /* Kommentar */ SELECT * FROM tabelle WHERE … 14.12.2010 15 MySQL Tuning 29 Query Cache Die Serversystemvariable have_query_cache gibt an, ob der Abfrage Cache verfügbar ist: mysql> show variables like 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.00 sec) 14.12.2010 15 MySQL Tuning 30 15 Query Cache Mehrere Systemvariablen steuern den Betrieb des Query-Caches: mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) 14.12.2010 15 MySQL Tuning 31 Query Cache 14.12.2010 15 MySQL Tuning 32 16 Parsing, Analyse, Optimierung Syntaktische Korrektheit wird überprüft Basisinformationen Query Typ? SELECT, INSERT, UPDATE, DELETE Welche Tabellen sind betroffen? Aliases? Was ist die Bedingung (WHERE)? Query wird in Basiseinheiten „zerlegt“ 14.12.2010 15 MySQL Tuning 33 Parsing, Analyse, Optimierung Ziel des Query Optimizers ist es, mit der gegebenen Information das effizienteste Query zu finden. Sind für die Abfrage Indizes vorhanden? Welcher Index ist der beste? Tabellenabhängigkeiten? Optimale JOIN Reihenfolge für die Abfrage? Der Optimizer wird laufend weiterentwickelt! 14.12.2010 15 MySQL Tuning 34 17 Query Optimizer Die Aufgabe des Optimizers besteht darin, einen optimalen Plan für die Ausführung einer SQL-Abfrage zu entwickeln. Der Unterschied zwischen „gut“ und „schlecht“ aus leistungstechnischer Sicht kann riesig sein (d. h. Sekunden, Stunden oder sogar Tage. Bei Join-Abfragen wächst die Anzahl möglicher Pläne, die vom Optimizer untersucht werden, exponentiell mit der Anzahl der Tabellen. Werden Abfragen mit mehr als 10 Tabellen abgesetzt, dann kann die für die Optimierung erforderliche Zeit schnell zum Engpass für die Leistung des Servers werden. 14.12.2010 15 MySQL Tuning 35 Search Depth Die Variable optimizer_search_depth sagt dem Optimizer, wie weit er bei unvollständigen Plänen „vorausschauen“ soll. Niedrige Werte für optimizer_search_depth führen zu drastisch kürzeren Abfragekompilierungszeiten. So kann die Kompilierung von Abfragen mit 12, 13 oder mehr Tabellen leicht Stunden oder sogar Tage dauern, wenn optimizer_search_depth einen Wert hat, der annähernd der Anzahl der Tabellen in der Abfrage entspricht. Umgekehrt benötigt der Compiler für dieselbe Abfrage noch nicht einmal eine Minute, wenn diese den Wert 3 oder 4 hat. optimizer_search_depth=0 weist den Optimizer an, den Wert automatisch zu ermitteln. 14.12.2010 15 MySQL Tuning 36 18 SELECT Tuning 1 Keine Operation auf indiziertes Attribut … WHERE listpreis*1.076 > 4000 14.12.2010 … WHERE listpreis > 4000/1.076 besser … WHERE listpreis > 3717.45 optimal 15 MySQL Tuning 37 SELECT Tuning 2 Keine Funktionen auf indiziertes Attribut … WHERE LEFT(name,3) = `Hub` … WHERE name LIKE `Hub%` besser … WHERE name BETWEEN `Hub` AND `Huc` oft noch besser 14.12.2010 15 MySQL Tuning 38 19 SELECT Tuning 3 Reihenfolge ändern … WHERE anrede=`Frau` AND name=`Huber` … WHERE name=`Huber` AND anrede=`Frau` meist besser Annahme: Es gibt mehr Frauen als Personen namens Huber. 14.12.2010 15 MySQL Tuning 39 SELECT Tuning 4 Abfrage mit <> (ungleich) vermeiden … WHERE ort <> `Chur` … WHERE ort < `Chur` OR ort > `Chur` 14.12.2010 15 MySQL Tuning besser 40 20 SELECT Tuning 5 Abfragen mit IS NOT NULL … WHERE bonus IS NOT NULL … WHERE bonus >= 0 14.12.2010 besser 15 MySQL Tuning 41 SELECT Tuning 6 Vermeiden von Datentyp Konversion … WHERE salaer > 70886.95 … WHERE salaer > 70886 Anmerkung: besser salaer ist vom Typ INTEGER 70886.95 ist z.B. der Salärdurchschnitt 14.12.2010 15 MySQL Tuning 42 21 SELECT Tuning 7 UNION statt OR Es wird pro Abfrage und Tabelle nur ein Index verwendet. So wird bei einer Oder-Abfrage mit zwei Attributen mindestens ein Fulltable-Scan nötig. Mit UNION kann für jede Abfrage der ideale Index verwendet werden! 14.12.2010 15 MySQL Tuning 43 JOIN Tuning Optimale Reihenfolge ist entscheidend JOINs sind meist schneller als Subqueries Die Kriterien für SELECT gelten hier auch 14.12.2010 15 MySQL Tuning 44 22 log-slow-queries Falls MySQL mit der Option --log-slow-queries gestartet wurde, protokolliert mysqld alle SQL-Queries, die für die Ausführung länger benötigen als der definierte Wert „long-query-time“. Ausserdem existiert im MySQL Datenverzeichnis das „hostname.err“ Logfile, das Informationen über Crashes oder Probleme aufzeichnet. „optimize table“ kann bei MyISAM und BDB Tabellen zur Defragmentierung verwendet werden. „analyze table“ gibt Aufschluss über Schlüsselverteilung und Reihenfolge der Tabellenverknüpfung. 14.12.2010 15 MySQL Tuning 45 Aber… Herauszufinden, welche Abfragen langsam sind, ist meist einfacher als warum und was kann dagegen getan werden kann. mysqldumpslow Perl-Script 14.12.2010 15 MySQL Tuning 46 23 MySQL Monitoring Tool http://jeremy.zawodny.com/mysql/mytop SHOW PROCESSLIST; 14.12.2010 15 MySQL Tuning 47 Tricks Join Order SELECT * FROM tab1 STRAIGHT_JOIN tab2 WHERE … Index USE INDEX (i1, in), IGNORE INDEX (i1, in), FORCE INDEX (i1) Resultate SQL_BUFFER_RESULTS, SQL_BIG_RESULT, SQL_SMALL_RESULT Query Cache SQL_CACHE, SQL_NO_CACHE 14.12.2010 15 MySQL Tuning 48 24 MyISAM 14.12.2010 15 MySQL Tuning 49 MyISAM 14.12.2010 15 MySQL Tuning 50 25 InnoDB 14.12.2010 15 MySQL Tuning 51 InnoDB 14.12.2010 15 MySQL Tuning 52 26 NDB 14.12.2010 15 MySQL Tuning 53 NDB 14.12.2010 15 MySQL Tuning 54 27 Weitere Tipps zur InnoDB Buffer-Einstellungen Der wahrscheinlich wichtigste Parameter zur Beeinflussung der Geschwindigkeit des InnoDBTabellentreibers ist innodb_buffer_pool_size. RAM-Grösse zur Zwischenspeicherung von InnoDB-Tabellen und -Indizes. Defaultwert „Nur“ 8MB!!! 14.12.2010 15 MySQL Tuning 55 Weitere Tipps zur InnoDB Blockoperationen Umfangreiche Blockoperationen können beschleunigt werden durch: - SET unique_checks=0 - SET foreign_key_checks=0 - SET autocommit=0 Transaktions_Logging_Dateien genug gross! ROLLBACK kann sehr lange dauern! CREATE TABLE wirkt wie COMMIT! 14.12.2010 15 MySQL Tuning 56 28 Weitere Tipps zur InnoDB Logging-Einstellungen innodb_flush_log_at_trx_commit=2 Logging-Dateien auf einer anderen Festplatte als die tablespace-Dateien innodb_flush_method=O_DSYNC 14.12.2010 15 MySQL Tuning 57 MySQL Server Tuning Lohnt sich in der Regel nur, wenn sehr grosse Datenbanken vorliegen (GByte) sehr viele Abfragen pro Sekunde ausgeführt werden der Rechner primär als Datenbank_Server dient 14.12.2010 15 MySQL Tuning 58 29 Optimale Speichernutzung key_buffer_size (8M) table_cache (64) sort_buffer (2M) read_buffer_size (128K) read_rnd_buffer_size (256K) bulk_insert_buffer_size (8M) join_buffer_size (128K) tmp_table_size (32M) max_connection (100) 14.12.2010 15 MySQL Tuning 59 Einfacher Test Die aktuelle Buffer Performance lässt sich berechnen mit Hilfe der vier Variablen: key_read_requests, key_reads, key_write_requests, and key_writes mysql> SHOW STATUS; … | Key_read_requests | Key_reads | Key_write_requests | Key_writes … | | | | 602843 151 1773 805 | | | | FLUSH STATUS; <run query>; SHOW STATUS; Optimal sind: 14.12.2010 key_reads / key_reads_requests < 0.01 key_writes / key_writes_requests < 1 15 MySQL Tuning 60 30 Server Verbindungen 14.12.2010 15 MySQL Tuning 61 Weitere Möglichkeiten Mit weiteren Serveroptionen lässt sich die Performance steigern (wenn i.a. auch nur minim). So ist z.B. MySQL 10% schneller, wenn der Code mit statischen Libraries kompiliert wurde. Auch kann ein plattformspezifischer Compiler Vorteile bringen. Bei der Hardware sind SCSI Festplatten der IDEVariante vorzuziehen. (Diskcharakteristika) Sogar das Betriebssystem hat Einfluss auf die Performance. Bei identischer Hardware soll Linux schneller sein. 14.12.2010 15 MySQL Tuning 62 31 Weitere Infos zum Server Tuning MySQL-Guru Jeremy Zawodny http://jeremy.zawodny.com/mysql 14.12.2010 15 MySQL Tuning 63 Übung Ein Anwender beschwert sich über die Performance eines Reports. Dies obwohl Sie gerade einen neuen Server aufgesetzt haben. Die Daten werden von einem SQL Statement geliefert und sind korrekt. Die Tabellen enthalten 500‘000 Datensätze und werden auch von anderen Systemen benutzt. Welches Vorgehen schlagen Sie vor? 14.12.2010 15 MySQL Tuning 64 32 Richtlinien Only change one parameter at a time. Don't make changes in production. Use real data. Perform realistic tests. Be systematic and record your findings. 14.12.2010 15 MySQL Tuning 65 14.12.2010 15 MySQL Tuning 66 33
