Command Center

[my-ship-it]

[leborchuk]

Lets discuss what does it means - Command Center. Original Command Center is UI utility for diagnose and manage Greenplum Database (Databases).

gpdb already have SQL interface for manage objects like tables and resource groups. But no open-source solutions for view running queries, limited info about opened sessions and no good history data about completed queries.

Is it Ok if we focused here on database tooling for gather information about SQL? (or maybe there is another more appropriate issue for that)

[tuhaihe]

Hi @leborchuk this issue is too old, but it does mean a tool like GPCC, will be implemented by the web technology, using the native interfaces for the information display, and other interactive actions, including deployment support, Cluster monitoring, SQL editor, Query monitor etc.

This tool has a simple prototype and is still in progress, it will open source in the plan but not very soon.

For the ideas @leborchuk mentioned above, @fanfuxiaoran could you help review them? After the open source, maybe we can call for the community members to help.

[leborchuk]

Let's go a little big deeper )

We have our own solutions for GPDB 6.X and we are trying to open them up. The problem is that all our diagnostic and tuning solutions are heavily linked to our internal infrastructure, so there is no obvious benefit to opening them up as they are. However, I think it could be possible to split them into modules and some of these modules could be easily adapted for use in CBDB.

1. GPCC have page page showing tables, their size, last usage https://docs.vmware.com/en/VMware-Greenplum-Command-Center/7.1/greenplum-command-center/topics-ui-table-browser.html

I propose for table size use https://github.com/yezzey-gp/gp_relsizes_stats extension

Also it should be great if we could show not only when the table was last analyzed but also when table last modyfied and last accessed. https://github.com/yezzey-gp/gp_relaccess_stats extension could be used for tracking objects activities.

(Both do not support CBDB yet, but we could add support)

[leborchuk]

2. Our diagnostic/monitoring/tuning tools is Web-application too.

(I) So we have our GP cluster (with extensions),

(II) Special application (named agent) that:

• gather info about SQL executed in GP (and their execution plans and statistics)
• gather info about sessions in GP (their SQLs - not the first/last one, the whole stack) and sessions stats
• could store historic info in history database (back to GP or another database)
• have superuser connection to GP
• may proxy queries for historic data to historic database
• may proxy database queries to GP (with additional checking)

(III) Special metric collection utility - https://github.com/influxdata/telegraf with plugins to get additional data from greenplum - metric collection utility could store info everywhere - grafana for example

(IV) Web application could

• get information from the agent and show it in different ways
• show grafana dashboards

(II) is written in Go.

(II) and (IV) communicate using grpc protocol.

Could we make the best here? Take some solutions from the CBDB to our project, and in exchange we will offer something in return.

For example use the grpc for communication betwen (II) and (IV) and use the same format of protobuf messages.

Or use the same ones telegraf plugins.

[fanfuxiaoran]

hi @leborchuk , sorry for the late reply.

gpdb already have SQL interface for manage objects like tables and resource groups. But no open-source solutions for view running queries, limited info about opened sessions and no good history data about completed queries.

Beside the tables, database, sessions etc. , we have a extension which can collect running queries and their stats, the stat is like below:

Query Text: create table test as select generate_series(0,100000000);
                    +
 Result (node status: Executing) (actual rows=21258921, loops=1)
                    +
   ->  Redistribute Motion 3:3  (slice1; segments: 3) (node status: Executing) (actual row
s=21258921, loops=1)+
         ->  Result (node status: Executing) (actual rows=63767835, loops=1)
                    +
               One-Time Filter: (gp_execution_segment() = 2)
                    +
               ->  ProjectSet (node status: Executing) (actual rows=63767835, loops=1)
                    +
                     ->  Result (node status: Executing) (actual rows=1, loops=1)

In addition, we can gather the metrics such as cpu, memory and spill filles used by the query.

All completed slow queries data are saved in the the table.