Last updated: 2007-04-02 15:30:00

Introduction

These tests were designed to test:

The value of the Synchronized Scan patch
The interactions between the Synchronized Scan patch and Simon Riggs' "recycle buffers" patch
The value of the Synchronized Scan patch's concept of "scan offset" which attempts to use blocks that were cached before the scan began.

Code

All tests were 8.2.3 with one, both, or neither of the following patches applied:

Synchronized Scan patch (SS): patch 15
Recycle Buffers patch (RB): patch 2

Machines

M1 - Consumer-grade desktop system. Core 2 Duo (E6700). 1GB physical memory. One 7200 RPM Seagate SATA drive. Debian (Linux 2.6.18). Test table is 10GB. shared_buffers=256MB.
M2 - Dell 2950. 2 x Dual Core "Woodcrest" cpu. 4GB physical memory. PERC 5/i Controller controlling a 6-disk RAID 10. Each disk is 15k SAS drive. FreeBSD. Test table is 20GB. shared_buffers=1GB.

Note that the table sizes are different, so don't directly compare M1 and M2 timings. The table sizes are different becuase the physical memory is different (and also the I/O systems, of course). The real comparisons in these tests are comparing the presence of the patches against plain 8.2.3.

Benchmarks

Single Scan (called "single" below) - a single "SELECT COUNT(*) FROM bigtable" done in psql.
scan.rb - a ruby script that does 5 scans spaced 30 seconds apart. It sets the GUCs appropriately (does not set GUCs on unpatched server because they don't exist).

Logs

These are the results in the form of logs. This is the "raw data".

Machine	Scheduler*	Patches	Benchmark	GUC sync_seqscan_threshold**	GUC sync_seqscan_offset	Log
M1	AS	None	single			Log
M1	AS	None	scan.rb	-1	0	Log
M1	AS	SS	single			Log
M1	AS	SS	scan.rb	-1	0	Log
M1	AS	SS	scan.rb	0	0	Log
M1	AS	SS	scan.rb	0	0.5	Log
M1	AS	SS	scan.rb	0	1	Log
M1	AS	SS,RB	single			Log
M1	AS	SS,RB	scan.rb	-1	0	Log
M1	AS	SS,RB	scan.rb	0	0	Log
M1	AS	SS,RB	scan.rb	0	0.5	Log
M1	AS	SS,RB	scan.rb	0	1	Log
M1	CFQ	None	single			Log
M1	CFQ	None	scan.rb	-1	0	Log
M1	CFQ	SS	single			Log
M1	CFQ	SS	scan.rb	-1	0	Log
M1	CFQ	SS	scan.rb	0	0	Log
M1	CFQ	SS	scan.rb	0	0.5	Log
M1	CFQ	SS	scan.rb	0	1	Log
M1	CFQ	SS,RB	single			Log
M1	CFQ	SS,RB	scan.rb	-1	0	Log
M1	CFQ	SS,RB	scan.rb	0	0	Log
M1	CFQ	SS,RB	scan.rb	0	0.5	Log
M1	CFQ	SS,RB	scan.rb	0	1	Log
M2	FreeBSD	None	single			Log
M2	FreeBSD	None	scan.rb	-1	0	Log
M2	FreeBSD	SS	single			Log
M2	FreeBSD	SS	scan.rb	-1	0	Log
M2	FreeBSD	SS	scan.rb	0	0	Log
M2	FreeBSD	SS	scan.rb	0	0.5	Log
M2	FreeBSD	SS	scan.rb	0	1	Log
M2	FreeBSD	SS,RB	single			Log
M2	FreeBSD	SS,RB	scan.rb	-1	0	Log
M2	FreeBSD	SS,RB	scan.rb	0	0	Log
M2	FreeBSD	SS,RB	scan.rb	0	0.5	Log
M2	FreeBSD	SS,RB	scan.rb	0	1	Log

*: Linux allows pluggable schedulers. AS stands for "anticipatory scheduler", CFQ stands for "completely fair queuing".

**: "-1" means disabled, "0" means all scans are synchronized.

Conclusions

Operating on Linux with the Anticipatory Scheduler (AS) with offset=0 gives ideal results for my patch: concurrent scans operate as if no other scan was in progress. This is true even in the presence of Simon Riggs' recycle buffers patch. This is a very big improvement over plain 8.2.3.

There are a few anomolies in the data that I might need to re-test. These anomolies are all on M1, which is my home machine. There's a possibility that other software made I/O requests during a test. I'll post updated data after the re-test.

On FreeBSD, the results were not quite ideal, but still much better than plain 8.2.3.

On Linux with CFQ, the results were still better than with plain 8.2.3 by about 2 times. However, I believe that it's nowhere near ideal because CFQ is not properly doing readahead in the case of the Synchronized Scan patch.

I did not observe any negative reactions between my patch and Simon's.

The results for sync_seqscan_offset were disappointing. I do not think these tests made a compelling case for the sync_seqscan_offset feature. Unless better numbers appear, I will most likely remove this aspect from the final version of my Synchronized Scan patch.