Re: Proposal Dashboard design for system of pgadmin

Hi

On Fri, 1 Jul 2022 at 16:29, Kunal Garg <gargkunal02(at)gmail(dot)com> wrote:

> Hi
>
> As per your suggestions, I believe that we can go with the following
> approaches
>
> 1. OS up time will be taken as a text field.
> 2. There will be a single graph for process and thread count (with
> different color lines) in the CPU section.
> 3. In the case of multiple NICs on the system, all of them can be
> displayed in a single graph with different graph legends.
> 4. Different disks can show all the information in the form of a bar graph
> or pie chart representing Total space, used space and free space.
>
>
> For the Dashboard UI/UX, I would like to proceed with option 2 that is
> making sub-tabs under the dashboard for the 2 options.
>

OK.

>
> *Below are some queries that I have coded for fetching relevant data in
> the required places, kindly review it and let me know of changes or errors
> and best practices.*
>
> To check whether the role exists or not, this query has been developed:
>
> O $$
> BEGIN
> IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname =
> 'monitor_system_stats') THEN
> CREATE ROLE monitor_system_stats WITH
> NOLOGIN
> NOSUPERUSER
> NOCREATEDB
> NOCREATEROLE
> INHERIT
> NOREPLICATION
> CONNECTION LIMIT -1;
> END IF;
> END
> $$;
>

You should not need to create a role at all. That's up to the DBA of the
system to do, if they want.

>
> Now I am listing queries for each component of system_stats output. A
> point to note here is there will be separate files for coding graphs and
> text based queries because for graphs the data will be json format and for
> textual representation the data will be text based.
>
> *1.CPU *
>
> Graphs-
>
> Activity graph - SELECT ‘CPU Activity’ as chart_name, row_to_json(t) as
> chart data FROM (SELECT
>
> (SELECT sum(usermode_normal_process_percent
>
> ) FROM pg_sys_cpu_usage_info() )AS “{{(‘CPU Activity’)}}”;
>
> (SELECT sum(kernelmode_normal_process_percent
>
> ) FROM pg_sys_cpu_usage_info() )AS “{{(Kernel Activity’)}}”;
>
> )t
>
> Process, thread and handle count Graph- SELECT ‘thread Activity’ as
> chart_name, row_to_json(t) as chart data FROM (SELECT
>
> (SELECT process_count FROM pg_sys_os_info() )AS “{{(Process Count’)}}”;
>
> (SELECT handle_count FROM pg_sys_os_info() )AS “{{(Handle count’)}}”;
>
> (SELECT thread_count FROM pg_sys_os_info() )AS “{{(Thread count’)}}”;
>
> )t
>

So, a few points here:

- Please test SQL statements before posting them. Neither of those above
are valid SQL for a few reasons:

- At some point you've used an editor that has replaced single and double
quotes with their "smart quote" equivalents. As a general rule, if you're
going to use Google Docs or MS Word to draft proposals, turn smart quotes
off because it will almost certainly mess up your SQL.
- Your row alias (chart data) has a space in the name and is not quoted.
Avoid the need to quote at all - use lower case names, with underscores for
spaces.
- You have semicolons following your sub selects. They should be commas.

- More importantly, those SQL statements (and many of the ones I haven't
quoted), need to be rewritten:

- There's no need to have lots of sub selects (and it's really
quite inefficient). In fact, the only reason you really need the sub select
at all is to get nice column names back from row_to_json(). The second
example above can be rewritten as:

postgres=# SELECT 'Thread Activity' as chart_name, row_to_json(t) as
chart_data FROM (
SELECT process_count, handle_count, thread_count FROM pg_sys_os_info()
) t;
chart_name | chart_data

-----------------+---------------------------------------------------------------
Thread Activity |
{"process_count":668,"handle_count":null,"thread_count":null}
(1 row)

- If you don't need the nice name in the JSON output (which is good to
avoid because it's really just wasting memory/bandwidth), don't use the sub
select, e.g:

postgres=# SELECT 'Thread Activity' as chart_name,
row_to_json(row(process_count, handle_count, thread_count)) as chart_data
FROM pg_sys_os_info();
chart_name | chart_data
-----------------+--------------------------------
Thread Activity | {"f1":668,"f2":null,"f3":null}
(1 row)

- I'm unclear why you have the additional braces: “{{(Thread count’)}}”
- Aim to minimise the number of queries being executed. Instead of the 4
queries you have for the OS text, use something like:

SELECT host_name, architecture, version, os_up_since_seconds FROM
pg_sys_os_info();

- Finally, all SQL queries used by dashboards should be prefixed with the
string "/*pga4dash*/ " to allow them to be easily filtered from server
logs. E.g:

/*pga4dash*/ SELECT host_name, architecture, version, os_up_since_seconds
FROM pg_sys_os_info();

--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com