1. Why row counters?

There is a class of user queries that may require special attention:

SELECT COUNT(*) FROM <database>.<table>

It has been discovered that this is one of the very first queries launched by users as they begin using Qserv (directly, or indirectly via the TAP service). Normally, Qserv would broadcast this query to all workers asking them to count rows at each chunk table. The partial results would be aggregated by Qserv's czar service into a single number. After that, the final count would be returned to the user. This is basically the very same mechanism found behind the normally shared scan (or just scan) queries. The problem is that, depending on a scale of a catalog (and the number of chunks in the table of interest), this operation may take many seconds, or even many minutes before a user would see the result. This could be a point of frustration for many Qserv users because this query looks (and it is in reality) a very trivial non-scan query.

To address this situation, Qserv has a built-in optimization that is targeting exactly this class of queries. Here is how it works. For each table, Qserv czar would have a counter of rows. This counter would need to be populated by the Qserv Replication/Ingest system. This operation can't be done by Qserv itself (like on the very first pass over the data) because Qserv doesn't have complete knowledge of what it should have. Qserv has been designed to deliver to users what it can see at a given moment of time. Therefore, it can't be an authoritative tool for such an operation. Besides, the counter is not just a simple number. In reality, the special row counters scanner would count a separate counter for each chunk of a table and store this information at czar. The per-chunk counters are planned to be used for implementing another optimization for the unconditional queries that have non-aggregate selectors, such as:

SELECT * FROM <database>.<table> LIMIT <N>
SELECT `col`,`col2` FROM <database>.<table> LIMIT <N>

For these "indiscriminate" data "probes" Qserv would dispatch chunk queries to a subset of random chunks that have enough rows to satisfy the requirements specified in LIMIT <N>.

Note that this optimization is presently an option. And there are a few reasons for that:

• building the index requires scanning all chunk tables, which would take time. Doing so at the time when the data are being ingested into Qserv would prolong the ingest time and increase the chances of instabilities for the workflows (in general, the longer some operation is going - the higher the probability it may fail).
• the counters are not needed for the purposes of the data ingest per se. These are just optimizations for the queries.
• sometimes building the counters before the ingested data have been Q&A-ed may not be a good idea.

The rest of this section along with the formal description of the corresponding REST services explains how to build the row counters needed for the optimization.

2. Building and deploying the counters

To build and deploy the counters one would need to use the following REST service:

• Collecting row counters and deploying them at Qserv

The service needs to be invoked for every table of the ingested catalog. This is the typical example of using this service that would work regardless if the very same operation was already done before:

curl http://localhost:25080/ingest/table-stats \
  -X POST -H "Content-Type: application/json" \
  -d '{"database":"test101","table":"Object","overlap_selector":"CHUNK_AND_OVERLAP","force_rescan":1,"row_counters_state_update_policy":"ENABLED","row_counters_deploy_at_qserv":1}'

This would work for tables of any type (director, child, RefMatch, or fully replicated). If the counters already existed in the Replication system's persistent state or Qserv, they would still be rescanned and redeployed in there.

It may be a good idea to compare the performance of Qserv for executing the above-mentioned queries before and after running this operation. Normally, if the table statistics are available at Qserv, it should take a small fraction of a second (about 10 milliseconds) to see the result on the lightly loaded Qserv.

3. Clearing the counters

Sometimes, if there is doubt that the row counters were incorrectly scanned, or when Q&A-in the ingested catalog, a data administrator may want to remove the counters and let Qserv do the full scan of the table instead. This can be done by using the following REST service:

• Deleting row counters for a table

Likewise, the previously explained service, this one should also be invoked for each table needing attention. Here is an example:

curl http://localhost:25080/ingest/table-stats/test101/Object \
  -X DELETE -H "Content-Type: application/json" \
  -d '{"overlap_selector":"CHUNK_AND_OVERLAP","qserv_only":1}'

Note that with a combination of the parameters shown above, the statistics will be removed from Qserv only. So, one would not need to rescan the tables again should the statistics need to be rebuilt. The counters could be simply redeployed later at Qserv. An alternative technique explained in the next section could be used to tell Qserv not to use the counters to optimize queries. Should the counters have to be removed from the Replication system's persistent state the last option in the request has to be set to qserv_only=0.

4. Disabling the optimization at run-time

To complement the previously explained methods for scanning, deploying, or deleting row counters for query optimization, Qserv also supports the run-time switch. The switch is turned on or off by the following statement to be submitted via the mysql-proxy  front-end of Qserv (that would be typically listening on port number 4040):

SET GLOBAL QSERV_ROW_COUNTER_OPTIMIZATION = 1
SET GLOBAL QSERV_ROW_COUNTER_OPTIMIZATION = 0

The default behavior of Qserv when the variable is not set is to enable the optimization for tables where the counters are available.

5. Retrieving the row counters

It's also possible to retrieve the counters from the Replication system's state using the following REST service:

• Retrieving rows counters of a table

The information obtained in this way could be used for various purposes, such as investigating suspected issues with the counters, monitoring data placement in the chunks, or making visual representations of the chunk density maps. See the description of the REST service for further details on this subject.