development

인덱스로 인해 레코드 수가 증가함에 따라 SQLite 삽입 속도가 느려집니다.

big-blog 2020. 12. 9. 21:08
반응형

인덱스로 인해 레코드 수가 증가함에 따라 SQLite 삽입 속도가 느려집니다.

원래 질문

배경

SQLite 는 50k 삽입 / 초 정도의 삽입 속도를 달성하기 위해 미세 조정해야 한다는 것은 잘 알려져 있습니다 . 느린 삽입 속도와 풍부한 조언 및 벤치 마크에 대한 많은 질문이 여기에 있습니다.

또한 SQLite가 올바른 설정에 문제를 일으키지 않는 50GB 이상의 보고서로 대량의 데이터를 처리 할 수 ​​있다는 주장 도 있습니다 .

이 속도를 달성하기 위해 여기와 다른 곳에서 조언을 따랐으며 35k-45k 인서트 / 초에 만족합니다. 내가 가진 문제는 모든 벤치 마크가 1m 미만의 레코드로 빠른 삽입 속도 만 보여 준다는 것입니다. 내가보고있는 것은 삽입 속도가 테이블 크기에 반비례하는 것 같습니다 .

발행물

내 사용 사례에서는 [x_id, y_id, z_id]링크 테이블에 몇 년 동안 (1m 행 / 일) 500m ~ 1b 튜플 ( ) 을 저장해야 합니다. 값은 1에서 2,000,000 사이의 모든 정수 ID입니다. 에 단일 인덱스가 z_id있습니다.

성능은 처음 10m 행, ~ 35k 삽입 / 초의 경우 훌륭하지만 테이블에 ~ 20m 행이 있으면 성능이 저하되기 시작합니다. 나는 현재 약 100 개의 삽입물 / 초를보고 있습니다.

테이블의 크기는 특별히 크지 않습니다. 행이 20m 인 경우 디스크 크기는 약 500MB입니다.

이 프로젝트는 Perl로 작성되었습니다.

질문

이것이 SQLite에서 큰 테이블의 현실 입니까, 아니면 행이 10m 이상인 테이블에 대해 높은 삽입 률 을 유지 하는 비결이 있습니까?

가능한 경우 피하고 싶은 알려진 해결 방법

  • 인덱스 삭제, 레코드 추가 및 재 인덱싱 : 해결 방법으로는 좋지만 업데이트 중에 DB를 계속 사용할 수 있어야하는 경우에는 작동하지 않습니다. x 분 / 일 동안 데이터베이스에 완전히 액세스 할 수 없게 만드는 것은 작동하지 않습니다.
  • 테이블을 더 작은 하위 테이블 / 파일로 나누기 : 이것은 단기간에 작동하며 이미 실험했습니다. 문제는 쿼리 할 때 전체 기록에서 데이터를 검색 할 수 있어야한다는 것입니다. 즉, 결국 62 개의 테이블 첨부 제한에 도달하게됩니다. 임시 테이블에 결과를 첨부하고, 수집하고, 요청 당 수백 번 분리하는 것은 많은 작업과 오버 헤드로 보이지만 다른 대안이 없으면 시도해 보겠습니다.
  • 세트 SQLITE_FCNTL_CHUNK_SIZE : 나는 C (?!)를 몰라서이 일을하기 위해 배우지 않고 싶습니다. 그래도 Perl을 사용하여이 매개 변수를 설정하는 방법을 볼 수 없습니다.

최신 정보

대규모 데이터 세트를 처리 할 수 ​​있다는 SQLite의 주장에도 불구하고 인덱스가 삽입 시간을 점점 더 느리게 만든다는 Tim의 제안 에 따라 다음 설정으로 벤치 마크 비교를 수행했습니다.

  • 삽입 된 행 : 1,400 만
  • 커밋 배치 크기 : 50,000 개 레코드
  • cache_sizepragma : 10,000
  • page_sizepragma : 4,096
  • temp_storepragma : 메모리
  • journal_modepragma : 삭제
  • synchronouspragma : 끄기

내 프로젝트에서는 아래 벤치 마크 결과와 같이 파일 기반 임시 테이블이 생성되고 CSV 데이터 가져 오기에 대한 SQLite의 기본 제공 지원이 사용됩니다. 그런 다음 임시 테이블이 수신 데이터베이스에 연결되고 50,000 개의 행 집합이 insert-select명령문 과 함께 삽입됩니다 . 따라서 삽입 시간은 파일 대 데이터베이스 삽입 시간이 아니라 테이블 대 테이블 삽입 속도 를 반영 합니다 . CSV 가져 오기 시간을 고려하면 속도가 25 ~ 50 % 감소합니다 (매우 대략적인 추정치로 CSV 데이터를 가져 오는 데 오래 걸리지 않음).

인덱스가 명확하면 테이블 크기가 증가함에 따라 삽입 속도가 느려집니다.

SQLite 삽입 속도 및 테이블 크기 플롯

위의 데이터에서 SQLite가 처리 할 수 ​​없다는 주장보다는 Tim의 대답에 정답을 할당 할 수 있다는 것이 분명합니다. 해당 데이터 세트 를 인덱싱하는 것이 사용 사례의 일부가 아닌 경우 분명히 대규모 데이터 세트 를 처리 할 수 있습니다 . 저는 SQLite를 로깅 시스템의 백엔드로 사용하고 있는데, 지금은 색인화 할 필요 가 없습니다 . 그래서 제가 경험 한 속도 저하에 상당히 놀랐습니다.

결론

누군가가 발견하면 스스로 SQLite는을 사용하여 많은 양의 데이터를 저장하고자 하고 는, 인덱스가 파편을 사용 해답이 될 수 있습니다. 나는 결국 z4,096 개의 데이터베이스 중 하나에 대한 할당을 결정하기 위해 고유 한 열인 MD5 해시의 처음 세 문자를 사용 하기로 결정했습니다. 내 유스 케이스는 본질적으로 아카이브이기 때문에 스키마는 변경되지 않으며 쿼리에는 샤드 워킹이 필요하지 않습니다. 매우 오래된 데이터가 감소하고 결국 폐기되기 때문에 샤딩, 프라 그마 설정, 심지어 일부의 조합 있도록 데이터베이스 크기에 한계가있다 드 정상화 위의 벤치마킹하는 것은, 삽입을 유지하는 속도에 따라, 그 뜻을 나에게 좋은 균형을 제공합니다 최소 10k 삽입 / 초.

귀하의 요구 사항은 특정 찾는 경우 z_id와 x_ids및 y_ids그 연결을 (별개의 신속의 범위 선택에서 z_ids) 당신이 즉시에 당신의 방법을 찾을 수 있도록 할가 아닌 인덱스 해시 테이블 중첩 된 관계형 DB로 볼 수 있었다를 특히 z_id얻기 위해 그 y_ids와 x_ids- 색인 오버 헤드와 인덱스가 성장함에 따라 삽입시 수반되는 성능 저하없이. 클 럼핑 (버킷 충돌이라고도 함)을 방지하려면 z_id변동이 가장 큰 숫자 (오른쪽 가중치) 에 가장 큰 가중치를 두는 키 해싱 알고리즘을 선택하십시오 .

PS B- 트리를 사용하는 데이터베이스는 처음에는 선형 해싱을 사용하는 db보다 빠르게 나타날 수 있지만 삽입 성능은 b- 트리의 성능이 저하되기 시작함에 따라 선형 해시와 동일한 수준을 유지합니다.

PPS @ kawing-chiu의 질문에 답하기 위해 여기에 관련된 핵심 기능은 이러한 데이터베이스가 레코드 키를 입력으로 사용하는 해싱 알고리즘에 의해 레코드의 물리적 위치가 결정되는 소위 "희소"테이블에 의존한다는 것입니다. 이 접근 방식 을 사용하면 인덱스 중개자없이 테이블에서 레코드의 위치를 직접 검색 할 수 있습니다.. 인덱스를 순회하거나 인덱스를 재조정 할 필요가 없기 때문에 테이블이 더 밀집되어 있어도 삽입 시간은 일정하게 유지됩니다. 반대로 b- 트리의 경우 인덱스 트리가 커짐에 따라 삽입 시간이 저하됩니다. 많은 수의 동시 삽입이있는 OLTP 애플리케이션은 이러한 희소 테이블 접근 방식의 이점을 얻을 수 있습니다. 레코드는 테이블 전체에 흩어져 있습니다. 희소 테이블의 "툰드라"에 흩어져있는 레코드의 단점은 우편 번호와 같이 공통된 값을 갖는 많은 레코드 집합을 수집하는 속도가 느릴 수 있다는 것입니다. 해시 된 스파 스 테이블 접근 방식은 개별 레코드를 삽입 및 검색하고 일부 필드 값이 공통 인 대규모 레코드 집합이 아닌 관련 레코드 네트워크 를 검색하도록 최적화되어 있습니다 .

중첩 관계형 데이터베이스는 행의 열 내 에서 튜플을 허용하는 데이터베이스입니다 .

좋은 질문과 매우 흥미로운 후속 조치!

간단히 말하고 싶습니다. 테이블을 더 작은 하위 테이블 / 파일로 나누고 나중에 첨부하는 것은 62 개의 연결된 데이터베이스라는 하드 한도에 빠르게 도달 할 수 있기 때문에 옵션이 아니라고 말씀하셨습니다. 이것이 완전히 사실이지만, 중간 옵션을 고려하지 않았다고 생각합니다. 데이터를 여러 테이블로 분할 하지만 동일한 단일 데이터베이스 (파일)를 계속 사용하는 것입니다.

내 제안이 실제로 성능에 영향을 미치는지 확인하기 위해 매우 조잡한 벤치 마크를 수행했습니다.

개요:

CREATE TABLE IF NOT EXISTS "test_$i"
(
    "i" integer NOT NULL,
    "md5" text(32) NOT NULL
);

데이터-2 백만 행 :

  • i = 1..2,000,000
  • md5 = md5 16 진수 다이제스트 i

각 트랜잭션 = 50,000 INSERT초.

데이터베이스 : 1; 테이블 : 1; 인덱스 : 0

0..50000 records inserted in 1.87 seconds
50000..100000 records inserted in 1.92 seconds
100000..150000 records inserted in 1.97 seconds
150000..200000 records inserted in 1.99 seconds
200000..250000 records inserted in 2.19 seconds
250000..300000 records inserted in 1.94 seconds
300000..350000 records inserted in 1.94 seconds
350000..400000 records inserted in 1.94 seconds
400000..450000 records inserted in 1.94 seconds
450000..500000 records inserted in 2.50 seconds
500000..550000 records inserted in 1.94 seconds
550000..600000 records inserted in 1.94 seconds
600000..650000 records inserted in 1.93 seconds
650000..700000 records inserted in 1.94 seconds
700000..750000 records inserted in 1.94 seconds
750000..800000 records inserted in 1.94 seconds
800000..850000 records inserted in 1.93 seconds
850000..900000 records inserted in 1.95 seconds
900000..950000 records inserted in 1.94 seconds
950000..1000000 records inserted in 1.94 seconds
1000000..1050000 records inserted in 1.95 seconds
1050000..1100000 records inserted in 1.95 seconds
1100000..1150000 records inserted in 1.95 seconds
1150000..1200000 records inserted in 1.95 seconds
1200000..1250000 records inserted in 1.96 seconds
1250000..1300000 records inserted in 1.98 seconds
1300000..1350000 records inserted in 1.95 seconds
1350000..1400000 records inserted in 1.95 seconds
1400000..1450000 records inserted in 1.95 seconds
1450000..1500000 records inserted in 1.95 seconds
1500000..1550000 records inserted in 1.95 seconds
1550000..1600000 records inserted in 1.95 seconds
1600000..1650000 records inserted in 1.95 seconds
1650000..1700000 records inserted in 1.96 seconds
1700000..1750000 records inserted in 1.95 seconds
1750000..1800000 records inserted in 1.95 seconds
1800000..1850000 records inserted in 1.94 seconds
1850000..1900000 records inserted in 1.95 seconds
1900000..1950000 records inserted in 1.95 seconds
1950000..2000000 records inserted in 1.95 seconds

데이터베이스 파일 크기 : 89.2 MiB.

데이터베이스 : 1; 테이블 : 1; 인덱스 : 1 ( md5)

0..50000 records inserted in 2.90 seconds
50000..100000 records inserted in 11.64 seconds
100000..150000 records inserted in 10.85 seconds
150000..200000 records inserted in 10.62 seconds
200000..250000 records inserted in 11.28 seconds
250000..300000 records inserted in 12.09 seconds
300000..350000 records inserted in 10.60 seconds
350000..400000 records inserted in 12.25 seconds
400000..450000 records inserted in 13.83 seconds
450000..500000 records inserted in 14.48 seconds
500000..550000 records inserted in 11.08 seconds
550000..600000 records inserted in 10.72 seconds
600000..650000 records inserted in 14.99 seconds
650000..700000 records inserted in 10.85 seconds
700000..750000 records inserted in 11.25 seconds
750000..800000 records inserted in 17.68 seconds
800000..850000 records inserted in 14.44 seconds
850000..900000 records inserted in 19.46 seconds
900000..950000 records inserted in 16.41 seconds
950000..1000000 records inserted in 22.41 seconds
1000000..1050000 records inserted in 24.68 seconds
1050000..1100000 records inserted in 28.12 seconds
1100000..1150000 records inserted in 26.85 seconds
1150000..1200000 records inserted in 28.57 seconds
1200000..1250000 records inserted in 29.17 seconds
1250000..1300000 records inserted in 36.99 seconds
1300000..1350000 records inserted in 30.66 seconds
1350000..1400000 records inserted in 32.06 seconds
1400000..1450000 records inserted in 33.14 seconds
1450000..1500000 records inserted in 47.74 seconds
1500000..1550000 records inserted in 34.51 seconds
1550000..1600000 records inserted in 39.16 seconds
1600000..1650000 records inserted in 37.69 seconds
1650000..1700000 records inserted in 37.82 seconds
1700000..1750000 records inserted in 41.43 seconds
1750000..1800000 records inserted in 49.58 seconds
1800000..1850000 records inserted in 44.08 seconds
1850000..1900000 records inserted in 57.17 seconds
1900000..1950000 records inserted in 50.04 seconds
1950000..2000000 records inserted in 42.15 seconds

데이터베이스 파일 크기 : 181.1 MiB.

데이터베이스 : 1; 테이블 : 20 개 (레코드 100,000 개당 1 개) 인덱스 : 1 ( md5)

0..50000 records inserted in 2.91 seconds
50000..100000 records inserted in 10.30 seconds
100000..150000 records inserted in 10.85 seconds
150000..200000 records inserted in 10.45 seconds
200000..250000 records inserted in 10.11 seconds
250000..300000 records inserted in 11.04 seconds
300000..350000 records inserted in 10.25 seconds
350000..400000 records inserted in 10.36 seconds
400000..450000 records inserted in 11.48 seconds
450000..500000 records inserted in 10.97 seconds
500000..550000 records inserted in 10.86 seconds
550000..600000 records inserted in 10.35 seconds
600000..650000 records inserted in 10.77 seconds
650000..700000 records inserted in 10.62 seconds
700000..750000 records inserted in 10.57 seconds
750000..800000 records inserted in 11.13 seconds
800000..850000 records inserted in 10.44 seconds
850000..900000 records inserted in 10.40 seconds
900000..950000 records inserted in 10.70 seconds
950000..1000000 records inserted in 10.53 seconds
1000000..1050000 records inserted in 10.98 seconds
1050000..1100000 records inserted in 11.56 seconds
1100000..1150000 records inserted in 10.66 seconds
1150000..1200000 records inserted in 10.38 seconds
1200000..1250000 records inserted in 10.24 seconds
1250000..1300000 records inserted in 10.80 seconds
1300000..1350000 records inserted in 10.85 seconds
1350000..1400000 records inserted in 10.46 seconds
1400000..1450000 records inserted in 10.25 seconds
1450000..1500000 records inserted in 10.98 seconds
1500000..1550000 records inserted in 10.15 seconds
1550000..1600000 records inserted in 11.81 seconds
1600000..1650000 records inserted in 10.80 seconds
1650000..1700000 records inserted in 11.06 seconds
1700000..1750000 records inserted in 10.24 seconds
1750000..1800000 records inserted in 10.57 seconds
1800000..1850000 records inserted in 11.54 seconds
1850000..1900000 records inserted in 10.80 seconds
1900000..1950000 records inserted in 11.07 seconds
1950000..2000000 records inserted in 13.27 seconds

데이터베이스 파일 크기 : 180.1 MiB.

보시다시피 데이터를 여러 테이블로 분할하면 삽입 속도가 거의 일정하게 유지됩니다.

불행히도 이것이 SQLite의 큰 테이블 제한이라고 말하고 싶습니다. 있어 설계되지 대규모 또는 대용량 데이터 세트에서 작동 할 수 있습니다. 프로젝트 복잡성이 크게 증가 할 수 있음을 이해하지만 필요에 적합한보다 정교한 데이터베이스 솔루션을 연구하는 것이 좋습니다.

연결 한 모든 것에서 액세스 속도에 대한 테이블 크기는 직접적인 절충안입니다. 둘 다 가질 수는 없습니다.

In my project, I couldn't shard the database, as it's indexed on different columns. To speed up the inserts, I've put the database during creation on /dev/shm (=linux ramdisk) and then copy it over to local disk. That obviously only works well for a write-once, read-many database.

I suspect the Index's hash value collision causes the insert speed slow.

When we have many many rows in one table, and then the indexed column hash value collision will happen more frequently. It means Sqlite engine needs to calculate the hash value two times or three times, or maybe even four times, in order to get a different hash value.

So I guess this is the root cause of the SQLite insert slowness when the table has many rows.

이 점은 왜 샤드를 사용하여이 문제를 피할 수 있는지 설명 할 수 있습니다. 여기서 내 요점을 확인하거나 부인하는 SQLite 도메인의 진정한 전문가는 누구입니까?

참고 URL : https://stackoverflow.com/questions/15778716/sqlite-insert-speed-slows-as-number-of-records-increases-due-to-an-index

반응형

'development' 카테고리의 다른 글

docker는 VM이 ​​아닙니다. 컨테이너에 기본 이미지 OS가 필요한 이유는 무엇입니까?  (0) 2020.12.09
명령형 대 일반 이전 하위 유형  (0) 2020.12.09
무엇을 사용합니까?  (0) 2020.12.09
고성능 동시 MultiMap Java / Scala  (0) 2020.12.09
TaskScheduler.Current가 기본 TaskScheduler 인 이유는 무엇입니까?  (0) 2020.12.09

'development'의 다른글

  • 현재글인덱스로 인해 레코드 수가 증가함에 따라 SQLite 삽입 속도가 느려집니다.

관련글

댓글

티스토리툴바