DB Advisor

Home > Select Project > DB Advisor

DB Advisor inspects database instance status, SQL, capacity, and alerts against fixed rules every day and brings the items that look wrong together on one screen. For each flagged item, the advisor estimates the cause and suggests an action. The advisor is the WhaTap AI that writes the explanations on the DB Advisor screen. Checks that used to mean opening one analysis screen after another now take a single inspection. Daily inspection results are stored by date, so you can use them as they are for shift handover or daily reporting. This feature is built for the DBAs and operators who run databases.

DB Advisor works on the following principles.

• Rules make the judgment. The Normal, Caution, and Fatal judgments come only from the judgment criteria and the values shown on screen.
• The advisor writes the explanation. For each inspection, the advisor writes the result summary, the probable cause, and the suggested action once. The numbers in the explanation are quoted from what the inspection rules calculated.
• You apply the action. An action is a suggestion. Applying it to the database is up to you.

Before you start

Supported databases

You can use DB Advisor in the following database projects. Cluster projects are not supported.

• PostgreSQL
• MySQL
• SQL Server
• Oracle
• Oracle Pro

Amazon RDS and Amazon Aurora are also supported when the database engine is one of the databases above.

Permissions

DB Advisor is turned on and off per project. Once one member turns it on, every member of the project sees the same screen. Only members with one of the following permissions can turn it on or off.

• Project edit permission
• Project Flex board edit permission

For members without permission, the Start DB Advisor button is locked and the Stop button does not appear on the home screen. For more information about member permissions, see the following document.

Data that is read

• No project data is read before you start using the feature.
• Once you start, instance counters, SQL statistics, capacity trends, and alert events are read. No command is run against the database.
• For each inspection, the values used for the judgment and the findings are sent to WhaTap AI so that it can write the explanation.

On-premises environments

DB Advisor is a WhaTap AI feature, so the menu appears only when ui_whatap_ai_mode in front.conf is set to show. If the value is not set, SaaS environments behave as show and on-premises environments as disabled. Where the menu is hidden, the screen does not open even if you go to the DB Advisor URL directly.

Starting and stopping

Starting

DB Advisor is off at first. Follow these steps to start using it.

1. Select a project, then select the DB Advisor menu at the top of the sidebar.

2. On the guide screen, review what DB Advisor does and the Things to know section.

3. Click the Start DB Advisor button.

Clicking the button starts the first inspection right away and moves you to the DB Advisor home screen. There is nothing else to set up.

The top of the home screen shows a sentence that summarizes the latest inspection result. Click Ask about the cause in the summary to ask about the cause in the WhaTap AI chat window. In the What I handle area below it, select the Diagnosis and action or Daily inspection card to move to each screen.

The Just say it and we will build it area provides the following features.

• Create a Flex board. Describe the metrics you want to see in a conversation and they are built into a Flex board in the current project. Dashboard edit permission is required.
• Handover note. Collects the current inspection results and the actions you applied into text you can hand to the next shift. You can copy the text and use it as it is.

Stopping

1. Click Stop at the bottom of the DB Advisor home screen.

2. In the Stop using DB Advisor? confirmation window, click the Stop button.

Once you stop, you return to the guide screen. Stored daily inspection records are not deleted, and you can start again at any time.

Judgments

Each inspection result is shown as one of the following four judgments.

Judgment	Description
Normal	Within criteria. Shown with the supporting value and the usual value
Caution	Outside criteria. Not a failure yet, but needs a look
Fatal	Needs attention right away
Verification required	No value, so no judgment was made

Caution

Verification required does not mean normal. Collection has stopped or the values needed for the judgment are missing, so check the agent collection status.

Inspection items fall into the following four types.

• Availability. Collection continuity, restarts, replication delay, and other items about whether the service runs normally
• Performance. CPU, sessions, SQL response time, and other items about processing speed
• Capacity. Tablespace, database size, and other items about storage space
• Event. Alerts, deadlocks, and other items judged by the number of occurrences

Diagnosis and action

Diagnosis and action inspects whether the database is healthy right now. It looks at the window from the previous inspection until now, and when something is wrong it tracks the state from the finding through the cause to the action. Select the Diagnosis and action card on the home screen to open it.

Screen layout

The Diagnosis and action screen consists of the following areas, from the top.

• Inspection summary. The advisor explains the result of this inspection. It also shows which inspection it is, morning, afternoon, or evening, and the window being examined. To have the explanation rewritten, click Regenerate.
• Instance status. Shows a trend chart and a judgment for each instance as a card, for the inspection window.
• What the advisor does. Shows the result of the five inspections and the time of the last inspection. To inspect again now, click Inspect again.
• Finding list. The items flagged by the inspection. For details, see Reviewing findings.

Inspection window

Diagnosis and action does not inspect automatically at a fixed time. It inspects based on the time you open the screen. The day is split into three inspections of six hours each, and the inspection name and the window examined depend on when you open the screen.

Inspection	Time you open the screen	Window examined
Morning inspection	06:00 ~ 12:00	From 18:00 the previous day until now
Afternoon inspection	12:00 ~ 18:00	From 06:00 the same day until now
Evening inspection	18:00 ~ 06:00 the next day	From 12:00 the same day until now

If you open the screen between 00:00 and 06:00, it counts as the previous day's Evening inspection and examines from 12:00 the previous day until now.

What the advisor does

The What the advisor does area shows the following five inspections. Each one records the window it examined and whether anything was wrong.

Inspection	What it examines
Instance status inspection	Checks the inspection window in 10-minute units and judges the current state from the last 30 minutes. Sessions use an absolute lower bound and a multiple of the usual value, CPU uses the 80% and 92% criteria
Event watch	One or more critical events, or three or more warning events repeated, within 24 hours
Degrading SQL watch	Compares the last 6 hours today with the same hours yesterday. 30 or more executions on both days, an average of 0.1 seconds or more today, and 3 times or more of yesterday
Baseline performance diagnosis	Among the top SQL by execution time in the last 6 hours, one SQL accounting for 40% or more with 60 seconds cumulative and 10 or more executions counts as load concentration
Capacity watch	Usage of 80% or more together with a 14-day trend predicting depletion within 30 days. On platforms without a limit, an increase of 100MB or more per day over the last 3 days together with 3 times the 14-day average

The usual value is the median of the hourly averages collected over the last 14 days.

Reviewing findings

When an inspection flags something, an item is added to the finding list. The finding list consists of the Priority, Finding, Target, First seen, and Action columns.

When the same finding continues, the list shows its history as follows.

• First today. A finding seen for the first time today
• Day n. A finding that continues over several days
• Recurred after resolution. A finding seen again after it was resolved
• Continuing after no effect. A finding that continues after an action was applied without effect

Click Show the flow from finding to action below the finding list to expand a flow diagram that shows the progress of each finding in one line. The flow runs Finding, Cause, Action, State, and the Action cell is empty while there is no action yet. To collapse it again, click Collapse flow.

Click a row in the finding list or a cell in the flow diagram to open the detail panel on the right. Clicking the name of an inspection that flagged something in What the advisor does also opens the detail panel for the finding it flagged. The detail panel consists of Judgment criteria, Cause, the Trend chart, and Basis, in that order.

Applying actions and their state

An action is a suggestion. Review it, then apply it to the database yourself. After applying it, click the Mark as applied button in the detail panel. This button appears only in the detail panel of a finding that suggested an action. To undo the mark, click the Undo applied mark button.

A marked action is tracked through the following states.

State	Description
Waiting to apply	Suggested. You run it yourself
Verifying	Marked as applied. The next inspection judges whether it is resolved
Effective	The same finding did not appear again after the action
No effect	The same finding appeared again after the action

How long it waits before judging the effect differs by inspection.

Inspection	Wait before judging
Instance status inspection, Degrading SQL watch, Baseline performance diagnosis	6 hours
Event watch	12 hours
Capacity watch	3 days

In-depth investigation

The finding detail panel provides the following buttons.

• Ask the advisor to investigate. Opens the WhaTap AI chat window and looks up more related data based on the selected finding to investigate the cause in more detail.
• A button that moves to the related analysis screen. Depending on the type of finding, one of View in instance monitoring, View in Top SQL, View in Top SQL Comparison, View in capacity trend, or View in alert history appears. Click it to move to that screen with the target instance and the inspection window already set.

Daily inspection

Daily inspection inspects whether the database was healthy over the past day. It looks at the 24 hours before 08:00 each day and stores the result by date. Select the Daily inspection card on the home screen to open it.

Creating an inspection record

Note

Daily inspection does not run automatically at a fixed time. It runs the first time you open the Daily inspection screen that day, and the result is stored. For days you did not open the screen, use the Fill in the previous 7 days button to fill in up to 21 days back.

• The reference time is 08:00 each day, in the time zone of the browser you are using.
• The record for the current day is created after 08:30, once the last hourly aggregation has finished.
• If you open it again the same day, the stored result is shown, so the numbers and judgments do not change.

You can create records for past dates with the Fill in the previous 7 days button. Note that the SQL, event, and capacity items are only calculated up to 7 days back, and dates older than that are shown as Verification required. To rebuild one specific date, click the Retry this day only button.

Inspection records are kept by date for a limited period, and only the summary remains for older dates. Use Copy summary to keep records you need to retain longer.

Reviewing the inspection result

The Daily inspection screen consists of the following areas, from the top.

• Inspection record information. Shows the reference date of the record and the data window it was built from. Use the arrow buttons or the date dropdown to select the record for another date.
• Record bar by date. Shows by color whether a record exists for each date and the worst judgment it contains. Select a date in the bar to open the record for that day.
• Judgment totals. Shows the number of items verified and the counts for Normal, Caution, Fatal, and Verification required.
• Item-specific summary. Shows the judgment distribution for each item as a card. Select a card to show only that item in the inspection result table below.
• Inspection result table. Shows Type, Item, Target, When, Verified result and basis, and Judgment for each item. Hover over the icon next to the judgment to see the reason for the judgment. Expand View reference above the table to see the judgment criteria for each item.

When a record exists for the previous day, the change compared with that day is shown below the table.

The inspection scope differs by item.

• Per project. Response time deteriorating SQL, SQL load concentration, warning notification trigger
• Per instance. All other items

The value for a counter item is the highest of the 24 hourly averages over 24 hours.

For the items this database inspects and their judgment criteria, see Daily inspection items. If the metrics the project collects do not contain a value, that item is not created, and the number of instances without a value is shown below the table.

Click the Copy summary button at the top of the screen to copy the record summary to the clipboard. Paste it into a shift handover or a daily report. Click the Ask about the cause button at the bottom of the screen to open the WhaTap AI chat window with the content of this record attached.

Viewing as of the current time

Click the View as of current time button to show a result rejudged over the most recent window as of now, instead of the stored record. The window used for the rejudgment differs by item type.

• Counter items use the last 30 minutes
• SQL items use the last 6 hours
• Event items use the last 24 hours
• Capacity items use the last 14 days

Because the window used for the judgment differs from the record, the judgment may differ as well. To return to the stored record, click the View as inspection record button.

Moving to an analysis screen

Each row of the inspection result table has a button that moves to the related analysis screen. The button name matches the sidebar menu name. Click it to open that screen with the inspected instance and the inspection window already set. For example, the Top SQL Comparison screen opens with the instance, the SQL condition, and the inspection window already filled in.

• The move button for a normal row appears once you expand the rows with View n more normal items.
• A row judged Verification required because there is no collected data moves to Agent list. Check the collection status of that instance.
• The Event History and Monitoring screen opens with only the inspection window set. Search for the target instance on the screen yourself.

For the screen each item moves to, see Daily inspection items.

Analyzing the cause with WhaTap AI

In a row judged Caution or Fatal, click the Cause button. The WhaTap AI chat window opens with the judgment criteria, the judgment, and the verified result for that item attached, so you do not have to describe the situation again. WhaTap AI organizes candidate causes from what it receives. The judgment is already final in the inspection record, so it does not change based on the chat.

For more information about WhaTap AI, see the following document.

Daily inspection items

The Daily inspection for a PostgreSQL project verifies the following items.

Item	Type	Scope	Judgment criteria	Move button
CPU utilization	Performance	Instance	80% or more is Caution, 92% or more is Fatal. Not judged when fewer than 6 hours have a value. Inspected only when the XOS agent is installed on the database server; without it the result is Verification required	Monitoring a Database Instance
Active sessions	Performance	Instance	10 or more together with 3 times the usual value is Caution, 30 or more together with 6 times is Fatal	Monitoring a Database Instance
Lock waiting session	Performance	Instance	2 or more together with 3 times the usual value is Caution, 5 or more together with 5 times is Fatal	Monitoring a Database Instance
Collection continuity	Availability	Instance	22 hours or more collected is Normal, 6 to 21 hours is Caution, less than 6 hours is Verification required (other items on the same instance are also held)	Monitoring a Database Instance
Restart status	Availability	Instance	Uptime of less than 24 hours is Caution	Monitoring a Database Instance
Replication delay	Availability	Instance	10 seconds or more is Caution, 60 seconds or more is Fatal. The item is not created when there is no replication value	Topology
Deadlock occurred	Event	Instance	An increase of 1 or more over 24 hours is Caution	Lock tree
Rapid increase in DB size	Capacity	Instance	An increase of 100MB or more per day over the last 3 days together with 3 times the 14-day average is Caution	Database size
Response time deteriorating SQL	Performance	Project	Compares the last 24 hours with the 24 hours before. Caution when at least 1 SQL has 30 or more executions in both windows, an average of 0.1 seconds or more, and is 3 times slower	Top SQL Comparison
SQL load concentration	Performance	Project	The top SQL accounting for 40% or more, with 60 seconds cumulative and 10 or more executions, is Caution	Top SQL
Warning notification trigger	Event	Project	1 or more critical is Fatal, 3 or more warnings from one rule is Caution	Event History and Monitoring
Transaction age depletion rate	Availability	Instance	50% or more of the limit is Caution, 80% or more is Fatal. Based on the worst table in the instance	Vacuum Analysis
Dead tuple ratio	Performance	Instance	20% or more is Caution, 50% or more is Fatal. Applies to tables with 10,000 rows or more	Vacuum Analysis

Note

Transaction age depletion rate and Dead tuple ratio are included only in the record for the current day. They are not calculated for past dates created with Fill in the previous 7 days.