PostgreSQL vs. SQLite: read & write in multithreaded environment

The start was humble. I needed to cache some data, and I thought just push them to database table and give index, and the rest will be database's job. There were only 2 TEXT fields, and I needed to refer to only one field to search for specific row - which is some kind of key-value store -, so I thought whatever database engine should be fine.

And yes. It was a BIG mistake.

First I tried SQLite, and I found out that, in multithreaded environment some records are evaporated when trying to write to the table simultaneously, even with -DSQLITE_THREADSAFE=2 compile time option. I pushed the same data in same condition, and sometimes I have only 20 records, other times 40, and yet 26 for some others....... What drove me crazier was that the SQLite itself worked fine without any I/O problems. A good moment to shout "WHAT THE HELL?!" in real time.

So I changed the engine to PostgreSQL. Our trustworthy elephat friend saved all the records without any loss. I was satisfied with that, but...... Though I applied b-tree index to necessary field of the table, it took 100 milliseconds for just running SELECT field2 WHERE field1='something'. No, the table was small enough. There were only 680 records and data lengh was at most 30 characters for field 1 and only 4 characters for field 2. I configured the engine with some optimization, so it worked fine for bigger tables so I felt assured for its performance, but I didn't expect something like this, even in my dreams.

Elephant is tough, but as a side effect it's too slow.......

So, one last chance: I ran pg_dump to move data from PostgreSQL to SQLite, and with same condition(same index, same table structure, ......), I turned on at .timer SQLite shell and it took less than 0.001 second. Yohoo!

After some more experiments, SQLite can't fully resist from data loss by itself even with multithread support option enabled, and you need more external support like std::mutex. I guess that it's fread() call doesn't support full serialization in multithread environment, but I have neither time nor abilities to do the proper inspection. :P

Anyway, now I use the combination of SQLite + WAL mode + more SQLite internal cache + std::mutex. Still the write performance looks good, but if needed, I think I could use more files with load balancing via non-cryptographic hash.

PostgreSQL vs. SQLite: 멀티스레드 환경에서의 읽기-쓰기

그러니까....... 시작은 소소했습니다. 뭔가 데이터를 캐싱할 일이 있었는데, DB에 쌓아두고 index 걸면 나머지는 DB가 알아서 하지 않겠느냐 하는 거였습니다. 데이터라고 해봐야 별거 없이 그냥 TEXT 필드 두 개가 전부인데다가 실제로 데이터를 찾을 때는 둘 중 하나만 가지고 찾으면 되는 매우 간단한 key-value store 형태의 구조라, 어떤 DB를 써도 상관없겠지 하고 안일하게(.......) 생각했습니다.

옙. 그건 큰 착각이었습니다.

처음에는 SQLite를 사용해봤는데, 멀티스레드 환경에서 동시에 쓰기를 수행하니 컴파일시 -DSQLITE_THREADSAFE=2 옵션을 추가해도 일부 레코드가 유실되더군요. 동일한 데이터를 동일한 조건에서 동일하게 넣는데 어떨 때는 레코드가 20개만 있고, 어떨 때는 40개, 어떨 때는 또 26개...... 게다가 심지어 동작에는 이상이 없습니다(......). 정말이지 What the hell을 라이브로 외치기 딱 좋은 순간이죠.

그래서 DB를 PostgreSQL로 바꿨습니다. 우리의 우직하고 단단한 코끼리 친구는 레코드 유실 없이 모든 데이터를 다 받아서 잘 보관해줍니다. 그리고 만족하고 있던 그 찰나....... 인덱스까지 b-tree로 잘 걸어줬음에도 불구하고 SELECT field2 WHERE field1='something' 하나 수행하는데 무려 100밀리초가 걸립니다. 그렇다고 해서 테이블의 크기가 컸던 것도 아닌게, 레코드라고 해봐야 겨우 680여개 뿐이었고, 레코드 길이도 하나는 길어봐야 30글자, 나머지는 4글자 고정이었거든요. 나름 환경설정 최적화도 해 주었고, 그래서 대형 테이블에서는 비교적 빠르게 돌아갔던 터라 안심하고 있었는데, 이런 사소한 부분에서 성능 문제를 맞닥뜨릴줄은 꿈에도 몰랐습니다.

코끼리는 딴딴하지만 그 대신 미친듯이 느린걸로......

해서...... 혹시나 해서 PostgreSQL에서 pg_dump로 데이터를 덤프해서 SQLite로 옮긴 뒤에 동일하게 SELECT를 수행해 봤습니다. SQLite shell에서 .timer 걸고 돌려보니 0.001초가 채 안 됩니다(......).

추가로 더 시험을 해 본 결과, SQLite에서는 멀티스레드 지원 옵션이 추가된 상황에서 데이터 유실을 완벽하게 방어하지는 못하고, std::mutex 같은 별도 외부 지원을 추가해야 되더군요. 아마 fread() call이 멀티스레드 상황에서의 serialization을 제대로 지원하지 않지 않는 것 아닐까 하고 추측하고 있습니다만, 거기까지 추적하기엔 시간도 없거니와 능력도 안 되어서...... :P

하여간, 지금은 SQLite + WAL mode + 내부 캐시 증량 + std::mutex 조합을 사용하고 있습니다. 쓰기 속도는 아직 충분하다고 여겨집니다만, 만일 더 필요하다면 파일을 여러개로 늘린 뒤에 non-cryptographic hash로 load balancing을 하면 될 것 같습니다.