Re: Proposal Dashboard design for system of pgadmin

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.

*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
$$;

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

Texts-

Model name- SELECT name FROM pg_sys_cpu_info();

Logical processor - SELECT logical_processor FROM pg_sys_cpu_info();

Physical processor - SELECT physical_processor FROM pg_sys_cpu_info();

No_of_cores - SELECT no_of_cores FROM pg_sys_cpu_info();

Base Speed - SELECT clock_speed_hz FROM pg_sys_cpu_info();

L1dcache_size- SELECT l1dcache_size FROM pg_sys_cpu_info();

L1icache_size- SELECT l1icache_size FROM pg_sys_cpu_info();

L2cache_size - SELECT l2cache_size FROM pg_sys_cpu_info();

L3cache_size - SELECT l3cache_size FROM pg_sys_cpu_info();

*2. Memory*

Graph-

Memory composition graph - SELECT 'composition’ as chart_name,
row_to_json(t) as chart data FROM (SELECT

(SELECT used_memory FROM pg_sys_memory_info() )AS “{{(‘Used Memory)}}”;

(SELECT free_memory FROM pg_sys_memory_info() )AS “{{(Free Memory)}}”;

)t

TEXT-

Swap Total- SELECT swap_total FROM pg_sys_memory_info();

Swap used -SELECT swap_used FROM pg_sys_memory_info();

Swap free - SELECT swap_free FROM pg_sys_memory_info();

*3. Disk*

Graph-

Disk info bar graph - SELECT ‘Disk_info’ as chart_name, row_to_json(t) as
chart data FROM (SELECT

(SELECT total_space FROM pg_sys_disk_info() )AS “{{(Total Memory)}}”;

(SELECT used_space FROM pg_sys_disk_info() )AS “{{(Used Memory)}}”;

)t

Avg Load- SELECT ‘Avg_load’ as chart_name, row_to_json(t) as chart data
FROM (SELECT

(SELECT load_avg_one_minute FROM pg_sys_load_avg_info() )AS
“{{(load_per_min)}}”;

(SELECT load_avg_five_minutes FROM pg_sys_load_avg_info() )AS
“{{(Load_per_5min)}}”;

(SELECT load_avg_ten_minutes FROM pg_sys_load_avg_info() )AS
“{{(Load_per_10_min)}}”;

)t

Text-

Read speed -

Write speed -

*4. Network*

Multiple NICs graph - SELECT ‘NIC’ as chart_name, row_to_json(t) as chart
data FROM (SELECT

(SELECT interface_name,tx_bytes FROM pg_sys_disk_info() )AS
“{{(Transmitted Bytes)}}”;

(SELECT interface_name,tx_packets FROM pg_sys_disk_info() )AS
“{{(Transmitted Packets)}}”;

(SELECT interface_name,rx_bytes FROM pg_sys_disk_info() )AS “{{(Received
Bytes)}}”;

(SELECT interface_name,rx_packets FROM pg_sys_disk_info() )AS “{{(Received
Packets)}}”;

)t

*5. Os*

Text-

Host name - SELECT host_name FROM pg_sys_os_info();

Architecture - SELECT architecture FROM pg_sys_os_info();

Version - SELECT version FROM pg_sys_os_info();

Os_up_time-SELECT os_up_since_seconds FROM pg_sys_os_info();

These were the queries for fetching data.

I have some questions regarding the same. We can discuss them in a meeting.

Regards
Kunal Garg

On Mon, Jun 27, 2022 at 2:07 PM Khushboo Vashi <
khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:

>
>
> On Mon, Jun 27, 2022 at 1:59 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Sun, 26 Jun 2022 at 12:40, Kunal Garg <gargkunal02(at)gmail(dot)com> wrote:
>>
>>> Plan:-
>>>
>>> To integrate a detailed view of system activity into the existing
>>> pgAdmin dashboard following a consistent design that matches the existing
>>> UI/UX and meets User Acceptance Testing Standards.
>>>
>>> Proposed Layout Design:-
>>>
>>>
>>>
>>>
>> Some initial thoughts on what you propose to display:
>>
>> - OS Uptime should not be a graph. It's not overly useful, and will just
>> trend upwards linearly before resetting to zero if there's a restart. Just
>> make it a text label.
>>
>> - Thread count is probably less important than process count on a
>> PostgreSQL server. I would probably show both, in a small chart as those
>> are values that do change over time in response to system activity.
>>
>> - I would put the "system info" data (Host name, architecture, kernel,
>> thread/process count etc) at the top as it's what you might consider
>> overview or meta data. Then have the rest of the info below that, probably
>> 2 full width columns with two rows for CPU, memory, disk, and network.
>>
>> - Consider what to display if there are multiple NICs on the system.
>>
>> - Consider what to display if there are multiple disks on the system -
>> and there alway will be on a Linux machine for example. Many of them will
>> be hidden here (e.g. /proc, /sys, tmpfs's), whilst others should be
>> displayed. We cannot assume stats on only one disk are important, as the
>> system might be configured with tablespaces or the WAL on different devices.
>>
>> Data fetched from queries:-
>>>
>>> Details of different queries will be shown on a given component/card as
>>> shown in the design.
>>>
>>
>> <snip data source info - that's more an implementation detail at this
>> stage>
>>
>>
>>>
>>>
>>> Impact on the existing layout:-
>>>
>>> Existing layout will remain the same for most of the part. Here are some
>>> options for showing the system stats dashboard option to the users.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> 1.
>>>
>>> New button for system statistics on the Dashboard Navbar (refer to
>>> the red box in the image for the location of this button)
>>>
>>>
>>>
>>> UI impact when the system_stats extension is not present in the
>>> PostgreSQL instance:
>>>
>>> The page will open as usual with the following message,
>>>
>>> “No system statistics information is available for the selected object”
>>>
>>>
>>>
>>> 1.
>>>
>>> On hovering over the Dashboard tab, a dropdown will come, giving the
>>> user an option to select either the already existing pgadmin dashboard or
>>> the system statistics dashboard. (refer to the green box in the
>>> image for the location of this button)
>>>
>>>
>>>
>>> UI impact when the system_stats extension is not present in the
>>> PostgreSQL instance:
>>>
>>> This will happen only when the user has not selected any server, then
>>> the dashboard will open as usual as shown in the image.
>>>
>>>
>>>
>>> 1.
>>>
>>> On Selecting the Dashboard tab, 2 new buttons will be displayed
>>> under the dashboard tab, clicking any one will show the respective
>>> dashboard. (refer to the blue box in the image for the location of this
>>> button)
>>>
>>>
>>>
>>> UI impact when the system_stats extension is not present in the
>>> PostgreSQL instance:
>>>
>>> This will happen only when the user has not selected any server, then
>>> the dashboard will open as usual as shown in the image
>>>
>>>
>>>
>>> Note: The dashboards are contextual, they're dependent on the node
>>> selected on the left-hand side tree, that is, the values change as per the
>>> server node selected. Similarly, the system statistics dashboard will also
>>> be contextual and change with the selected server.
>>>
>>
>> I'm 90% sure I wouldn't want to see an additional dashboard (option 1),
>> and option 3 would almost certainly require modifying the wcDocker library
>> which I think is a bad idea. I would consider how to accommodate the
>> additional information on the existing dashboard. A couple of ideas:
>>
>> 1) Move all the existing elements and the new elements into collapsible
>> horizontal regions. Then you can display as much or as little as the user
>> wants. This is essentially how it would work in Grafana.
>>
>> 2) Add sub tabs inside the dashboard. If (and only if) system_stats is
>> present on a server, have a top-level tabset within the dashboard with tabs
>> for "Database Info" (with the existing display) and "System Info". By
>> default, the user is shown what they see now, but they can click the System
>> Info tab to see that data. Remember the user's choice, so the same tab can
>> be displayed first in the future. This is essentially your option 3, but
>> using tabsets instead of buttons.
>>
> +1 for this approach.
>
>>
>>
>>>
>>>
>>>
>>>
>>> Filters available in the preferences:-
>>>
>>> Under the files tab -> Preferences
>>>
>>> In preferences there are multiple dropdowns for different parts of the
>>> dashboard, giving the user power to customize the dashboard as per their
>>> need by enabling or disabling certain graphs and changing the refresh rate
>>> of the graphs.
>>>
>>> In the preferences tab, there is a drop-down by the name of “dashboard”,
>>> which gives 2 options for selecting “graphs” and “display”. A similar
>>> layout will be followed for the “system statistics” dashboard.
>>>
>>
>> Yes.
>>
>> Thanks!
>>
>> --
>> Dave Page
>> Blog: https://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EDB: https://www.enterprisedb.com
>>
>>