[MySQL] Table lock 조회 쿼리 SHOW OPEN TABLES WHERE In_use > 0; # MySQL 8.0 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id INNER..
[MySQL] GROUP BY Optimization MySQL은 GROUP BY 사용 시 대개는 전체 대상 테이블을 스캔한 후 새로운 임시 테이블을 만든 후 사용을 하도록 되어 있다. 하지만 인덱스 접근이 가능하다면 임시 테이블 생성을 피할 수 있다. GROUP BY에 가장 중요한 인덱스 사용 조건은 컬럼들이 호출한 쿼리 순서대로 저장하고 있는지 이다. 인덱스 사용에 성공한다면, 두가지의 방법이 있다. Loose Index Scan: 모든 범위 조건과 함께 그룹화하는 방법. Tight Index Scan: 범위 스캔 후 결과를 그룹화하는 방법. Loose Index Scan 테이블 t1(컬럼 a, b, c, d, e)이 인덱스로 (a, b, c) 를 가지고 있다고 가정해 보겠다. 인덱스 사용 가능 GR..
MySQL ORDER BY Optimization MySQL Order by 사용시 인덱스를 사용 조건에 충족하다면 인덱스를 이용해서 정렬을 하겠지만 인덱스를 사용할 수 없는 경우에는 filesort를 사용을 한다. ORDER BY 인덱스 사용 충족 조건 Order by는 꼭 인덱스와 매치가 안되더라도 인덱스를 사용할 수도 있다. 각설하고 실제 예시를 통해 알아보자. SELECT * FROM t1 ORDER BY col_a, col_b; 테이블 t1에 복합 인덱스로 index (col_a, col_b) 가 생성되어 있다고 가정을 하자. MySQL의 Optimizer는 실행시 인덱스를 읽는게 효율적이라고 생각 한다면 인덱스를 사용할 것이고 그것을 통해서 추가적인 정렬하는 자원을 방지 할 수도 있다. SEL..
SHOW INDEX Statement SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] mysql> SHOW INDEX FROM City\G *************************** 1. row *************************** Table: city Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 4188 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: ***************..
[MySQL] binlog to SQL 변환 /usr/local/mysql/bin/mysqlbinlog mysql-bin.xxxxxx > binlog.xxxxxx.sql vi binlog.xxxxxx.sql
MariaDB sharding using docker (도커를 이용한 마리아DB 세팅) https://github.com/sanggi-wjg/docker_mariadb_sharding sanggi-wjg/docker_mariadb_sharding Contribute to sanggi-wjg/docker_mariadb_sharding development by creating an account on GitHub. github.com
# Master mysql > show master status; ex ) +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 6696003 | | | | +------------------+----------+--------------+------------------+--------..
SELECT t1.table_name, t1.table_comment, column_name, data_type, column_type, column_key, is_nullable, column_default, extra, column_comment FROM (SELECT table_name, table_comment FROM information_schema.TABLES WHERE table_schema='WH') t1, (SELECT table_name, column_name, data_type, column_type, column_key, is_nullable, column_default, extra, column_comment, ordinal_position FROM information_sche..
vi /ect/mysql/my.cnf # innodb 설정 값 중 메모리량을 적절하게 변경 innodb_buffer_pool_size = 5120M innodb_data_file_path = ibdata1:1000M:autoextend 2018-07-13 14:00:43 21419 [Note] InnoDB: Initializing buffer pool, size = 5.0G InnoDB: mmap(686817280 bytes) failed; errno 12 2018-07-13 14:00:44 21419 [ERROR] InnoDB: Cannot allocate memory for the buffer pool 2018-07-13 14:00:44 21419 [ERROR] Plugin 'InnoDB' ini..
CREATE DEFINER=`jay_g`@`192.168.1.88` PROCEDURE `debug_msg`(_type VARCHAR(100) ,msg VARCHAR(255)) BEGIN SELECT concat("** DEBUG ** ", _type ," : ", msg) AS 'DEBUG'; END
MySQL 5.6 이전에는 필드 생성시 default 값을 따로 지정을 안 해도 insert 시에 '' 처럼 자동으로 디폴트 값이 반영이 되었습니다. 5.6 이후부터는 STRICT 모드라고 해서 테이블 생성시에 default 값을 지정하지 않을 경우 insert 시에 아래와 같이 에러가 발생합니다. Field 'name' doesn't have a default value 외부 프로그램을 사용시 테이블의 필드들을 모두 수정하기는 힘든 경우가 많습니다. 이런 경우 my.cnf 에서 기본 설정을 되어 있는 STRICT 모드를 해제해 줍니다. #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES sql_mode=NO_ENGINE_SUBSTITUTION 해제 후에 MyS..
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES 에러 발생 시 GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost'; flush privileges;
# 현재 유저별 호스트 확인 select user,host,password from mysql.user order by user; # 유저 추가 create user 'ID'@'a.b.c.d' identified by 'PASSWD'; grant all privileges on *.* to 'ID'@'a.b.c.d' with grant option; flush privileges; 5.7 버전 이상시 select user,host,authentication_string from mysql.user order by user; create user 'id'@'a.b.c.d59' identified by 'passwd'; grant all privileges on *.* to 'id'@'a.b.c.d' wit..
MySQL 5.7에서 가쟝 중요한 4가지 변수가 있다. [mysqld] innodb_buffer_pool_size = 10240M (램의 50~70% 정도로 설정) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0 innodb_flush_method = O_DIRECT // performance_schema 데이터베이스 사용 USE performance_schema // 현재 쓰레드(연결) 개수 확인 SELECT * FROM performance_schema.threads // 현재까지 누적된 쿼리 패턴 통계 확인 SELECT * FROM performance_schema.events_statement..
Indexing for High Performance Index 는 좋은 성능을 위해서 중요하며 데이터가 점점 커지면 커질수록 더욱 중요해진다. 불행하게도, Index 는 종종 잊혀지거나 오해 되어 잘못 사용 될 수 있고 이는 현실 세계에서 문제가 된다. (저자 : 그래서 우리가 query optimzation 보다 앞 챕터로 넣은 것이다.) 인덱스 최적화는 쿼리 최적화를 위한 가장 강력한 방법이다. 진실된 인덱스 작업은 너의 쿼리 재작성을 요구할 수 있다. 따라서 쿼리 최적화보다 먼저 배워랑 Indexing Basics MySQL에서 storage engine 비슷한 방법으로 인덱스를 사용한다. storage engine은 인덱스 자료 구조에서 값을 찾는다. 값을 매칭 되는 것을 찾을 때, storag..
데이터 베이스 설계 프로세스를 수행하는 방법에 대한 전반적인 생각과 프로세스에 포함된 각 순서에 대한 일반적인 생각을 가지고 있는 것은 중요하다. 일곱단계에 걸쳐서 설계 프로세스에 있는 정리 했으며, 설계 프로세스에 대한 전체적인 그림과 기술들의 이해를 보다 명확하게 할 수 있도록 도움이 되길 바란다. 새로운 데이터베이스를 설계할 때나 현재 운영중인 데이터베이스를 개선할 때, 혹은 분석한 결과를 토대로 새로운 데이터베이스를 설계하기 위해 운영 중인 데이터 베이스를 설계 할때 사용할 수 있을 것이다. 데이터 베이스에서 구조적인 무결성과 데이터 무결성의 수준이 전체적으로 설계 프로스세를 준수한 수준과 정비례 관계에 있다. 임무 목표와 임무 명세 정의 데이터 베이스의 임무 목표와 임무 명세를 정의하는 단계이다..
Isolation Levels (트랜잭션 고립(격리) 수준) 우선 Transaction에서 사용 되는 개념이다. SQL은 4개의 Isolation level을 정의하고 있다. READ UNCOMMITTED, READ COMMITED, REPEATABLE READ, SERIALZABLE 각 Storage Engine 혹은 DBMS 마다 조금씩 다르게 구현 되어 있다. 따라서, 어떤것을 사용하기 전에 메뉴얼을 읽어보자(언제 읽냐...) READ UNCOMMITTED Transaction은 uncommitted 결과를 볼 수 있다. 이 단계에서 너가 정말 정말 좋은 의도로 개발을 했지만 많은 문제가 발생할 수 있다. 이 단계는 거의 실용적이지 않다. 왜냐하면, 성능이 다른 레벨들보다 좋지 않기 때문이다. un..
InnoDB Engine ** 개요 ** InnoDB는 가장 중요하며 광범위하게 활용적인 Default Transactional storage engine 이다. 짧은 생명주기의 Transaction 처리 완료를 위해 디자인 되었다. (롤백 보다는) InnoDB는 Tablespace 라고 알려진 한개 이상의 데이터 파일 시리지 안에 자체 파일을 저장한다. (Tablespace 는 근본적으로 InnoDB가 자기 자신을 관리하는 블랙박스이다.) InnoDB는 각 테이블의 데이터와 인덱스들을 분리된 파일로 저장할 수 있다. (각각의 Tablespace를 만듬으로서 raw disk partition을 사용할 수 있다. 최신의 파일시스템이 이를 필요하지 않게 만들기는 했지만) ** MVCC ** 높은 동시 처리성..
The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include: They allow modular programming. They allow faster execution. They can reduce network traffic. They can be used as a security mechanism. Determine when to use stored procedures vs. SQL in the code Stored procedures in SQL Server are similar to procedures in other programm..
When should I use transactions in my queries? Basically any time you have a unit of work that is either sensitive to outside changes or needs the ability to rollback every change, if an error occurs or some other reason. 트랜잭션의 특징 트랜잭션의 특징은 크게 4가지로 구분된다. 원자성 (Atomicity) 일관성 (Consistency) 독립성 (Isolation) 지속성 (Durability) 첫번째로, 원자성은 트랜잭션이 데이터베이스에 모두 반영되던가, 아니면 전혀 반영되지 않아야 한다는 것이다. 트랜잭션은 사람이 설계한 논리적..
