0

To minimize down time and to detect database slow down early, we query the processlist table every second, and if we find the queries slowing down, we throttle the lower priority queries and execute other measures.

SELECT avg(time) FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE db = 'mydb'
    AND user = 'app_user'
    AND state != ''
    AND info NOT LIKE '%ALTER TABLE%'
    AND info NOT LIKE '%CREATE INDEX%'
    AND info NOT LIKE '%CREATE UNIQUE INDEX%'
    AND info NOT LIKE '%performance_summary%'
    AND info NOT LIKE '%certain_table%'
    AND info NOT LIKE '%certain_other_table%';

However, we realise that querying the processlist table is in itself pretty expensive and are looking for an alternative. We already tried performance_schema.threads, but found that the numbers reported by it were not that accurate and it showed low numbers, even when we were having obvious database issues.

SELECT AVG(PROCESSLIST_TIME) FROM performance_schema.threads

Another alternative is to just have our application track average times itself, but was wondering whether there may be another database specific solution possible.

EDIT: To clarify, the above query is run real time with no human intervention. If the application detects degrading performance, it immediately throttles down other queries to try to get the overall performance back up. Everything is automated.

Granted process list contains many idle queries, but this system has worked for us for many months now, and we are just trying to implement a "no temporary table on disk" solution.

0

2 Answers 2

0

Uncertainty Principle. The monitoring you describe will, quite likely, make things worse. I recommend against doing that.

The PROCESSLIST usually shows

  • lots of Sleeping tasks; those can be ignored
  • a few 'system' tasks; ignore
  • maybe one task with several seconds in Time; think about looking into it
  • lots of 'similar' (same query with different constants); look into that type
  • 'nothing' -- This is where some lock is hanging things to the point where the Processlist cannot be displayed -- there's not much you can do here

Note, however, there is no benefit in fetching the processlist every second. Every 10 seconds might do. (And be less invasive.) Think about how long it takes you to analyze the output and react to it. It's probably more like 60 seconds. So why do the SHOW more often than that?

My favorite tool is the slowlog. It will pinpoint which query "types" are the biggest burden on the system -- whether the query is an infrequent, but long-running, query, or one fast query that runs often. This is a big benefit of the slowlog.

It is not very invasive; I leave it turned on all the time, even on production systems. (However, I am cautious about using long_query_time = 0 since that would record all queries. A value of 1 or maybe 0.5 is a reasonable compromise.)

Slowlog entries are not added to the file (or table) until after the query finished. This is a drawback relative to SHOW PROCESSLIST.

Use pt-query-digest or mysqldumpslow -s t to dump the slowlog in order of "burden on system". Often the top 1 item accounts for half of the system load. Sometimes the load comes from a daily dump.

Most tools (Processlist's "Time"; slowlog's "Query time") measure "elapsed" time, not "CPU" or "I/O". While this may seem like a defect, it is not really bad.

  • The user sees "latency", which is "elapsed".
  • The CPU is rarely overloaded; when it is, a better (possibly composite) index is often a cure.
  • Long CPU or I/O lead to long elapsed. So "elapsed" is a good catchall.

Another possible tip -- MariaDB has

4
  • Thanks for your detailed response, but everything is automated and done real time. I edited my original question with more details. The solution needs to be real time and react immediately. Oct 6, 2022 at 19:30
  • @ShahidThaika - "react immediately"... I'm puzzled by what you will do with the result of that SELECT. It is transient info and contains very little actionable info. Is someone sitting at the monitor watching it like they might watch "top" or "task manager"?
    – Rick James
    Oct 7, 2022 at 4:05
  • Yes exactly, but it is automated within our application. For example, we have certain queries that compute stats every minute. If we detect that the overall db performance is degrading, we delay running these computational queries. Our server has 488GB RAM, DB size is in the TBs and we get 1000s of connections every second. So when things go bad, they get worse immediately. Oct 7, 2022 at 5:17
  • @ShahidThaika - If you like, I'll review the settings: mysql.rjweb.org/doc.php/mysql_analysis#tuning
    – Rick James
    Oct 7, 2022 at 5:53
0

There are products that do exactly that. JetProfiler (even just the free version should be helpful), Percona Toolkit and there are probably other tools as well.

Full disclosure: I work at the company now maintaining JetProfiler. But previous to that I used JetProfiler for years as a tool to weed out badly written db code when working as a consultant, so I personally really think it's a good tool.

(And having worked for lots of years previous to that just mostly doing SHOW PROCESSLIST and DESCRIBE - until I finally started using actually using real analysis tools, it was really a world of difference).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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