Re: Proposal Dashboard design for system of pgadmin
| От | Dave Page |
|---|---|
| Тема | Re: Proposal Dashboard design for system of pgadmin |
| Дата | |
| Msg-id | CA+OCxoywH63aGi5PNV_-JzFN2n7LRfTpJx6wE+_yzFP7Rh-8xg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Proposal Dashboard design for system of pgadmin (Kunal Garg <gargkunal02@gmail.com>) |
| Список | pgadmin-hackers |
Hi
On Fri, 1 Jul 2022 at 16:29, Kunal Garg <gargkunal02@gmail.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();
В списке pgadmin-hackers по дате отправления:
Следующее
От: Akshay JoshiДата:
Сообщение: pgAdmin 4 commit: Added support for visualise the graph using a Pie cha