Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL] Statement level metrics for YCQL as PG view for query level visibility, troubleshooting #20616

Open
1 task done
hbhanawat opened this issue Jan 15, 2024 · 0 comments
Open
1 task done

[YSQL] Statement level metrics for YCQL as PG view for query level visibility, troubleshooting #20616

hbhanawat opened this issue Jan 15, 2024 · 0 comments
Assignees
@hbhanawat
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue

Comments

@hbhanawat
Copy link
Contributor

hbhanawat commented Jan 15, 2024 •
edited by jira bot

Jira Link: DB-9617

Description

Make YCQL /statements metrics available as a PG view. This view can be used is isolation or joined with other views (eg: ASH views once available) for further analysis.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

  • I confirm this issue does not contain any sensitive information.
@hbhanawat hbhanawat added the area/ysql Yugabyte SQL (YSQL) label Jan 15, 2024
@hbhanawat hbhanawat self-assigned this Jan 15, 2024
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue labels Jan 15, 2024
hbhanawat pushed a commit that referenced this issue Mar 27, 2024
[#20616] YSQL: PG view for YCQL metrics
ce7d880 
Summary:
**ycql_stat_statements**
Added a new YSQL view for YCQL statement metrics so that it can be joined with YCQL wait events in yb_active_universe_history table. The columns of this table are similar to what pg_stat_statements columns are.

| Column | Type | Description |
| queryid | int8 | Hash code to identify identical normalized queries. |
| query | text | Text of a representative statement |
| is_prepared | bool | Prepared statement or unprepared query |
| calls | int8 | Number of times the statement was executed |
| total_time | float8 | Total time spent executing the statement, in milliseconds |
| min_time | float8 | Minimum time spent executing the statement, in milliseconds |
| max_time | float8 | Maximum time spent executing the statement, in milliseconds |
| mean_time | float8 | Mean time spent executing the statement, in milliseconds |
| stddev_time | float8 | Population standard deviation of time spent executing the statement, in milliseconds |

This view is added in an extension - yb_ycql_utils, which is not enabled by default. The extension has to be enabled before the view can be used. If ycql is disabled, this view will not return any data.

```
yugabyte=# create extension yb_ycql_utils;
CREATE EXTENSION
yugabyte=# select * from ycql_stat_statements;
       queryid        |                     query                      | is_prepared | calls | total_time | min_time | max_time  | mean_time |    stddev_time
----------------------+------------------------------------------------+-------------+-------+------------+----------+-----------+-----------+--------------------
  5685694520060019787 | SELECT * FROM system.peers_v2                  | f           |     2 |   23.15232 | 6.606628 | 16.545692 |  11.57616 |           4.969532
 -6069774349418914791 | SELECT * FROM system.local WHERE key='local'   | f           |     2 |  11.241228 | 0.516517 | 10.724711 |  5.620614 |           5.104097
 -4656374775045675304 | SELECT * FROM system_schema.tables             | f           |     2 |   5.773887 | 1.937739 |  3.836148 | 2.8869435 |          0.9492045
   477300852678741015 | SELECT * FROM system.peers                     | f           |     2 |   1.867352 | 0.709899 |  1.157453 |  0.933676 |           0.223777
  6930116125454979846 | SELECT * FROM system_schema.views              | f           |     2 |   3.671581 | 1.320594 |  2.350987 | 1.8357905 |          0.5151965
 -1896671018756022147 | SELECT * FROM system_schema.functions          | f           |     2 |   3.156819 | 1.502305 |  1.654514 | 1.5784095 |          0.0761045
  1413414562899452953 | SELECT * FROM system_schema.indexes            | f           |     2 |    4.56578 | 1.694535 |  2.871245 |   2.28289 |           0.588355
 -3220527242581763013 | SELECT * FROM system_schema.keyspaces          | f           |     2 |   4.304142 | 1.504853 |  2.799289 |  2.152071 |           0.647218
 -2674195503457906853 | SELECT * FROM system_schema.aggregates         | f           |     2 |    2.64328 | 1.123457 |  1.519823 |   1.32164 |           0.198183
 -2256941656319582329 | SELECT * FROM system_schema.columns            | f           |     2 |  11.021614 | 3.881885 |  7.139729 |  5.510807 |           1.628922
  2381078306997755979 | SELECT * FROM system_schema.triggers           | f           |     2 |   2.722613 | 1.216926 |  1.505687 | 1.3613065 |          0.1443805
  6202644009413539627 | SELECT * FROM system_schema.types              | f           |     2 |   3.718101 | 1.490935 |  2.227166 | 1.8590505 |          0.3681155
 -7196980672916816745 | select * from system.local where key = 'local' | f           |     2 |   1.169078 | 0.524634 |  0.644444 |  0.584539 | 0.0599050000000001
(13 rows)

```
Jira: DB-9617

Test Plan: ./yb_build.sh  release --java-test 'org.yb.pgsql.TestPgViewYCQLStats'

Reviewers: asaha, amitanand, jason

Reviewed By: amitanand, jason

Subscribers: mihnea, yql, ybase, bogdan

Differential Revision: https://phorge.dev.yugabyte.com/D31705
hbhanawat pushed a commit that referenced this issue Mar 27, 2024
[#20616] YSQL: Fixed compilation error
0eab5c4 
Summary:
Removed duplicate declaration and extra function added because of merge issues. The issue was introduced with https://phorge.dev.yugabyte.com/rYBDBce7d880d22626813cb228f41e782b90b0c147e45
Jira: DB-9617

Test Plan:
Jenkins: compile only

Reviewers: stiwary

Reviewed By: stiwary

Subscribers: yql, ybase, bogdan

Differential Revision: https://phorge.dev.yugabyte.com/D33565
hbhanawat pushed a commit that referenced this issue Apr 10, 2024
[BACKPORT 2024.1][#20616] YSQL: PG view for YCQL metrics
3cdfc59 
Summary:
Original commits:
- ce7d880 / D31705
- 0eab5c4 / D33565

**ycql_stat_statements**
Added a new YSQL view for YCQL statement metrics so that it can be joined with YCQL wait events in yb_active_universe_history table. The columns of this table are similar to what pg_stat_statements columns are.

| Column | Type | Description |
| queryid | int8 | Hash code to identify identical normalized queries. |
| query | text | Text of a representative statement |
| is_prepared | bool | Prepared statement or unprepared query |
| calls | int8 | Number of times the statement was executed |
| total_time | float8 | Total time spent executing the statement, in milliseconds |
| min_time | float8 | Minimum time spent executing the statement, in milliseconds |
| max_time | float8 | Maximum time spent executing the statement, in milliseconds |
| mean_time | float8 | Mean time spent executing the statement, in milliseconds |
| stddev_time | float8 | Population standard deviation of time spent executing the statement, in milliseconds |

This view is added in an extension - yb_ycql_utils, which is not enabled by default. The extension has to be enabled before the view can be used. If ycql is disabled, this view will not return any data.

```
yugabyte=# create extension yb_ycql_utils;
CREATE EXTENSION
yugabyte=# select * from ycql_stat_statements;
       queryid        |                     query                      | is_prepared | calls | total_time | min_time | max_time  | mean_time |    stddev_time
----------------------+------------------------------------------------+-------------+-------+------------+----------+-----------+-----------+--------------------
  5685694520060019787 | SELECT * FROM system.peers_v2                  | f           |     2 |   23.15232 | 6.606628 | 16.545692 |  11.57616 |           4.969532
 -6069774349418914791 | SELECT * FROM system.local WHERE key='local'   | f           |     2 |  11.241228 | 0.516517 | 10.724711 |  5.620614 |           5.104097
 -4656374775045675304 | SELECT * FROM system_schema.tables             | f           |     2 |   5.773887 | 1.937739 |  3.836148 | 2.8869435 |          0.9492045
   477300852678741015 | SELECT * FROM system.peers                     | f           |     2 |   1.867352 | 0.709899 |  1.157453 |  0.933676 |           0.223777
  6930116125454979846 | SELECT * FROM system_schema.views              | f           |     2 |   3.671581 | 1.320594 |  2.350987 | 1.8357905 |          0.5151965
 -1896671018756022147 | SELECT * FROM system_schema.functions          | f           |     2 |   3.156819 | 1.502305 |  1.654514 | 1.5784095 |          0.0761045
  1413414562899452953 | SELECT * FROM system_schema.indexes            | f           |     2 |    4.56578 | 1.694535 |  2.871245 |   2.28289 |           0.588355
 -3220527242581763013 | SELECT * FROM system_schema.keyspaces          | f           |     2 |   4.304142 | 1.504853 |  2.799289 |  2.152071 |           0.647218
 -2674195503457906853 | SELECT * FROM system_schema.aggregates         | f           |     2 |    2.64328 | 1.123457 |  1.519823 |   1.32164 |           0.198183
 -2256941656319582329 | SELECT * FROM system_schema.columns            | f           |     2 |  11.021614 | 3.881885 |  7.139729 |  5.510807 |           1.628922
  2381078306997755979 | SELECT * FROM system_schema.triggers           | f           |     2 |   2.722613 | 1.216926 |  1.505687 | 1.3613065 |          0.1443805
  6202644009413539627 | SELECT * FROM system_schema.types              | f           |     2 |   3.718101 | 1.490935 |  2.227166 | 1.8590505 |          0.3681155
 -7196980672916816745 | select * from system.local where key = 'local' | f           |     2 |   1.169078 | 0.524634 |  0.644444 |  0.584539 | 0.0599050000000001
(13 rows)

```
**Upgrade/Rollback safety:**
The proto file changes are for the communication between local tserver and PG. There is no communication between PG and remote tserver and hence the diff is upgrade safe.
Jira: DB-9617

Test Plan: ./yb_build.sh  release --java-test 'org.yb.pgsql.TestPgViewYCQLStats'

Reviewers: asaha, amitanand, jason

Reviewed By: jason

Subscribers: bogdan, ybase, yql, mihnea

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D33917
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@hbhanawat hbhanawat

Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@hbhanawat @yugabyte-ci