File much larger than the data it contains

[zippoxer]

I'm storing hundreds of millions of small records (key is 4 bytes and value is 18 bytes - total 22 bytes), and with a million records the database file size should be approximately 21 MB -- but it's 104 MB.

Obviously, I know the database file would always be larger than the data it contains due to pages, but when the database size is 5 times bigger than the data it contains - I feel like I'm wasting a lot of disk space.

(Edit: I tried bolt compact and it does reduce the file size from 104 MB to 93 MB, however it's still over 4 times bigger than the 21 MB of data it contains).

Examining the database file, I noticed records are stored in many small chunks and there's about a 4 KB of zeros between each chunk. How can I reduce that 4 KB padding between chunks? Is it because of my system's page size? Can I reduce page size only for the bolt database without adjusting it system-wide? Can bolt make better use of that zero padding?

Maybe there another approach I can take to storing millions of small records without consuming so much disk space?

Thanks, Moshe

[benbjohnson]

@zippoxer Are you using a lot of buckets? Each bucket requires at least one full page regardless of how much data is inside. I agree that 5x the size of the data seems excessive.

Bolt splits pages when they become full so it's not uncommon to see half full pages. Unfortunately, packing data tightly would cause inserts to become much slower when pages split.

[zippoxer]

@benbjohnson I only use a single bucket and put the million records there. So according to what you said, I shouldn't be seeing many chunks of records separated with 4 kb padding in the DB file. Why am I seeing it then?

[benbjohnson]

@zippoxer Do you have example code that reproduces the issue?

[zippoxer]

@benbjohnson

Yes, I have a self-contained test that produces this result:

package main

import (
	"log"

	"math/rand"
	"time"

	"encoding/binary"

	"bytes"

	"github.com/boltdb/bolt"
	_ "github.com/lib/pq"
)

type PriceRecord struct {
	D         time.Time
	ProductID int32
	StoreID   int32
	Price     int32
}

func gen(ch chan<- PriceRecord) {
	startDay := time.Date(2016, time.October, 0, 0, 0, 0, 0, time.UTC)
	day := 0
	for day < 10 {
		for i := 0; i < 100e3; i++ {
			ch <- PriceRecord{
				D:         startDay.AddDate(0, 0, day),
				ProductID: int32(i)/5 + 1,
				StoreID:   rand.Int31n(5e3),
				Price:     rand.Int31(),
			}
		}
		day++
	}
	close(ch)
}

type BoltRecord struct {
	Month     int16
	Day       int16
	Year      int16
	ProductID int32
	StoreID   int32
	Price     int32
}

func saveBolt() {
	start := time.Now()
	db, err := bolt.Open("zapt.db", 0600, nil)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	tx, err := db.Begin(true)
	if err != nil {
		log.Fatal(err)
	}
	b, err := tx.CreateBucket([]byte("prices"))
	if err != nil {
		log.Fatal(err)
	}
	buf := bytes.NewBuffer(nil)
	i := 0
	ch := make(chan PriceRecord, 100)
	go gen(ch)
	for rec := range ch {
		id, err := b.NextSequence()
		if err != nil {
			log.Fatal(err)
		}
		err = binary.Write(buf, binary.BigEndian, BoltRecord{
			Month:     int16(rec.D.Month()),
			Day:       int16(rec.D.Day()),
			Year:      int16(rec.D.Year()),
			ProductID: rec.ProductID,
			StoreID:   rec.StoreID,
			Price:     rec.Price,
		})
		if err != nil {
			log.Fatal(err)
		}
		err = b.Put(itob(int(id)), buf.Bytes())
		buf.Reset()
		if err != nil {
			log.Fatal(err)
		}
		i++
	}
	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}
	log.Println("took", time.Now().Sub(start))
}

func itob(v int) []byte {
	b := make([]byte, 8)
	binary.BigEndian.PutUint64(b, uint64(v))
	return b
}

func main() {
	saveBolt()
}

Look at saveBolt() - it saves 1 million records (18 bytes value & 4 byte key each) to the database file zapt.db which ends up taking 98-103 MB of disk space (depends on if you divide the bytes by 1024 or 1000).

BTW, I found that if I split it up to smaller transaction of 1K record per transaction, the files weighs less (98 MB instead of 103 MB). But it's still a lot, and there's still a 2 to 4 KB of zeros between chunks of records in the database file.

[dtfinch]

What if you add a "b.FillPercent = 0.9" after the CreateBucket line? I haven't tried it, but that should tell it to do a 90-10 split instead of a 50-50 split when a page is full, which is more appropriate for sequential inserts.

[zippoxer]

@benbjohnson

Setting FillPercent to 0.9 reduces the file size by 37% from 103 MB to 64 MB. So that really helps, thanks :)

So 64 MB is a bit less than 3 times the pure data (22 MB). There's still some zero padding - here's a hex snippet from the file: http://pastebin.com/hZJdrwt3

Notice how there's a bunch of records. The records are consuming ~26 bytes each -- that's only ~15% higher than the real data which is awesome -- but then there's a padding before the next chunk of records. Notice how the padding isn't all zeros, some of it is some values as well.

I checked one of the paddings and it's about 1800 bytes long. Much better than 4000, but I still don't see why the paddings are so many and so big to make 22 MB of pure data to 64 MB.

I can live with that, but I really wish to know if there's anything I can do to optimize for my use case.

Also, If it helps, here are the bucket's stats after the insertion of the million records:

bolt.BucketStats{BranchPageN:77, BranchOverflowN:0, LeafPageN:11495,
LeafOverflowN:0, KeyN:1000000, Depth:3, BranchAlloc:315392,
BranchInuse:278936, LeafAlloc:47083520, LeafInuse:42183920,
BucketN:1, InlineBucketN:0, InlineBucketInuse:0}

[dtfinch]

Your keys are 8 bytes, so 26 is correct. Database size is also rounded to the next power of two when it grows (see mmapSize() in db.go).

The mostly empty part in between looks like leafPageElement records (page.go), judging by 2nd half being "08 00 00 00 12 00 00 00", 8 being the key size, and 12 being the value size in hex, both as 32-bit ints. The other 8 bytes are flags and relative address. So those add 16 to each of the 26 byte records.

[zippoxer]

@dtfinch

Alright, now it seems reasonable to me :) And yes, my key is 8 bytes (didn't notice the itob function I copied from bolt's README).

Thanks for walking me through the details, and thank you Ben for helping me reduce the file size to almost a half.