일단은 SQL 쪽은 초보입니다.
데이터는 간신히 웹서핑해가면서 import 했습니다.
연구용 건강보험 데이터인데요
대략 구조가 테이블 6개 정도
레코드는 아주 작은 size 로 10억건 정도 됩니다.
primary key 는 모든 table 에 없습니다. (ID 나 청구번호 등이 중복되어서요)
이것에 대한 쿼리를 처리해주는 서버를 하나 올려야 되는데요.
DB server 는 무조건 스토리지에 투자하라는 글을 여러번 읽어서
SSD 4개로 raid0 (백업은 따로) 볼륨으로 사용을 해보았더니
쿼리 처리 속도가 SSD 단일 보다 빠르지 않았습니다.
쿼리는 테이블 1에서 환자 ID, 테이블 2에서 청구서 번호, 테이블 3에서 진단코드 를 조인하는 간단한 쿼리인데요
레코드 수가 많아서 그런지 쿼리 한판에 3~10 분 정도 소요가 됩니다.
이 시간을 줄일 수 있는 묘수가 있을까요?
DB 는 innodb 입니다.
DB 튜닝을 하라 하시면 사람을 불러야 되고요
하드웨어를 업글하라고 하시면 사면되겠는데
어느쪽부터 손을 보는 것이 현명한 일인지 여쭙습니다.
아래는 그저 하는 소리입니다.
SSD raid 0는 연속 쓰기나 읽기가 빨라질뿐이고 DB같은 4k 속도는 그대로거나 더느려지고
인텔 내장레이드를 쓸경우 DMI자체의 대역폭 한계가 있기때문에 어차피 병목이 생깁니다
NVMe SSD로 DB를 해보시는건 어떠신지요?
4k 속도가 AHCI에 비해서 매우 높고
자체 레이턴시도 매우 낮기 때문에 DB같은거에 좋지 않을까합니다.
물론 CPU 클럭빨도 좋으면 좋져
사용하시는 하드웨어 전체를 상세히 가르쳐 주세요..
참고로 전 프로그램, 디비같은거 전혀 모릅니다..
그런데, SQL 튜닝도 나름...단계가 있으니....
괜찮게 튜닝하는 사람을 섭외하시기 바랍니다.
만일 그래도, 안된다면....HW적인 튜닝인데...이건, HW 의 성능향상폭만큼 SQL 성능의 향상이 되다보니...
비용과 확장의 한계의 문제겠죠
그런데, 10억건인데...3~10분이나 걸리다니...개인적으로 궁금하긴 하네요
(오라클 DBA/튜너 입장에서요....뭐 MYSQL도 간단한건 하지만요....그냥...엔지니어적으로 궁금하긴 하네요)
한번 튜닝해보고 싶긴하네요....
SSD가 온보드 SATA나 온보드 SAS로 묶었다면 부드럽지는 않겠네요..(그렇다고 디스크 묶은 것보다는 퍼포먼스는 좋습니다.)
캐쉬 달린 레이드카드에 SAS HDD 4-8개 정도 1+0으로 묶어주면 더 확실할 것입니다..
저는 일단 레이드 파인지라..
프로그램 튜닝이 더 중요해보입니다..
select * where ... 하루종일 입니다..
SELECT * FROM nhid.gj INNER JOIN nhid.gy20 ON nhid.gj.PERSON_ID = nhid.gy20.PERSON_ID;
요게 그나마 3분 정도 쿼리구요
nhid.gj 는 레코드 220만, nhid.gy20 은 레코드 1억2천만건의 비교적 작은(?) 테이블입니다.
200만건에서 1억2천만건을 비교 해야되니
그정도 시간은 걸릴듯 합니다..
200만 * 1억2천만 ....
현재 쿼리문은 특정 키값없이 두 테이블의 값이 있는냐 입니다..
뭐때문에 이런씩의 비교 쿼리를 해야되는지는 모르겠네요..
인쇄물인가요??..
어떤 업무에 필요한건가요??..
업무는 건강보험적용 행태 분석입니다. ( 뭐 무슨 진단은 진료비가 어쩌고 의료자원이 어쩌고 이런.. )
아래내용도 고려하겠습니다.
다른 방법은 db 테이블을 쪼개어 사용 하는 것입니다..
1억2천만건의 자료를 nhid.gy20-1,nhid.gy20-2....
이렇게 나누어 기간별 검색으로 하심이..
저위 처럼 쿼리문 걸리면 끝날때까지 기다려야됩니다..
아니면 오라클....
하지만 대부분 정확히 인덱스가 적용되지않은 경우가 많습니다
쿼리 실행후 다른 콘솔을 열어 show processlist등을 해보시면 동작중의 쿼리를 알수 있습니다
인덱스부터 확인하시고 그후에 튜닝을 하시는게 맞아보입니다
일단 책보고 머리튜닝부터 해야겠습니다.
인덱스는 필요하실것 같고 조인문을 활용하셔도 될거 같고.
(작업 진행시 점차적으로 서버리소스가 cpu , i/o가 full load로 올라간다면 튜닝밖에 답이 없긴 합니다..)
우선 slow quary한번 적용해 분석해 수정해보는 것도 좋을것 같습니다.
H/W로 투자하실거라면
차라리 mysql 이면 빵빵한 성능의 단일 H/W보다는 좀더 덜 빵빵한? H/W두대로 repliaction으로 구성하시는게 좋지 않을까 하네요
일단 select가 많으시다면 Master & Slave Replication 구성이 괜찬을듯 합니다.
MySQL-Repliction 찾아보시는거 추천드립니다 ^^;
건승을 빕니다. ㅜㅠ
mysql 은 목적에 합당하지 않다는 결론을 내려주시었습니다.
친절하신 답변 모두 감사드립니다.
2cpu 짱~
초울트라 슈퍼갑인지라 저번에 전세계적으로 3배를 가격 올리더라고요..
innoDB는 key가 있어야 좋습니다.
1번째 컬럼에 unique key를 auto로 잡아주는게 속도에 좋습니다.
스토리지는 SSD를 raid 0 잡을 필요 없습니다.
그냥 SSD 1개 쓰나 몇개 잡아서 0 만드나 그게 그겁니다.
제대로된 코딩은 disk io가 크지 않습니다.
이것은 SQL의 방법론이 잘못된 경우 입니다.
10억건. 별로 많지 않습니다.
한번에 full join을 하는 것은 엄청난 부하가 소요되는 것이고 temp db를 disk에 생성 합니다.
그래서, temp db를 메모리에 생성하도록 my.cnf 설정을 바꿔야 합니다.
RDB의 경우 3개의 테이블을 1번에 join하는 것보다
2개를 join하고 그 결과를 가지고 마지막 테이블을 join하는 것이 더 빠를 수 있고
join 보다는 table operation이 더 빠를 수 있습니다.
DB를 튜닝하고 프로그램하는 방법론의 이슈이지 MySQL이 Oracle보다 못해서 그런 것은 절대로 아닙니다.
Oracle을 사용해도 비슷한 상황이구요.
어떤 버젼의 MySQL을 쓰는지 모르겠지만, 속도는 5.5.x가 가장 빠르고 좋고
MariaDB는 10.0.x 버젼대에 해당 합니다.
2CPU가 델 R620 장비 1대에서 웹+DB 모두 돌아가지만 속도 괜챦습니다.
SSD도 싼 것 씁니다.
튜닝빨 입니다^^
my.cnf는 올려뒀으니, 참조 하시기 바랍니다.
http://www.2cpu.co.kr/bbs/board.php?bo_table=PDS&wr_id=11246
많은 분들이 말씀해주셨지만 기본적으로 설계가 잘못된 디비는 하드웨어를 아무리 보강해도 한계에 금방 도달합니다.
테이블이 크고 거기에 큰테이블들끼리 조인을 걸어서 작업하면 당연히 느릴수 밖에 없습니다.