Re: checkpointer continuous flushing - Mailing list pgsql-hackers

Hello Amit,

About the disks: what kind of HDD (RAID? speed?)? HDD write cache?

Speed of Reads -
Timing cached reads:   27790 MB in  1.98 seconds = 14001.86 MB/sec
Timing buffered disk reads: 3830 MB in  3.00 seconds = 1276.55 MB/sec

Woops.... 14 GB/s and 1.2 GB/s?! Is this a *hard* disk??

Copy speed -

dd if=/dev/zero of=/tmp/output.img bs=8k count=256k
262144+0 records in
262144+0 records out
2147483648 bytes (2.1 GB) copied, 1.30993 s, 1.6 GB/s

Woops, 1.6 GB/s write... same questions, "rotating plates"??

Looks more like several SSD... Or the file is kept in memory and not committed to disk yet? Try a "sync" afterwards??

If these are SSD, or if there is some SSD cache on top of the HDD, I would not expect the patch to do much, because the SSD random I/O writes are pretty comparable to sequential I/O writes.

I would be curious whether flushing helps, though.

max_wal_size=5GB

Hmmm... Maybe quite small given the average performance?

We can check with larger value, but do you expect some different
results and why?

Because checkpoints are xlog triggered (which depends on max_wal_size) or time triggered (which depends on checkpoint_timeout). Given the large tps, I expect that the WAL is filled very quickly hence may trigger checkpoints every ... that is the question.

checkpoint_timeout=2min

This seems rather small. Are the checkpoints xlog or time triggered?

I wanted to test by triggering more checkpoints, but I can test with
larger checkpoint interval as wel like 5 or 10 mins. Any suggestions?

For a +2 hours test, I would suggest 10 or 15 minutes.

I don't think increasing shared_buffers would have any impact, because
8GB is sufficient for 300 scale factor data,

It fits at the beginning, but when updates and inserts are performed postgres adds new pages (update = delete + insert), and the deleted space is eventually reclaimed by vacuum later on.

Now if space is available in the page it is reused, so what really happens is not that simple...

At 8500 tps the disk space extension for tables may be up to 3 MB/s at the beginning, and would evolve but should be at least about 0.6 MB/s (insert in history, assuming updates are performed in page), on average.

So whether the database fits in 8 GB shared buffer during the 2 hours of the pgbench run is an open question.