1

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.

Improve this question
  • The answer depends on your specific server configuration and workload. I'm not sure why you value opinions of random people on the internet higher than that of your own DBA. – mustaccio Jun 4 '18 at 19:56
  • Can you show how the load will vary based on configuration variables? Is it bounded by a function? E.g. #iterations = ( #tables * (#tables -1)) / 2. What's the cost of querying the information_schema? Does it have to compute data_length and index_length on the fly or is it already in RAM because MySQL is tracking it already? So, essentially, yes, I'm hoping that a random person on the Internet will be able to describe the actual workings of the query, instead of giving me a vague answer like "it depends..." or "it's intensive", whoever that person may be - be it our DBA or a random person. – Liczyrzepa Jun 4 '18 at 22:06
  • I would expect that an experienced sysadmin spent less time measuring the actual query impact on their server than typing all these words here and waiting for a speculative answer. – mustaccio Jun 5 '18 at 0:54
  • Answers can be verified, the same way proofs in math can. A simple test won't show whether the growth is linear or exponential. My question to you is, why would do have a problem with someone asking a question on a site that is dedicated to answering people's questions? Is it offensive that I would like to know what happens with MySQL internally? If you question the value of the answers people give on stackexchange, why do you even use it? You don't have to answer any of my questions, at all, but it's ridiculous to marginalize the value of understanding the software's internal workings. – Liczyrzepa Jun 5 '18 at 14:37
  • 1
    Spare us the agony of decoding that echo; show us what the resulting SELECT looks like. – Rick James Jun 6 '18 at 1:46
1

This 'random person on the Internet' can shed some light...

  • The information_schema is not a 'real' database (until MySQL 8.0), rather it is derived from a bunch of data that may or may not be in RAM already.
  • If not all the data is in memory, then the query will have to get it from disk -- by reading lots of .frm files (etc). "Thousands" in your case. So, this could be quite costly.
  • The Data_length, etc are relatively stable; I hope your monitoring is not doing this query every minute; that may be a noticeable burden. Once an hour should be sufficient.
  • What is the value of table_open_cache? Is it more than the number of tables? If not, then this random person on the Internet thinks that you could be in deep đź’©.
  • How lame! Rather than generating a different query when you ask for "all", the package builds a complex boolean expression.

I have been involved in the monitoring of hundreds of machines. A simple df is sufficient to alert a human to dig into what is causing the problem. Checking the MySQL directory with a du is one of several secondary steps.

But, I disagree with every monitor I have seen. I don't care how big the data is. It does not matter if the disk is 90% full -- as long as it is not growing. I do care if it is 20% full but growing so fast that it will fill up before the end of the week.

Sure graphing the size is handy, but that means that a human has to look at it (and not fall asleep). I want an alert. Furthermore, due to cyclical things, such as binlog purging, the graph may look like saw teeth -- up, down, up down! This makes "trend analysis" difficult.

Back to your question of whether that query is "intensive". If Zabbix is worth having it will give you the answer on a silver (or electronic) platter! Does it monitor the "slowlog"? If so, does this query show up? QED.

Improve this answer
  • So the template for MySQL isn't part of stock zabbix; the query is part of an add on template. By default that item isn't queried, only simple global status variables - like bytes sent, received, etc. I think the Slow_queries counter is tracked by default too, although, I don't think it tracks the slowlog itself. Any item that is queried in ZBX can be set to alert based on a formula, which could potentially check for growth rate of the tables. I checked on the # of .frm files - it's over 20k. They should all be buffered in RAM by the OS, once read the first time, given the system's size. – Liczyrzepa Jun 6 '18 at 20:58
  • Also, thanks for putting some thought into your answer. I have a better idea of what's going on - searching for and adding data from the 20k+ sources could get expensive, if the relevant pages in the OS memory are marked dirty. Question - did you mean "Table_open_cache_hits"? I didn't see table_open_cache in global status. (and can you share why table_open_cache is important in this case?) (and also, yes, it would have been trivial to have a separate one liner in the zabbix configuration for the "all" case). Thank you! – Liczyrzepa Jun 6 '18 at 21:06
  • 1
    Lower case things (eg table_open_cache) are in SHOW VARIABLES and can (mostly) be changed. Initial caps things (eg, Table_open_cache_hits) are in SHOW GLOBAL STATUS and are tallied, measured, etc. If, for example, Table_open_cache_misses divided by Uptime is "high", then table_open_cache probably needs increasing. Can you set it to, say, 21000? If you would like more detailed critique of VARIABLES and STATUS, see mysql.rjweb.org/doc.php/mysql_analysis#tuning – Rick James Jun 6 '18 at 21:29
  • 1
    Certain operations on a table, possibly including the operations that are being discussed here, require "opening" the table. Opening a table requires finding info in the "table open cache". If it is not currently there, then (as with any "cache"), an entry needs to be bumped out and the desired entry built. So... If all 20K tables need to be "opened", but the cache has less than 20K entries, you will get thrashing, which probably involves lots of I/O. – Rick James Jun 6 '18 at 21:32
  • From Linux Command Prompt, please post results of A) ulimit -a so we can see your OS operating limits and B) SHOW GLOBAL VARIABLES LIKE '%open%; and C) SHOW GLOBAL STATUS LIKE '%open%'; for analysis. – Wilson Hauck Dec 25 '19 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.