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

Reply via email to