Thread: Proposal Dashboard design for system of pgadmin
Attachment
This is the updated proposal answering the questions and queries given in feedback, I have provided varying options for the UI/UX. we can set up a short call to finalize one of the approaches.
Please find it below.
thanks kunal garg
GUI representation of monitoring System Activity
with the system_stats Extension in pgAdmin 4
Proposal for Layout of Pgadmin4 Dashboard
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:-
Data fetched from queries:-
Details of different queries will be shown on a given component/card as shown in the design.
CPU
Memory
Disk
OS
Network
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.
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”
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.
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.
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.
Graphs:
- Here, the user can select the refresh rate or the time interval of the graphs as per their need. The default value will be set to 1 second, but the users can vary it to any value they prefer.
Display:
- Enabling or disabling this dashboard / enabling or disabling the individual charts within the dashboard
As per the preferences selected, the system statistics dashboard will be dynamically updated.
i.e.
CPU
Memory
Disk
Network
Os time
Os name
Architecture
Version
Show graph data points?
Show graphs?
HiOn Thu, 23 Jun 2022 at 07:27, Kunal Garg <gargkunal02@gmail.com> wrote:Thankyou for the feedback, I will incorporate the changes accordingly and email the revised version.Please also send it as a plain email, not a PDF. That makes it much easier to reply and comment inline.Thanks!On Thu, 23 Jun, 2022, 10:49 am Ashesh Vashi, <ashesh.vashi@enterprisedb.com> wrote:Hi Kunal,The proposal must include:- Impact on the existing layout (Where would the existing contents of the dashboard go?).- UI impact when the system_stats extension is not present in the PostgreSQL instance.- Introduction of the preferences for enabling/disabling this dashboard and/or the individual charts within this dashboard.
- Introduction of the preferences to change the time interval between probing the database server itself for these chart metrics.NOTE:
* Please study the existing charts of the existing dashboards.
* Dashboards are contextual, they're dependent on the node selection of the 'Browser' tree on the left hand side.On Thu, Jun 23, 2022 at 4:02 AM Kunal Garg <gargkunal02@gmail.com> wrote:Hi,Proposing the dashboard design of pgadmin for the system stats.Attached is a proposal for incorporating the os details to the pgadmin dashboard.--
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:-
Data fetched from queries:-
Details of different queries will be shown on a given component/card as shown in the design.
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.
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”
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.
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.
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.
HiOn Sun, 26 Jun 2022 at 12:40, Kunal Garg <gargkunal02@gmail.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.
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”
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.
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.
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!--
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 1:59 PM Dave Page <dpage@pgadmin.org> wrote:HiOn Sun, 26 Jun 2022 at 12:40, Kunal Garg <gargkunal02@gmail.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.
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”
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.
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!--