Sven Schönhoff

MySQL Optimierung

Früher oder später wird sich jeder MySQL-DBA einmal mit dem Thema Performance Tuning beschäftigen. Der KIWI (kill it with iron)-Ansatz, bei dem Performance Tuning durch Hardwareanschaffungen realisiert wird, kann dabei vermieden werden. Das größte Verbesserungspotential liegt nämlich im Schema-Design, der Applikationslogik + SQL-Statements und dem Feintuning von MySQL-Parametern.

Folge Infos habe ich jahrelang u.a. bei meinem Lotto-Statistik Projekt in Microsoft One-Note gesammelt. Die Abfrage der Lottostatistik für alle Möglichen Kombinationen für einer bis fünfer dauerte ohne Optimierung über 24 Stunden auf meinem Debian Squeeze vServer mit 1GHz und 512MB Ram. Sprungweise konnte ich die Laufzeit auf 3:20 Stunden, ca. 47 Minuten und letztendlich ca. 39 Minuten reduzieren.

Ich werde diese Seite laufend ergänzen, da ich noch zahlreiche weitere Notizen besitze.

Das Datenbankschema

Man sollte die passende Collation wählen

Die Collation eines Schemas lässt sich mit einfachen Mitteln ändern:

ALTER DATABASE svensql4 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

SELECT CONCAT(‚ALTER TABLE svensql4.‘,table_name,‘ COLLATE utf8_general_ci;‘)

FROM information_schema.tables WHERE table_schema = ’svensql4′;

Das erste Statement ändern die Collation des Schemas und das zweite generiert die Statements zum Ändern der Tabellen.

Man sollte NOT-NULL Constraints nutzen, wann immer es möglich ist

NULLable Spalten benötigen einen Bit mehr Speicherplatz und sind daher langsamer.

Datentypen

Der kleinstmögliche Datentyp bringt die beste Performance, da eine Tabelle dadurch signifikant weniger Speicherplatz benötigt. Ein Tinyint verbraucht 1Byte und kann im Normalfall maximal die Zahlen -127 bis 127 speichern, ein Integer benötigt 4 Byte und kann im Normalfall maximal die Zahlen -2147483648 bis 2147483648 speichern. Mit dem Begriff UNSIGNED lässt der Wertebereich jedoch verschieben, so dass man z.B. in einem Tinyint die Werte 0 bis 255 speichern kann.

Beispiel:

CREATE TABLE svensql4.test (

test TINYINT UNSIGNED NOT NULL

) ENGINE = MYISAM ;

Infos zu den Datentypen finden sich unter: http://dev.mysql.com/doc/refman/5.5/en/data-types.html

Indizes

Indizes beschleunigen SELECTs aber verlangsamen INSERTs und UPDATEs, da jedesmal der Index aktualisiert werden muss. Daher erstellt man Indizes nur dort, wo die SELECTs:(INSERTs+UPDATEs)-Ratio hoch ist.

Ob und welche Indizes der MySQL-Optimizer lässt sich über das Statement EXPLAIN EXTENDED prüfen. Ich habe dazu alle möglichen Kombinationen für einen Test angelegt:

EXPLAIN EXTENDED SELECT NULL, anzahl, 4, 10, 20, 26, 28, NULL FROM
(SELECT COUNT(id) AS anzahl FROM(SELECT z.r_id AS id FROM zahlen z
INNER JOIN zeitpunkte t ON z.r_id = t.r_idAND t.datum >= DATE_SUB(CURDATE( ), INTERVAL 10 YEAR)
INNER JOIN zahlen z1 ON z.r_id = z1.r_id AND z1.zahl = 4
INNER JOIN zahlen z2 ON z.r_id = z2.r_id AND z2.zahl = 10
INNER JOIN zahlen z3 ON z.r_id = z3.r_id AND z3.zahl = 20
INNER JOIN zahlen z4 ON z.r_id = z4.r_id AND z4.zahl = 26
INNER JOIN zahlen z5 ON z.r_id = z5.r_id AND z5.zahl = 28
LIMIT 1) auswahl) auswertung WHERE anzahl <> 0;

Man sieht (zum Vergrößern anklicken), ob und welcher Index verwendet wurde. Bei zusammengesetzen Indizes steht das Attribut vorne, welches der meistverwendeten Spalte im Query entspricht. Weiter Infos zu den Begriffen findet man unter http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

Tabellen Defragmentieren

Dieses Skript erzeugt dynamisch SQL-Statements für alle Tabellen, die defragmentiert werden müssen.

SELECT CONCAT(‚OPTIMIZE TABLE ‚, table_schema, ‚.‘, table_name, ‚;‘) FROM information_schema.tables WHERE data_free !=0 AND table_schema != ‚information_schema‘;

Langsame Queries finden

Langsame Queries und Queries, die keine Index nutzen lassen sich sehr komfortabel mit einer Logdatei aufspüren.

Dazu fügt man in die Konfigurationsdatei my.cnf folgendes ein, um alle Queries die länger als 4 Sekunden benötigen oder keinen Index benutzen in die Datei slow.log zu speichern:

log-slow-queries = slow.log

long_query_time = 4

log-queries-not-using-indexes

SQL

MySQL-Parameter in Konfigurationsdatei my.cnf

Derzeit existieren rund 330 Performance-Parameter, an denen man Änderungen vornehmen kann http://dev.mysql.com/doc/refman/5.5/en/mysqld-server.html

Die Optimierung der Konfigurationsdatei ist sehr von den Hardware-Ressourcen des Servers und der Auslastung des MySQL-Servers abhängig. Sich in die Bedeutung jedes einzelnen Parameters einzulesen und die beste Einstellung zu finden ist eine wahre Sisyphus-Arbeit. Grundlegend liefern folgende Website, Perl- bzw. Shell-Skripte sehr gute Infos darüber, welche Verbesserungen getätigt werden sollten:

http://www.fromdual.com/de/mysql-database-health-check bietet die Möglichkeit, seine MySQL-Parameter durch ein Online-Formular zu überprüfen. http://www.fromdual.com/de/mysql-performance-tuning-key ist ein Guide, der ein systematisches Tuning der einzelnen Parameter ermöglicht.

 

 

 

http://github.com/rackerhacker/MySQLTuner-perl

Der Aufruf erfolt über folgende Kommandos in der Shell:

perl mysqltuner.pl

>>  MySQLTuner 1.2.0 – Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with ‚–help‘ for additional options and output filtering

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 1M (Tables: 82)
[–] Data in MEMORY tables: 252K (Tables: 1)
[!!] Total fragmented tables: 2

——– Security Recommendations  ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 12d 14h 6m 16s (24M q [22.179 qps], 29K conn, TX: 1B, RX: 7B)
[–] Reads / Writes: 81% / 19%
[–] Total buffers: 44.0M global + 3.2M per thread (20 max threads)
[OK] Maximum possible memory usage: 107.7M (21% of installed RAM)
[OK] Slow queries: 0% (18/24M)
[OK] Highest usage of available connections: 25% (5/20)
[OK] Key buffer size / total MyISAM indexes: 4.0M/1.7M
[OK] Key buffer hit rate: 100.0% (45B cached / 6K reads)
[OK] Query cache efficiency: 55.8% (38K cached / 69K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9M sorts)
[OK] Temporary tables created on disk: 0% (30K on disk / 61M total)
[OK] Thread cache hit rate: 99% (5 created / 29K connections)
[!!] Table cache hit rate: 0% (150 open / 386K opened)
[OK] Open file limit used: 25% (260/1K)
[OK] Table locks acquired immediately: 100% (189M immediate / 189M locks)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 497)

 

bzw. http://launchpad.net/mysql-tuning-primer

sh tuning-primer.sh

— MYSQL PERFORMANCE TUNING PRIMER —
– By: Matthew Montgomery –

MySQL Version 5.1.49-3-log x86_64

Uptime = 12 days 14 hrs 9 min 59 sec
Avg. qps = 22
Total Questions = 24281549
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL’s Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 4.000000 sec.
You have 18 out of 24281570 that take longer than 4.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 4
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 20
Current threads_connected = 1
Historic max_used_connections = 5
The number of used connections is 25% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 27 M
Configured Max Per-thread Buffers : 63 M
Configured Max Global Buffers : 12 M
Configured Max Memory Limit : 75 M
Physical Memory : 512 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 1 M
Current key_buffer_size = 4 M
Key cache miss rate is 1 : 6948656
Key buffer free ratio = 66 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 498 K
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 6.08 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 516.00 K
You have had 1 queries where a join could not use an index properly
You should enable „log-queries-not-using-indexes“
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 497 tables
Current table_definition_cache = 256 tables
You have a total of 106 tables
You have 150 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 61882523 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 256 K
Current table scan ratio = 1372 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 24281756
Your table locking seems to be fine

 

Weitere Tipps

Viele weiter Tipps findet man hier: http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Die mobile Version verlassen