This Postgres SQL checks how much each query in each database uses the CPU. It provides a resultset sorted in descending order by the most CPU-intensive queries. For PostgreSQL versions 12 and earlier: SELECT pss.userid, pss.dbid, pd.datname as db_name, round(pss.total_time::numeric, 2) as total_time, pss.calls, round(pss.mean_time::numeric, 2) as…

This script connects to each DB on a DB instance and resets pg_stat_database statistics. #!/bin/bash ################################################################################################################################## # # Name: reset_pg_stat_database_statistics.sh # # Description: This script connects to each DB on a DB instance and resets pg_stat_database statistics # # Author: Dmitry # # Date: 11-Jan-2023 # # Usage Example: # # ./reset_pg_stat_database_statistics.sh -h localhost -p port -x db_pattern -a…

dmi@dmi-VirtualBox:~$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 20.04.4 LTS Release: 20.04 Codename: focal dmi@dmi-VirtualBox:~$ [ one line command ] wget -qOhttps:// packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - [ one line command ] sudo add-apt-repository "$(wget -qOhttps:// packages.microsoft.com/config/ubuntu/20.04/mss ql-server-2019.list)"

A part of working with databases is improving the performance of queries. In this post, I’ll demonstrate an approach “Aggregate first, join later” that will help to write more effective queries. I’ll create the table gender, and populate it with 2 records. Then I’ll create another table person, and populate…

To find PostgreSQL tables without a Primary Key, run the following SQL query: select tab.table_schema, tab.table_name from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tab.table_type = 'BASE TABLE' and tab.table_schema not in ('pg_catalog', 'information_schema') and tco.constraint_name is null order by table_schema, table_name;

The /etc/passwd is a plain text-based database that contains information for all user accounts on the Linux system. Each record in the /etc/passwd file has 7 fields separated by a colon ( : ) Let’s consider the /etc/passwd file format. www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin www-data — Registered username. It is used when a…

The log_min_duration_statement is the configuration parameter of PostgreSQL. It sets the minimum execution time in milliseconds (ms) above which all statements will be logged. The default value for the log_min_duration_statement parameter is -1, which disables logging statements. Setting the PostgreSQL parameter log_min_duration_statement to 0 will print all statements' durations. Note…

To find the number of transactions per second for the given PostgreSQL DB instance run the following script. #!/bin/sh ############################################### # # Check transactions per second # for the given PostgreSQL DB instance # # This script connects # to the given PostgreSQL DB instance # and retrieves information - # the number of transactions per second # # Date: 10-Aug-2020 # #…

To check the number of queries per second (QPS) on the PostgreSQL DB instance run the following SQL query: with t1 as (select sum(calls) n from pg_stat_statements), t2 as (select sum(calls) n from pg_stat_statements , pg_sleep(1)) select t2.n-t1.n the_num_of_queries_per_second from t1,t2; Example: postgres=> with postgres-> t1 as (select sum(calls) n from pg_stat_statements), postgres-> t2 as (select sum(calls) n from pg_stat_statements , pg_sleep(1)) postgres-> select postgres-> t2.n-t1.n the_num_of_queries_per_second postgres-> from postgres-> t1,t2; the_num_of_queries_per_second ------------------------------- 1234 (1 row) postgres=>