FROSCON 2011: MySQL Performance Tuning

Click here to load reader

  • date post

    09-May-2015
  • Category

    Documents

  • view

    696
  • download

    2

Embed Size (px)

Transcript of FROSCON 2011: MySQL Performance Tuning

  • 1.MySQL Performance Tuning FrOSCon 2011, St. Augustin Oli SennhauserSenior MySQL Consultant at FromDual GmbHoli.sennhauser@fromdual.com www.fromdual.com1

2. ber FromDual GmbH FromDual bietet neutral und unabhngig: Beratung fr MySQL (vor Ort und remote) Remote-DBA Dienstleistungen / MySQL Betrieb Premium Support (ex. 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. Inhalt Performance Tuning FromDual Perfromance Waage Kritische Ressourcen Hardware / OS Datenbank Parameter Applikations-Tuning Architektur & Design Messenwww.fromdual.com 3 4. 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.com4 5. FromDual Performance Waage 6. 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.com6 7. Kritische Ressourcen HardwareOSwww.fromdual.com 7 8. 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 8 9. Messen: CPU top vmstat oder mpstat Welcher Prozess braucht denn CPU? www.fromdual.com 9 10. 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.com10 11. 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 11 12. Messen: Netzwerk dstat watchdn1ifconfig Frames pro Sekunde (80k 1.5M) /Durchsatz (1 Gbit/s 120 Mbyte/s)?www.fromdual.com12 13. 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.com13 14. 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.com14 15. Datenbank Tuningwww.fromdual.com 15 16. 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 16 17. 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 17 18. 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.com21 22. 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.com22 23. 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 23 24. Architektur & Designwww.fromdual.com 24 25. MySQL Scale-Out vs Scale-Up Kosten Scale-Up MySQL Design Phsikalische Flaschenhlse Relaxation of Constraints Scale-Outwww.fromdual.com 25 26. Der MySQL Scale-Out AnsatzApplikationro rtwSlave ReportingMasterSlave Backup Slave 1Slave 2 Slave 3... Load balancer www.fromdual.com26 27. 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.com27 28. 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.com28 29. Monitoring www.fromdual.com 29 30. 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 30 31. Messen, messen, messen... Messen Simulieren Kapazittsplanung www.fromdual.com 31 32. Der Bogen schliesst sich... Im Idealfall: Nur einenderung aufs Mal!www.fromdual.com 32 33. Q&AFragen ?Diskussion?Wir haben noch Zeit fr persnliche und indviduelle Beratungen... www.fromdual.com 33