Postgresql Performance Taping pour une exécution de requête plus rapide

Objectif

Notre objectif est de rendre une exécution de requête factice à exécuter plus rapidement sur la base de données PostgreSQL en utilisant uniquement les outils intégrés disponibles
dans la base de données.

Système d'exploitation et versions logicielles

• Système opérateur: Red Hat Enterprise Linux 7.5
• Logiciel: PostgreSQL Server 9.2

Exigences

PostgreSQL Server Base Installe Installe. Accès à l'outil de ligne de commande PSQL et la propriété de l'exemple de base de données.

Conventions

• # - Exige que les commandes Linux soient exécutées avec des privilèges racine soit directement en tant qu'utilisateur racine, soit par l'utilisation de Sudo commande
• $ - Étant donné les commandes Linux à exécuter en tant qu'utilisateur non privilégié régulier

Introduction

PostgreSQL est une base de données open source fiable disponible dans de nombreux référentiels de distribution moderne. La facilité d'utilisation, la possibilité d'utiliser des extensions et la stabilité qu'il offre à tous ajouter à sa popularité.
Tout en fournissant la fonctionnalité de base, comme répondre aux requêtes SQL, stocker les données insérées de manière cohérente, gérer les transactions, etc. La plupart des solutions de base de données matures fournissent des outils et des connaissances sur la façon de
Organisez la base de données, identifiez les goulots d'étranglement possibles et être en mesure de résoudre les problèmes de performances qui se produisent à mesure que le système alimenté par la solution donnée se développe.

PostgreSQL ne fait pas exception, et dans ce
Guide, nous utiliserons l'outil intégré expliquer Pour que la requête lente complète plus rapidement. C'est loin d'être une base de données du monde réel, mais on peut souligner l'utilisation des outils intégrés. Nous utiliserons un serveur postgresql version 9.2 sur Red Hat Linux 7.5, mais les outils illustrés dans ce guide sont présents dans des versions de base de données et de système d'exploitation beaucoup plus anciennes.

Le problème à résoudre

Considérez ce tableau simple (les noms de colonne sont explicites):

FOOBARDB = # \ D + Table des employés "public.Employés "Colonne | Type | Modificateurs | Stockage | Statistiques Target | Description ------------------ + --------- + ------- ---------------------------------------------- + --- ------- + -------------- + ------------- EMP_ID | NUMERIQUE | NON NULL NEXTval ('Employés_Seq' :: RegClass) | Main | | First_name | Texte | Not Null | Extension | | Last_name | Texte | non nul | Extension | | Birth_year | Numeric | Not Null | Main | | Birth_Month | Numeric | Not Null | Main | | Birth_Dayofmonth | Numeric | pas nuls | Main | | Index: "Employés_pkey" Clé primaire, Btree (EMP_ID) a des OID: Non 

Avec des enregistrements comme:

FOOBARDB = # SELECT * From Employés Limit 2; EMP_ID | First_name | Last_name | Birth_year | Birth_month | naissance_dayofmonth -------- + ------------ + ----------- + ------------ + - ----------- + ------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

Dans cet exemple, nous sommes la belle entreprise et avons déployé une demande appelée HBApp qui envoie un e-mail «joyeux anniversaire» à l'employé le jour de son anniversaire. L'application interroge la base de données tous les matins pour trouver des destinataires pour la journée (avant les heures de travail, nous ne voulons pas tuer notre base de données RH par gentillesse).
L'application exécute la requête suivante pour trouver les destinataires:

foobardb = # select emp_id, first_name, last_name des employés où naissance_month = 3 et naissance_dayofmonth = 20; EMP_ID | First_name | Last_name -------- + ------------ + ----------- 1 | Emily | James 

Tout fonctionne bien, les utilisateurs reçoivent leur courrier. De nombreuses autres applications utilisent la base de données, et les employés tablent à l'intérieur, comme la comptabilité et le BI. La belle entreprise se développe et se développe ainsi la table des employés. Avec le temps, l'application fonctionne trop longtemps et l'exécution chevauche le début des heures de travail, ce qui entraîne le temps de réponse de base de données lent dans les applications critiques de la mission. Nous devons faire quelque chose pour que cette requête s'exécute plus rapidement, ou l'application ne sera pas exploitée, et avec elle, il y aura moins de gentillesse dans une belle entreprise.

Pour cet exemple, nous n'utiliserons aucun outil avancé pour résoudre le problème, un seul fourni par l'installation de base. Voyons comment le planificateur de la base de données exécute la requête avec expliquer.

Nous ne testons pas en production; Nous créons une base de données pour tester, créer la table et y insérer deux employés mentionnés ci-dessus. Nous utilisons les mêmes valeurs pour la requête tout au long de ce tutoriel,
Donc, à n'importe quelle course, un seul record correspondra à la requête: Emily James. Ensuite, nous exécutons la requête avec précédent Expliquer analyser Pour voir comment il est exécuté avec un minimum de données dans le tableau:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- --- scan seq sur les employés (coût = 0.00… 15.40 lignes = 1 largeur = 96) (temps réel = 0.023… 0.025 lignes = 1 boucles = 1) filtre: ((naissance_month = 3 :: numérique) et (naissance_dayofmonth = 20 :: numérique)) Rows supprimé par filtre: 1 Total d'exécution: 0.076 ms (4 rangées) 

C'est vraiment rapide. Peut-être aussi vite que lorsque la société a déployé le HBApp pour la première fois. Imitons l'état de la production actuelle foobardb En chargeant autant de (faux) employés dans la base de données que nous l'avons fait en production (Remarque: nous aurons besoin de la même taille de stockage dans la base de données de test que dans la production).

Nous allons simplement utiliser Bash pour remplir la base de données de test (en supposant que nous en avons 500.000 employés en production):

$ pour j dans 1… 500000; faire écho "Insérer dans les employés (first_name, last_name, naissances_year, naissance_month, naissance_dayofmonth) ('user $ j', 'test', 1900,01,01);"; fait | PSQL -D FOOBARDB 

Nous avons maintenant 500002 employés:

foobardb = # select count (*) des employés; Count -------- 500002 (1 ligne) 

Exécutons à nouveau la requête Expliquez:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- -------- SCAY SEQ sur les employés (coût = 0.00… 11667.63 lignes = 1 largeur = 22) (temps réel = 0.012… 150.998 lignes = 1 boucles = 1) filtre: ((naissance_month = 3 :: numérique) et (naissance_dayofmonth = 20 :: numérique)) Rows supprimé par filtre: 500001 Total d'exécution: 151.059 ms 

Nous n'avons encore qu'un seul match, mais la requête est nettement plus lente. Nous devons remarquer le premier nœud du planificateur: Scan SEQ qui signifie Scan séquentiel - la base de données lit l'ensemble
table, alors que nous n'avons besoin que d'un seul enregistrement, comme un grep serait dans frapper. En fait, il peut être en fait plus lent que Grep. Si nous exportons le tableau vers un fichier CSV appelé / TMP / EXP500K.CSV:

 foobardb = # copier les employés dans '/ tmp / exp500k.CSV «Delimiter», «En-tête CSV; Copier 500002 

Et grep les informations dont nous avons besoin (nous recherchons le 20e jour du 3ème mois, les deux dernières valeurs du fichier CSV dans chaque
doubler):

$ time grep ", 3,20" / tmp / exp500k.CSV 1, Emily, James, 1983,3,20 Real 0M0.067S User 0m0.018S SYS 0M0.010 

Ceci est, la mise en cache à part, considérée comme plus lente à mesure que la table grandit.

La solution est de cause d'indexation. Aucun employé ne peut avoir plus d'une date de naissance, qui consiste en exactement Année de naissance, mois de naissance et naissance_dayofmonth - Ces trois champs offrent donc une valeur unique pour cet utilisateur particulier. Et un utilisateur est identifié par son emp_id (Il peut y avoir plus d'un employé dans l'entreprise du même nom). Si nous déclarons une contrainte sur ces quatre champs, un index implicite sera également créé:

FOOBARDB = # ALTER TABLE Les employés ajoutent une contrainte Birt_Uniq unique (EMP_ID, BAIN_YEAR, BAIN_MONTH, BAIN_DAYOFMONTH); AVIS: Alter Table / Add Unique créera un index implicite "Birth_Uniq" pour la table "Employés" 

Nous avons donc obtenu un index pour les quatre champs, voyons comment notre requête fonctionne:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- ---------- SCAN SEQ sur les employés (coût = 0.00… 11667.19 lignes = 1 largeur = 22) (temps réel = 103.131… 151.084 lignes = 1 boucles = 1) filtre: ((naissance_month = 3 :: numérique) et (naissance_dayofmonth = 20 :: numérique)) Rows supprimé par filtre: 500001 Total d'exécution: 151.103 ms (4 rangées) 

C'est identique au dernier, et nous pouvons voir que le plan est le même, l'indice n'est pas utilisé. Créons un autre index par une contrainte unique sur emp_id, mois de naissance et naissance_dayofmonth seulement (après tout, nous ne nous interrogeons pas pour Année de naissance dans hbapp):

FOOBARDB = # ALTER TABLE Les employés Ajouter une contrainte Birt_Uniq_M_Dom unique (EMP_ID, BATTH_MONTH, BAIN_DAYOFMONTH); AVIS: Alter Table / Add Unique créera un index implicite "naissance_uniq_m_dom" pour la table "Employés" 

Voyons le résultat de notre réglage:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- --------- SEQ SCAN sur les employés (coût = 0.00… 11667.19 lignes = 1 largeur = 22) (temps réel = 97.187… 139.858 lignes = 1 boucles = 1) filtre: ((naissance_month = 3 :: numérique) et (naissance_dayofmonth = 20 :: numérique)) Rows supprimé par filtre: 500001 Total d'exécution: 139.879 ms (4 rangées) 

Rien. La différence ci-dessus vient de l'utilisation des caches, mais le plan est le même. Allons plus loin. Ensuite, nous créerons un autre index sur emp_id et mois de naissance:

FOOBARDB = # ALTER TABLE Les employés Ajouter une contrainte Birt_Uniq_M UNIQUE (EMP_ID, BATTER_MONTH); AVIS: Alter Table / Add Unique créera un index implicite "naissance_uniq_m" pour la table "Employés" 

Et exécutez à nouveau la requête:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- ---------------------------- Index SCAN Utilisation de Birth_Uniq_M sur les employés (Cost = 0.00… 11464.19 lignes = 1 largeur = 22) (temps réel = 0.089… 95.605 lignes = 1 boucles = 1) index cond: (naissance_month = 3 :: numérique) Filtre: (naissance_dayofmonth = 20 :: numérique) Exécution totale: 95.630 ms (4 rangées) 

Succès! La requête est 40% plus rapide, et nous pouvons voir que le plan a changé: la base de données ne scanne plus la table entière, mais utilise l'index sur mois de naissance et emp_id. Nous avons créé tous les mélanges des quatre champs, il ne reste plus qu'un. La peine d'essayer:

FOOBARDB = # ALTER TABLE Les employés Ajouter une contrainte Birt_Uniq_dom unique (EMP_ID, naissance_dayofmonth); AVIS: Alter Table / Add Unique créera un index implicite "naissance_uniq_dom" pour la table "Employés" 

Le dernier index est créé sur les champs emp_id et naissance_dayofmonth. Et le résultat est:

FOOBARDB = # Expliquez Analyze Select EMP_ID, First_name, Last_name des employés où Birth_Month = 3 et Birth_DayOfMonth = 20; Plan de requête ------------------------------------------------ -------------------------------------------------- ------------------------------ SCAN INDEX Utilisation de Birth_Uniq_dom sur les employés (Cost = 0.00… 11464.19 lignes = 1 largeur = 22) (temps réel = 0.025… 72.394 lignes = 1 boucles = 1) index cond: (naissance_dayofmonth = 20 :: numérique) Filtre: (naissance_month = 3 :: numérique) Exécution totale: 72.421 ms (4 rangées) 

Maintenant, notre requête est environ 49% plus rapide, en utilisant le dernier (et seul le dernier) indice créé. Notre tableau et nos index connexes semblent comme suit:

FOOBARDB = # \ D + Table des employés "public.Employés "Colonne | Type | Modificateurs | Stockage | Statistiques Target | Description ------------------ + --------- + ------- ---------------------------------------------- + --- ------- + -------------- + ------------- EMP_ID | NUMERIQUE | NON NULL NEXTval ('Employés_Seq' :: RegClass) | Main | | First_name | Texte | Not Null | Extension | | Last_name | Texte | non nul | Extension | | Birth_year | Numeric | Not Null | Main | | Birth_Month | Numeric | Not Null | Main | | Birth_Dayofmonth | Numeric | Not Null | Main | | Index: "Employés_pkey" Clé primaire, Btree (EMP_ID) "Birth_Uniq" CONSTRAINTION UNIQUE, BTREE (EMP_ID, BAIN_YEAR, BAIN_MONTH, BATTH_DAYOFMONTH) "BATTH_UNIQ_DOM" CONSTRAINT Contrainte, btree (emp_id, naissance_month) "naissance_uniq_m_dom" contrainte unique, btree (emp_id, naissance_month, naissance_dayofmonth) a des OID: non 

Nous n'avons pas besoin des index intermédiaires créés, le plan indique clairement qu'il ne les utilisera pas, nous les supprimons donc:

FOOBARDB = # ALTER TABLE Les employés de la suppression de la contrainte Birt_Uniq; Alter table fooBardb = # alter table employés de la contrainte de dépôt naissance_uniq_m; Alter table foobardb = # alter les employés de la table CONSTRAINTION NAJEAT_UNIQ_M_DOM; MODIFIER TABLE 

En fin de compte, notre tableau ne gagne qu'un seul indice supplémentaire, ce qui est un coût faible pour une vitesse étroite de HBApp:

FOOBARDB = # \ D + Table des employés "public.Employés "Colonne | Type | Modificateurs | Stockage | Statistiques Target | Description ------------------ + --------- + ------- ---------------------------------------------- + --- ------- + -------------- + ------------- EMP_ID | NUMERIQUE | NON NULL NEXTval ('Employés_Seq' :: RegClass) | Main | | First_name | Texte | Not Null | Extension | | Last_name | Texte | non nul | Extension | | Birth_year | Numeric | Not Null | Main | | Birth_Month | Numeric | Not Null | Main | | Birth_Dayofmonth | Numeric | pas nuls | Main | | Index: "Employés_pkey" Clé primaire, Btree (EMP_ID) "Birth_Uniq_Dom" Contrainte unique, Btree (EMP_ID, BNITL_DAYOFMONTH) a des OID: Non 

Et nous pouvons introduire notre réglage à la production en ajoutant l'index que nous avons vu être le plus utile:

Les employés de la table alter Ajouter la contrainte naissance_uniq_dom unique (emp_id, naissance_dayofmonth);

Conclusion

Inutile de dire que ce n'est qu'un exemple factice. Il est peu probable que vous stockiez la date de naissance de votre employé dans trois domaines distincts alors que vous pouvez utiliser un champ de type de date, permettant des opérations liées à la date d'une manière beaucoup plus facile que de comparer les valeurs de mois et de jour en tant qu'entiers. Notez également que les requêtes d'explication ci-dessus ne sont pas adaptées à des tests excessifs. Dans un scénario du monde réel, vous devez tester l'impact du nouvel objet de base de données sur toute autre application qui utilise la base de données, ainsi que les composants de votre système qui interagissent avec HBApp.

Par exemple, dans ce cas, si nous pouvons traiter le tableau pour les destinataires dans 50% du temps de réponse d'origine, nous pouvons pratiquement produire 200% des e-mails à l'autre bout de l'application (disons, le HBApp s'exécute en séquence pour Toute la 500 filiales de la compagnie de Nice), ce qui peut entraîner une charge de pointe ailleurs - peut-être que les serveurs de messagerie recevront beaucoup d'e-mails «joyeux anniversaire» à relayer juste avant qu'ils ne envoient les rapports quotidiens à la direction, entraînant des retards de livraison. Il est également un peu loin de la réalité que quelqu'un a réglé une base de données créera des index avec des essais et des erreurs aveugles - ou du moins, espérons que c'est ainsi dans une entreprise employant que de nombreuses personnes.

Remarque cependant, que nous avons gagné une augmentation des performances de 50% sur la requête uniquement en utilisant le PostgreSQL intégré expliquer fonctionnalité pour identifier un seul indice qui pourrait être utile dans la situation donnée. Nous avons également montré que toute base de données relationnelle n'est pas meilleure qu'une recherche de texte claire si nous ne les utilisons pas car ils sont censés être utilisés.

Tutoriels Linux connexes:

• Choses à installer sur Ubuntu 20.04
• Ubuntu 20.04 Installation de PostgreSQL
• Ubuntu 22.04 Installation de PostgreSQL
• Une introduction à l'automatisation Linux, des outils et des techniques
• Optimisation des performances de Linux: outils et techniques
• Choses à faire après l'installation d'Ubuntu 20.04 Focal Fossa Linux
• Téléchargement Linux
• Fichiers de configuration Linux: 30 premiers
• Comment persister les données à PostgreSQL à Java
• Choses à installer sur Ubuntu 22.04