This is mainly for anyone who has read the early versions of the Hotsos ‘How Many Slaves’ paper. It won’t make as much sense if you haven’t.
I was doing some more work on multi-user tests at the weekend. I reduced the size of the tables to eliminate disk i/o (as described in the paper) which worked well. However, the PX slaves weren’t doing enough work for my liking and I realised that it was because they were joining and sorting small numbers of rows – 128,000. That’s because PCTFREE was set to 90 on the test tables. So the query has to read a 170Mb table but then only has to process about a tenth of that once the blocks have been read. I changed the table creation script to PCTFREE 10, inserted more rows and the slaves started doing more work and experiencing more waits.
Then, this morning, it hit me. The same is also true of the single user/large volume tests which are always I/O bound. If I had used PCTFREE 10 on the larger tables and inserted more data, that would be likely to move the resource usage balance for the Hash Join/Group By example towards CPU and memory, which is what I’d been looking for. Doh! In my efforts to make sure the disks worked hard enough at the start of the tests, I’d loaded the test against CPU usage.
There’s no way I have the time to re-run the tests in time to include them in the paper, but I’ll mention this there and hope to run the tests this week so I can talk about them more in the presentation. If you do want to read the paper, the final version 1.0 will be updated tonight, so I’d wait until I’ve done that, if I were you.