Automated Database Performance Advisors

The ClusterControl Developer Studio provides you a set of monitoring and performance advisors to use and lets you create custom advisors to add security and stability to your database infrastructures.

ClusterControl Developer Studio

ClusterControl comes with a library of Advisors to help you keep track of your database performance, alert you when there are anomalies, keep your data secure by checking and monitoring who has access and ensuring that all the necessary security features are enabled. You can also easily extend the functionality of ClusterControl by creating custom advisors that can run on-demand or on a schedule.

Download for FREE

Featured Advisors

MySQL

Galera & NDB Cluster

MongoDB

Host Statistics

Auto Tune table_cache_size

This advisor calculates the percentage of open_tables over tables_open_cache and adjusts table_open_cache automatically based on the running workload.

File Name in ClusterControl

s9s/mysql/auto_tuners/table_cache.js

Auto Tune thread_cache_size

This advisor calculates the percentage of threads_created over threads_cached and adjust the thread_cache_size automatically based on the running workload.

File Name in ClusterControl

s9s/mysql/auto_tuners/thread_cache.js

Connections Currently Used

This advisor calculates the percentage of threads_connected over max_connections, if the percentage is higher than 90% you will be notified, preventing your database server from becoming unstable.

File Name in ClusterControl

s9s/mysql/connections/connections_used_pct.js

Connections Ever Used

This advisor calculates the percentage of max_used_connections over max_connections to determine the maximum ever used connections, if the percentage is higher than 90% you will be notified, preventing your database server from becoming unstable.

File Name in ClusterControl

s9s/mysql/connections/max_connections_used_pct.js

InnoDB Buffer Pool Hit Ratio

This advisor calculates the ratio of innodb_buffer_pool_reads over innodb_buffer_pool_read_requests and notifies you if the ratio is lower than 999. Hit ratio provides insight on how often MySQL pages are retrieved from memory instead of disk.

File Name in ClusterControl

s9s/mysql/innodb/innodb_bufferpool_hitratio.js

InnoDB Buffer Pool Waits

This advisor reads the innodb_buffer_pool_wait_free value from the runtime status and notifies you if the value is more than 0, which indicates the innodb_buffer_pool_size is too small, and that it had to wait for checkpointing.

File Name in ClusterControl

s9s/mysql/innodb/innodb_bufferpool_waits.js

InnoDB Log File Size Check

This advisor calculates the InnoDB log growth per hour and compares it with the innodb_log_file_size configured on the host and notifies you if the InnoDB log growth is higher than what is configured, which is important to avoid IO spikes during flushing.

File Name in ClusterControl

s9s/mysql/innodb/check_logfile_size.js

InnoDB Percent Dirty Pages

This advisor calculates the ratio of innodb_buffer_pool_pages_dirty over innodb_buffer_pool_pages_total and notifies you if the ratio is higher. This informs you when to increase the innodb_buffer_pool_size, if the ratio stays high for a long time.

File Name in ClusterControl

s9s/mysql/innodb/innodb_dirty_pages_pct.js

Retrieve DB Size

This advisor provides a snapshot of your database by selecting the total number of rows, data size, index size, total size and index ratio per database object from information_schema.

File Name in ClusterControl

s9s/mysql/i_s/db_size.js

System Health Check

This advisor is a Health Check which executes a series of queries to test DDL (metadata locks) and DML and notifies you if the check fails; which could indicate that although the server may be up the database may not be responding correctly.

File Name in ClusterControl

s9s/mysql/health/system_check.js

Check Performance Schema

This advisor reads the performance_schema value in runtime and suggests that you to enable performance_schema in the configuration file is disabled, allowing you to inspect internal execution of the server at runtime.

File Name in ClusterControl

s9s/mysql/p_s/check_ps.js

InnoDB Log Waits

This advisor reads the innodb_log_waits value from the runtime status and notifies if the value is more than 0, which indicates a peak write load and possibility of disks are too slow to sustain the IO.

File Name in ClusterControl

s9s/mysql/innodb/innodb_log_waits.js

Processlist From PS

This advisor selects the MySQL process list on all database servers from the performance_schema and provides a summary of all running processes across the MySQL server.

File Name in ClusterControl

s9s/mysql/p_s/processlist.js

Secure MySQL Installation

This advisor runs multiple checks and provides security recommendations based on mysql_secure_installation script.

File Name in ClusterControl

s9s/mysql/programs/mysql_secure_installation.js

Table Access Without Using Index

This advisor selects all tables being accessed without using an index from performance_schema to show you where to improve the queries running against these tables.

File Name in ClusterControl

s9s/mysql/p_s/no_indexes_used.js

Top Accessed DB Files

This advisor selects the top 10 most frequently accessed files by MySQL and shows you the number of times accessed and total time from performance_schema.

File Name in ClusterControl

s9s/mysql/p_s/top_accessed_db_files.js

Top Queries

This advisor selects the top 5 queries, sorted by execution count, from performance_schema.

File Name in ClusterControl

s9s/mysql/p_s/top_queries.js

Top Tables by IO Wait

This advisor selects the top 10 tables, sorted by the sum of IO wait, from performance_schema.

File Name in ClusterControl

s9s/mysql/p_s/top_tables_by_iowait.js

Top Tables by Lock Wait

This advisor selects the top 10 tables, sorted by the sum of lock wait, from performance_schema.

File Name in ClusterControl

s9s/mysql/p_s/top_tables_by_lockwait.js

Unused Indexes

This advisor selects the unused existing indexes from performance_schema to allow you to remove the unused indexes which improves the schema and overall performance.

File Name in ClusterControl

s9s/mysql/p_s/unused_indexes.js

Access From Any Host

This advisor identifies all users that use a wildcard host from the mysql system table, and let you have more control over which hosts are able to connect to the servers.

File Name in ClusterControl

s9s/mysql/security/grant_any_host.js

Account Without Password

This advisor identifies all users who do not have a password in the mysql system table, which helps to increase data security.

File Name in ClusterControl

s9s/mysql/security/grant_no_password.js

Binlog Expiration

This advisor reads the value of expire_log_days to see if it exists and provides a recommendation to the user to set one, preventing indefinite binary logs growth.

File Name in ClusterControl

s9s/mysql/replication/binlog_expiration.js

Check Compatible Schema

This advisor checks the table design to ensure it's compatible with MySQL Group Replication technology and notifies you if any tables require alteration.

File Name in ClusterControl

s9s/mysql/group_repl/compatible_schema.js

Check for MyISAM Tables

This advisor identifies all tables with MyISAM storage engine from the information_schema, which is not a recommended "crash-safe" type of storage engine.

File Name in ClusterControl

s9s/mysql/schema/schema_check_myisam.js

Check for Primary Keys

This advisor identifies tables that have no explicit primary keys from the information_schema which is important to have as a unique identifier for each row in a dataset.

File Name in ClusterControl

s9s/mysql/schema/schema_check_nopk.js

Query Cache Hit Ratio

This advisor calculates the ratio of Qcache_hits over the sum of Qcache_hits and Qcache_inserts and provides recommendations for you to configure query_cache_size correctly, a benefit for users with read-intensive workloads.

File Name in ClusterControl

s9s/mysql/query_cache/qc_hitratio.js

Query Cache Size

This advisor calculates the query_cache_size whether it's between 64M and 256M and provides you with recommendations to configure the correct query_cache_size.

File Name in ClusterControl

s9s/mysql/query_cache/qc_size.js

Security Audit

This advisor highlights any account that has been allowed to access the database with global host access and without password, allowing you to remove or patch the unsafe accounts and increase security.

File Name in ClusterControl

s9s/mysql/programs/security_audit.js

Setup MySQL Logrotate

This advisor creates a MySQL logrotate configuration file under /etc/logrotate.d/mysql on every MySQL server which prevents your MySQL error log from growing out of hand.

File Name in ClusterControl

s9s/mysql/programs/setup_mysql_logrotate.js

Check GRA Log Files

This advisor scans the MySQL data directory and notifies you if the GRA_*.log file exists. This file only appears if there is a failure or warning in your Galera replication.

File Name in ClusterControl

s9s/mysql/galera/check_gra_log_files.js

Check wsrep_cluster_address

This advisor reads the value of wsrep_cluster_address inside each database node's configuration file and notifies you if the parameter does not exist, preventing potential catastrophic failure and data loss during service startup.

File Name in ClusterControl

s9s/mysql/galera/wsrep_cluster_address.js

Check wsrep_node_name

This advisor reads the value of wsrep_node_name inside each database node's configuration file and notifies you if the node does not contain the parameter; which is a recommended way to simplify recovery in especially in WAN setup.

File Name in ClusterControl

s9s/mysql/galera/wsrep_node_name.js

Check wsrep_slave_threads

This advisor reads the value of wsrep_slave_threads in runtime and compares the value with the warning threshold and notifies you if the slave thread is lower than 4 (a value that can be increased later depending on the workload).

File Name in ClusterControl

s9s/mysql/galera/wsrep_slave_threads.js

Check wsrep_sst_method

This advisor reads the value of wsrep_sst_method and and notifies you if wsrep_sst_xtrabackup-v2 is not installed, which is a recommended SST method for all Galera variants.

File Name in ClusterControl

s9s/mysql/galera/wsrep_sst_method.js

DataMemory Usage

This advisor calculates the ratio of DataMemory usage over DataMemory total so you can increase or clear the history tables as needed.

File Name in ClusterControl

s9s/ndbcluster/data_memory_usage.js

Fix wsrep_cluster_address

This advisor reads wsrep_cluster_address in runtime and adjusts the value of wsrep_cluster_address inside configuration file automatically to ensure the MySQL service will be started correctly.

File Name in ClusterControl

s9s/mysql/galera/fix_wsrep_cluster_address.js

Galera replication window

This advisor determines the write load on the Galera cluster and estimates if the Galera cache file is sufficient in size to sustain 4 hours of transactions.

File Name in ClusterControl

s9s/mysql/galera/check_replication_window.js

IndexMemory Usage

This advisor calculates the ratio of IndexMemory usage over IndexMemory total so you can increase or clear the history tables as needed.

File Name in ClusterControl

s9s/ndbcluster/index_memory_usage.js

Validate wsrep_sst_auth

This advisor checks the users and passwords from wsrep_sst_auth and compares it with the xtrabackup directive inside configuration file. This ensures they are the same and minimized the risk of failure during state transfer and syncs.

File Name in ClusterControl

s9s/mysql/galera/validate_sst_auth.js

Authentication/Authorization Check

This advisor collects all database information, users/roles per database per host, and verifies if these databases are protected and notifies you if users have excessive rights or weak passwords.

File Name in ClusterControl

s9s/mongodb/security/authorization_check.js

Collection Lock Percentage

This advisor collects lock information from MongoDB every 20 minutes and notifies you if the percentage of locking exceeds the threshold you have established.

File Name in ClusterControl

s9s/mongodb/mmap/collection_locks.js

Collection Sharding Check

This advisor checks all collections daily on the mongo router and verifies that they are enabled for sharding, which prevents you from have an unsharded collection that may fill up to capacity.

File Name in ClusterControl

s9s/mongodb/sharding/non_sharded_collections.js

Connections Used

This advisor collects the number of connections every minute and notifies you if the connections aren't exceeding the threshold in percentage.

File Name in ClusterControl

s9s/mongodb/connections/connections.js

MongoDB Authentication Enabled

This advisor collects all configuration and runtime options to determine if authentication has been enabled on this host and notifies you of any security anomalies it finds, ensuring your database is protected.

File Name in ClusterControl

s9s/mongodb/security/auth_enabled_check.js

Replication Check

This advisor collects the replication information of the primary every minute, then calculates the lag between the primary and secondaries in seconds and notifies you if the lag exceeds 60 seconds. This is important as Mongo client drivers tend to pick the least lagging secondaries and bringing more stress on these less laggy nodes.

File Name in ClusterControl

s9s/mongodb/replication/check_lag.js

Replication Window

This advisor collects the first and the last oplog entry every minute from every host, calculates the time difference between the two and notifies you if the window is within the set threshold (less than 24 hours for warning, less than 1 hour for critical).

File Name in ClusterControl

s9s/mongodb/replication/replication_window.js

Tickets Used

This advisor collects the number of tickets every minute and notifies you if the number of tickets exceeds 90%. Tickets are used in the WiredTiger storage engine to control read/write operations (like semaphores), if you run out of tickets it will prevent your ability to read/write to the database.

File Name in ClusterControl

s9s/mongodb/connections/tickets.js

Disk Space Used and Predicted Usage

This advisor performs a disk check every 30 minutes and notifies you if the disk space usage is greater than 95%. It also predicts the storage usage in "X" days based on linear estimation.

File Name in ClusterControl

host/disk_space_usage.js

Excessive CPU Usage

This advisor performs CPU check every 5 minutes and notifies you if the average CPU usage for the last hour exceeds 90%, which enables you to prevent database performance issues caused by high CPU usage.

File Name in ClusterControl

host/cpu_usage.js

Swappiness Check

This advisor checks if swappiness is disallowed (set to 1) on the host to ensure that the host does not "swap" which is not optimal for database workload performance.

File Name in ClusterControl

host/swappiness.js

Share Your Advisors With Us!

Have You Made a Great Advisor?

You can easily develop your own advisors to add even more database management and monitoring functionality to your ClusterControl setup. We encourage our Community and Enterprise users to share the advisors you have created on GitHub by adding a fork to our current advisor bundle. If we like them and think that they might be good for other users we’ll include them in future ClusterControl releases.

Share them with us on GitHub

Demo

Introduction to the Developer Studio

Included free with all versions of ClusterControl, the Developer Studio saves you time by automating the tasks you have to perform on a regular basis via so called Advisors, using the powerful ClusterControl DSL (Domain Specific Language). These Advisors let you detect and solve any unique problems which may occur in your database environment.