5 Replies Latest reply: Dec 26, 2013 5:02 PM by sb92075 RSS

    slow I/O performance on database server.

    user4477432

      Hello folks,

       

      We have two database hosted on one server. Oracle version is 11G release 3,Servers are having high I/O spikes, Total 4 cores is assign to server, Server have 16GB of memory. Please advise me the way to fix the problem of I/O, only oracle process is taking spikes, when we turn off database server then everything is fine. SGA size is 8GB.

       

      Please suggest.

        • 1. Re: slow I/O performance on database server.
          user4477432

          operating system is RHEL6.4 64bit, Oracle is also 64 bit. Please advise is there any performance tuning calculation script so we can configure huge pages and other value in /etc/sysctl.conf. Waiting for kind reply from guru's

          • 2. Re: slow I/O performance on database server.
            Paul M.
            SGA size is 8GB.

            For both databases or each one ?

            when we turn off database server then everything is fine.

            And didn't come to your mind that the culprit might be the database(s) ? It's 20 years, or so, that I'm using Oracle databases on Linux, and every time I had poor performances the cause has been a not well tuned database.

             

            You didn't specify the edition of your databases (standard, enterprise, ...), but did you try anything about database tuning, for example ADDM or Statspack reports ?

            • 3. Re: slow I/O performance on database server.
              Suntrupth

              Hi,

               

              Couple of questions you need to ask yourself:

               

              1) What is the filesystem being used? Has your storage been optimally tuned to reduce I/O contention?

              2) What are the processes which consume high I/O ? Background processes, user processes?

              3) What is the application that is connected to this database and what kind of queries does the application execute against this Database?

              4) What are the type of I/O's that your system usually perform? sync, async?

               

              Hugepages can deliver benefits in terms of memory management. They only reduce the number of pages that your kernel needs to manage. If you are on 11g you will benefit from hugepages. But before implementing a solution, try to analyze the problem in detail.

               

              First, use Iostat (command in unix) and check the I/O health of the entire system.

               

              Although the following link shows 8.1 , this is a good start to diagnose I/O problems.

              http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/ch20_io.htm

               

              Regards,

              Suntrupth

              • 4. Re: slow I/O performance on database server.
                jgarry

                MOS 401749.1 is a script for calculating hugepages, and 1392497.1 explains an init parameter that can be useful, though it is hard to tell without the version that the database thinks it is.

                 

                Not likely to be your i/o problem, unless maybe managing all the page tables and actual sql blocks your cpu from talking to the i/o.

                 

                You might run some 15 minute statspacks during good and bad times, post them with a fixed font and let us help you with what they are trying to tell you.

                 

                Also if you are licensed for performance and diagnostics, sometimes a quick clue can come from the dbconsole performance screens.  Post AWR reports instead of statspack if you are so licensed.

                • 5. Re: slow I/O performance on database server.
                  sb92075

                  7fbef38b-8f5d-4110-b821-334822cf8d34 wrote:

                   

                  Hello folks,

                   

                  We have two database hosted on one server. Oracle version is 11G release 3,Servers are having high I/O spikes, Total 4 cores is assign to server, Server have 16GB of memory. Please advise me the way to fix the problem of I/O, only oracle process is taking spikes, when we turn off database server then everything is fine. SGA size is 8GB.

                   

                  Please suggest.

                   

                  post SQL & results that show how/why you concluded that I/O is the source of the performance problem.

                   

                   

                   

                  HOW To Make TUNING request

                  https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360003