Getting full details on slow SQL queries

By Christophe Dujarric, on Sep 24, 2019

Blackfire enables to measure one of the most common bottlenecks in code: SQL queries. Showing developers how many calls to SQL queries and how much time is spent within it is very often an eye-opening experience.

Privacy and confidentiality

At the same time, Blackfire cares for privacy and confidentiality (we are GDPR compliant). By design, Blackfire never collects any end user information. One of the things we implemented in order to achieve that is to anonymize all SQL queries. During profiling, those values are cleared, and are never sent to our servers (check how the Blackfire stack works).

However, in some situations, getting the actual parameters of a SQL query can be very useful. For instance, that enables to use the EXPLAIN keyword, which can provide valuable hints in order to optimize a query.

Disabling anonymization via the Debug Add-on

Under the condition that no sensitive information is sent to Blackfire, it is now possible to disable the anonymization for a specific profiling request.

To do so, check the box in the browser extension, or use the --debugoption if creating profiles with blackfire curl or blackfire run.

SQL parameters will then be shown in the list of SQL queries of the related profile, and on the timeline.

Notes:

  • There is known limitation for prepared SQL statements. Arguments will never be collected. Only columns names will be.
  • This also disables pruning. In order to avoid collecting heavy profiles that would take to long to load, using this feature automatically disables aggregation. The profile will be executed only once instead of getting the average of 10 samples.
  • this add-on is an option for Profiler, Premium and Enterprise subscriptions.

Happy SQL performance optimization,

Christophe Dujarric

Christophe is the Chief Product Officer at Blackfire. He's an engineer, but probably one of the least "tech" people in the company. He's wearing many hats, from product management to marketing and sales. He loves the beauty of simple solutions that solve actual problems.