월: 2016 3월

MySQL 함수 사용 시 NAME_CONST 변환

프로덕션에서 character set 관련 변수들이 모두 utf8 임에도 불구하고 쿼리 내 함수 수행 시 입력 변수 부분에 NAME_CONST latin1 이 붙는 현상

select if( NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’) < NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’) , datediff( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) – dbtest.fn_count( NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’)) , datediff( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) + dbtest.fn_count( NAME_CONST(‘c_date’,_latin1’2016-03-22′ COLLATE ‘latin1_swedish_ci’), NAME_CONST(‘s_date’,_latin1’2016-03-29 17:36:10′ COLLATE ‘latin1_swedish_ci’)) ) into diffdays

+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+

http://www.justincarmony.com/blog/2011/02/02/mysql-stored-procedure-name_const-and-character-sets/

위 블로그에서는 @variable 을 설정하여 NAME_CONST 변환을 우회하여 속도 향상을 시켰다고 함
(utf8 에서 latin1 으로 변경), 적용 시 개선 확인이 어려웠음

Advertisements

MySQL 에서 MariaDB 로 마이그레이션 해야할 10가지 이유

MySQL 에서 MariaDB 로 마이그레이션 해야할 10가지 이유
https://seravo.fi/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql
2015년 초 글, 아직까지 지켜봐야할 것 같지만:-)

1) MariaDB 개발이 좀 더 개방적이고 활발함
– MySQL 5.7 커밋 로그엔 컨트리뷰터들이 @oracle.com
https://github.com/mysql/mysql-server/
– MariaDB 는 공개 메일링 리스트와 버그트래커가 있고 완전히 공개된 소스코드 리파지토리가 있음
https://launchpad.net/~maria-developers
https://jira.mariadb.org/secure/Dashboard.jspa
https://github.com/MariaDB/server
– MariaDB 가 github 에 컨트리뷰터 수가 많음
– 래드햇 배포본 등에도 MariaDB 가 들어감
– 문서화에 더 열심인 걸로 보임
– MySQL 에 현재 큰 모멘텀 있고 커뮤니티가 여전히 많지만 MariaDB 중심으로 새 움직임이 있음

2) 빠르고 투명한 보안패치 릴리즈
– 오라클은 3개월 주기로 보안패치를 적용 (MySQL 은 2개월 주기)
– 간혹 보안 정보와 업그레이드가 싱크가 맞지 않는 경우가 발생함
– MySQL 은 릴리즈노트에 CVE 식별번호가 모두 리스트업 되지 않음
– 이슈와 픽스에 대한 확인이 모호하여 불만들이 많음
https://lists.launchpad.net/maria-discuss/msg00514.html
– 백포팅이 불가능하게 함, 최신의 MySQL 릴리즈로 업그레이드 할 수 밖에 없는 상황이 있음
– MariaDB 는 CVE 식별번호를 명시하고 이슈 관리를 잘함

3) 좀 더 다듬어진 기능들
– MySQL 5.7 에서 GIS 지원 하지만 MariaDB 는 더 많은 기능들 포함
https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/
https://mariadb.com/products/mariadb#highlight2-wrapper
– 예를 들어 GIS 는 MariaDB 5.3 에서 이미 소개되었음
– MariaDB 는 Dynamic 컬럼 지원

4) 더 많은 스토리지 엔진
– Connect 와 Cassandra, 샤딩을 위한 Spider, 프랙탈 인덱스의 TokuDB
– MySQL 에서도 써드파티 플러그인 형태로 사용은 가능하나 MariaDB 는 공식 릴리즈에 포함시켜 사용이 쉬움

5) 나은 성능
– MariaDB 옵티마이저 성능이 향상되었다고 주장하고 벤치 결과도 나와 있음
– 하지만 이것은 환경에 따라 실제와 좀 다를 수 있음, 아티클엔 3-5% 차이라고 언급

6) Galera 액티브-액티브 마스터 클러스터링
– 2007에 개발을 시작했지만 Percona 와 MariaDB 에서 도입한 이후에도 오라클 MySQL 에 공식적으로 포함되지 않았음
http://galeracluster.com/

7) 오라클 관리하의 불확실성
– 많은 사람들이 MySQL 을 살려두는 오라클의 본 목적을 의심함
– 오라클은 처음에 EU 반독점규제로 썬을 합병할 수 없었는데 MySQL을 살리는 것으로 합병을 승인했다고 함
http://www.oracle.com/us/corporate/press/042364
– 문서에 존속에 대한 만기가 2014년말로 되어있고 이미 지났음
– 아마 오라클 경영진들은 MySQL의 미래에 대해 생각하고 있을 것임
– 오라클이 미묘한 방법으로 벌써 MySQL 약화를 진행하고 있다는 논쟁도 있었음 (2번 참조)
https://lists.launchpad.net/maria-discuss/msg00514.html
– 하지만 오라클 MySQL 활동이 리브레오피스나 젠킨스로 빠르게 포크된 이후 사장된 오픈오피스나 허드슨에 비해 훨씬 성공적이라는 것은 참고할 만함
– 반면에 오라클과 참된 오픈소스 프로젝트 간에 선택이 주어질 때 소프트웨어 자유와 협력 개발의 이점을 잘 알고 있는 사람이라면 선택이 어렵지 않을 것임

8) 유명세가 높아지고 있음
– 2013년 위키피디아 마이그레이션
http://www.infoworld.com/article/2614268/open-source-software/wikipedia-dumps-mysql–hooks-up-with-mariadb.html
– 2013년 구글의 MariaDB 사용
http://www.zdnet.com/article/google-quietly-dumps-oracle-mysql-for-mariadb/
– WordPress, booking.com, Craiglist 의 서포트
https://mariadb.org/about/supporters/
– 페도라, OpenSUSE 의 기본 데이터베이스, RHEL7, SUSE Enterprise 12 부터 MySQL 대신 MariaDB 5.5 탑재, Ubuntu 14.04

9) 호환성과 쉬운 마이그레이션
– MariaDB 5.5 는 바이너리 교체로 MySQL 5.5 를 완전히 대체할 수 있음

10) 15년 이후에는 마이그레이션이 어려울 수 있음
– MariaDB 10.0 과 MySQL 5.6 에서 분기하기 시작했으나 어려움없이 전환이 가능함
– MariaDB 10.1 과 MySQL 5.7 에서는 어려울 수도 있으며 덤프 후 새 데이터베이스에 밀어넣어야 할 수 있음

relay log 가 깨져서 리플리케이션 중단 시 대처

https://www.percona.com/blog/2008/08/02/troubleshooting-relay-log-corruption-in-mysql/

다음과 같은 메시지가 찍히고 리플리케이션이 중단될 때

Last_Error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.

마스터 쪽 바이너리 로그의 문제가 아니라면 리플리케이션 연결 포인트를 재지정하는 것으로 IO쓰레드에 의해 미반영되고 쌓이고 있는 릴레이로그를 모두 purge 하고 재개할 수 있음

#현재 마스터 포지션
Master_Log_File: mysql-bin.040051
Read_Master_Log_Pos: 227179404

#IO쓰레드가 받고있는 릴레이 포지션
Relay_Log_File: mysql-relay.003082
Relay_Log_Pos: 227178650

#SQL쓰레드가 반영한 마스터 포지션
Relay_Master_Log_File: mysql-bin.040051
Exec_Master_Log_Pos: 227178491

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_LOG_FILE = Relay_Master_Log_File , MASTER_LOG_POS = Exec_Master_Log_Pos;
mysql> start slave;

다른 케이스로,
마스터의 바이너리로그가 expire_logs_days 일자가 지나서 소실되었고 대신 리플리카에서 받아놓은 릴레이로그가 있다면 릴레이로그 포지션 지정 (정확한 반영 포인트를 알아야겠지만..)

mysql> CHANGE MASTER TO RELAY_LOG_FILE = ‘Relay_Log_File’, RELAY_LOG_POS = Relay_Log_Pos;

Truncate Table 시 일시적인 서버 지연 현상

5.5, 5.6 버전 대에서 Truncate 시 일시적으로 서버에 지연이 생기는 현상이 있음
쓰레드들이 stall 되면서 순간적으로 running 쓰레드 수치가 치솟음

http://bugs.mysql.com/bug.php?id=68184
https://bugs.launchpad.net/percona-server/+bug/1241657

5.7.8 에서 픽스되었으나 5.6 이하에는 백포팅되지 않았음
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html
InnoDB: A TRUNCATE TABLE operation appeared to hang when run in parallel with a read-write workload. (Bug #19873470, Bug #74312)

언급된 내용에 따른 엔트리를 삭제할 때 LRU 스캔이 들어가면서 퍼포먼스 문제가 있는 것 같은데
DROP 이 예전에 비슷한 문제가 있어서 해결되었지만 이 때 TRUNCATE 는 빠졌다고 함;
AHI=OFF 인 서버에서 발생한 내용이라 본문에 언급된 AHI 에 국한되는 것 같진 않음

small limit 옵티마이저 버그도 백포팅되지 않은 것으로 알고 있는데
5.7 이 메인으로 되다보니 누락되는 것 같음

5.6 업그레이드 후 5.5 바이너리포맷 컬럼 import 오류

5.5 를 in-place 로 업그레이드 한 이후 테이블 리빌드 하지 않아 time 계열 컬럼이 5.5 바이너리 포맷이라면,
테이블스페이스의 import 시 *.cfg 에 의한 매칭 실패 오류가 나고

mysql> ALTER TABLE tab IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column created_at precise type mismatch.)

무시하고 cfg 삭제 후 진행하면 import 는 완료되나 테이블 접근할 때 테이블 스페이스 범위를 벗어나는 메모리 페이지 참조로 크래시가 발생함
null alter 로 바이너리 포맷을 맞춰준 뒤 수행 필요

InnoDB: Error: trying to access page number 919442480 in space 1151,
InnoDB: space name test_db/tab,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2016-02-25 10:22:17 7f0e10836700 InnoDB: Assertion failure in thread 139698383316736 in file fil0fil.cc line 5609
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
01:22:17 UTC – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=13
max_threads=2000
thread_count=5
connection_count=5
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8348665 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0xd64a4700
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f0e10835e18 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x35)[0x8e1555]
/usr/libexec/mysqld(handle_fatal_signal+0x494)[0x6678e4]
/lib64/libpthread.so.0(+0xf790)[0x7f19ae2ce790]
/lib64/libc.so.6(gsignal+0x35)[0x7f19acd6e625]
/lib64/libc.so.6(abort+0x175)[0x7f19acd6fe05]
/usr/libexec/mysqld[0xa9e7bf]
/usr/libexec/mysqld[0xa6caad]
/usr/libexec/mysqld[0xa6d21b]
/usr/libexec/mysqld[0xa5a864]
/usr/libexec/mysqld[0xa46916]
/usr/libexec/mysqld[0x9f1c16]
/usr/libexec/mysqld[0x94f058]
/usr/libexec/mysqld[0x947e09]
/usr/libexec/mysqld(_ZN7handler14ha_index_firstEPh+0x66)[0x5a7a06]
/usr/libexec/mysqld(_Z15join_read_firstP13st_join_table+0x81)[0x6c3411]
/usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x15d)[0x6c40cd]
/usr/libexec/mysqld(_ZN4JOIN4execEv+0x2fa)[0x6c499a]
/usr/libexec/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x250)[0x708670]
/usr/libexec/mysqld(_Z13handle_selectP3THDP13select_resultm+0x1a7)[0x708f17]
/usr/libexec/mysqld[0x6e2e6d]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x159f)[0x6e569f]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3c0)[0x6e9290]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1528)[0x6ea868]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b62bf]
/usr/libexec/mysqld(handle_one_connection+0x47)[0x6b63e7]
/usr/libexec/mysqld(pfs_spawn_thread+0x12a)[0xb0e17a]
/lib64/libpthread.so.0(+0x7a51)[0x7f19ae2c6a51]
/lib64/libc.so.6(clone+0x6d)[0x7f19ace2493d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (d5130210): select count(1) from tab
Connection ID (thread ID): 4854616
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
160225 10:22:17 mysqld_safe Number of processes running now: 0
160225 10:22:17 mysqld_safe mysqld restarted