The user clicks on "Databases | Overview | SQL Performance" and error message appears “Failed to retrieve data from SQL PI repository.”
A popup with a message similar to the following will appear
With error messages similar to these:
Failed to execute SQL: SELECT SUM(active_time) AS sum_active_time, SUM(cpu_time) AS sum_cpu_time, MIN(sample_starttime) AS min_sample_starttime, MAX(sample_endtime) AS max_sample_endtime FROM pass_unique_stat_fact_1m_YYYYMMDDSSSS WHERE sample_endtime >= ? AND sample_starttime <= ? AND instance_key = ? Param: [YYYY-MM-DD HH:MM:SS.SSS -XXXX, YYYY-MM-DD HH:MM:SS.SSS -XXXX, 1] Connection Details: PostgresProfile: host [hostname, port , database [spimssql], user [postgres] Cause: ERROR: relation "pass_unique_stat_fact_1m_201504271800" does not exist Position: 170 Error Code: 0 SQL State: 42P01
Caused by: java.lang.RuntimeException: Failed to execute SQL: SELECT instance_key AS instance_key FROM spiora_instance_dim WHERE instance_key = ? AND rac_instance_key = ? Param: [2, -1] Connection Details: PostgresProfile: host [HOSTNAME], port , database [spioracle-efdd5bbb-17c5-4441-a72d-5e6d4293e7ca], user [postgres] Cause: Cannot create PoolableConnectionFactory (The connection attempt failed.) Error Code: 0 Cause: The connection attempt failed. Error Code: 0 SQL State: 08001 Cause: HOSTNAME Cause: The connection attempt failed. Error Code: 0 SQL State: 08001 Cause: HOSTNAME
Failed to execute SQL: SELECT instance_key AS instance_key, inserted_time AS inserted_time, create_date AS create_date, updated_date AS updated_date, instance_name AS instance_name, host AS host, additional_info AS additional_info, monitored AS monitored, db_version AS db_version, pass_version AS pass_version, hw_type AS hw_type, os_type AS os_type, os_release AS os_release, os_version AS os_version FROM pass_instance_dim WHERE instance_key = ? Param:  Connection Details: PostgresProfile: host [HOSTNAME], port , database [spimssql], user [postgres] Cause: Cannot create PoolableConnectionFactory (The connection attempt failed.) Error Code: 0 Cause: The connection attempt failed. Error Code: 0 SQL State: 08001 Cause: Connection reset Cause: The connection attempt failed. Error Code: 0 SQL State: 08001 Cause: Connection reset Cause: Connection reset
This alarm is invoked when data has not been inserted to the performance repository from more than 10 minutes. This is a rare situation that may occur if there is a problem connecting to the monitored instance or to the performance repository.
Error messages similar to the following may appear in the log files
The postgresql.log from the SQL PI repository states:
Sizing issues on the Foglight Agent Manager and SQL PI repository servers.This is the most common cause for SQL PI issues.
Network connection issues
Foglight Agent Manager is not running
Insufficient Java Heap settings for the Foglight Agent Manager.
The Performance Investigator repository is not running
Performance Investigator agents are paused
On FGLAM Linux, repository log states "could not create shared memory segment: Invalid argument" due to the OS level shmax.
The SQL PI dashboard shows "Failed to retrieve data from SQL PI repository. Cannot create PoolableConnectionFactory" message.
Address sizing issues - *** This is the most commonly reported cause for SQL PI issues
Review the Foglight for Databases deployment guide to confirm that the SQL PI repository server meets the CPU, memory, and disk space required based on the number of database agents. The latest release of the deployment guide is available for download from here.
Also please review the Foglight for Databases deployment guide for common sizing suggestions for SQL PI environments.
Note: CPU and memory reservations are a requirement for SQL PI installed on virtual environments using Infobright as the PI repository. As well, please ensure that sufficient pagefile capacity is available on the SQL PI repository and Foglight Agent Manager servers.
Network connection issues
Please refer to resolution area in KB 146730 for troubleshooting recommendations for SQL PI environments.
Reactivating the Foglight Agent Manager (FglAM)
Insufficient Java Heap sizing for the Foglight Agent Manager
Ensure JMX settings are set correctly for SQL PI
Foglight Agent Manager
|Number of database agents||<5||<50||<100||<200||<400||<600||<800|
JVM Settings need to be adjusted higher for the FglAM (Foglight Agent Manager) where the database agents are installed for SQL PITo change the memory settings:
An additional tuning step is: Increase the disk space parameter of the FglAM, following the steps below:
Please refer to KB 204485 for details on unpausing PI monitoring
On FGLAM with Linux with an Infobright SQL PI repository:
Here is a reference link to the Linux docummentation.
Note: If the problem continues, upgrade to the latest database cartridge version. If the problem continues, send the FGLAM support bundle with the SQL PI repository, FGLAM with the DB agents, and FMS support bundle to the Support Team to further investigate. Please, include the screenshot and expand the Details link if it appears on the error message.