0

I want to know during the execution of sysbench --time=100 ..., the bottleneck (function) of MySQL. So I want to profile MySQL when executing sysbench --time=100 ....

I know by set profiling = 1;, I can get such profile for a specific query, but not for a period of time (100 seconds). What can I do?

Thanks.

3
  • Profiling is virtually useless. Use the slowlog: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog And you can do that on a live system without bothering with sysbench. – Rick James Apr 7 at 15:08
  • @RickJames OK - so what is profiling good for? Timings? AFAIK, it's being deprecated - what's going to be in its place? – Vérace Apr 7 at 16:21
  • After exhausting indexing and tuning options there is using perf to profile the internal workings of MySQL. – danblack Apr 7 at 22:16
2

In my experience, PROFILE always said that 99% of the time was spent in some "useless" category, such as "sending data". Such categories 'never' translated into anything actionable. PROFILE is being deprecated -- good riddance.

EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON ... have some use. So does the "optimizer trace".

Use the slowlog: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog And you can do that on a live system.

Another technique for looking into what is going on is with the "Handler" counts. This works even when a table is [currently] so small that timings are not very useful. Details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

1

MySQL query profiling is being deprecated in favor of a performance_schema solution.

The query profiler gives you latencies for each "stage" of query execution for a given query in your current session.

The performance schema gives you latencies for each stage of query execution across many sessions, not just your current session. You can basically get profiled queries for all queries in a span of time, like you asked.

Unfortunately, navigating the performance schema is complex. It's a very powerful feature, but not much work has been done to make it user-friendly.

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.