MySQL Performance Tuning Variables

  • Published on
    09-May-2015

  • View
    827

  • Download
    8

Embed Size (px)

DESCRIPTION

Most important MySQL Performance Tuning Variables and Status Variables.

Transcript

  • 1.www.fromdual.com1 / 24MySQL Performance TuningDOAG 2013 Datenbank14. Mai 2013, DsseldorfOli SennhauserSenior MySQL Berater, FromDual GmbHoli.sennhauser@fromdual.com

2. www.fromdual.com2 / 24ber FromDual GmbH FromDual bietet neutral und unabhngig: Beratung fr MySQL Support fr MySQL und Galera Cluster Remote-DBA Dienstleistungen fr MySQL MySQL Schulungen Oracle Silver Partner (OPN)www.fromdual.com 3. www.fromdual.com3 / 24InhaltHA SolutionsRead scale-outReplication set-up for HAActive/passive fail-overMySQL ClusterReplication ClusterStorage-Engine-ReplicationMySQL Performance Tuning mit System-VariablenPerformance Tuning HebelMySQL System VariablenMySQL Status VariablenMySQL ArchitekturSpeicherbelegungSession VariablenGlobale VariablenMyISAM VariablenInnoDB VariablenMonitoringInformationen 4. www.fromdual.com4 / 24Performance Tuning Hebel 5. www.fromdual.com5 / 24MySQL System Variablen Datenbank-Konfigurationmy.cnfmysqldhelpverbose/etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf$basedir/my.cnf~/.my.cnfSHOWGLOBALVARIABLES; 6. www.fromdual.com6 / 24MySQL Status Variablen Was wir damit anrichten sieht man mit:SHOW/*!50001GLOBAL*/STATUS;SHOWENGINEINNODBSTATUSG 7. www.fromdual.com7 / 24MySQL ArchitekturmysqldApplication / ClientThreadCacheConnectionManagerUser Au-thenticationCommandDispatcherLoggingQuery CacheModuleQueryCacheParserOptimizerAccess ControlTable ManagerTable OpenCache (.frm, fh)Table DefinitionCache (tbl def.)Handler InterfaceMyISAM Memory NDB PBXTInnoDB ...Aria XtraDB Tokutek 8. www.fromdual.com8 / 24SpeicherbelegungStorage EnginesMySQL Server (mysqld)Client 2 Client NLocal Memory AreasClient 1read_buffersort_buffertmp_tableread_bufferread_buffersort_buffertable_open_cachetable_definition_cachequery_cache_sizeetc...Global Memory Areakey_buffer_sizeinnodb_buffer_pool_sizeinnodb_log_buffer_sizeetc... 9. www.fromdual.com9 / 24Thread Cache Konifguration:thread_cache_size Status:threads_created+++|Variable_name|Value|+++|Threads_cached|8||Threads_connected|242||Threads_created|15355||Threads_running|16|+++ 10. www.fromdual.com10 / 24Query Cache Konfiguration: query_cache_size Status:Com_select,Qcache_hits Hit ratio:Qache_hits/(Com_select+Qcache_hits)+++|Variable_name|Value|+++|Com_select|25712||Qcache_free_memory|19553200||Qcache_hits|65137||Qcache_inserts|172167|+++ 11. www.fromdual.com11 / 24Per Session Parameter Im Executor Alloziert/Dealloziert on Demand Per Session oder global einstellbar Meisst Effekt gering oder gar kontraproduktiv! Join Buffer join_buffer_size Read Buffer und Read Random Buffer read_buffer_size,read_rnd_buffer_size 12. www.fromdual.com12 / 24Sort Buffer, temporre TabellenTemporre Tabellen(MEMORY oder MyISAM) Konfiguration: tmp_table_size,(max_heap_table_size) Status: created_tmp_tables,created_tmp_disk_tablesSort Buffer Konfiguration:sort_buffer_size Status: sort_merge_passes 13. www.fromdual.com13 / 24Table Definition Cache Ab v5.1 Enthlt Info vom .frm File Konfiguration: table_definition_cache Status: open_table_definitions,opened_table_defintions+++|Variable_name|Value|+++|Open_table_definitions|100||Opened_table_definitions|100|+++ 14. www.fromdual.com14 / 24Table (Open) Cache Ab v5.1 File-Descriptioren (1 pro Connection pro Table) Frher table_cache Konfiguration: table_open_cache Status: open_tables,opened_tables+++|Variable_name|Value|+++|Open_tables|64||Opened_tables|11812|+++ 15. www.fromdual.com15 / 24MyISAM Bis MySQL 5.1 default Storage Engine Konfiguration: key_buffer_size Status: Key_blocks_used,Key_blocks_unused,Key_reads,Key_read_requests 16. www.fromdual.com16 / 24InnoDB Buffer Pool InnoDB seit 5.5 default Storage Engine InnoDB Buffer Pool Konfiguration:innodb_buffer_pool_size,innodb_buffer_pool_instances,innodb_io_capacitymysqldsystem table space: ibdata1table table space table table spacetable table spaceib_logfile1ib_logfile2ib_logfilenundodouble write areatables/indexesddOS file cachesyncasyncbuffer poolsecondary index buffertrxlogbufferdata pagesindex pagesadaptive hash idxundo recordslock info table 17. www.fromdual.com17 / 24InnoDB Buffer Pool Status:innodb_buffer_pool_pages_%,Innodb_buffer_bytes_%,Innodb_data_% 18. www.fromdual.com18 / 24InnoDB Log File REDO Log, TransactionLog Konfiguration:innodb_log_file_size,innodb_flush_log_at_trx_commit Status: innodb%log%mysqldsystem table space: ibdata1table table space table table spacetable table spaceib_logfile1ib_logfile2ib_logfilenundodouble write areatables/indexesddOS file cachesyncasyncbuffer poolsecondary index buffertrxlogbufferdata pagesindex pagesadaptive hash idxundo recordslock info table 19. www.fromdual.com19 / 24Binary LogApplication ApplicationApplicationmysqldbin-log.nbinlog_cache_sizebinlog_stmt_cache_sizesync_binlog+++|Variable_name|Value|+++|Binlog_cache_disk_use|3456||Binlog_cache_use|12786||Binlog_stmt_cache_disk_use|0||Binlog_stmt_cache_use|0|+++ 20. www.fromdual.com20 / 24Monitoring Von Hand: SHOWGLOBALSTATUS; SHOWENGINEINNODBSTATUSG Performance Schema (P_S, >= v5.5) Irgend ein graphisches Monitoring Tool... 21. www.fromdual.com21 / 24PERFORMANCE_SCHEMA In Diskussion seit mindestens 2006 Eingefhrt in MySQL 5.5 (2010) Einblick in die Datenbank In v5.5 disabled, in v5.6 enabled per defaultperformance_schema=1 Overhead 5% - 200%! also Vorsicht! 22. www.fromdual.com22 / 24Beispiel: Table I/O waitsSELECTOBJECT_SCHEMA,OBJECT_NAME,ROUND(SUM_TIMER_WAIT/1000000000,3)ASSUM_TIMER_WAIT,ROUND(SUM_TIMER_READ/1000000000,3)ASSUM_TIMER_READ,ROUND(SUM_TIMER_WRITE/1000000000,3)ASSUM_TIMER_WRITEFROMtable_io_waits_summary_by_tableORDERBYSUM_TIMER_WAITDESCLIMIT10;++++++|OBJECT_SCHEMA|OBJECT_NAME|SUM_TIMER_WAIT|SUM_TIMER_READ|SUM_TIMER_WRITE|++++++|zabbix|items|1182496.258|616630.696|565865.562||zabbix|history_uint|889884.116|370528.991|519355.125||zabbix|history_str|460411.155|246159.458|214251.697||zabbix|history|389815.659|86285.980|303529.679||zabbix|trends_uint|133955.560|107365.626|26589.934||zabbix|functions|128657.654|128657.654|0.000||zabbix|trends|43683.159|36314.732|7368.428||mpm|mpm_agent|27837.056|27837.056|0.000||zabbix|hosts|23820.284|14044.672|9775.612||zabbix|triggers|21052.459|20970.604|81.855|++++++ 23. www.fromdual.com23 / 24Informationen MySQL System Variablen:http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html MySQL Status Variablen:http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html MySQL DB Health Check:http://www.fromdual.com/mysql-database-health-check MySQL Performance Monitor:http://www.fromdual.com/mysql-performance-monitor 24. www.fromdual.com24 / 24Q & AFragen ?Diskussion?Wir haben Zeit fr ein Performance Audit...! FromDual bietet neutral und unabhngig: Beratung Remote-DBA Support fr MySQL und Galera Cluster Schulungwww.fromdual.com/presentations

Recommended

View more >