Documentation forSolarWinds Observability

Database advisor checks

SolarWinds Observability automatically reviews the settings of your host and monitored database instances and identifies settings that are inconsistent with established best practices. For clients with query samples enabled, SolarWinds Observability also proactively alerts you to possible query-writing and index optimizations. The Advisors tab on the Databases Overview displays the results of these tests, allowing you to quickly find areas where you can improve the performance and security of your database.

To access the Advisors tab, click Database > Advisors. Use the Advisors tab to view details about potential problems and optimization opportunities within your databases monitored with SolarWinds Observability.

View the advisor count in the Summary

The Advisors Summary details the total recommendation count, displaying the advisor checks that failed and are actionable to improve database best practices. The Advisors tab displays whether the advisor categories are informational (blue), critical (red), or cautionary (yellow). Critical recommendations should take priority, and require you to optimize an aspect of your database for improved database performance.

Advice categories are displayed beneath the Summary and provide you with recommendations grouped into specific database categories. Use the Advice categories to filter for specific database aspects that you may want to optimize. For example, you could expand the MySQL Configuration category to see any critical, cautionary, or informational recommendations. Each recommendation category has its own count, with listed recommendations underneath the category. Expand any recommendation to view affected database(s) by ID, their status, and an explanation of the recommendation. Click any database ID to filter the view for that database, and see all the advisor checks for that database.

Filter advisors

Apply filters to all available advisors with the severity filter drop down menu. Quickly filter all advisors to display only information, cautionary, or critical categories. Click the X in the severity filter dropdown to remove the applied filter.

Click the Hide passed checks checkbox to hide all occurrences that passed, and are within suggested database best practices.

View the available advisor checks

See the database specific sections for information about available advisor checks.

MongoDB Advisor checks

MongoDB checks are based on the official MongoDB documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the MongoDB documentation.

MongoDB and OS configuration

Check Metric name Description More information
Transparent huge pages mongo.os.huge_pages Transparent huge pages should be disabled on the server, as MongoDB generally performs better with normally sized virtual memory pages.

Journaling mongo.os.journaling Journaling should be enabled on the server to help avoid data loss in the case of a crash.
Swap mongo.os.swap MongoDB recommends having some swap space configured to help avoid getting terminated by the OOM Killer when the server is under memory pressure.
TCP keepalive MongoDB recommends having a short keepalive time set for network connections, as this results in generally better performance for replica sets and sharded clusters.
Self resolution When running in a replica set, it is important to ensure that the MongoDB server can resolve its own hostname.
Filehandle limit mongo.limits.file_max Ensure that MongoDB has enough file handles to handle your workload. For production systems a limit of 98,000 is usually reasonable.
Thread limit mongo.limits.thread_max Ensure the server has a high enough thread limit for your workload. For production systems a limit of 64,000 is usually reasonable.
PID limit mongo.limits.pid_max Ensure that the server has a high enough PID limit for your workload. For production systems a limit of 64,000 is usually reasonable.
Readahead settings mongo.dbpath.fs.readahead Ensure the readahead settings for your storage device are appropriate. For the WiredTiger storage engine, set readahead between 8 and 32 regardless of storage media type (spinning disk, SSD, etc.), unless testing shows a measurable, repeatable, and reliable benefit in a higher readahead value. For MMAPv1, consider a value of 32 or lower.
Disk scheduler mongo.dbpath.fs.scheduler Use the noop or deadline disk scheduler for the database’s storage device for maximum performance.
dbPath mount point mongo.dbpath.fs.noatime Use noatime for the dbPath mount point to improve performance.
Filesystem mongo.dbpath.fs.type The XFS filesystem is recommended with MongoDB as it provides generally better performance. For WiredTiger it is strongly recommended due to known performance problems with EXT4.
Index sizes mongo.index.size Ensure that each of your indexes fit entirely in memory so that the system can avoid reading the index from disk.
Index total size mongo.index.size Ensure that all your indexes fit entirely in memory so that the system can avoid reading the index from disk.
Collection sizes mongo.collection.size MongoDB recommends your working set should stay in memory to achieve good performance.
Total size mongo.collection.total_size MongoDB recommends the total size of your collections should stay in memory to achieve good performance.

MongoDB replication configuration

Check Metric name Description More information
Hostnames mongo.repl.hostnames Use hostnames when configuring replica set members rather than IP-addresses, as it makes them easier to maintain in production.
Cluster voting members mongo.repl.voting_members Ensure that the replica set has an odd number of voting members; otherwise during an election it may not be possible to reach a quorum successfully.
Cluster vote config mongo.repl.allocated_votes It is recommended that replica set members have either 0 or 1 votes allocated to them.

MongoDB security configuration

Check Metric name Description More information
Authorization Ensure that MongoDB is configured with role-based access control enabled so that specific user actions can be limited as necessary.
Encryption MongoDB enterprise edition supports at-rest encryption for the WiredTiger storage engine. Enabling this option is recommended to limit the possibility of data theft.
Network exposure Ensure that MongoDB’s HTTP status interface, REST API and JSON API are disabled to limit untrusted access to the database.
Javascript MongoDB supports the execution of JavaScript code for certain server operations; this should be disabled if possible to avoid unsafe use.

MySQL Advisor checks

MySQL checks are based on the official MySQL documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the MySQL documentation.

Check Metric name Description More information
Anonymous users It is recommended to not run MySQL without usernames.
Server running as root MySQL should not be running with a user or group with root privileges.
File system privileges The MySQL server configuration file and data directory should not be world-writable. The slow query log file should not be world readable.
Binary log durability mysql.binlog_durability Some values of the sync_binlog, binlog_checksum, innodb_locks_unsafe_for_binlog, and innodb_support_xa variables can reduce the durability of transactions in the binary log and result in data loss or corruption.

File descriptor limits mysql.file_descriptor_limits MySQL’s file descriptor limit should be high enough to handle the configured number of open files, max connections, and open tables. Five times max_conections is a good starting point.
Network exposure mysql.network_exposure MySQL should not be listening on a public IP.
Performance Schema mysql.offhost_performance_schema_disabled Disabling the Performance Schema will affect DPM’s ability to monitor MySQL.  
Query cache mysql.query_cache The query cache can cause occasional stalls which affect query performance. It’s recommended to disable this feature (except for Aurora).
Sort buffer size mysql.sort_buffer_size The sort buffer size is dynamic and per-connection, so it should be changed per connection instead of by default.
Swappiness mysql.swappiness Swap should be enabled with a low swappiness value (0 or 1) in order to avoid out-of-memory termination when low on memory.
MySQL upgrade mysql.upgrade mysql_upgrade should be run after a major version update of MySQL.
Relative buffer pool size mysql.innodb.buffer_pool_memory_size The configured buffer pool size should not be greater than 90% of total memory.
Buffer pool size mysql.innodb.buffer_pool_size The buffer pool size should be set according to the amount of memory available instead of the default of 128 MB. 60%-80% is a reasonable range.
Doublewrite buffer mysql.innodb.doublewrite The doublewrite buffer should be enabled to prevent torn page writes which can lead to data loss or corruption.
Flush method mysql.innodb.flush_method To maximize durability, innodb_flush_method should not be set to “littlesync” or “nosync.”
Log flush on commit mysql.innodb.flush_on_tx_commit innodb_flush_log_at_trx_commit should be set to “1” to maximize ACID compliance.
I/O capacity mysql.innodb.io_capacity The default value of innodb_io_capacity (200) may not be optimal for modern systems with SSDs. Consider increasing it to match the I/O capabilities of the disk.

Log output to file mysql.log_output log_output should be set to 'FILE' in order to write queries to a file as destination.  
Log Queries Without Indexes mysql.log_queries_not_using_indexes log_queries_not_using_indexes should be enabled (ON), otherwise queries that do not use indexes won't be logged to the slow log file.  
Log Short Format mysql.log_short_format log_short_format should be set to FALSE, otherwise the server will write less information to the slow log file.  
Log Slow Admin Statements mysql.log_slow_admin_statements log_slow_admin_statements should be enabled (ON), otherwise administrative statements won't be logged to the slow log file.  
Log Throttle Queries Without Indexes mysql.log_throttle_queries_not_using_indexes log_throttle_queries_not_using_indexes should be set to 0, otherwise the number of queries not using indexes will be limited to this value.  
Log UTC Timestamps mysql.log_timestamps log_timestamps should be set to 'UTC' (default value).  
Long Query Time mysql.long_query_time_disabled long_query_time should be set to 0 in order to log all the queries on the log file.  
Minimum Examined Row Limit for Slow log mysql.min_examined_row_limit min_examined_row_limit should be set to 0, otherwise not all the queries will be written to the slow log file.  
Off-host query digests mysql.offhost_digests_disabled Off-host query digests require the "statements_digest" consumer.  
Performance Schema mysql.performance_schema_disabled Disabling the Performance Schema will affect SolarWinds Observability's ability to monitor MySQL.  
Performance Schema for Slow log mysql.performance_schema_disabled_slow_log Performance schema should be enabled when slow log is the capture method. This won't affect the ability to monitor but it's a good practice having it enabled as a few metrics are obtained from it.  
Slow Query Log mysql.slow_log_disabled Reading queries from the slow query log requires this log to be enabled. Set the server's slow_query_log parameter to ON.  
Durable table definitions mysql.sync_frm sync_frm should be enabled to make sure table definitions are crash-safe.  

PostgreSQL advisor checks

PostgreSQL checks are based on the official PostgreSQL documentation, which makes a number of configuration recommendations. Visit the more information links in the table below to continue to the PostgreSQL documentation.

Check Metric name Description More information
Statement logging log_statement should be set to “none” because certain values will expose passwords when they are changed with ALTER ROLE, and can expose query text in some logging modes.
Max connections pgsql.config.max_connections Connections are relatively expensive in PostgreSQL, so connection pooling is recommended to keep the maximum number of connections low.
Autovacuum pgsql.config.autovacuum Autovacuum should be enabled to perform periodic maintenance automatically.
Synchronous commit pgsql.config.synchronous_commit Synchronous commit should be enabled to ensure transaction durability.
Fsync pgsql.config.fsync fsync should be enabled to ensure data integrity and avoid data corruption, especially after crashes or hard restarts.
Memory consumption pgsql.config.shared_buffers PostgreSQL should be configured to use 25% to 40% of total memory for shared memory buffers.
Effective I/O concurrency pgsql.config.effective_io_concurrency I/O concurrency parameters should be set according to the capabilities of the database disk volume.
Old snapshot threshold pgsql.config.old_snapshot_threshold old_snapshot_threshold should be enabled to avoid a gradual slowdown on production databases due to snapshot data bloat.
I/O page cost pgsql.config.page_cost I/O page cost parameters should be set according to the capabilities of the database disk volume.
Work mem pgsql.config.work_mem work_mem should generally be changed only for specific sessions that need larger buffers.
Effective cache size pgsql.config.effective_cache_size effective_cache_size should be configured with a value between 50% and 75% of the total memory. If it is set too low indexes may not be used for executing queries.
WAL Buffers pgsql.config.wal_buffers wal_buffers should be set to 16MB (size of a single WAL segment) or even higher specially if you have a lot of concurrent write activity.
Maintenance work memory pgsql.config.maintenance_work_mem maintenance_work_mem should be set to at least 10% of total memory available.
Idle transaction timeout pgsql.config.idle_in_transaction_session_timeout idle_in_transaction_session_timeout is 0 or disabled. Setting a timeout allows any locks held by a terminated session with an idle open transaction to be released, so that connection slot will be reused.
Backend process limit pgsql.backend.process_limit max_connections should be less than the soft process limit set in the OS.
Time since last vacuum pgsql.vacuum.time_since_last_vacuum Vacuums and/or autovacuums should be performed regularly to purge old data and avoid transaction ID wraparound, among other reasons.
Network exposure PostgreSQL should not be configured to listen on a publicly routable IP address to limit network exposure.
File system privileges PostgreSQL configuration files and data directories should have proper permissions to prevent unauthorized access.  
Outdated extensions pgsql.extensions.versions Extensions should not be older than the version of PostgreSQL. You should update extensions when upgrading PostgreSQL.
Log Destination pgsql.slow_var_log_destination Server's "log_destination" parameter should be set to "stderr".  
Slow Log Duration pgsql.slow_var_log_duration Server's "log_duration" parameter should be set to "on" to log the duration of statements.  
Log Min Duration Statement pgsql.slow_var_log_min_duration_statement Server's "log_min_duration_statement" parameter should be set to "0" to log all statements.