Get query stats

This page describes how to getquery stats using thecbt CLI. You canalso get query stats using the Go client library for Cloud Bigtable.

The content provided here is intended for application developers. Before youread this page, you should understand theBigtable storage model. Youshould also be familiar withSchema design best practices andunderstand performance.

The ability to get information about a Bigtable query can beuseful when you are developing an application. It lets you get data on a query'sperformance and then adjust the query or change your schema design to fine-tunethe performance of your query.

Although in general aquery is any data request sent to a table,Bigtable returns query statistics only for read requests.

Before you begin

1. If you haven't already installed thecbt CLI, follow theinstructions on thecbt CLIoverview including the steps to create a.cbtrc file.
2. Review theRead rows section of thecbt CLI reference to familiarize yourself with thecommand-line syntax and options available for sending acbt read requestto your table. Note that thecbt CLI does not support filters.
3. Ensure that you havebigtable.tables.readRows permission for the tableor that you are a principal of thebigtable.reader role.

Run your query

1. Create a query using the syntax forRead rows.

2. Run the following command. Theinclude-stats=full parameter tellsBigtable to return the query stats.

   cbt readTABLE_IDQUERY include-stats=full

   Replace the following:

   • TABLE_ID: the unique ID for the table that you arequerying
   • QUERY: acbt CLI-formatted query

   The terminal displays the data that was read and a list containing thefollowing stats:

   • rows_seen_count: the number of rows that Bigtableprocessed for the request
   • rows_returned_count: the number of rows returned after applying filters
   • cells_seen_count: the number of cells processed for the request
   • cells_returned_count: the number of cells returned after applyingfilters
   • frontend_server_latency: request latency measured from theBigtable front-end server pool, expressed in milliseconds

Interpret the results

• Look at the ratio of cells seen to cells returned. If the quotient forcells_seen_count /cells_returned_count is 1 or close to 1, that's anindication that your query didn't cause Bigtable to process datathat was ultimately filtered out.

• Consider the number of rows returned. The higher that number is, the more workthat Bigtable is doing. The optimal number here is dependent onyour use case and latency requirements, but in general, a query that returnsfewer rows will have lower latency.

Example

If you want to get query stats when you read all rows that match the regularexpressionorange:*.* in a table calledmy-table, you can run thefollowing:

cbtreadmy-tableregex='.*orange'columns='.*:.*'include-stats=full

The output is similar to the following:

  ----------------------------------------  orange    Details:Color                            @ 2022/09/26-14:48:01.286000      "orange"    Details:Size                             @ 2022/09/26-14:48:01.286000      "medium"  Request Stats  ====================  rows_seen_count: 10  rows_returned_count : 1  cells_seen_count: 2  cells_returned_count: 2  frontend_server_latency: 13ms

What's next

• Identify a cluster's hot tablets.
• Review schema design best practices.