Well, it's important to understand what we mean by " long idle periods on the database engine " - in the case of Oracle, the majority of the user work is done by the user process, rather than a centralised engine, and that user process is typically only active 1-5% of the time in an OLTP system - leaving plenty of opportunity to manage the PGA.
I'll leave the discussion of swap rates to virtual disk to the VM experts here, but believe me, a properly configured system can sustain insane rates with no significant observable impact on overall system throughput (as far as the database can tell, anyway). My presentations last year in webcasts and IOUG were very much concerned with virtual memory management - and the same issue applies to real memory in a non-virtual environment. The larger the SGA, the larger the space allocated to page translation tables for the shared memory segment. The more processes you have, the more page tables. Having a large SGA and a large process count can cripple a system to the point where the memory shortage comes from page tables, not SGA and PGA itself - remember the page tables are fixed in memory. Some of these presentations are on the ZSeries SIG site I posted earlier, and I really do recommend you join and participate in the SIG if you are running Oracle on zSeries - it's not run by Oracle, but by customers, so it's well worth investigating (did I mention Veegaaas!!!) . I have worked on numerous migration projects, where we have taken the Win/*nix system with hundreds of GB of SGA, and run it on Linux on IBM zSeries with much less memory - my usual recommendation is to reduce by 90% to start with, and we generally get there or better - in common with this thread it takes a while to convince the DBAs :-). I have the numbers, AWR, throughput etc from a specific project in front of me, so this is no idle statement. Add hugepages to the mix, and it just gets better. And thanks to James Barton for reminding me of the top level note on virtualisation everywhere: http://www.oracle.com/technetwork/database/virtualizationmatrix-172995.html which kinda shows that we do support virtualisation :-) Cheers Damian -----Original Message----- From: Bernd Oppolzer [mailto:bernd.oppol...@t-online.de] Sent: 22 February 2012 20:32 To: LINUX-390@vm.marist.edu Subject: Re: Oracle in virtual environments I don't think that the situation you describe (long idle periods on the database engine) is a realistic one. Of course, if such situations happen, the database engine's pages have to be paged out by the paging subsystem. But I had another situation in mind: normal continuous operation of the database. Now, how large should the DB system buffer be? Normally, the larger, the better. The DB engine can handle larger amounts of data in memory and the hit ratio (= buffer pool usage) is better. Less disk access needed. But: if the DB bufferpools are too large (= larger than the available REAL memory frames, under normal circumstances), the DB engine competes with other address spaces for those real memory page frames, and paging activity starts on the DB bufferpool pages. So it is possible that the DB page needed by the DB engine was just paged out by the paging subsystem. The efforts of the DB engine and the paging subsystem to reduce disk accesses disturb each other. This is the normal behaviour: when you enlarge the DB buffer pool, the performance first gets better, but at a certain point (when paging activity starts due to real page frame constraints), performance gets dramatically worse. Knowing this, I believe that performance on a virtual DB system may also degrade significantly, if the virtualization implies paging activity on the address space of the database engine. Kind regards Bernd Am 22.02.2012 20:26, schrieb Rob van der Heij: > On Wed, Feb 22, 2012 at 7:09 PM, Bernd Oppolzer > <bernd.oppol...@t-online.de> wrote: > >> The database engine or process uses large memory buffers to >> cache the (least recently used) database pages in REAL memory, >> and database performance may degrade significantly, if there is >> paging acitivity on the memory buffers of the database engine. >> So the DB admins have to take care that the real storage on the >> DB machine is large enough that there is no paging on the DB >> buffers, and the DB buffers have to be defined only as large as the >> available REAL memory (not the address space). > I don't think anyone disagrees with your claim that a running DB > system needs the code and data in memory to operate on it. But when > the database utilization is only 5%, it only needs that 5% of the > time. Not 100% of the time. This means that the paging subsystem has > to page out the idle guest to make room for others, and page it back > in when it becomes active. The challenge becomes a bit easier if the > DBA reduces the peak memory requirements (smaller SGA and PGA target) > even when that means a bit more overhead during peak times. > >> This is true for every DB system, regardless of name or vendor. >> >> So, of course, the performance of a DB system on a virtual system >> will suffer - if you don't have features like V=R pages (on z/VM, for >> example), where you can fix the real pages of the DB machine. > It's natural for a DBA to care about his own databases and want those > to be resident all the time. But sharing unused capacity is something > else. To some extent, it's the VM systems programmer role to implement > the business requirements to address that. And some push it way beyond > that :-) > > Rob > > ---------------------------------------------------------------------- > For LINUX-390 subscribe / signoff / archive access instructions, > send email to lists...@vm.marist.edu with the message: INFO LINUX-390 or visit > http://www.marist.edu/htbin/wlvindex?LINUX-390 > ---------------------------------------------------------------------- > For more information on Linux on System z, visit > http://wiki.linuxvm.org/ > ---------------------------------------------------------------------- For LINUX-390 subscribe / signoff / archive access instructions, send email to lists...@vm.marist.edu with the message: INFO LINUX-390 or visit http://www.marist.edu/htbin/wlvindex?LINUX-390 ---------------------------------------------------------------------- For more information on Linux on System z, visit http://wiki.linuxvm.org/ ---------------------------------------------------------------------- For LINUX-390 subscribe / signoff / archive access instructions, send email to lists...@vm.marist.edu with the message: INFO LINUX-390 or visit http://www.marist.edu/htbin/wlvindex?LINUX-390 ---------------------------------------------------------------------- For more information on Linux on System z, visit http://wiki.linuxvm.org/
