Heikki Linnakangas wrote:
> But all these assumptions need to be validated. I'm setting up tests
> with different ring sizes and queries to get a clear picture of this:
> - VACUUM on a clean table
> - VACUUM on a table with 1 dead tuple per page
> - read-only scan, large table
> - read-only scan, table fits in OS cache
> - COPY
Just to keep you guys informed, here's my results on a read-only scan on
a table bigger than shared_buffers but smaller than RAM:
select-1 | 00:00:10.853831 select-1 | 00:00:10.380667 select-1 | 00:00:11.530528 select-2 |
00:00:08.634105select-2 | 00:00:02.674084 select-4 | 00:00:02.65664 select-8 | 00:00:02.662922 select-16 |
00:00:02.682475select-32 | 00:00:02.693163 select-64 | 00:00:02.722031 select-128 | 00:00:02.873645 select-256 |
00:00:03.185586select-512 | 00:00:03.534285 select-1024 | 00:00:03.741867
lshw utility tells me that this server has 32KB of L1 cache and 4MB of
L2 cache. The performance starts to drop between 64-128 buffers, which
is 512 - 1024 KB, so I'm not sure how it's related to cache size but
using a small number of buffers is clearly better than using a large number.
However, it caught me by total surprise that the performance with 1
buffer is so horrible. Using 2 buffers is enough to avoid whatever the
issue is with just 1 buffer. I have no idea what's causing that. There
must be some interaction that I don't understand.
All the numbers are quite repeatable, I ran the same test script many
times. The runtime of the first select-2 test however varied between
3-10 seconds, somehow the bad karma from using just 1 buffer in the
earlier test carries over to the next test.
I'm not sure what to think about this, but I'll set up more test
scenarios with VACUUM and COPY.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com