The follow advisories are provided in the MySQL cartridge overview page (current as of the MySQL 7.1.0 cartridge)
Users With Wildcard Hostname
- Checks for users that can access server from any location.
Allowing users to have a wildcard host name is a potential security risk because it allows unauthorized users to attempt to gain access from any location. In most situations, access should be restricted only to known hosts. Remote Root User
- Checks that the root user is only accessible locally.
Allowing the root user to log in from a remote location can be a significant security risk. If the root user were to be compromised, an attacker would have complete control over the MySQL server. High Number of Aborted Connects
- Checks for a high number of aborted connection attempts to MySQL server.
If a client is unable to connect to the MySQL server, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for one of several reasons, including attempting to access a database without having the necessary privileges, using an incorrect password, or timing out. While some errors may be due to misconfigurations on the database or application layer, authentication errors from unknown hosts are likely attempts to gain unauthorized access to the server.
Table Cache Optimization
- Checks for a large number of opened tables that may indicate table cache needs resizing.
The table cache is a system that improves performance by keeping frequently used tables open in order to service queries. The system variable table_open_cache controls the size of the table cache. MySQL closes an unused table and removes it from the table cache under the following circumstances:
- When the cache is full and a thread tries to open a table that is not in the cache.
- When the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads.
- When a table-flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
- Tables not currently in use are released, beginning with the table least recently used.
If a new table must be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.
Ideally, once the table cache is full, opening many new tables should not happen unless the FLUSH TABLES command is being executed. A large amount of turnover indicated by the opened_tables status counter probably means that the table_open_cache value should be increased.
The number of open tables can exceed the number of actual tables on the server. MySQL is multithreaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance. For more information on how MySQL opens and closes tables, go here: https://dev.mysql.com/doc/refman/8.0/en/table-cache.html
.Joins Requiring Full Table Scans
- Checks for SQL joins on MySQL server that require full table scans.
A full table scan is an operation that requires reading the entire contents of a table, rather than just selected portions using an index. It is typically performed either with small lookup tables, or in data warehousing situations with large tables where all available data is aggregated and analyzed. How frequently these operations occur, and the sizes of the tables relative to available memory, have implications for the algorithms used in query optimization and managing the buffer pool.
If full joins are occurring on large tables or on tables where the entire contents are not necessary to read, a full join is likely not necessary. Using an index, range, other type of join will improve performance and reduce memory usage.Large Number of Temporary Disk Tables Created
- Checks for temporary disk tables being created.
For several kinds of operations (generally those requiring evaluation using multiple copies of a single table), the MySQL server will create internal temporary tables which exist in-memory. A full list of these conditions can be found here: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html. Users have no direct control over when this occurs.
A temporary in-memory table is converted to an on-disk table when the size of the in-memory table reaches a maximum size - the smaller of system variables tmp_table_size and max_heap_table_size - or when certain other conditions are met, also listed in the above link. An on-disk temporary table may also be created if using the TempTable storage engine for temporary disks and the temptable_max_ram value is exceeded.\r\n\r\nIt is generally more desirable to use in-memory temporary tables as they are faster, though this may not be feasible for all statements or on all systems if memory availability is low.Redundant Indexes
- Checks for redundant indexes that can take up unnecessary space and affect performance.
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.
If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table. This means that having another index on the table that matches the prefix of a larger index is redundant, taking up unnecessary space and slowing performance.InnoDB Buffer Pool Concurrency Optimization
- Checks if InnoDB buffer pool size is large enough to allow more buffer pool instances.
For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option, and you might also adjust the innodb_buffer_pool_size value.
When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool. Prior to MySQL 8.0, each buffer pool was protected by its own buffer pool mutex. In MySQL 8.0 and later, the buffer pool mutex was replaced by several list and hash protecting mutexes, to reduce contention.InnoDB IO Thread Sizing
- Checks for high number of pending I/O requests.
InnoDB uses background threads to service various types of I/O requests. A high number of pending I/O reads or writes on the system can indicate that the database would benefit from a larger number of threads being allocated for use.InnoDB Cluster Fault Tolerance
- Checks that InnoDB Cluster has some degree of fault tolerance. MySQL Group Replication builds on an implementation of the Paxos distributed algorithm to provide distributed coordination between servers. As such, it requires a majority of servers to be active to reach quorum and thus make a decision. This has direct impact on the number of failures the system can tolerate without compromising itself and its overall functionality. The number of servers (n) needed to tolerate f failures is then n = 2 * f + 1.
In practice this means that to tolerate one failure the group must have three servers in it. As such if one server fails, there are still two servers to form a majority (two out of three) and allow the system to continue to make decisions automatically and progress. However, if a second server fails involuntarily, then the group (with one server left) blocks, because there is no majority to reach a decision.