{question}
How to identify the number of successful query executions in a given period?
{question}
{answer}
Whenever queries are executed, they are either successfully executed or failed. The number of times the query is executed varies depending on the workload. This article describes the steps that can be used to find the exact number of successful executions of the query between two timestamps.
The management view information_schema.MV_ACTIVITIES_EXTENDED_CUMULATIVE provides a detailed and complete overview of the currently running and completed tasks in the cluster. The column SUCCESS_COUNT in the view essentially tells us the number of completed instances of the particular activity. This will contain a large number of success counts as this number increases every time the query execution is successful on the particular node. The following query gives an output that contains the query executed, the node id, and the success counts of this query:
SELECT Current_timestamp(),NODE_ID,ACTIVITY_NAME, AGGREGATOR_ACTIVITY_NAME,LAST_FINISHED_TIMESTAMP, SUCCESS_COUNT FROM information_schema.MV_ACTIVITIES_EXTENDED_CUMULATIVE where activity_name = '<activity_name>' ORDER BY NODE_ID;
The below example can be used as a reference to find this information.
- The same insert query has been executed within a 10 seconds gap on a table(t). Below is an output from the information_schema.MV_ACTIVITIES_EXTENDED_CUMULATIVE table after the first insert:
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
| Current_timestamp() | NODE_ID | ACTIVITY_NAME | AGGREGATOR_ACTIVITY_NAME | LAST_FINISHED_TIMESTAMP | SUCCESS_COUNT |
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
| 2022-11-05 00:32:19 | 1 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:01 | 148275865 |
| 2022-11-05 00:32:19 | 22 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:01 | 90057914 |
| 2022-11-05 00:32:19 | 23 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:02 | 491595447 |
| 2022-11-05 00:32:19 | 24 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:02 | 387200591 |
| 2022-11-05 00:32:19 | 70 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:01 | 319295470
| 2022-11-05 00:32:19 | 71 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:01 | 345781413 |
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
- Output after the second insert:
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
| Current_timestamp() | NODE_ID | ACTIVITY_NAME | AGGREGATOR_ACTIVITY_NAME | LAST_FINISHED_TIMESTAMP | SUCCESS_COUNT |
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
| 2022-11-05 00:32:29 | 1 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 148276316 |
| 2022-11-05 00:32:29 | 22 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 90058792 |
| 2022-11-05 00:32:29 | 23 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 491596345 |
| 2022-11-05 00:32:29 | 24 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 387202521 |
| 2022-11-05 00:32:29 | 70 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 319296294 |
| 2022-11-05 00:32:29 | 71 | InsertSelect_t__et_al_8963b25b104f21ca | InsertSelect_t__et_al_8963b25b104f21ca | 2022-11-05 00:32:24 | 345781903 |
+---------------------+---------+------------------------------------------------+------------------------------------------------+-------------------------+---------------+
- The total number of successful query executions can be determined by taking a sum of the SUCCESS_COUNT from both outputs and then finding the difference between them. In the current scenario, this would be as follows:
Total success count after first insert = (148276316 + 90058792 + 491596345 + 387202521 + 319296294 + 345781903 ) = 1782212171
Total success count after second insert = (148275865 + 90057914 + 491595447 + 387200591 + 319295470 + 345781413 ) = 1782206700
The difference between the success counts = 5471
Hence, the query was successfully executed 5471 times.
References:
information_schema.MV_ACTIVITIES_EXTENDED_CUMULATIVE
{answer}