PostgreSQL performance Turing Indexes

https://www.timescale.com/learn/postgresql-performance-tuning-optimizing-database-indexes

[Postgres] Fonction pour compter les lignes

Pratique lors d'une migration pour vérifier que toutes les lignes sont bien là !

[Postgres] Mesure the bloat

intéressant mon cher watson

Convertisseur EUR BTC

convertisseur euro btc

Connexions aux db du cloud

#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script to connect to IBM Cloud DB, version 2.0
# Author : JJY
#
#-------------------------------------------------------------------------------
#set -vx
##############
#INSTANCIATION
##############
DOMAIN="<domain>"
DATABASE="postgres"
USERNAME="<user_dba>"

while true
do
DATABASE="postgres"
 clear
 echo "CONNEXION AUX BASES DU CLOUD "
 echo " "
 echo "CONNEXION A l ECOSYSTEM : <ecosystem>"
 echo " "
 echo "1 - DEV(dbname) - serveur : <instance> connexion DBA"
 echo " "
 echo "2 - DEV(dbname) - serveur : <instance> connexion dynamique applicative"
 echo " "
 echo "3 - QUAL(dbname) - serveur : <instance> connexion DBA"
 echo " "
 echo "4 - QUAL(dbname) - serveur : <instance> connexion dynamique applicative"
 echo " "
 echo "0 - TOOLBOX VAULT "
 echo " "
 echo " Faites votre choix (0 a 4) :"
 echo " Taper Q pour quitter."
 echo " "
 read var
 case $var in
        1)
        echo "Connexion a la base, veuillez patienter..."
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
        vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
        secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
        export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
        ;;
        2)
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
        echo "Liste des roles:"
        vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
        echo " "
        echo "A quel role souhaitez-vous vous connecter:"
        read ROLE
        clear
        echo "Connexion a la base en mode dynamique, veuillez patienter..."
        var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
        username=$(echo "$var" | grep username |  awk '{print $2}');
        password=$(echo "$var" | grep password |  awk '{print $2}');
        echo "User is : $username"
        echo "Password is : $password"
        echo " Tapez entrée pour continuer."
        read bidon
        export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
        ;;
        3)
        echo "Connexion a la base, veuillez patienter..."
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
        vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
        secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
        export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
        ;;
        4)
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
        echo "Liste des roles:"
        vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
        echo " "
        echo "A quel role souhaitez-vous vous connecter:"
        read ROLE
        clear
        echo "Connexion a la base en mode dynamique, veuillez patienter..."
        var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
        username=$(echo "$var" | grep username |  awk '{print $2}');
        password=$(echo "$var" | grep password |  awk '{print $2}');
        echo "User is : $username"
        echo "Password is : $password"
        echo " Tapez entrée pour continuer."
        read bidon
        export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
        ;;
        0)
        ./toolbox.sh
        echo " Tapez entrée pour continuer."
        read bidon
        ;;
        Q)
        echo "Merci d'avoir utiliser ce programme."
        exit
        ;;
        *)
        echo "Merci de renseignez un chiffre entre 0 et 4."
        echo " Tapez entrée pour continuer."
        read bidon
        ;;
 esac
done
exit

[Postgres] Spot a missing postgreSQL index

--tuto tuning psql
--Spot a missing PostgreSQL index

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

--To find unused index in a db

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

--returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
SELECT schemaname, 
              relname, 
              seq_scan, 
              seq_tup_read, 
              idx_scan, 
              seq_tup_read / seq_scan AS avg
 FROM         pg_stat_user_tables 
 WHERE        seq_scan > 0 
 ORDER BY     seq_tup_read DESC;

--Finding slow queries to be executed on pgadmin
SELECT query, 
              total_exec_time, 
              calls, 
              mean_exec_time 
       FROM   pg_stat_statements 
       ORDER BY total_exec_time DESC;

Résoudre l'ORA-600 doc id 2523249.1

Optimizer expression statistics monitoring is introduced in 12.2 database version.

 For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

 

 

Solution:

Disable future monitoring:

alter system set "_column_tracking_level"=17 scope=both;

--wait 10 minutes

 

To purge data from tables:

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

truncate table sys.exp_head$ drop storage;

truncate table sys.exp_obj$ drop storage;

truncate table sys.exp_stat$ drop storage;

alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

 

To re-enable expression statistics monitoring later :

alter system set "_column_tracking_level"=21 scope=both;

Migration assessment

[Postgres] Découverte d'une DB

Découverte d'une base postgres

PostgreSQL in IBM Cloud.

Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..