DOAG 2011: MySQL Performance Tuning

  • View
    538

  • Download
    1

Embed Size (px)

Text of DOAG 2011: MySQL Performance Tuning

  • 1.MySQL Performance TuningDOAG Conference 2011, Nrnberg Oli SennhauserSenior MySQL Consultant, FromDual GmbH oli.sennhauser@fromdual.com www.fromdual.com1

2. FromDual GmbH FromDual bietet neutral und unabhngig: Beratung fr MySQL (vor Ort und remote) Remote-DBA / MySQL Betrieb Support fr Galera (synchrone Replikation) Support fr MySQL (Basic und Silber) Schulung fr MySQL Consulting Partner der Open Database Alliance(ODBA.org) Oracle Silber Partner (OPN) Mehr Informationen unter:http://www.fromdual.com www.fromdual.com2 3. Kunden www.fromdual.com 3 4. Inhalt Performance Tuning FromDual Perfromance Waage Kritische Ressourcen Hardware / OS Datenbank Parameter Applikations-Tuning Architektur & Design Messenwww.fromdual.com 4 5. Der FromDual Weg des PT Wer von Euch hat oder hatte PerformanceProbleme mit MySQL? Wer von Euch hat ein systematischesVorgehen fr Performance Tuning? Viele Wege fhren nach Rom! Ausgangslage: Kunde schreit, weil er einPerformance Problem hat! www.fromdual.com5 6. FromDual Performance Waage 7. Zusammentragen von Fakten Wie sieht das Problem aus? DB ist pltzlich, manchmal oder schon immer langsam? Was ist genau langsam? Neuer Release wurde eingespielt? Jemand hat rumgefummelt? Wir sind kurz vor Produktionseinfhrung und viel zu langsam? Haben wir historische Messdaten? Am besten wenn: man das Problem (gezielt) simulieren kann es vorhersagbar oder periodisch auftritt Finde das Muster! www.fromdual.com7 8. Kritische Ressourcen HardwareOSwww.fromdual.com 8 9. Kritische Ressourcen Finde den Flaschenhals / die limitierendeRessource: Zum Glck nur: CPU Speicher (RAM) I/O (IOPS, Durchsatz) Netzwerk (FpS, Durchsatz) Aber wie? finde die kritische Ressource!www.fromdual.com 9 10. Messen: CPU top vmstat oder mpstat Welcher Prozess braucht denn CPU? www.fromdual.com 10 11. Messen: Speicher (RAM) free / top: #free totalusedfreesharedbufferscached Mem:103601698386452152035484547432 /+buffers/cache:400948635068 swap:4202112961484105964 ps #pseouser,pid,%cpu,%mem,vsz,rsz,commsortvsz| egrepmysql|COMMAND USERPID%CPU%MEMVSZRSZCOMMAND mysql13610.01.510836816444mysqld mysql12100.00.145361956bash mysql12890.00.140601444safe_mysqld mysql12040.00.140481404suwww.fromdual.com11 12. Messen: I/O vmstat #vmstat1 procsswapiocpu rbsisobiboussyidwa 0033941432121562 000004937540 iostat(sysstatpackage)#iostatx1avgcpu:%user%nice%system%iowait%steal%idle5.880.0034.312.940.0056.86Device:r/sw/srkB/swkB/sawaitsvctm%utilhda0.000.000.000.000.000.000.00hdc0.002.940.0023.5314.6712.003.53 pidstat www.fromdual.com 12 13. Messen: Netzwerk dstat watchdn1ifconfig Frames pro Sekunde (80k 1.5M) /Durchsatz (1 Gbit/s 120 Mbyte/s)?www.fromdual.com13 14. Zusammenfassung CPU I/O Welcher ProzessDurchsatz oder IOPS welches Device? wie viele Cores? Random oder Sequential meist SQL Abfragen Read oder Write Caches zu klein, tmp SpeicherTabellen? Welcher Prozess Netzwerk Swapping?Errors / Drops? ber- oder Unterallo-Durchsatzzierung von DB Caches! FpS www.fromdual.com14 15. Stellschrauben CPU NWSchnellere Cores? 1 Gbit Mehr Cores Speicher OS Mehr RAM? Neue 64-bit KernelXFS I/O RAID-5 :-( I/O Scheduler SAN :-( noop RAID-10, viele Spindeln, SSD? Deadline Batterie gepufferter I/O Cache! Virtualisierung :-(www.fromdual.com15 16. Datenbank Tuningwww.fromdual.com 16 17. MySQL Tuning Welche Storage Engine verwendet Ihr zurZeit? Welchen MySQL Release? ( 5.1 und neuer) Zur Zeit: ca. 330 MySQL Parameter aber nur ca. 8 davon sind primr signifikant! Grob-Tuning Alle anderen nur nach ausfhrlichemBenchmarken Fine-Tuning www.fromdual.com 17 18. InnoDB Grob-Tuning innodb_buffer_pool_size ca. 80% vom RAM auf dedizierter Maschine SHOWSTATUSLIKEInnodb_buffer_pool_pages%; innodb_log_file_size Grsser = schneller, aber lngere Recovery Zeiten 2 x 256M innodb_flush_log_at_trx_commit 0, 2 fr Performance, 1 fr Sicherheit sync_binlog != 0 langsam(er)www.fromdual.com 18 19. MySQL Grob-Tuning key_buffer_size ca. 25 33% vom RAM auf dedizierter Maschine SHOWSTATUSLIKEKey_blocks_%; table_open_cache Running connections x used tables 2 4k ist nichtungewhnlich! Siehe Open[ed]_tables. table_definition_cache Siehe Open[ed]_table_definitions 512 4096 ist nichtungewhnlich! query_cache_type/query_cache_size Nicht zu gross machen (slow.log.profile EXPLAINSELECT...www.fromdual.com22 23. EXPLAINDer Schlssel zur Wahrheit!Query Tuning: x schneller mglich!EXPLAINSELECT*FROMtest;+++++++++++|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+++++++++++|1|SIMPLE|test|ALL|NULL|NULL|NULL|NULL|261369||+++++++++++EXPLAIN Output Format: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html www.fromdual.com23 24. Applikations-Tuning Loslassen! Spalten, welche nicht gebraucht werden weg (char(0) oderausNULLen)! Alte Daten weg (archivieren)! Schema Tuning mysqldumpnodata>structure_dump.sql Richtige Datentypen und richtige Lngen!int(1) 4 byte int! utf8 nur wenn ntig NULL or NOTNULL Lokalitt der Daten InnoDB Primary Key V-Partitioning / (H-)Partitioningwww.fromdual.com 24 25. Architektur & Designwww.fromdual.com 25 26. MySQL Scale-Out vs Scale-Up Kosten Scale-Up MySQL Design Phsikalische Flaschenhlse Relaxation of Constraints Scale-Outwww.fromdual.com 26 27. Der MySQL Scale-Out AnsatzApplikationro rtwSlave ReportingMasterSlave Backup Slave 1Slave 2 Slave 3... Load balancer www.fromdual.com27 28. MySQL Architektur Applikation / ClientThreadConnectionmysqldCacheManager Parser OptimizerUser Au-thenticationAccess ControlCommand Table OpenLoggingDispatcher Cache (.frm, fh) Table Manager QueryQuery CacheTable Definition CacheModule Cache (tbl def.) Handler InterfaceMyISAM InnoDBMemory NDBPBMSAria XtraDBFederated-X ...www.fromdual.com28 29. Architektonische Mglichkeiten RDBMS sind eine langsame Technologie! Daher: Cachen (heisse Daten in Cache!) MySQL Memcached Plugin / Memcached HandlerSocket MySQL Cluster Abstraktionslayer (ORM, Frameworks, etc.) Schneller entwickeln aber Standard == nicht optimiert schlecht fr Performance! BLOB sind ungeeignet fr RDBMS auf Filer legen Blob Streaming (PBMS) Sharding / verteilen von Hand Spider SE MySQL Replikation (r/w Traffic Split) MySQL Cluster Synchrone Replikation mit Galerawww.fromdual.com29 30. Monitoring www.fromdual.com 30 31. Was passiert, wenn...? Kunde fragt mich: Kann mein System 30% mehrLast vertragen? Chemische Verfahrenstechnik: Gibt es Unterschiede zu eine DB basiertenSystem? Was brauche ich um diese Frage zu beantworten? www.fromdual.com 31 32. Messen, messen, messen... Messen Simulieren Kapazittsplanung www.fromdual.com 32 33. Der Bogen schliesst sich... Im Idealfall: Nur einenderung aufs Mal!www.fromdual.com 33 34. MySQL Community EventHeute Dienstag 15. November 18:00Restaurant El Canto (peruanische Kche)unterhalb der Burg Keine Meerschweinchen!Bei Interesse bitte melden!www.fromdual.com34 35. Q&AFragen ?Diskussion?Wir haben noch Zeit fr persnliche und indviduelle Beratungen... www.fromdual.com 35