Skip to main content

Database at 100% CPU

OutSystems

Database at 100% CPU

Problem

A database server hosting one or more OutSystems Platform environments is suddenly using up 100% of the CPU resources of the machine. This applies to SQL Server, Oracle or MySQL.

CPU usage in the database is caused by the workload being sent from the application server to the database, such as select, insert, update, delete. This workload comes from one of the following:

  • Applications running in OutSystems Platform Server
  • Development activity in OutSystems Platform, such as publish activities and stages
  • External activity like backups, maintenance tasks and other applications accessing the same database.

Additionally, databases have their own processes running that may or may not cause load in the system. So this means the causes of 100% CPU usage are many and varied. Finding out the exact cause of CPU usage is a process of problem-solving on its own.

This article includes the "quick wins" that we have discovered while solving similar problems for our customers, as well as a structured approach to resolution. 

Quick wins

The following actions have no impact on your system and have been proven to minimize or resolve 100% CPU usage:

  • SQL Server: Queries can have a bad execution plan that causes them to make bad decisions, which can cause them to use too much CPU. Run DBCC FREEPROCCACHE and SQL Server will be forced to rebuild the "execution plan cache.".This action will typically allow SQL Server to make the decision again, and do it well.

  • All databases: Run maintenance plans in the database that do an index rebuild or reorganize throughout the database. This article can help; the subject is SQL Server, but the principles apply to all databases.

Resolution

Here is a structured approach to resolving 100% CPU usage:

  1. Verify the problem: Confirm that CPU usage is at 100% by looking up performance data in your system. 100% CPU usage in a database server differs from 100% CPU usage in an application server. In addition, CPU usage that is peaking regularly is different from a flat line.

  2. Define the problem: Explain the problem you are observing to others. This step helps you confirm that you found the problem, and be able to solve it. For example, "CPU usage has been at 100% since 8:50 AM" is very different from "CPU usage has been peaking at 100% in five-minute periods every hour since 8:50 AM."

  3. Isolate the problem: If the CPU usage is very high, it means something is running in the system that is using up the CPU. This might be one or more processes in the operating system and outside the database or it could be an activity in the database created by one or more queries. Involve your system administrators and database administrators in the process of isolating the problem because they have the appropriate tools for this task..

  4. Identify probable causes: After you isolate the activity that is causing the high CPU usage, you will need to understand why. Assuming that it's activity in the database, you should start by answering one or more of the following questions:
    • Does this query come from the servers running OutSystems Platform or is it coming from somewhere else?

    • What database objects are involved? Are they tables, views or procedures? Are they part of the applications built with OutSystems Platform? Which ones?

    • Are these queries supposed to be running? Where in the business logic are they run? Are they running too frequently?

    • Are these queries tuned? Can the query be rewritten or the data model be changed so the query becomes faster?

  5. Determine a solution: Solving the problem will depend its cause. You might have to change your application or the data model, reconfigure the database, or add more processing power as a last resort.

Final notes

The cause of 100% CPU usage is rarely associated to the internal workings of OutSystems Platform. It has a very low fixed footprint and most of the activity will come from the applications and the users accessing them. However, if you do find something you do not understand, such as queries that originate in OutSystems Services or that involve OutSystems Platform system tables (OSSYS_*), consider contacting OutSystems support with your findings  so we can help.