Exploring ZendHQ Database Query Introspection

The Zend team is excited to announce the latest feature addition to ZendHQ, database query introspection. Database query introspection allows users to inspect database queries and trace the code that led up to them, making it easy to find and fix performance or functionality issues at the database query level.

In this blog, we’ll give an overview of the importance of performant database queries in PHP apps, then walk through how ZendHQ database query introspection works on an applied example. At the end of the blog, we’ll provide a full demo video, as well as info on how to book a personalized demo for your team.

Watch the Full Database Query Introspection Demo

 

Schedule a Demo

What Role Do Database Queries Play in PHP App Performance?

Depending on the application, database queries can play a significant role in the overall performance of that application. For web applications that are heavily dependent on the storage and retrieval of data, performance of those queries can have a direct, positive or negative impact on the end user.

As an example, think of a user of a web app requesting a password reset. The application receives the request from the user, vets the information, generates a token that is stored in a temporary token storage database, then sends a reset link to the user once the process is complete. If a database query in that chain takes an inordinate amount of time, it holds up the reset link send, and ultimately delays the user from resetting their password and accessing the account.

When you apply this idea at scale, and across any of the numerous areas where you web app is interacting with a database, database queries that don’t perform their best can have a ripple effect on everything from user experience, to support queues, to hosting costs, to customer/user retention.

Introducing ZendHQ Database Query Introspection

Database Query introspection is a feature of ZendHQ's Z-Ray support. When Z-Ray is active, any request that performs a database query will feature an additional "Queries" tab for that request. That tab lists all queries performed during the request, along with timings, and allows you to click on any given query to find out what database connection was used, transaction information if it was part of a transaction, and a PHP backtrace that led to the query execution.

Currently, only PDO connections are supported, with support arriving in the coming weeks for the mysqli/mysqlnd, postgresql, DB2, SQL Server, SQLite, and other extensions. Since PDO allows connecting to most database extensions in PHP, chances are that you will be able to use it immediately.

ZendHQ Database Query Walkthrough

Let's take a tour of ZendHQ's DB query introspection. Within ZendHQ, you will need to go to the “Z-Ray Live” section, start tracking (if you are not already), and then copy your Z-Ray session token and provide it to the request. (See the ZendHQ Z-Ray documentation for more details on the session token and how to provide it in a request.) In the Z-Ray Live screen, you should immediately see the request pop up. Assuming that the page does not have any database connectivity, you'll observe only the standard Z-Ray items: request information, request, response, variables, and functions.

But now let's go into a page that makes a database request. The demo used for this post includes a page that pulls data from a database and lists it in a table.

When requesting the new page, the corresponding request in Z-Ray will contain a new tab called “Queries”. In that tab, you can see all the queries that were made during that particular request. In this case, the page had two, one that was performing a count operation, and another that was pulling database rows for the data table.

Clicking on any query opens a drawer that shows the full query. From there, you can view a backtrace of all the lines that happened within the PHP application before the query actually took place.

The demo also includes a button for performing a set of operations across the entire table. Selecting this produces a new request, with a corresponding entry in Z-Ray.

The Queries tab shows far more information for this request. In particular, it displays a transaction consisting of a select operation and dozens of update queries, as well as two additional select operations outside the transaction.

The Queries tab allows freeform filtering. To view only select operations, you could type “SELECT“ in the search box; similarly, you could search only for “UPDATE“ operations. Since the filtering looks at the full query line, you could also search for column names or specific values. Additionally, there is a drop down to allow filtering by result status.

When opening the drawer for a query that is part of a transaction, the drawer will include a “Transaction“ tab, allowing you to view the entire transaction; this can be used to provide better context to how and when a query executed.

The ”Queries” tab also includes columns for connection and transaction identifiers. When you have multiple connections, you will have an additional filter to allow viewing queries only from a given database connection. When using the search box, you can use the transaction identifier (e.g. ”tr#1”) to filter only queries belonging to the given transaction.

In the demo example, if we filter on “tr#1”, we will see only the queries that were part of that transaction.

If you clear the search, the list will contain the additional queries in the list.

Final Thoughts

Database query introspection gives your DevOps teams a powerful tool for understanding what queries are occurring in production applications, how many are being performed, and the timings of each of them. This information can help teams understand application bottlenecks, and allow them to determine if they need to invest in better caching strategies, better indexes, or even better queries, in order to improve application performance. It’s a powerful tool for profiling and improving your production applications.

Try ZendHQ for Free

Ready to see how ZendHQ works on your stack? Try it for free today with a ZendPHP trial — no commitment necessary. To get started, request a quote for custom pricing information.

See Trial Details Request a Quote

Additional Resources

• Product Page - ZendHQ
• Product Page - ZendPHP
• Product Demo - ZendHQ Demo
• Blog - PHP Debugging: Go Beyond Step Debuggers With ZendHQ
• Blog - Exploring ZendHQ Role-Based Authentication Features
• Blog - How to Improve PHP Performance
• Blog - Why Good PHP Monitoring Matters
• Blog - Introducing JobQueue for ZendPHP
• Blog - Finding and Fixing PHP Errors in Production
• Release Notes - ZendHQ 1.4.0