쪽팔리면 질문하지 맙시다. 소중한 답변 댓글을 삭제하는건 부끄러운 일 입니다
MariaDB 10.6을 사용중입니다.
숫자로만 이루어진 DB의 튜닝 방법에 대해 문의합니다.
서버는 16코어, 메모리는 32GB, NVMe SSD 30TB 입니다.
CPU는 Xeon Gold 5415+ 입니다.
테이블은 이런 구성입니다.
| 필드설명 | 날짜/시간 | X축 | Y축 | 농도 |
| 데이터형식 | DATETIME | DECIMAL(20,26) | DECIMAL(20,26) | DECIMAL(20,26) |
| 값 예시 | 2022-05-15 9:10:00 | 105.6089931 | 386.3575651 | 21.11433506 |
데이터 용량은 약 50억행 정도 됩니다.
이 DB는 읽기만 하고, 쓰기는 이루어지지 않습니다.
(정확하게는 쓰기는 아주 가끔 한 번에 이루어집니다.)
(그러므로 쓰기는 고려하지 않아도 됩니다.)
날짜/시간 값으로 테이블 파티셔닝을 할 것입니다.
(정확하게는 날짜/시간 중 '년' 혹은 '년-월' 정도)
쿼리는 한 개 입니다.
특정 X축, Y축 값을 1년치 끄집어 냅니다.
쿼리문으로 만들면 이렇습니다.
SELECT *
FROM TBL
WHERE (날짜/시간 BETWEEN '2020-01-01' AND '2020-12-31 23:59:59')
AND (X축=[X축값]) AND (Y축=[Y축값])
인덱스나 테이블 파티셔닝 같은 것은 여러 실험을 통해서 진행할 예정입니다.
이것이 질문은 아닙니다.
MariaDB의 설정이 질문입니다.
다음과 같이 기본적인 설정만 한 상황입니다.
innodb_buffer_pool_size = 20G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
thread_handling = pool-of-threads
thread_pool_size = 16
table_open_cache = 2000
query_cache_type = 0
query_cache_size = 0
이런 DB의 특성을 보았을 때, 추가적으로 무엇을 어떻게 설정하면 좋을까요?
=============
MariaDB 10.6에서 숫자로만 이루어진 대용량 데이터베이스를 최적화하기 위한 설정을 고려할 때, 주로 다음과 같은 설정을 고려해야 합니다. 아래 설정들은 성능 향상을 도모하고 데이터를 효율적으로 관리하기 위한 것입니다.
1. InnoDB 설정:
- `innodb_buffer_pool_size`: 이 설정은 InnoDB 버퍼 풀의 크기를 결정합니다. 대용량 데이터베이스에 대한 메모리 할당이 중요합니다. 더 큰 메모리 할당을 고려해보세요. 시스템의 가용 메모리를 고려하여 적절한 크기로 설정하세요. 대략 70-80%의 가용 메모리 크기로 설정하는 것이 일반적입니다.
- `innodb_log_file_size`: InnoDB 로그 파일의 크기를 조정할 수 있습니다. 로그 파일 크기가 작으면 쓰기 작업이 느려질 수 있으므로 적절한 크기로 설정하세요. 2GB는 꽤 큰 크기이므로 쓰기 작업이 빈번하지 않다면 줄일 수도 있습니다.
- `innodb_flush_method`: 이 설정은 InnoDB의 디스크 쓰기 방식을 결정합니다. `O_DIRECT`는 좋은 옵션이며 변경하지 않는 것이 좋습니다.
- `innodb_io_capacity` 및 `innodb_io_capacity_max`: I/O 성능을 높이기 위해 설정할 수 있는 옵션입니다. 디스크의 I/O 성능을 고려하여 조정하세요. 높은 값을 설정하여 디스크 성능을 최대한 활용하세요.
2. 쿼리 캐시 설정:
- `query_cache_type` 및 `query_cache_size`: 쿼리 캐시를 사용하지 않는 것은 대용량 데이터베이스의 읽기 성능을 향상시킬 수 있습니다. 따라서 `query_cache_type`을 0으로 설정하고 `query_cache_size`를 0으로 설정하세요.
3. 스레드 설정:
- `thread_handling` 및 `thread_pool_size`: MariaDB의 스레드 관리 방식을 설정할 수 있습니다. 스레드 풀을 사용하려면 `thread_handling`을 `pool-of-threads`로 설정하고 `thread_pool_size`를 조정하세요. 스레드 풀은 여러 클라이언트 요청을 효율적으로 처리하는 데 도움을 줄 수 있습니다.
4. 테이블 파티셔닝:
- 날짜/시간을 기반으로 테이블 파티셔닝을 고려하고, 파티션 키를 잘 선택하여 데이터를 분산시키세요. 이렇게 하면 쿼리의 성능을 향상시킬 수 있습니다.
5. 인덱스:
- 테이블에서 사용하는 인덱스를 최적화하세요. 쿼리에서 자주 사용되는 열에 인덱스를 생성하면 쿼리 성능이 향상될 수 있습니다.
6. 하드웨어 및 파일 시스템:
- 서버의 하드웨어 성능 및 파일 시스템 설정도 고려해야 합니다. 디스크 속도, CPU 성능, 메모리 크기 등을 확인하고 필요한 경우 업그레이드하거나 조정하세요.
7. 백업 및 복구 계획:
- 대용량 데이터베이스의 안정성을 위해 정기적인 백업 및 복구 계획을 수립하세요.
마지막으로, 설정 변경 시에는 주의해서 하고, 변경 후에는 성능을 모니터링하며 조정해야 합니다. 시스템의 특성에 따라 최적의 설정값이 다를 수 있으므로 성능 테스트와 모니터링이 중요합니다.
ChatGPT 이니만큼, 확인해 보겠습니다.
마랴디비가 대용량을 소화할수 있을지가 관건이네요.
웨어절 컬럼 넌클러스터 인덱스로 한번더 잡아주고 파티셔닝 잡으면 될듯 싶네요.
1년치 데이터 긁어가는건 거이 SSD속도에 달려 있습니다.
파티셔닝의 목적은 속도가 빨라지게 한다는 목적보다는 파티션으로 쪼갠 논리적 ID로 트랜잭션 경함을 피해가는 장점이 있어서입니다. 그래서 건수가 많고 범위가 크면 그냥 SSD속도에 맡기는수 밖에 없습니다.
만약 같은 데이터를 여러번 조회하거나 긁어간다고 하면 임시테이블을 생성하는 방법도 있습니다.
'파티셔닝의 목적은 속도가 빨라지게 한다는 목적보다는 파티션으로 쪼갠 논리적 ID로 트랜잭션 경함을 피해가는 장점이 있어서입니다.'
'X축'과 'Y축'은 인덱스를 잡을 예정입니다.
SSD도 NVMe 엔터프라이즈용이니, 더 이상 빠르게 할 수는 없겠네요. ^^
어차피 특정 X축, Y축에 대해서 '1년치' 데이터 전체이다보니..
성능이 안 나오면 특정 X축, Y축에 대해서 테이블을 다 쪼개버리는 방법도 생각하고 있습니다.
(쓰기가 거의 없는 DB라서요..)
클러스터 인덱스로 잡으면 CPU 점유율을 사용해서 더 빨라지긴 합니다.
압축테이블 지원하면 압축테이블도 써보세요.쓰기속도는 좀 느려지나 읽기속도는 더 빨라집니다.
MSSQL에서 대용량 테이블은 파티션과 압축테이블을 필수로 사용되고 있습니다.
PK/FK 연관되는 테이블이 많아지고 조인하는 경우가 많아지는 순간 지옥문이 열립니다.
오라클 사서 쓸 돈으로 중저가 서버사서 램으로 발라버리거나
차라리 여러대 병렬로 구성하면 성능도 훨씬 좋고, 병렬이니 자연스럽게 HA까지 되죠
오라클 써서 정교하게 튜닝해서 쓰느니 그 돈으로 하드웨어에 바르면 발로짜도 오라클보다 빠릅니다.
솔~~직히 오라클은 면피용 아닌가요?
오라클 썻는데 서버가 터졌다... 오라클인데도 터졌네요
mysql 썻는데 서버가 터녔다 ... mysql 이라 터졌다. 누가 mysql 쓰래?
이런 상황싫어서 관공서에서 오라클 쓰는거지 저런 말 안하는데서 누가 오라클 쓰나요.
명성, 성능 인정하죠. 하지만 가성비는 절대 아니라 생각합니다.
아니면 실험적인 PG-Strom 같은 솔루션?
https://heterodb.github.io/pg-strom/release_v3.0/
https://positivemh.tistory.com/474
상당히 모험적이긴 합니다. ㅎㅎ
마리아DB가 그리 활발하게 워킹하지 않아서 mysql쪽의 성능이 다시 좋아졌습니다.
그런데 데이터 형식 자체가 너무 단순해서 튜닝할 수 있는 요소가 많지 않을것 같습니다.
X,Y축은 hash로 인덱스 잡으셨을 거고,
날짜/시간으로 파티션 잡았다면, core갯수보다 조금 작은 만큼으로 파티션을 나누면 딱 좋을것 같습니다.
그런데 TPS가 높다면 솔직히 core갯수와 파티션도 좀 무의미 해질것 같구요..
데이터는 30TB인데 메모리가 32GB밖에 안되면 캐시의 효과를 내기도 쉽지 않을거고..
여러가지로 튜닝할 요소가 많지 않을 구성이네요.
차라리 hbase로 노드 여러개 구성하는게 그나마 조금 TPS측명에서 좋은 결과를 낼 수 있을 듯한 데이터 네요..
몰랐습니다.
말씀하신대로, 튜닝은 결과적으로 의미 없을 것 같기는 합니다. ^^
계속 연구해 보겠습니다.
년 단위로 테이블을 만들어서 핸들링 하시면 큰 문제가 없을 것 같은데요.
실제로 수행하시면서 최적화 작업과 함께 테스트 해보셔요..
그렇다면, x, y값은 VARCHAR로 하는 것이 나을 수도 있겠습니까?
인덱싱을 하면 어차피 줄을 세우는 거라서 decimal 형식이 안될일은 없을 것 같고,
varchar 로 하게 되면 문자열이 되서 대소 비교할때 길이때문에 이슈가 될 수 있으니
작업을 순서대로 하시면서 테스트 하시면 될 것 같습니다.
1. 년도기준 테이블 파티셔닝
2. 해당 데이터 주입 및 데이터 건수 확인 및 조회, 성능 확인
3. 인덱싱(x,y) 후 성능 차이나는지 확인
4. 느리면 년월 테이블로 파티셔닝
등의 순서로 진행하시면서 상태 확인하시면 되겠지요.
쿼리 캐시라던지 메모리량, 쓰기로그 관련 등은 반복실행 환경이 아닌이상 큰 영향은 없을 것 같습니다.
오라클에서 파티션 적용하는거와 mariradb/mysql 와 postgresql 에서 파티션 적용시 각각 dbms 특성이 다르므로, 매우 주의해서 파티션 적용을 해야됩니다.
오라클 : global index 지원
mariadb/mysql/postgresql : global index 미지원
오라클/postgresql : heap 방식 테이블 구조
mariadb/mysql + innodb : 클러스터링 인덱스 구조
만일 mariadb에 pk또는 uk 를 안잡으면, 엔진 내부적으로 랜덤값을 써서 pk를 잡으며,
문제는 이러한 구조인경우 index scan 이후 테이블에 접근할때 buffer hit 율이 매우 낮아지죠
결국 io 가 매우 높아지는 문제가 발생하며, 성능저하가 발생하죠
뭐 방법이 몇가지 있긴하나, 너무 길어지니,
관련된 mariadb/mysql + innodb + 파티션 + 인덱스와 테이블(pk) 관계 등의 정보를 공부하셔야 될겁니다.
데이터의 특성에 따라서, mariadb를 사용한다면, 거의 선택지는 몇개 없을겁니다.
그리고, 파라미터는 구글링하면 권장 파라미터 산정해주는 웹사이트를 참고하세요
저런 파라미터값을 완전히 엉뚱한 값을 사용하면, 성능저하가 있지만, 적당히 알려진 수치를 넣으면, 성능차이는 많이 안날겁니다.
그리고, 읽기 위주라면, 딱히 손댈만한게 없긴합니다.
대략 innodb_io_capacity 이 수치를 20000정도로 올리시면 될듯합니다
그런데, nvme ssd 라면 일반 사용자용 m.2 nvme ssd라면, 가능하면 서버용 m.2 또는 u.2 ssd로 바꾸는걸 강력히 권장합니다.
일반 사용자용 m.2 nvme 는 ssd 캐쉬영역이 있어서, 그 범위를 넘어서면 성능저하가 심하죠
서버용은 단일 io보다는 멀티 io에 최적화 된거라, 동시에 여러개 돌려도 되죠ㅏ
권장 파라미터 산정해주는 웹사이트가 있군요?
또 하나 배워갑니다.
io_capacity 수치도 참고해서 설정해 보겠습니다.
SSD는 엔터프라이즈용입니다. ^^
파티션은 일반적으로 월단위로 쪼갭니다. 1년을 추려낼거니 월로 나누세요.
위에 글에 글로벌 인덱스도 지원안되는거 보니 낭패이긴 하네요. 컬럼스토어 인덱스에 기대를 걸어볼수 밖에 없을듯 싶습니다.
희망이 생기네요.
(물론, 엄청난 결과가 나오지 않을 수는 있겠지만요..)
고맙습니다~