I am in the process of implementing Zabbix monitoring on multiple servers at an organization. I have recently run into some resistance from an Oracle / MySQL DBA. He claims that the following query is resource intensive because his database server contains thousands of tables. It's a one liner, but I've split it across multiple lines to help with readability:
echo "select sum($(case "$3" in both|"") echo
"data_length+index_length";; data|index)
echo "$3_length";; free) echo "data_free";; esac)) from
information_schema.tables$([[ "$1" = "all" || ! "$1" ]] ||
echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] ||
echo "and table_name=\"$2\"");" | HOME=/var/lib/zabbix mysql -N
which looks like it reduces to:
select sum(data_length+index_length) from information_schema.tables;
My specific question is, how resource intensive is this query and should the organization be worried about the performance impact it will have on the DB server if it runs once per minute?
As an experienced UNIX/Linux sysadmin and from reading a little bit from the MySQL manual, it seems like it should be simple addition of values retrieved from the already in memory "information_schema", but I'm hoping for an answer that can definitively describe the internal actions MySQL has to take and the associated impact.
SELECT
looks like. – Rick James Jun 6 '18 at 1:46