Re: Proposal Dashboard design for system of pgadmin - Mailing list pgadmin-hackers
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!--
pgadmin-hackers by date: