{"id":835,"date":"2006-03-14T12:00:00","date_gmt":"2006-03-14T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=835"},"modified":"2006-03-14T12:00:00","modified_gmt":"2006-03-14T12:00:00","slug":"px-and-the-magic-of-2","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/03\/14\/px-and-the-magic-of-2\/","title":{"rendered":"PX and &#8220;the Magic of 2&#8221;"},"content":{"rendered":"<p>Now that I&#8217;ve had time to sit back and relax and absorb things a little, I thought I should fix a couple of outright bugs in the <a href=\"http:\/\/oracledoug.com\/px_slaves.pdf\">recent PX paper<\/a> (<strong>Later update &#8211; link to the paper) <\/strong>and revisit the conclusions before moving on to testing different aspects. First, the bugs :-<\/p>\n<ul>\n<li>I&#8217;ve changed processes to 800 and removed the comment about parallel_max_servers never exceeding 385<\/li>\n<li>I&#8217;ve changed the various references to the EMC stripe width to 960Kb, not 970Kb.<\/li>\n<\/ul>\n<p>Both of these are courtesy of <a href=\"http:\/\/www.jlcomp.demon.co.uk\/\">Jonathan Lewis<\/a>. The new version of the document is dated 14th March 2006. I&#8217;ve resisted the temptation to change the substance of the paper significantly. For example, the more I look at the detailed data at home and think about the results, the more I think the tests reflect both <a href=\"http:\/\/download-east.oracle.com\/docs\/cd\/B19306_01\/server.102\/b14237\/initparams156.htm#sthref660\">Oracle&#8217;s documentation<\/a> and <a href=\"http:\/\/www.hotsos.com\/e-library\/abstract.php?id=13\">Cary Millsap&#8217;s work<\/a>. I&#8217;ll come back to that later.<\/p>\n<p>First, some additional info that was available to the Hotsos Symposium attendees as the very last graph I put up, but isn&#8217;t in the paper. (I&#8217;ll tell you &#8211; you lot owe me! Not only do you get almost twice-daily updates during the conference &#8211; you get presentation updates, too &#128521; )<\/p>\n<p>I&#8217;ve talked about this before in <a href=\"http:\/\/oracledoug.blogspot.com\/2006\/02\/something-for-v2.html\">this blog<\/a> but, in essence, the tests that I ran <span style=\"FONT-STYLE: italic\">didn&#8217;t<\/span> use a reasonable amount of CPU because they were based on unusually empty blocks of data with PCTFREE set to 90. Your application might parallel scan a large table and only be interested in 10% of the columns, so it&#8217;s not a completely redundant test, but it&#8217;s probably not representative of standard operations and certainly not very CPU-intensive. When I changed the blocks to use PCTFREE 10, I could squeeze 65 million rows into less space than the original 8 million rows. When I re-ran the tests against the new more densely packed blocks, the benefits of PX were more apparent and at higher Degrees of Parallelism.<\/p>\n<p><span style=\"FONT-STYLE: italic\">A quick interlude on graphs. Putting aside the very funny &#8216;Whoo! Yeah!&#8217; responses from Mogens and him taking pictures of my beautiful graphs (I really enjoyed it and it was hard to carry on and not just burst out laughing), I think they can be both useful and dangerous. Let&#8217;s deal with the danger first. The graphs reduce the detail, particularly because I wanted to leave the noparallel numbers in there so most of the values are compressed into a very small vertical range. They also don&#8217;t really prove anything because there could be all sorts of waits, execution plan changes, errors (this could be a long list &#8230;) hidden in there. Let&#8217;s deal with the useful now. I wanted to <span style=\"FONT-WEIGHT: bold\">illustrate<\/span> that, even though things might get a little quicker at higher DOPs, the <span style=\"FONT-WEIGHT: bold\">big<\/span> benefits come when going from noparallel to parallel 2 and also that the additional (and expensive) CPUs didn&#8217;t help much at all.<\/span><\/p>\n<p>Here is the original Graph 5 from the paper, which shows run times when running the Hash Join example against sparsely-populated blocks (PCTFREE 90) on the Sun E10K. (I realise this might not display too well, but I have the source data and images if you want a copy)<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_a.jpg\" onblur=\"try {parent.deselectBloggerImageGracefully();} catch(e) {}\"><img decoding=\"async\" alt=\"\" border=\"0\" src=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_a.jpg?w=750\" style=\"DISPLAY: block; MARGIN: 0px auto 10px; TEXT-ALIGN: center\" data-recalc-dims=\"1\"\/><\/a><br \/>And now the same graph against densely-populated (PCTFREE 10) blocks. Because I only had a limited amount of server time left, I didn&#8217;t run it for all 12 CPU counts individually and the legend&#8217;s in a strange order, but I think there&#8217;s enough here to see the difference.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_b.jpg\" onblur=\"try {parent.deselectBloggerImageGracefully();} catch(e) {}\"><img decoding=\"async\" alt=\"\" border=\"0\" src=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_b.jpg?w=750\" style=\"DISPLAY: block; MARGIN: 0px auto 10px; TEXT-ALIGN: center\" data-recalc-dims=\"1\"\/><\/a><br \/>Note that these two tests are on different data volumes, number of blocks and the response times are very different because the second test is doing so much work, but on fewer blocks.<\/p>\n<p>The main difference I see is that the additional CPUs offer much bigger response time improvements as they are added but I hope it&#8217;s also apparent that the fastest response times are at higher DOPs, up to the 6-8 range. The response time &#8216;knee&#8217; is more curved. Now then, what does this all mean?<\/p>\n<p>In fact, in the last few slides of the presentation, I talked about a couple of things<\/p>\n<ol>\n<li>That the biggest benefits are gained when going from noparallel to parallel 2 and that benefits diminish rapidly after that. I think that&#8217;s been apparent in all the tests that I&#8217;ve run and I still believe that to be the case, although I&#8217;m looking forward to being proved wrong. That&#8217;s what I meant by the deliberately provocative comment about sticking to parallel 2, regardless of your configuration.<\/li>\n<li>That I&#8217;d be very interested in seeing any results that showed <span style=\"FONT-STYLE: italic\">even<\/span> a DOP of CPU * 1 that gave the fastest response time. Looking at these results though, and some other results on some weekend tests on the 4 CPU ISP4400, it&#8217;s pretty close to that already.<\/li>\n<\/ol>\n<p>Here are the latest results from weekend 4 CPU tests<br \/><a href=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_c.jpg\" onblur=\"try {parent.deselectBloggerImageGracefully();} catch(e) {}\"><img decoding=\"async\" alt=\"\" border=\"0\" src=\"https:\/\/i0.wp.com\/oracledoug.com\/images\/hotsos_graph_c.jpg?w=750\" style=\"DISPLAY: block; MARGIN: 0px auto 10px; TEXT-ALIGN: center\" data-recalc-dims=\"1\"\/><\/a>You can see again the big difference in response times between 1 CPU and 4 CPU so now there is value for money in those extra CPUs! However, of particular interest here is the 4 CPU series. There is a clear (albeit small) performance improvement at DOPs of 5 and 6. Now, if you double that (for the two slave sets that this example uses) and add the Query Co-ordinator, there are a total of 10 or 13 processes working on this query. Which is more than 2 * CPUs, or 8.<\/p>\n<p>That would fit in with Oracle (and Cary&#8217;s) advice that around about 2 * CPUs is good, but that more than 2 * CPUs would suit jobs that are particularly I\/O-intensive or where the I\/O subsystem isn&#8217;t quite up to the job (which it probably isn&#8217;t in this case)<\/p>\n<p>Hopefully that clarifies a couple of things. At some point in the future (personal stuff permitting) I plan to blog in more detail about the wait event profile of the tests, because I felt that was lacking in the paper and almost entirely absent from the presentation.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Now that I&#8217;ve had time to sit back and relax and absorb things a little, I thought I should fix a couple of outright bugs in the recent PX paper (Later update &#8211; link to the paper) and revisit the conclusions before moving on to testing different aspects. First, the bugs :- I&#8217;ve changed processes&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2006\/03\/14\/px-and-the-magic-of-2\/\">Continue reading <span class=\"screen-reader-text\">PX and &#8220;the Magic of 2&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-835","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":977,"url":"http:\/\/orcldoug.com\/blog\/2005\/05\/14\/getting-it-right\/","url_meta":{"origin":835,"position":0},"title":"Getting it right","date":"May 14, 2005","format":false,"excerpt":"Well I've had a busy time recently. As well as moving house and starting a new contract, I decided that I should run my PX paper past a few trusted individuals for review before it's published in Select magazine. (Sorry Tom, I do tend to trust *some* people.) Here's a\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":853,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/27\/ok-im-done\/","url_meta":{"origin":835,"position":1},"title":"OK, I&#8217;m done!","date":"February 27, 2006","format":false,"excerpt":"I've had to draw a line under the Hotsos paper (not least because I'm supposed to be going out tonight to someone's birthday celebration meal). So I've uploaded the first proper version to both the Symposium website and here.For those poor souls who have had a look at earlier versions,\u2026","rel":"","context":"With 16 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":976,"url":"http:\/\/orcldoug.com\/blog\/2005\/05\/29\/px-slave-trace-files\/","url_meta":{"origin":835,"position":2},"title":"PX Slave Trace Files","date":"May 29, 2005","format":false,"excerpt":"It's sad that it takes a Bank Holiday weekend for me to get round to doing some more research on parallel execution. Anyway, I was on the verge of modifying the following line from the PX paper ...The trace files for the slave processes may be created in background_dump_dest, rather\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":854,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/27\/something-for-v2\/","url_meta":{"origin":835,"position":3},"title":"Something for V2","date":"February 27, 2006","format":false,"excerpt":"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\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1508,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/21\/the-best-oracle-parallel-execution-paper\/","url_meta":{"origin":835,"position":4},"title":"The Best Oracle Parallel Execution Paper","date":"July 21, 2009","format":false,"excerpt":"I'd already made a note to read and blog about Greg Rahn's Parallel Execution over RAC blog post which is excellent, as usual, but Jonathan Lewis beat me to it \ud83d\ude09 What struck me when I eventually took the time to read it, though, was the link in his post\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1212,"url":"http:\/\/orcldoug.com\/blog\/2007\/02\/23\/orasrp\/","url_meta":{"origin":835,"position":5},"title":"OraSRP","date":"February 23, 2007","format":false,"excerpt":"While working on the PX paper for Hotsos, I've been trying to show the same issue using different tools that people might have available to them and struggled to get OraSRP working on Solaris 10. From digging around the net I started to realised that the Python distribution included in\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=835"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/835\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=835"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}