实验数据库性能监视与优化实验

实验 数据库性能监视与优化实【实验5-1】使用SHOW语句查询设备吞吐量Questions>Com_SelectComJnsertCom_update>Com_delete【实验5-2］访问Performance_schema中的events_statements_summary_by_digest表，获取有关的延迟、错误和查询量信息的性能指标。5-3SHOW语句查询连接检查指标Threads_connected>Threads_running>Connection_errors_internal、Aborted_connects和Connection_errors_max_connectionsG【实验5-4］使用SHOW语句查询Innodb缓冲区指标lnnodb_buffer_pool_pages_total、Innodbbufferpoolreadrequests>InnodbbufferpoolreadSo【实验5-5］使用SHOW语句获取与查询缓冲有关的指标：Qcache_free_blocks、 Qcache_not_cached>Qcache_queries_in_cache、Qcache_total_blocks。、5-6］SHOWCreated_tmp_disk_tables、5-7］SHOWOpen_tablesOpened_tables5-8SHOWPROCESSLIST5-9mysqldumpslow5-10］EXPLAINEXTENDEDUNIONSELECT5-11］EXPLAINEXTENDEDSELECT*FROMtlWHEREal<10AND(EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=l)OREXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=2));5-12EXPLAINEXTENDEDSELECT*FROMtl,(SELECT*FROMt2WHEREt2.a2>10)v_t2WHEREtl.al<10AND5-13c5-14］PROCEDUREANALYSE5-15lnet_ATONIP192.128.1.1IP5-16］5-17］SQL5-1SHOWQuestions、Com_Select、Com_insert、Com_update、Comdelete5-2］Performance_schemaevents_statements_summary_by_digest获取有关的延迟、错误和查询量信息的性能指标。5-3SHOWThreadsconnected、Threadsrunning、Connectionerrorsinternal、Abortedconnects和Connectionerrorsmaxconnectionsc5-4SHOWInnodblnnodb_buffer_pool_pages_total、Innodbbufferpoolreadrequests>InnodbbufferpoolreadSo【实验5-5 ］使用SHOW语句获取与查询缓冲有关的指标：Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcachejnserts、Qcache_lowmem_prunes、5-6SHOWCreated_tmp_disk_tablesCreated_tmp_files、5-7］SHOWOpen_tablesOpened_tables5・8SHOWPROCESSLIST5・9mysqldumpslow5-10］EXPLAINEXTENDEDUNIONSELECT5-11EXPLAINEXTENDEDSELECT*FROMtlWHEREal<10AND(EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDEXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=l)EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDWHEREtl.al<10AND5-1