2009-04-23 #1

Inserts got slow

First I turned on indexing around the 65 million record mark. Indexing took 45 minutes, during which time the inserts totally stopped. Inserts after indexing was turned on took much longer due to wait-on-IO issues on commit, probably due to index updating.

Second, I didn't optimize Postgres. This is the information I got second hand regarding a mail/posgres server with 2GB of RAM:

1. Set effective_cache_size to about 1GB.
2. Increase shared_buffers from 48MB to 96MB.
3. Set checkpoint_segments to 16.
4. Set wal_buffers to 512KB.
5. Set default_statistics_target to 50.

effective_cache_size = 131072           # typically 8KB each 131072x8KB=1GB
shared_buffers = 40000                  # min 16 or max_connections*2, 8KB each
checkpoint_segments = 16                # in logfile segments, min 1, 16MB each
wal_buffers = 64                        # min 4, 8KB each
default_statistics_target = 50          # range 1-1000

Rerunning the import now, we'll see what happens.

Later: More Changes

• changed the running loop so we insert flow files in chronological order instead of random
• changed the running loop so that we read 12 flow files at a time
• changed the internal logic so we only commit once per run (ie once per 12 flow files read)