Performance Tuning WebWizard

These are classic tuning "gotcha"

You have been warned...

• Runnaway Process
  • Check if one process has gone berzerk.
  • Check with nmon if one process which is normally busy in small peaks of 20% CPU is now taking 95% of the CPU and never stops.
  • This is a poorly written program or it was started in a manor where the normal functions to stop this problem were disabled
  • This can happen when a process is conntected to a dumb terminal port and was switched off with out logging off or can happen when a telnet session end abnormally.
• Processes Fail
  • This is nearly always a lack of paging space.
  • Check that there is at least 30% of paging space unused with nmon or lsps -a
  • We recommend paging space is 2 to 3 times phyical RAM.
  • To check physical RAM use lsdev -Cc memory
• Multiple Databases
  • Only run one RDBMS on a server machine.
  • Running multiple RDBMS results in a fight to the death as they compete for resources.
  • If the databases are of different types (like OLTP, batch reports, decission support) then it is even worse. The DSS RDBMS will take 100% of the Disk I/O on a large join. The OLTP RDBMS will take 100% of the CPU servicing user requests.
  • This is equally true for SMP machines as CPU and Disk I/O cannot be partitioned so that the RDBMS will not interact.
• Do not mix Production and Development
  • First, there are security and reliability issues with allowing the developer near the production system as they will be too keen to "improve" or "experimnt" with the production system especially if there are bugs to fix.
  • Secondly, developers are clever people and can hijack the resources of the production system to "save time" on the development.
  • A simple rebuild of the source code can bring the production system grinding to a stop.
• Tune your RDBMS
  • Have you installed the correct version for your hardware?
    For example, many RDBMS have special versions for SMP machines or require special options to enable effective use of SMP machines.
  • Most RDBMS have some sort of disk block cache mechanism. Has this been sized appropriately for the size of RAM and workload? For example as a rough guide for Oracle:

    System type	Stand alone	Server only
    OLTP	30% of RAM	40% to 60% of RAM
    DSS	40% to 70% of RAM	50% to 80% of RAM

• Tune you RDBMS
  • The benchmark team now spend
    • 80% of their time tuning the RDBMS and
    • 20% of their time tuning AIX.
  • This is because AIX is largely self tuning and managing.
  • In the tuning of the RDBMS
    • 30% - Database layout on the disks
    • 10% - RDBMS tuning options
    • 20% - disk cache sizes
    • 30% - SQL
    • 10% - Database hot-spots on disks, RDBMS locking, network resources
• Tune your RDBMS
  • If you have not got 5 years of DBA experience in tuning your RDBMS then get someone who has.
  • Would you tune a new ?0,000 BMW car engine, while reading the Haines D.I.Y. manual?
  • The RDBMS tuning experts are the RDBMS vendors
  • Ask the vendor to:
    • check your system basic setup
    • check your recovery and backup mechanism
    • tune your database
• Have you installed the latest version of the RDBMS and application
  We have seen in the past two years great improvements in performance for new versions due to better optimisation and new functions being added internally to the RDBMS. If you are two versions behind you may be missing out on a 40% performance improvement.
• Too few disks
  You cannot safely run a RDBMS on less 6 disks.
  • Disks are cheap - a 2GB disk costs less than one days consultancy!!!
  • Some parts of the database MUST be on different disks or you cannot recover the database.
  • Some parts (physical redo logs) must be on dedicated disks for performance.