IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration PostgreSQL Discussion :

Aide à la configuration d'un serveur


Sujet :

Administration PostgreSQL

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Homme Profil pro
    Responsable Informatique
    Inscrit en
    Août 2010
    Messages
    42
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable Informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Août 2010
    Messages : 42
    Par défaut Aide à la configuration d'un serveur
    Bonjour,

    Nous avons une utilisation assez particulière de PostgreSQL et nous recherchons un peu d'aide pour configurer, ou plutôt optimiser les configurations de notre serveur.

    Notre matériel :
    Nous avons un serveur Dell PowerEdge T620 avec 32 Go de RAM et 32 CPU 2.6GHz avec un système Ubuntu 12.04.

    Notre usage :
    Nous ne faisons pas de collecte de données en temps réelle (pas de site web avec des SELECT / INSERT / UDPDATE réguliers). Nous faisons plutôt de l'analyse et du traitement de données en masse. Nous collectons de gros fichiers de données (jusqu'à plusieurs Go, plusieurs dizaines de millions d'enregistrements avec parfois une centaine de colonnes). Et en général, nous effectuons quelques requêtes SELECT avec des COUNT, MIN, MAX, AVERAGE, etc. Pas mal de GROUP BY, quelques JOIN (mais beaucoup plus rares car en général, on a une grosse table avec toutes les infos dedans). Enfin, il nous est assez rare de faire des UPDATE car ceux-ci prennent beaucoup trop de temps ! Lorsqu'on doit modifier des données, on utilise plutôt des requêtes du genre CREATE TABLE x_bis AS SELECT ... FROM x; en utilisant des CASE dans le SELECT pour modifier les choses. A titre d'exemple, pour un résultat identique, une requête CREATE TABLE AS SELECT va prendre 2 à 3h alors qu'un UPDATE peut mettre jusqu'à 5 jours...

    La problématique :
    Aujourd'hui, nous n'arrivons pas à utiliser la pleine puissance du serveur. Dès que l'on fait tourner plusieurs requêtes en parallèle (CREATE TABLE AS SELECT), PostgreSQL devient très long, même pour de simples requêtes SELECT. Et au max, on utilise 15 à 20% de la puissance CPU total et de la mémoire RAM. Le serveur est dédié à la base de données, donc nous souhaiterions changer les configurations de PostgreSQL afin qu'il puisse utiliser toute la puissance du serveur. Nous cherchons donc un peu d'aide ou quelques conseil là-dessus.

    Pour le moment, nous avons changé les configurations suivantes :
    shared_buffers = 6144MB
    checkpoint_segment = 30
    checkpoint_timeout = 10min
    effective_cache_size = 16384MB

    Nous avons essayé d'espacer les checkpoints pour limiter l'accès disque, car nous n'avons pas besoin d'une restauration "rapide" en cas de crash (nous n'avons jamais eu de crash de PostgreSQL dans tous les cas). Et au pire, même si on met des checkpoints beaucoup plus espacés et que ça nous permet de nous faire gagner 30min sur des requête qui peuvent durer 4 à 5h, c'est toujours ça de gagner, même si derrière il nous faut une heure pour redémarrer PostgreSQL suite à un crash. Mais après, nous ne sommes pas sûr que ça améliore la performance globale dans le sens où les checkpoints se font moins souvent, mais du coup, sont plus long à faire puisqu'il y a plus de données à écrire...

    Nous pensons aussi que nous pourrions gagner en rendant plus léger l'autovacuum, dans le sens où nous avons très peu, voire pas du tout d'UPDATE...

    Donc en bref, si quelques personnes expérimentées souhaitent nous donner quelques astuces ou conseil pour améliorer notre configuration, elles sont les bienvenues

    Merci d'avance.

  2. #2
    Membre éprouvé Avatar de Lady
    Femme Profil pro
    Développeur Java
    Inscrit en
    Mars 2003
    Messages
    678
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Développeur Java
    Secteur : Santé

    Informations forums :
    Inscription : Mars 2003
    Messages : 678
    Par défaut
    Nous n'utilisons pas d'aussi grosse machine que la votre mais voici quelques pistes qui nous avaient été fournies par quelqu'un qui s'y connaissait en Postgres ( par contre cela date de la version 8.4)

    effective_cache_size : 2/3 de la RAM disponible

    work_mem : Vous n'avez pas indiqué si vous l'aviez augmentée c'est pourtant un des paramètres qui joue le plus sur les performances . Si vous avez peu de connections simultanées mais qu'elle font de gros calculs c'est surement le paramètre le plus important.

    maintenance_work_mem : Sert au vacuum à priori pas utile pour vous mais cela sert aussi aux créations d'index. Ici sur des machine avec 8Go de RAM nous mettons 512m vous devriez pouvoir monter à 1 ou 2 Go.

    Après si le problème viens de l'accès disque il faudra peut être envisager de distribuer votre base sur plusieurs disques si possible.

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Vinorcola Voir le message
    Bonjour,

    Nous avons une utilisation assez particulière de PostgreSQL et nous recherchons un peu d'aide pour configurer, ou plutôt optimiser les configurations de notre serveur.

    Notre matériel :
    Nous avons un serveur Dell PowerEdge T620 avec 32 Go de RAM et 32 CPU 2.6GHz avec un système Ubuntu 12.04.

    Notre usage :
    Nous ne faisons pas de collecte de données en temps réelle (pas de site web avec des SELECT / INSERT / UDPDATE réguliers). Nous faisons plutôt de l'analyse et du traitement de données en masse. Nous collectons de gros fichiers de données (jusqu'à plusieurs Go, plusieurs dizaines de millions d'enregistrements avec parfois une centaine de colonnes). Et en général, nous effectuons quelques requêtes SELECT avec des COUNT, MIN, MAX, AVERAGE, etc. Pas mal de GROUP BY, quelques JOIN (mais beaucoup plus rares car en général, on a une grosse table avec toutes les infos dedans). Enfin, il nous est assez rare de faire des UPDATE car ceux-ci prennent beaucoup trop de temps ! Lorsqu'on doit modifier des données, on utilise plutôt des requêtes du genre CREATE TABLE x_bis AS SELECT ... FROM x; en utilisant des CASE dans le SELECT pour modifier les choses. A titre d'exemple, pour un résultat identique, une requête CREATE TABLE AS SELECT va prendre 2 à 3h alors qu'un UPDATE peut mettre jusqu'à 5 jours...

    La problématique :
    Aujourd'hui, nous n'arrivons pas à utiliser la pleine puissance du serveur. Dès que l'on fait tourner plusieurs requêtes en parallèle (CREATE TABLE AS SELECT), PostgreSQL devient très long, même pour de simples requêtes SELECT. Et au max, on utilise 15 à 20% de la puissance CPU total et de la mémoire RAM. Le serveur est dédié à la base de données, donc nous souhaiterions changer les configurations de PostgreSQL afin qu'il puisse utiliser toute la puissance du serveur. Nous cherchons donc un peu d'aide ou quelques conseil là-dessus.

    Pour le moment, nous avons changé les configurations suivantes :
    shared_buffers = 6144MB
    checkpoint_segment = 30
    checkpoint_timeout = 10min
    effective_cache_size = 16384MB

    Nous avons essayé d'espacer les checkpoints pour limiter l'accès disque, car nous n'avons pas besoin d'une restauration "rapide" en cas de crash (nous n'avons jamais eu de crash de PostgreSQL dans tous les cas). Et au pire, même si on met des checkpoints beaucoup plus espacés et que ça nous permet de nous faire gagner 30min sur des requête qui peuvent durer 4 à 5h, c'est toujours ça de gagner, même si derrière il nous faut une heure pour redémarrer PostgreSQL suite à un crash. Mais après, nous ne sommes pas sûr que ça améliore la performance globale dans le sens où les checkpoints se font moins souvent, mais du coup, sont plus long à faire puisqu'il y a plus de données à écrire...

    Nous pensons aussi que nous pourrions gagner en rendant plus léger l'autovacuum, dans le sens où nous avons très peu, voire pas du tout d'UPDATE...

    Donc en bref, si quelques personnes expérimentées souhaitent nous donner quelques astuces ou conseil pour améliorer notre configuration, elles sont les bienvenues

    Merci d'avance.

    De toute façons, si vous faites de l'analyse de données et que vous vouliez des performances, PostGreSql ne permettant pas de faire des vues indexées, il vous faudrait l'intégralité de la base en RAM. Comme l'indique Lady, PG ne peut pas avoir un cache supérieur à 2/3 de la RAM, cela signifie grosso modo que si votre base fait 200 Go, il faudrait 300 Go de RAM pour que toutes vos requêtes soient efficace.
    Le fait de splitter les fichiers de la base sur plusieurs disques n’améliorera que très marginalement les performances.
    Enfin pour de grosses requête, PG ne sait pas faire du parallélisme, de la compression de données... Ce qui empêche de bonne performances.

    Essayez avec SQL Server... Vous verrez une différence incommensurable (tables "in memory", vues indexées, compression des données, parallélisme, index couvrant, index verticaux/ColumnStore...)
    Pour information, vous pouvez tester n'importe quelle version de SQL Server pendant 180 jours, voir utiliser la version CTP 3 de SQL Server 2016 qui ne sera pas commercialisée avant janvier 2016 :
    https://technet.microsoft.com/fr-fr/.../dn205290.aspx
    https://www.microsoft.com/en-us/serv...6/default.aspx
    Si vous le faite, je serais curieux de voir les résultats, même sur votre machine avec 32 Go de RAM... et je pense que vous aussi serez très surpris !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Rédacteur/Modérateur

    Avatar de gorgonite
    Homme Profil pro
    Ingénieur d'études
    Inscrit en
    Décembre 2005
    Messages
    10 322
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur d'études
    Secteur : Transports

    Informations forums :
    Inscription : Décembre 2005
    Messages : 10 322
    Par défaut
    Quelques remarques :
    • Posez-vous vos données en vrac dans un format proche de celui d'origine ? ou sont-elles un peu structurées pour aider les traitements ? Cela ralentit l'insertion, mais on gagne vraiment par la suite...
    • Les traitements concernent-ils systématiquement l'ensemble de la base ? Y a-t-il un moyen de faire des pré-calculs qui aideraient par la suite ?
    • Pourquoi autant de CPU et si peu de RAM ?
    • J'ai à gérer une problématique similaire (1 insertion quotidienne de qq Go de données, profondeur de 3 mois et analyses quotidiennes/hebdo/globales ; 1 suppression hebdo des données brutes trop vieilles), moins d'une dizaine de connexions simultanées, on a mis 5 fois plus de RAM, ce qui a considérablement amélioré la situation : l'utilisateur de base peut se permettre de faire des requêtes simples, depuis une IHM conçue pour, sans demander de l'aide pour optimiser et obtenir des résultats dans la minute. (cela ne change rien pour tout le reste qui doit être taillé sur mesure).


    EDIT : pour ceux qui voudraient lancer un troll sur le prix des licences, etc. ; a priori vu le prix de l'infra et le temps consommé pour avoir une solution évoluant pour coller aux besoin des utilisateurs, c'est peanuts

    Dernier point : Avez-vous une contrainte vous obligeant à rester sur des solutions libres ?
    Evitez les MP pour les questions techniques... il y a des forums
    Contributions sur DVP : Mes Tutos | Mon Blog

  5. #5
    Membre averti
    Homme Profil pro
    Responsable Informatique
    Inscrit en
    Août 2010
    Messages
    42
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Responsable Informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Août 2010
    Messages : 42
    Par défaut
    @lady,

    Merci pour la config de work_mem. Nous l'avons augmenter et nous allons voir si ça nous permet d'améliorer un peu. Concernant les disques, notre base de données est répartie sur plusieurs disques (en utilisant des tablespace). Mais nous avons acheté de nouveaux disque et nous allons monter un RAID5. Je penses que ça pourra améliorer pas mal les accès disques plutôt que de faire la répartition "à la main" dans des tablespaces.

    @SQLPro,

    Merci pour cette réponse. Étant donné que nous sommes une start-up, nous avons opté au début pour des logiciels libre afin d'économiser le prix des licences (vu que l'achat du serveur a représenter un coût très important déjà). Au début, mes collègues utilisaient MySQL, puis en arrivant, j'ai proposer l'utilisation de PostgreSQL pour déjà améliorer les perfs.

    Mais en effet, à un moment donné, je me suis demandé s'il ne fallait pas s'orienter vers une options propriétaire. Alors après, entre SQL Server, et Oracle, je ne sais pas trop. Il faudra que je fasse quelques essais en situation réel (quand j'aurais un peu de temps) En tout cas, merci pour ce point de vue.

    EDIT: En fait SQL Server, c'est mort puisqu'on a un système Ubuntu...

    @Gorgonite,

    • C'est-à-dire ? Qu'est-ce que vous appelez format en vrac ? Nous recevons les données par fichier CSV avant de les importés dans PostgreSQL. Elle ne sont pas structurées dans le sens où découpées en plusieurs tables. En générale, c'est une grosse table avec des dizaines de colonnes (donc oui, ya plein de données répétées).
    • Non, les traitements concernent en général une seule table (puisque toutes les infos sont dans uns table). En fait, chaque livraison (chaque fichier importé) est indépendant. Nous avons un fichier par projet, et donc une table par projet. Après, nous avons besoin de faire des matching de records entre plusieurs tables. Mais la plupart du temps, on ne travail que sur une table.
    • Euuh... Très bonne question. J'imagine que c'était une offre de Dell. Le serveur était déjà là lorsque je suis arrivé. Mais en fait, a un moment du projet, nous avons un gros algorithme mathématique qui va tourner sur chacun des records d'une table et y greffer de un résultat par record. Mais le calcul passe par un logiciel intermédiaire (R par exemple). Donc voilà pourquoi il y a beaucoup de CPUs. Concernant la RAM, eh bien vu qu'on n'a jamais dépassé les 30% d'utilisation, j'imagine que les 32Go nous suffisaient...

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par Vinorcola Voir le message
    EDIT: En fait SQL Server, c'est mort puisqu'on a un système Ubuntu...
    Aujourd'hui les licences Windows c'est peanuts.... Les licences SQL Server c'est juste 4 à 25 fois moins cher qu'Oracle....

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    Trois petites choses auxquelles je viens de penser pour améliorer vos performances d'insertions :
    1) journaliser de manière asynchrone : http://www.postgresql.org/docs/8.3/s...nc-commit.html
    2) n'utilisez JAMAIS du RAID 5 ou 6 ou toutes combinaison (50, 60, DP...) pour le journal de transactions. Utilisez du RAID 10 (ou 0 + 1) suivant votre carte contrôleur.
    3) séparez les fichiers de données (table_space) du fichier de journalisation sur des agrégats RAID différents...
    Lisez l'article que j'ai écrit au sujet du stockage de PostGreSQL pour comprendre de quoi je parle.... http://blog.developpez.com/sqlpro/p1...vec-postgresql
    Notamment §8.
    Enfin, assurez-vous que votre carte contrôleur disque permettent de faire du parallélisme d'accès. Certaines carte bas de gamme ne le supporte pas et les demandes d'IO sont mises en série ce qui créé de la contention...

    Dans mon livre sur SQL Server, je montre comment le mauvais choix d'une carte contrôleur sur un serveur DELL peut avoir des conséquences catastrophiques sur les performances. Et comme tous les SGBDR utilisent le même algorithme ARIES c'est aussi valable pour SQL Server...
    Extrait :
    SQL Server 2014 - CH 10 stockage carte DELL.pdf

    De mon livre :
    Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 579
Taille : 105,0 Ko

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. besoin d'aide pour configurer un serveur internet
    Par dj_techno dans le forum Réseau
    Réponses: 0
    Dernier message: 26/05/2014, 18h11
  2. aide pour configurer serveur de messagerie perso
    Par adrien239 dans le forum Windows 7
    Réponses: 0
    Dernier message: 08/12/2013, 19h25
  3. Aide pour configurer un serveur samba
    Par rigel dans le forum Administration système
    Réponses: 2
    Dernier message: 01/10/2006, 03h29
  4. [Serveur] Besoin d'aide pour configuration
    Par rigel dans le forum Ordinateurs
    Réponses: 8
    Dernier message: 19/09/2006, 10h29
  5. [configuration] lancer plusieurs serveurs Tomcat
    Par polo54 dans le forum JBuilder
    Réponses: 4
    Dernier message: 13/06/2003, 15h52

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo