Best Practices Dashboard

The VividCortex application automatically reviews the settings of your OS and database instances and identifies settings which are inconsistent with established best practices. For clients with query samples enabled, VividCortex also proactively alerts you to possible query-writing and index optimizations.

The Best Practices dashboard displays the results of these tests, allowing you to quickly find areas where you can improve the performance and security of your database. These checks are performed every 15 minutes.

To visit the Best Practices dashboard, click on the “Health” icon in the left-hand navigation. Then at the top of the window, click “Best Practices.”

Best Practices Dashboard

The Best Practices page lists all of the checks which one or more of your database instances or queries are currently failing (you can also see checks which have passed by clicking “Show passed best practices”). To view specific Best Practices and which checks have failed, click one of the Categories, and then click one of the Types to expand. For example, in this screenshot, there are three Categories, and the first Category is expanded to show three Types:

Best Practices Dashboard

Click on one of the Types to see a list of instances which have failed that check:

Best Practices Dashboard

You can also mute checks which you no longer want to be displayed. You can mute a check for every host (by selecting Mute all), or just for specific hosts. To show those checks again, select the box marked “Show muted best practices.” If you have a best practice that is both passed and muted, you will need to select to see both passed and muted best practices in order to view it again.

Below you will find a list of checks for each database technology:

MongoDB Checks

These are based on the official MongoDB documentation, which makes a number of configuration recommendations. Each of the “Reference Source” links in the table below are links to the MongoDB documentation for further reading.

MongoDB & OS Configuration

Check Description Official Documentation
Transparent huge pages Transparent huge pages should be disabled on the server, as MongoDB generally performs better with normally sized virtual memory pages. Reference Source
Journaling Journaling should be enabled on the server to help avoid data loss in the case of a crash. Reference Source
Swap MongoDB recommends having some swap space configured to help avoid getting terminated by the OOM Killer when the server is under memory pressure. Reference Source
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. Reference Source
Self resolution When running in a replica set, it is important to ensure that the MongoDB server can resolve its own hostname. Reference Source
Filehandle limit Ensure that MongoDB has enough file handles to handle your workload. For production systems a limit of 98,000 is usually reasonable. Reference Source
Thread limit Ensure the server has a high enough thread limit for your workload. For production systems a limit of 64,000 is usually reasonable. Reference Source
PID limit Ensure that the server has a high enough PID limit for your workload. For production systems a limit of 64,000 is usually reasonable. Reference Source
Readahead settings Ensure the readahead settings for your storage device are appropriate; if using WiredTiger, readahead should be set to 0. For MMAPv1, consider a value of 32 or lower. Reference Source
Disk scheduler Use the noop or deadline disk scheduler for the database’s storage device for maximum performance. Reference Source
dbPath mount point Use noatime for the dbPath mount point to improve performance. Reference Source
Filesystem 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. Reference Source
Index sizes Ensure that each of your indexes fit entirely in memory so that the system can avoid reading the index from disk. Reference Source
Index total size Ensure that all your indexes fit entirely in memory so that the system can avoid reading the index from disk. Reference Source
Collection sizes MongoDB recommends your working set should stay in memory to achieve good performance. Reference Source
Total size MongoDB recommends the total size of your collections should stay in memory to achieve good performance. Reference Source

MongoDB Replication Configuration

Check Description Official Documentation
Hostnames Use hostnames when configuring replica set members rather than IP-addresses, as it makes them easier to maintain in production. Reference Source
Cluster 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. Reference Source
Cluster vote config It is recommended that replica set members have either 0 or 1 votes allocated to them. Reference Source

MongoDB Security Configuration

Check Description Official Documentation
Authorization Ensure that MongoDB is configured with role-based access control enabled so that specific user actions can be limited as necessary. Reference Source
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. Reference Source
Network exposure Ensure that MongoDB’s HTTP status interface, REST API and JSON API are disabled to limit untrusted access to the database. Reference Source
Javascript MongoDB supports the execution of JavaScript code for certain server operations; this should be disabled if possible to avoid unsafe use. Reference Source



MySQL Checks

Check Description Official Documentation
Anonymous users It is recommended to not run MySQL without usernames. Reference Source
Server running as root MySQL should not be running with a user or group with root privileges. Reference Source
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. Reference Source
Binary log 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. Binlog Reference InnoDB Reference
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. Reference Source
Network exposure MySQL should not be listening on a public IP. Reference Source
Performance Schema Disabling the Performance Schema will affect VividCortex’s ability to monitor MySQL.
Query cache The query cache can cause occasional stalls which affect query performance. It’s recommended to disable this feature (except for Aurora). Reference Source
Sort buffer size The sort buffer size is dynamic and per-connection, so it should be changed per connection instead of by default. Reference Source
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. Reference Source
Durable table definitions sync_frm should be enabled to make sure table definitions are crash-safe. Reference Source
MySQL upgrade mysql_upgrade should be run after a major version update of MySQL. Reference Source
Relative buffer pool size The configured buffer pool size should not be greater than 90% of total memory. Reference Source
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. Reference Source
Doublewrite buffer The doublewrite buffer should be enabled to prevent torn page writes which can lead to data loss or corruption. Reference Source
Flush method To maximize durability, innodb_flush_method should not be set to “littlesync” or “nosync.” Reference Source
Log flush on commit innodb_flush_log_at_trx_commit should be set to “1” to maximize ACID compliance. Reference Source
I/O 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. Reference Source

MySQL Query Checks

VividCortex automatically analyzes captured MySQL queries to see if they fail one or more rules designed to ensure adherence to query-writing best practices. Queries which fail one or more of these checks will be displayed on the Best Practices page. For a complete list of rules which VividCortex uses when examining your database queries, please see this list in the Query Analysis Rules documentation.



PostgreSQL Checks

Check Description Official Documentation
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. Reference Source
Max connections Connections are relatively expensive in PostgreSQL, so connection pooling is recommended to keep the maximum number of connections low. Reference Source
Autovacuum Autovacuum should be enabled to perform periodic maintenence automatically. Reference Source
Synchronous commit Synchronous commit should be enabled to ensure transaction durability. Reference Source
Fsync fsync should be enabled to ensure data integrity and avoid data corruption, especially after crashes or hard restarts. Reference Source
Memory consumption PostgreSQL should be configured to use 25% to 40% of total memory for shared memory buffers. Reference Source
Effective I/O concurrency I/O concurrency parameters should be set according to the capabilities of the database disk volume. Reference Source
Old snapshot threshold old_snapshot_threshold should be enabled to avoid a gradual slowdown on production databases due to snapshot data bloat. Reference Source
I/O page cost I/O page cost parameters should be set according to the capabilities of the database disk volume. Reference Source
Backend privileges It is recommended to run PostgreSQL as a non-root user and group.
Backend process limit max_connections should be less than the soft process limit set in the OS. Reference Source
Time since last vacuum Vacuums and/or autovacuums should be performed regularly to purge old data and avoid transaction ID wraparound, among other reasons. Reference Source
Network exposure PostgreSQL should not be configured to listen on a publicly routable IP address to limit network exposure. Reference Source
File system privileges PostgreSQL configuration files and data directories should have proper permissions to prevent unauthorized access.
Outdated extensions Extensions should not be older than the version of PostgreSQL. You should update extensions when upgrading PostgreSQL. Reference Source
Permanent user passwords Permanent passwords (passwords without an expiration) should be avoided. Reference Source
Unsecure clear-text passwords Users should not be allowed to use clear-text passwords over unencrypted connections. Reference Source

PostgreSQL Query Checks

VividCortex automatically analyzes captured PostgreSQL queries to see if they fail one or more rules designed to ensure adherence to query-writing best practices. Queries which fail one or more of these checks will be displayed on the Best Practices page. For a complete list of rules which VividCortex uses when examining your database queries, please see this list in the Query Analysis Rules documentation.