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/ |