MySQL Performance 스키마

1. Performance 스키마 구성 : instrument, consumer

2. Instrument Elements

   1) statement/sql/select

       => statement instrument 타입이고 select에 정보를 수집

   2) wait/synch~~/autoinc_mutex 

       => wait instrument 타입이고, auto increment관련 mutex 정보 수집

   각 instrument에 대한 설명은 setup_instruments테이블에 documentation 항목 참조

  select * from performance_schema.setup_instruments

  where documentation is not null limit 10\G;

  NULL 항목이 많을 수 있으므로 해당 instrument에 이름과 추측을 통해 용도를 알 수 있음

3. Consumer : instrument에 정보를 가지고 있는 테이블

  1) *_current : 현재 서버에 발생하는 이벤트

  2) *_history : thread당 완료된 마지막 10개 이벤트

  3) *_history_long : globally하게 thread당 완료된 마지막 10,000개 이벤트

  events_wait : low-level server waits (예, muxtes)

  events_statements : SQL statements

  events_stages : profile info (ex, tempoary tables.)

  event_transactions : transactions

4. enable or disable performance schema

  performance_schema = ON or OFF ( my.cnf , restart )

 

  각 instrument enable/disable (아래 3가지 방법중 1개 선택해서 적용)

  1) setup_instruments 테이블 (1회성, restart시 초기화)

      update performance_schema.setup_instruments 

      set enabled='YES' where name='statement/sql/select'

  2) sys stored procedure (1회성, restart시 초기화)

      call sys.ps_setup_enable_instrument('statement/sql/select')

  3) startup options

      performance-schema-instrument='statement/sql/select=ON'

 

  각 consumers enable/disable

   1) setup_consumers 테이블 (1회성, restart시 초기화)

   2) sys stored procedure (1회성, restart시 초기화)

   3) startup options

       performance-schema-consumer=''

5. 모니터링

   1) 특정 Object 모니터링

       insert into performance_schema.setup_objects 

       (object_type, object_schema, object_name, enabled) 

       values ('TABLE', 't1', '%', 'YES');

   2) 특정 threads 모니터링

       update performance_schema.setup_threads set HISTORY='NO' 

       where NAME='thread/sql/event_scheduler';

       user에 대해 모니터링

       insert into performance_schema.setup_actors

       (HOST, USER, ENABLED, HISTORY)

       values ('127.0.0.1', 'test', 'YES', 'NO');

6.  Performance 스키마 사용

     1) SQL statement

        - 테이블 : events_statements_current, events_statements_history,

                        events_statements_history_long

        - 확인사항

          위에 테이블에 있은 컬럼중 아래 사항 확인

          created_tmp_disk_tables : disk base로 하는 table 생성이 증가하는지 체크.  

                                                         증가할 경우 memory temporary 사이즈를 증가

          select_full_join : join시 full table scan 하는지 체크. 적당한 인덱스 생성

          select_range_check : join에 적당한 인덱스가 없을 경우 확인작업 수행, 인덱스 생성필요

          sort_merge_passes : merge pass수 , 값이 증가하면 sort_buffer_size 증가 검토

          sort_scan : 테이블 스캔닝하면서 sort 하는 값 , 값이 증가하면 sql 튜닝 필요

          no_index_used : 쿼리에 index가 사용되지 않을 경우, 적당한 인덱스 생성

          no_good_index_used : 쿼리에 사용된 인덱스가 맞지 않을 경우, 적당한 인덱스 생성

 

          인덱스 사용되지 않거나, 맞지 않는 인덱스를 사용하는 쿼리 확인

          select thread_id, sql_text, rows_sent, rows_examined, created_tmp_tables, 

                     no_index_used, no_good_index_used

          from performance_schema.events_statements_history_long

          where no_index_used > 0 or no_good_index_used > 0;

 

          temporary tables를 생성하는 쿼리 확인

          select thread_id, sql_text, rows_sent, rows_examined, created_tmp_tables,

                      created_tmp_disk_tables

          from performance_schema.events_statements_history_long

          where created_tmp_tables > 0 or created_tmp_disk_tables > 0;

 

          결과값을 error return 하는 쿼리 확인

          select thread_id, sql_text, rows_sent, rows_examined, created_tmp_tables

          from performance_schema.events_statements_history_long

          where errors > 0;

 

          응답 시간이 5초 이상인 쿼리 확인

          select thread_id, sql_text, rows_sent, rows_examined, created_tmp_tables,

                     no_index_used, no_good_index_used

          from performance_schema.events_statements_history_long

          where timer_wait > 5000000000 ;

         

          sys database에서 확인 (performance에 view 제공)

          statement_analysis  : total 수행 시간으로 정렬된 statement 확인

          statements_with_errors_or_warnings : 에러나 warnings이 발생한 쿼리 확인

          statements_with_full_table_scan : full table scan를 하는 쿼리 확인

          statements_with_runtimes_in_95th_percentile : 평균 수행 시간이 상위 95%인 쿼리 확인

          statements_with_sorting : sort이 발생한 쿼리 확인

          statements_with_temp_tables : temporary tables를 사용한 쿼리 확인

 

   2) Database에 workload 확인 

         db가 read인지 write인지 확인하기 위해 아래 쿼리 수행

         select event_name, count(event_name) from events_statements_history_long

         group by event_name;

 

         query문에  latency 확인

         select event_name, count(event_name),

                     sum(lock_time/1000000) as latency_ms

         from events_statements_history

         group by event_name order by latency_ms desc;

 

         db에 rows read and write 양 확인

         with rows_read as (select sum(variable_value) as rows_read

         from global_status

         where variable_name in ('Handler_read_first', 'Handler_read_key', 'Handler_read_next',

         'Handler_read_last', 'Handler_read_prev', 'Handler_read_rnd, 'Handler_read_rnd_next')),

         rows_written as (select sum(variable_value) as rows_written from global_status 

         where variable_name in ('Handler_write'))

         select * from row_read, rows_written\G

 

         metalock 확인

         select processlist_id, object_type, lock_type, lock_status, source

         from metadata_locks join threads on (owner_thread_id = thread_id)

         where object_name = ' ' and object_name = ' ' \G

 

         memory 사용량 확인

         select event_name, 

                     current_number_of bytes_used/1024/1024 as current_mb,

                     high_number_of bytes_used/1024/1024 as high_mb,

         from performance_schema.memory_summary_global_by_event_name

         where event_name like 'memory/innodb/%'

         order by current_nubmer_of_bytes_used desc limit 10;

 

         select * from sys.memory_global_total;

 

         select thread_id, user, current_allocated, total_allocated

         from sys.memory_by_thread_by_current_bytes limit 9;

 

        사용자 thread 환경 대한 변수/값 확인

         select * from variables_by_thread where variable_name='tx_isolation';

 

         사용자 thread 통계 변수/값 확인

         select * from status_by_thread where variable_name = 'Handler_write';

 

         sql 수행시 에러가 발생하는 account

         select * from performance_schema.events_errors_summary_by_account_by_error

         where sum_error_raised > 10 and user is not null order by sum_error_raised desc\G;

 

         사용자 기준으로 메모리 사용량 확인

         select substring_index(event_name, '/', -1) as event,

                    current_number_of_bytes_used/1024/1024 as current_mb,

                    high_number_of_bytes_used/1024/1024 as high_mb

         from performance_schema.memory_summary_global_by_event_name

         where event_name like 'memory/performance_schema/%'

         order by current_number_of_bytes_used desc limit 10;

 

         select substring_index(event_name, '/', -1), current_alloc

         from sys.memory_global_by_current_bytes

         where event_name like 'memory/performance_schema/%'  limit 10;

 

         show engine performance_schema status\G;