CODEWITHSUNDEEP
mysqlperformancemysql-workbench
Mehran
Mehran

Reputation: 16911

Preventing SQL statements from getting truncated by MySQL's Workbench in `Performance Reports` section

Recently I was introduced to the new feature of MySQL: performance_schema, and it's awesome. Specially when it's joined with MySQL Workbench's Performance Reports. I find the High Cost SQL Statements section pretty useful and practical. There's only one downside to it, the SQL column which holds the executed SQL statement is truncated in long cases.

I believe it's truncated by Workbench and not the performance_schema but I've got no solid evidence to prove it. Does anyone know how to have the complete version of SQL?

Upvotes: 9

Views: 5338

Answers (1)

Mehran
Mehran

Reputation: 16911

I managed to figure it out myself.

The information presented in MySQL Workbench's Dashboard are extracted using a series of views defined within sys database. The mentioned views are created based on the tables found in performance_schema database. The field responsible to hold SQL statements resides in sys.x$statement_analysis view which is taking it from performance_schema.events_statements_summary_by_digest table in turn. Even though this field is defined as LONGTEXT and can hold as many characters as 4G, but the SQL statements are truncated when they are inserted.

The maximum length of characters that will be inserted into DIGEST_TEXT is controlled by max_digest_length config. Its default value is set to 1024 and it can be increased as big as 1048576. But keep in mind that this config can be set on MySQL version greater than 5.6.24 only!!

Upvotes: 15

Related Questions