DB monitoring to discover missing indexes

[atodorov]

Figure out how to monitor queries to the DB and log them in some nifty web service so that we can discover slow queries and figure out if they are missing indexes. I am sure there are such queries but we need data to be able to discover them.

[jhutar]

If on PostgreSQL, on DB lvl you can use log_min_duration_statement.

[Prome88]

For MariaDB in-built slow_query_log can be used

The slow query log is a record of SQL queries that took a long time to perform.

See https://mariadb.com/kb/en/slow-query-log-overview/

and furthermore log_queries_not_using_indexes

Description: Queries that don't use an index, or that perform a full index scan where the index doesn't limit the number of rows, will be logged to the slow query log (regardless of time taken). The slow query log needs to be enabled for this to have an effect.

See https://mariadb.com/kb/en/server-system-variables/#log_queries_not_using_indexes