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.634105
select-2 | 00:00:02.674084
select-4 | 00:00:02.65664
select-8 | 00:00:02.662922
select-16 | 00:00:02.682475
select-32 | 00:00:02.693163
select-64 | 00:00:02.722031
select-128 | 00:00:02.873645
select-256 | 00:00:03.185586
select-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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
