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. |
https://docs.mongodb.com/manual/tutorial/transparent-huge-pages/ |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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/ |
| 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 |
| 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 |
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. | https://docs.mongodb.com/manual/administration/production-checklist-operations/#sharding |
| 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 |
| 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 |
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/ |
| 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/ |
| 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/ |
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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 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 | 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 |
| 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 |
| Autovacuum | pgsql.config.autovacuum
|
Autovacuum should be enabled to perform periodic maintenance automatically. | https://www.postgresql.org/docs/current/static/routine-vacuuming.html |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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/ |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| File system privileges | pgsql.security.fs_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. | https://www.postgresql.org/docs/current/static/sql-alterextension.html |
| 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. |