Re: Enabling Checksums

On Fri, 2013-03-22 at 17:09 +0200, Ants Aasma wrote:
> For performance the K8 results gave me confidence that we have a
> reasonably good overview what the performance is like for the class of
> CPU's that PostgreSQL is likely to run on. I don't think there is
> anything left to optimize there, all algorithms are pretty close to
> maximum theoretical performance.

Great work!

> The worst case workload is set up using
> CREATE TABLE sparse (id serial primary key, v text) WITH (fillfactor=10);
> INSERT INTO sparse (v) SELECT REPEAT('x', 1000) FROM generate_series(1,100000);
> VACUUM ANALYZE sparse;
>
> The test query itself is a simple SELECT count(v) FROM sparse;
>
> Results for the worst case workload:
> No checksums: tps = 14.710519
> Fletcher checksums: tps = 10.825564 (1.359x slowdown)
> CRC checksums: tps = 5.844995 (2.517x slowdown)
> SIMD checksums: tps = 14.062388 (1.046x slowdown)

I assume this is in the "bad region" identified by Greg, where there is
no disk activity, but shared_buffers is small, leading to a lot of
movement between the OS cache and shared buffers?

What do you mean by TPS exactly? If the select query is writing hint
bits, then you wouldn't be able to repeat it because they are already
set. So are you repeating the creation/loading of the table, as well?

> Results for pgbench scale 100:
> No checksums: tps = 56623.819783
> Fletcher checksums: tps = 55282.222687 (1.024x slowdown)
> CRC Checksums: tps = 50571.324795 (1.120x slowdown)
> SIMD Checksums: tps = 56608.888985 (1.000x slowdown)
>
> So to conclude, the 3 approaches:

Great analysis. Still a tough choice.

One thing that might be interesting is to look at doing SIMD for both
data and WAL. I wonder if that would be a noticeable speedup for WAL
full-page writes? That would give greater justification for the extra
work it will take (intrinsics/ASM), and it would be a nice win for
non-checksum users.

I also notice that http://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%
80%93Vo_hash_function explicitly mentions adapting FNV to a smaller
size. That gives me a little more confidence. Do you have other links we
should read about this approach, or possible weaknesses?

Regards,
Jeff Davis