DB Library

MySQL 성능 최적화 작업 중 하나로 DISK I/O를 줄이는 방법중 하나

참고

http://jason-heo.github.io/mysql/2014/03/05/innodb-barracuda.html

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=seuis398&logNo=70094098699

MySQL Blocking Query 

Version 5.7

select blocking.trx_mysql_thread_id                       as blocking_processlist_id
      ,max(proc.USER)                                          as blocking_user
      ,max(proc.HOST)                                          as blocking_host
      ,max(proc.DB)                                            as blocking_db
      ,max(ifnull(blocking.trx_query, 'Null'))                 as blocking_query
      ,max(TIMESTAMPDIFF(second, blocking.trx_started, now())) as blocking_started_sec
      ,max(ifnull(blocking.trx_started, 'Null'))               as blocking_started_time
      ,max(blocking.trx_state)                                 as blocking_state
      ,'Null'                                             as blocking_command
      ,max(concat(blocking.trx_id, ' (', ifnull(waiting.blocking_lock_id, 'Null'), ')')) as trx_id
      ,max(ifnull(lock_info.lock_mode, 'Null'))                                          as lock_mode
      ,max(ifnull(lock_info.lock_type, 'lock')) as lock_type
      ,if(max(waiting_info.trx_started is not null)
      ,concat('\nlock waiting into : ', count(*)
      ,substring(group_concat('\n\t'
                 ,'waiting ', now() - waiting_info.trx_started, '(sec) : '
                 ,'query -> ', waiting_info.trx_query
                  order by waiting_info.trx_started), 1, 1000))
      ,'\nlock waiting into : Null') as trx_info
from information_schema.INNODB_TRX blocking
left join information_schema.INNODB_LOCK_WAITS waiting on waiting.blocking_trx_id = blocking.trx_id
left join information_schema.INNODB_TRX waiting_info on waiting_info.trx_id = waiting.requesting_trx_id
left join information_schema.INNODB_LOCKS lock_info on lock_info.lock_trx_id = blocking.trx_id
inner join information_schema.processlist proc on proc.id = blocking.trx_mysql_thread_id
where blocking.trx_state = 'RUNNING'
and blocking.trx_requested_lock_id is null
and blocking.trx_wait_started is null
group by blocking.trx_mysql_thread_id ;

MySQL Status Check Query

Version 5.7 & 8.0

select concat('LongQuery', '::', count(*)) as current_value
from information_schema.processlist proc
where proc.command not in ('sleep', 'daemon', 'binlog dump', 'killed')
and proc.id <> connection_id()
and proc.time > 10
union all
select concat('LongTransaction', '::', count(*)) as current_value
from information_schema.innodb_trx trx
where TIMEDIFF(now(), trx.trx_started) > '00:01:00'
union all
select concat('MetaLock', '::', count(*)) as current_value
from performance_schema.metadata_locks ml
where ml.lock_status = 'PENDING'
union all
select concat('LockWait', '::', count(*)) as current_value
from information_schema.INNODB_TRX trx
where trx.trx_state = 'LOCK WAIT'
and trx.trx_wait_started <= date_sub(now(), interval 3 second) ;

MySQL Long Query

Version 5.7 & 8.0

select proc.time, proc.command, proc.id
     , proc.user, proc.host, proc.db, proc.info
from information_schema.processlist proc
where proc.command not in ('sleep', 'daemon', 'binlog dump', 'killed')
and proc.id <> connection_id()
and proc.time > 10
order by proc.time desc ;

MySQL Long Transaction Query

Version 5.7 & 8.0

select trx_mysql_thread_id as 'id'
     , proc.user
     , proc.host
     , proc.command
     , trx.trx_state
     , concat(trx_started, '') as 'start_time'
     , concat(TIMEDIFF(now(), trx_started), '') as 'diff_time'
     , ifnull(trx_query, '.......') as 'query_info'
     , ( select ifnull(esc.SQL_TEXT, '.......')
         from performance_schema.threads thread
       , performance_schema.events_statements_current esc
         where thread.processlist_id = trx.trx_mysql_thread_id
         and esc.thread_id = thread.thread_id) as 'query_info2'
from information_schema.innodb_trx trx
    ,information_schema.processlist proc
where proc.id = trx.trx_mysql_thread_id
and trx.trx_query is null
and TIMEDIFF(now(), trx.trx_started) > '00:01:00'
order by trx_started ;

MySQL Meta Lock Query

Version 5.7 & 8.0

select th.thread_id as 'blocking_thread_id'
     , th.processlist_id as 'blocking_id'
     , th.processlist_user as 'blocking_user'
     , th.processlist_host as 'blocking_host'
     , th.processlist_db as 'blocking_db'
     , th.processlist_command as 'blocking_command'
     , th.processlist_time as 'blocking_time'
     , ifnull(th.processlist_state, '---') as 'blocking_state'
     , ifnull(th.processlist_info, ifnull((select esc.sql_text
                                           from performance_schema.events_statements_current esc
                                           where esc.thread_id = th.thread_id), '---')) as 'blocking_query'
     , (select group_concat(concat('(id : ', waiting.processlist_id, ', waiting : ', waiting.processlist_time, ' sec) -> ', processlist_info)
        order by waiting.processlist_time desc)
        from performance_schema.threads waiting
        where waiting.processlist_state = 'Waiting for table metadata lock') as 'waiting_query'
from (select granted.owner_thread_id
            ,count(*) as cnt
      from performance_schema.metadata_locks pending
          ,performance_schema.metadata_locks granted
      where pending.lock_status   = 'PENDING'
      and granted.object_type   = pending.object_type
      and granted.object_schema = pending.object_schema
      and granted.object_name   = pending.object_name
      and granted.owner_thread_id != pending.owner_thread_id
      and granted.lock_status   = 'GRANTED'
      group by granted.owner_thread_id
      order by 2 desc
      limit 1) as metalock_parent
     ,performance_schema.threads th
where th.thread_id = metalock_parent.owner_thread_id
and th.processlist_time >= 3 ;

MySQL Lock Wait Query

Version 8.0

select TIMESTAMPDIFF(second, trx_waiting.trx_started, now()) as 'waiting_started_sec'
     , concat(trx_waiting.trx_started, '') as 'waiting_started_time'
     , trx_waiting.trx_state as 'waiting_state'
     , waiting_thread.processlist_command as 'waiting_command'
     , waiting_thread.processlist_id as 'waiting_processlist_id'
     , waiting_thread.processlist_user as 'waiting_user'
     , waiting_thread.processlist_host as 'waiting_host'
     , waiting_thread.processlist_db as 'waiting_db'
     , waiting_thread.processlist_info as 'waiting_query'
     , TIMESTAMPDIFF(second, blocking_thread_time.trx_started, now()) as 'blocking_started_sec'
     , concat(blocking_thread_time.trx_started, '') as 'blocking_started_time'
     , blocking_thread_time.trx_state as 'blocking_state'
     , blocking_thread.processlist_command as 'blocking_command'
     , blocking_thread.processlist_id as 'blocking_processlist_id'
     , blocking_thread.processlist_user as 'blocking_user'
     , blocking_thread.processlist_host as 'blocking_host'
     , blocking_thread.processlist_db as 'blocking_db'
     , ifnull(blocking_thread.processlist_info
           , (select esc.SQL_TEXT
              from performance_schema.events_statements_current esc
              where esc.thread_id = blocking_thread.thread_id)) as 'blocking_query'
from information_schema.innodb_trx trx_waiting
    ,performance_schema.data_lock_waits dlw
    ,performance_schema.threads waiting_thread
    ,performance_schema.threads blocking_thread
    ,information_schema.innodb_trx blocking_thread_time
    -- performance_schema.events_statements_current
where trx_waiting.trx_state = 'LOCK WAIT'
and trx_waiting.trx_wait_started <= date_sub(now(), interval 3 second)
and cast(dlw.requesting_engine_transaction_id as char charset utf8mb4) = trx_waiting.trx_id
and waiting_thread.thread_id = cast(dlw.requesting_thread_id as char charset utf8mb4)
and blocking_thread.thread_id = cast(dlw.blocking_thread_id as char charset utf8mb4)
and blocking_thread_time.trx_id = cast(dlw.BLOCKING_ENGINE_TRANSACTION_ID as char charset utf8mb4) ;

테이블 생성시 auto_increment 설정

CREATE TABLE people 
(
    number INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

AUTO_INCREMENT 확인

다음에 삽입될 Auto_increment(number 컬럼)의 값 확인

SHOW TABLE STATUS WHERE name = '테이블 이름';
SHOW TABLE STATUS LIKE '테이블 이름';

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 82
    Create_time: 2021-12-31 15:02:42
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  초기값 변경

AUTO_INCREMENT를 초기화(변경)할 때는 변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야 합니다

ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table _people auto_increment=5
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-12-31 15:57:51
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  1부터 순서대로 정렬

레코드 삭제와 업데이트 등과 같은 이유로 AUTO_INCREMENT로 설정된 컬럼의 숫자가 난잡하게 될 수 있습니다. 이때 보기 좋게 1부터 순서대로 정렬할 수 있습니다.

SET @count=0;
UPDATE 테이블 이름 SET 컬럼 이름=@count:=@count+1;

mysql> SET @count=0;
mysql> UPDATE people SET number=@count:=@count+1;

# 데이터 정렬 후 AUTO_INCREMENT 값을 변경
ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table people AUTO_INCREMENT=4;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

예제

AUTO_INCREMENT를 5로 변경

 

 

 

 

 

 

 

 

참고

https://m.blog.naver.com/dldudcks1779/222006115309