[optimisation]augmenter les performances d'une base de données

Version imprimable

17/05/2011, 11h48
ratata

[optimisation]augmenter les performances d'une base de données

Bonjour,

ma question porte sur les moyens d'optimisation d'une base de données postgreSQL 8.4, ca fait bien quelques semaines que je travaille dessus, l'essentiel de ma base est un ensemble de procédures stockées qui contiennent pas mal de select, d'Insert, d'update et de delete. heureusement je suis passée en terme de transactions vers ma base de 45 req/s -> 300 -> 750 (approximatives), mais je suis censé atteindre les 1200 req/s, alors j'ai essayé de passer par l'indexation des colonnes de certaines de mes tables ou les clauses "where" sont utilisées fréquemment , mais ce qui est bizarre c'est que ca n'a fait qu'empirer la situation, je suis revenu à 600 req/s ,alors j'aimerais avoir quelques directives la dessus, ou bien s'il y a d'autres moyens de réglages "SOFT" pour optimiser ma base de données.Merci à tous.

Passer une agréable journée.
18/05/2011, 09h37
scheu

Déjà fait de l'optimisation niveau serveur Postgresql, avant d'essayer d'en faire dans ton modèle de données
Regarde notamment les paramètres sur les ressources :
https://postgresql.developpez.com/do...tion/francais/
Il faut très souvent les augmenter car les valeurs par défaut sont insuffisantes
18/05/2011, 10h59
SQLpro

Ne jamais oublier que tous les SGBDR fonctionnent de la même façon (sauf MySQL) et traitent les données exclusivement en RAM. Donc, privilégier beaucoup la RAM (préférez donc le 64 bits) puis ensuite le disque. Le CPU n'intervient qu'en 3e position, d'autant que PG ne sait pas traiter ses requêtes en parallèle.

Enfin comme le dit ratata, les réglages standard ont été conçu à une époque ou l'install standard était de 2 Go de RAM....

A +
18/05/2011, 11h54
ratata

Bonjour,

Merci à tous , scheu je crois que il faut faire attention par rapport à certains paramètres qu'il ne faut pas augmenter comme par exemple le shared buffer.
SQLPro pour prévilégier la RAM, il y a vraiment une panoplie de paramètres qui se rapportent à la RAM, j'essayerai d'en trouver les bons.

Bonne journée.
18/05/2011, 14h07
bustaf

Bonjour

(Ne jamais oublier que tous les SGBDR fonctionnent de la même façon
(sauf MySQL) et traitent les données exclusivement en RAM:)
C'est faux
C'est Le système qui gère ou décide des écritures en différées en fonction
de ses propres paramètres instruits.

Le CPU n'intervient qu'en 3e position: C'est faux
Le taux de transfert des contrôleurs est fortement lié
à la puissance du cpu utilisé.

d'autant que PG ne sait pas traiter ses requêtes en parallèle:
C'est plus que faux, Lisez le code source vous trouverez certaines fonctions
qui utilisent les ipcs ,les forks ou les threads.

Le SQL est géré automatiquement en parallèle si une cohérence progressive
est établie.

Désolé là, il est impossible de laisser passer ça ...

Cordialement
18/05/2011, 14h39
ratata

Merci Bustaf, je tacherais de vérifier ces corrections mais par rapport à ma question bustaf, qu'est ce que vous pouvez dire la dessus :roll: ...

bonne journée.
18/05/2011, 15h52
SQLpro

Citation:

Envoyé par bustaf

Bonjour

(Ne jamais oublier que tous les SGBDR fonctionnent de la même façon
(sauf MySQL) et traitent les données exclusivement en RAM:)
C'est faux
C'est Le système qui gère ou décide des écritures en différées en fonction
de ses propres paramètres instruits.

C'est le SGBDR qui décide du moment opportun de l'écriture physique des données et cela de manière planifiée. Les écritures physiques des données étant asynchrone.

Citation:

Le CPU n'intervient qu'en 3e position: C'est faux
Le taux de transfert des contrôleurs est fortement lié
à la puissance du cpu utilisé.

Ce que vous dite peut être vrai pour un desktop, mais pas pour un serveur et encore moins dans une game spécialement conçue pour les SGBDR !

Citation:

d'autant que PG ne sait pas traiter ses requêtes en parallèle:
C'est plus que faux, Lisez le code source vous trouverez certaines fonctions qui utilisent les ipcs ,les forks ou les threads.

Là vous dites n'importe quoi. C'est la grande différence qui existe entre Oracle, SQL Server et IBM DB2 d'un côté et PostGreSQL de l'autre. Aucune requête ne peut actuellement faire des process parrallélisé dans PG, à la différence de SQL Server par exemple ou un simple SUM sur une machine avec 4 CPU lance 4 threads distincts pour faire chacun 1/4 du travail de la requêt e finale.

Citation:

Le SQL est géré automatiquement en parallèle si une cohérence progressive
est établie.

Désolé là, il est impossible de laisser passer ça ...

Cordialement

A +
18/05/2011, 16h10
bustaf

Bonjour S....

Lisez le code source de PG si vous en avez les facultés avant d’émettre ou persister dans vos inepties.

Cordialement
18/05/2011, 16h16
bustaf

Bonjour ratata
Votre système d’exploitation est de type (UNIX LINUX) OU MICROSOFT ?
Cordialement
18/05/2011, 16h27
ratata

je travail sur Linux distribution ubuntu
18/05/2011, 16h28
scheu

Citation:

Envoyé par ratata

Bonjour,

Merci à tous , scheu je crois que il faut faire attention par rapport à certains paramètres qu'il ne faut pas augmenter comme par exemple le shared buffer.
SQLPro pour prévilégier la RAM, il y a vraiment une panoplie de paramètres qui se rapportent à la RAM, j'essayerai d'en trouver les bons.

Bonne journée.

Les plus importants à mon sens (ça n'engage que moi) :
- shared_buffers (1/4 maximum de la RAM disponible pour Postgresql)
- work_mem (tu peux monter jusqu'à 256 Mo mais attention car chaque session simultanée pourra consommer jusqu'à cette valeur donc saturation de la RAM si trop de sessions concurrentes)
maintenance_work_mem : tu peux mettre au moins 128 Mo si tu as suffisamment de RAM, c'est pour les tâches de maintenance (reindex, vacuum, ...)
- wal_buffers : je mets toujours 512 Ko
- random_page_cost : mettre 2.0 voir moins si tu as des disques très rapides, ça favorise les full scan plutôt que les accès par index ce qui généralement est préférable si ta volumétrie n'est pas énorme
- effective_cache_size (environ la moitié de la RAM disponible pour Postgresql)

Après, calcul régulièrement tes statistiques (autovacuum ou vacuumdb), voir des reindexdb

Après il faut coupler les problèmes de perfs avec la supervision OS : consommation de la mémoire (swap ?), utilisation CPU, I/O sur tes disques, ...
Ca te servira généralement à identifier plus facilement les requêtes longues, et pouvoir après voir comment les optimiser
18/05/2011, 16h29
ratata

déjà là j'ai modifié quelques paramètres j'arrive déjà 1693 req/s comme chiffre, ca me donne encore envie de l'augmenter, donc j'y travail encore.

Merci
18/05/2011, 16h38
ratata

Super, c'est exactement les mêmes paramètres que j'ai modifié,à part quelque différences près comme au niveau de l'effective_cash_size j'ai pris complétement 75%, 1% pour work_mem(déterminé par mes connexions), 4 Mo pour le wal_buffers, voila je crois que j'ai fait le tour d'horizon sur les différences , qu'est ce que vous en pensez.demain si je n'augmente pas encore ce chiffre je marque la discussion comme résolue . :D
18/05/2011, 16h41
ratata

Oups, Merci scheu, SQLpro et bustaf
18/05/2011, 16h51
scheu

Pour le work_mem c'est la mémoire que chaque session pourra utiliser en cas de besoin (tris, jointures en mémoire, ...)
Ca dépend surtout du type de requêtes qui sont faites sur ta base :
- si c'est une base transactionnelle où il y a énormément de sessions simultanées et qui sont font des requêtes très courtes, tu peux laisser 1%
- si c'est une base qui se rapproche plus d'une utilisation datawarehouse (peu de sessions simultanées mais qui font des traitements plus longs, des requêtes plus gourmandes en volumétrie, plus complexes avec des jointures, des group by, etc ...), alors tu peux songer à augmenter ce paramètre

A toi de voir en fonction de ton besoin ;)
18/05/2011, 16h58
scheu

Au passage aussi puisque je relis ton message initial : évite de mettre trop d'indexes car ça ralentit les insert/update/delete dans ta table (car à chaque fois ça doit mettre à jour les indexes)

N'indexe vraiment que ce qui est utile (dans 90% des cas ce sont juste les colonnes de la PK et les colonnes concernées par les FK)

Tu as même moyen avec les vues pg_stat_all_indexes et pg_statio_all_indexes de voir les indexes les plus sollicités, et donc par déduction de trouver ceux qui sont inutiles et que tu pourrais supprimer pour améliorer les perfs en insert/update/delete de tes tables) 8-)
18/05/2011, 17h10
SQLpro

Citation:

Envoyé par scheu

Tu as même moyen avec les vues pg_stat_all_indexes et pg_statio_all_indexes de voir les indexes les plus sollicités,

Tu les as en 8.4 celles là ???

A +
18/05/2011, 17h53
scheu

Citation:

Envoyé par SQLpro

Tu les as en 8.4 celles là ???

A +

Elles existent déjà en 8.2
18/05/2011, 20h15
bustaf

Re Bonjour ratata

Le système Ubuntu est correctement paramétré pour des machines courantes.
Si vous disposez d'une configuration type gros serveurs Ex: une grappe de machines (XEON 8X) etc..
vous avez certains paramètres (dynamiques) instruits dans des fichiers du répertoire
/proc/sys/kernel, regardez ce coté qui est important pour optimiser.
Sur des gros serveurs de type (Cloud) je gagne parfois 10 20%
c'est long et relativement complexe ça change suivant le type de processeur.
pour votre performance contrôleur
tapez df pour identifier vos types de contrôleurs exemple pour du raid std ( /dev/ida/c0d0p1 etc...)
tapez hdparm -t /dev/ida/c0d0p1
cela va vous donner une idée des taux de transferts.
Si vous utilisez des armoires de disques identiques sur des machines avec des processeurs
de différentes puissances vous allez voir que les taux de transferts changent.
Je n'utilise plus les version 8 les 9 sont bien plus performantes.
Là aussi il faut compiler les sources avec certain paramètres propres aux différents types
de processeurs existants pour gagner un soupçon de performance ou une meilleur stabilité.

Pour la réflection de notre amis S...
(C'est la grande différence qui existe entre Oracle, SQL Server et IBM DB2)
Compilez les sources de PG en mode debug pour ensuite tracer l'activité des
(ipcs,des forks des threads) vous allez vite voir sur du concret ou est la vérité.
Avec Postgresql vous avez rien a envier d'autres moteurs comme Oracle ou Db2.
Postgresql c'est superbe,même directement en C/C++ avec des fonctions spécifiques
très poussées c'est souvent très difficile d'égaler ses performances.
Essayez autre chose vous comprendrez vite ou est votre réel bonheur.....
J' attend un serveur avec du Xeon E7 je pense que cela va être superbe avec les
PG 9+..
Cordialement.
19/05/2011, 11h13
ratata

Bonjour bustaf,

la je travail encore sur des simples machines qui n'ont pas une configuration très avancée, et c'est en deuxième temps que je vais fixer les paramètres du serveur qui va héberger ma base de données, déjà là vous me donner une poussée la dessus Merci , encore je vois qu'il faudrait aussi que j'optimise au niveau des algorithmes de mes procédures stockées et carrément migrer vers le C/C++ au lieu de pl/pgdsql et c'est un ainsi que je vais passer à un chiffre bien satisfaisant dépassant les 1693 que j'ai obtenu hier.Merci bustaf et merci à tous .

Passez une agréable journée