Documentation forSolarWinds Observability SaaS

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.

Mute advisors that don't apply to your environment

You can mute any advisor, or advisor category that may not apply to your environment. Navigate to the advisor category, and click the mute assertion button to mute the single advisor occurrence. Click the Mute All button for an advisor category to mute the entire advisor category.

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
Collection sizes mongo.collection.size MongoDB recommends your working set should stay in memory to achieve good performance. https://docs.mongodb.com/manual/faq/diagnostics/#must-my-working-set-size-fit-ram
dbPath mount point mongo.dbpath.fs.noatime Use noatime for the dbPath mount point to improve performance. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Disk scheduler mongo.dbpath.fs.scheduler Use the noop or deadline disk scheduler for the database’s storage device for maximum performance. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
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. https://docs.mongodb.com/manual/tutorial/ensure-indexes-fit-ram/
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. https://docs.mongodb.com/manual/tutorial/ensure-indexes-fit-ram/
Journaling mongo.os.journaling Journaling should be enabled on the server to help avoid data loss in the case of a crash. https://docs.mongodb.com/manual/administration/production-notes/#journaling
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Self resolution mongo.net.resolve_self When running in a replica set, it is important to ensure that the MongoDB server can resolve its own hostname. https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
TCP keepalive mongo.net.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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#operating-system-configuration
Total size mongo.collection.total_size MongoDB recommends the total size of your collections should stay in memory to achieve good performance. https://docs.mongodb.com/manual/faq/diagnostics/#must-my-working-set-size-fit-ram
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.

https://docs.mongodb.com/manual/tutorial/transparent-huge-pages/

MongoDB replication configuration

Check Metric name Description More information
Cluster vote config mongo.repl.allocated_votes It is recommended that replica set members have either 0 or 1 votes allocated to them. https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
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. https://docs.mongodb.com/manual/administration/production-checklist-operations/#replication
Hostnames mongo.repl.hostnames Use hostnames when configuring replica set members rather than IP-addresses, as it makes them easier to maintain in production. https://docs.mongodb.com/manual/administration/production-checklist-operations/#sharding

MongoDB security configuration

Check Metric name Description More information
Authorization mongo.security.authorization Ensure that MongoDB is configured with role-based access control enabled so that specific user actions can be limited as necessary. https://docs.mongodb.com/manual/administration/security-checklist/
Encryption mongo.security.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. https://docs.mongodb.com/manual/administration/security-checklist/
Javascript mongo.security.javascript MongoDB supports the execution of JavaScript code for certain server operations; this should be disabled if possible to avoid unsafe use. https://docs.mongodb.com/manual/administration/security-checklist/
Network exposure mongo.security.net.exposure Ensure that MongoDB’s HTTP status interface, REST API and JSON API are disabled to limit untrusted access to the database. https://docs.mongodb.com/manual/administration/security-checklist/

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 mysql.security.anonymous_users It is recommended to not run MySQL without usernames. https://dev.mysql.com/doc/refman/5.7/en/default-privileges.html
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.

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

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. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
CloudWatch credentials mysql.offhost_cloudwatch Remote MySQL instance lacks CloudWatch credentials.  
Doublewrite buffer mysql.innodb.doublewrite The doublewrite buffer should be enabled to prevent torn page writes which can lead to data loss or corruption. https://dev.mysql.com/doc/refman/5.7/en/innodb-doublewrite-buffer.html
Durable table definitions mysql.sync_frm sync_frm should be enabled to make sure table definitions are crash-safe.  
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. https://dev.mysql.com/doc/refman/5.7/en/not-enough-file-handles.html
File system privileges mysql.security.privileges The MySQL server configuration file and data directory should not be world-writable. The slow query log file should not be world readable. https://dev.mysql.com/doc/refman/5.7/en/security-against-attack.html
Flush method mysql.innodb.flush_method To maximize durability, innodb_flush_method should not be set to “littlesync” or “nosync.” https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
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.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_io_capacity

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. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
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.  
MySQL upgrade mysql.upgrade mysql_upgrade should be run after a major version update of MySQL. https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html
Network exposure mysql.network_exposure MySQL should not be listening on a public IP. https://dev.mysql.com/doc/refman/5.7/en/security-guidelines.html
Off-host query digests mysql.offhost_digests_disabled Off-host query digests require the "statements_digest" consumer.  
Off-host query samples mysql.offhost_samples_disabled Off-host query samples require the "events_statements_current" and "events_statements_history_long" consumers".  
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.  
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). https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
Relative buffer pool size mysql.innodb.buffer_pool_memory_size The configured buffer pool size should not be greater than 90% of total memory. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
Server running as root mysql.security.backend_is_root MySQL should not be running with a user or group with root privileges. https://dev.mysql.com/doc/refman/5.7/en/security-against-attack.html
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.  
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. https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size
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. https://www.kernel.org/doc/Documentation/sysctl/vm.txt

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
Autovacuum pgsql.config.autovacuum Autovacuum should be enabled to perform periodic maintenance automatically. https://www.postgresql.org/docs/current/static/routine-vacuuming.html
Backend privileges pgsql.backend.privileges It is recommended to run PostgreSQL as a non-root user and group.  
Backend process limit pgsql.backend.process_limit max_connections should be less than the soft process limit set in the OS. https://www.postgresql.org/docs/current/static/runtime-config-connection.html
CloudWatch credentials pgsql.offhost_cloudwatch Remote PostgreSQL instance lacks CloudWatch credentials.  
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. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#effective_cache_size
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. https://www.postgresql.org/docs/current/static/runtime-config-resource.html#runtime-config-resource-async-behavior
File system privileges pgsql.security.fs_privileges PostgreSQL configuration files and data directories should have proper permissions to prevent unauthorized access.  
Fsync pgsql.config.fsync fsync should be enabled to ensure data integrity and avoid data corruption, especially after crashes or hard restarts. https://www.postgresql.org/docs/current/static/runtime-config-wal.html
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. https://www.postgresql.org/docs/current/static/runtime-config-query.html#runtime-config-query-constants
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. https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
Log Destination pgsql.slow_var_log_destination Server's "log_destination" parameter should be set to "stderr".  
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.  
Maintenance work memory pgsql.config.maintenance_work_mem maintenance_work_mem should be set to at least 10% of total memory available. https://pydanny-event-notes.readthedocs.io/en/latest/DjangoConEurope2012/10-steps-to-better-postgresql-performance.html#maintenance-work-mem
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. https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
Memory consumption pgsql.config.shared_buffers PostgreSQL should be configured to use 25% to 40% of total memory for shared memory buffers. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers
Network exposure pgsql.security.network_exposure PostgreSQL should not be configured to listen on a publicly routable IP address to limit network exposure. https://www.postgresql.org/docs/current/static/runtime-config-connection.html
Off-host query metrics pgsql.feature.query_metrics Off-host query metrics require the agent have access to the pg_stat_statements extension.  
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. https://www.postgresql.org/docs/current/static/runtime-config-resource.html
Outdated extensions pgsql.extensions.versions Extensions should not be older than the version of PostgreSQL. You should update extensions when upgrading PostgreSQL. https://www.postgresql.org/docs/current/static/sql-alterextension.html
Permanent user passwords pgsql.security.user_passwords Permanent passwords (passwords without an expiration) should be avoided. https://www.postgresql.org/docs/current/static/sql-createrole.html
Slow Log Duration pgsql.slow_var_log_duration Server's "log_duration" parameter should be set to "on" to log the duration of statements.  
Slow Query Log pgsql.slow_log_disabled Reading queries from the slow query log requires this log to be enabled. Set the server's "log_statement" parameter to "all".  
Statement logging pgsql.security.log_statement 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. https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement
Synchronous commit pgsql.config.synchronous_commit Synchronous commit should be enabled to ensure transaction durability. https://www.postgresql.org/docs/current/static/runtime-config-wal.html
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. https://www.postgresql.org/docs/current/static/routine-vacuuming.html
Unsecure clear-text passwords pgsql.security.clear_text_passwords Users should not be allowed to use clear-text passwords over unencrypted connections. https://www.postgresql.org/docs/current/static/auth-methods.html
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. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Work mem pgsql.config.work_mem work_mem should generally be changed only for specific sessions that need larger buffers. https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/