prometheus
Collect innodb table and index stats
Hi,
We found this information useful in troubleshooting some database performance issues at my job. I hope this information can be useful for others who run multi-TB sized databases with tons of indices.
Information regarding these metrics can be found at
- https://mariadb.com/kb/en/mysqlinnodb_index_stats/
- https://mariadb.com/kb/en/mysqlinnodb_table_stats/
The metric descriptions where mostly copy pasted from https://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html
Using a test mariadb container, the new metrics will appear as
mysql_mysql_innodb_index_stats{database_name="mysql",index_name="PRIMARY",stat_name="n_diff_pfx01",table_name="gtid_slave_pos"} 0
mysql_mysql_innodb_index_stats{database_name="mysql",index_name="PRIMARY",stat_name="n_diff_pfx02",table_name="gtid_slave_pos"} 0
mysql_mysql_innodb_index_stats{database_name="mysql",index_name="PRIMARY",stat_name="n_leaf_pages",table_name="gtid_slave_pos"} 1
mysql_mysql_innodb_index_stats{database_name="mysql",index_name="PRIMARY",stat_name="size",table_name="gtid_slave_pos"} 1
mysql_mysql_innodb_table_stats_clustered_index_size{database_name="mysql",table_name="gtid_slave_pos"} 1
mysql_mysql_innodb_table_stats_n_rows{database_name="mysql",table_name="gtid_slave_pos"} 0
mysql_mysql_innodb_table_stats_sum_of_other_index_sizes{database_name="mysql",table_name="gtid_slave_pos"} 0
Nice work! 👏
In a future iteration, what do you think about adding CLI flags to be able to specify which schema and table to watch? In a big deployment exporting metrics for all database/tables can be expensive.
I think that would be wise to add to this PR. When I first started on this work I knew 0 golang. I now know 1 golang and can probably suss it out.
@pgporada I'm curious how you're updating these stats, are you calling ANALYZE TABLE on a regular cadence or do you just have enough row churn that they get regular updates?
Per https://mariadb.com/kb/en/innodb-system-variables/#innodb_stats_auto_recalc you need ~10% of the rows to get touched for a stats refresh.
@mdkent The Boulder CA software has very high row churn when updating certificate status information for 200+ million active TLS certificates.
I've not had a chance to pick this back up since I stated that I would try 16 days ago.
The problem with these generic stats dumps is that they're not useful without some extended documentation/help.
@SuperQ Where should I do that, in the README.md or in the specific .go files?
The mysql-server:5.7 broken test seems unrelated to this change fwiw.