Vous n'êtes pas identifié(e).
Bonjour à tous,
Voici mon soucis : Je génère ~250 millions de lignes 4 fois par jour, via un script C++ (je passe les détails inutiles 
). Ces lignes sont générées au format plat, avec COPY. Lorsque celles-ci sont écrites, j’exécute le fichier via psql pour l'insérer en bloc sur ma base. Le problème se laisse entrevoir, l'insertion en bd prend du temps ... En soit, pas forcément, mais j'aimerai que ça aille bcp plus vite (sans changer de serveur ). Il faut ~25min pour l'ensemble des lignes, soit ~160k lignes / seconde.
Comment procéder autrement et améliorer ces valeurs ? L'outil pg_restore pourrai m'aider avec l'option permettant l'exécution sur plusieurs cœurs, mais je ne vois pas comment sortir un format qui pourrait lui convenir depuis du C++.
Petites précisions, la table n'a aucune contrainte, et j'ai comme configuration celle ci-dessous :
fsync = off
synchronous_commit = off
commit_delay = 50000
checkpoint_segments = 500
checkpoint_timeout = 1h
Les logs me sortent, même avec un checkpoint à 500, un warning m'indiquant de l'augmenter encore.
Que vous dire de plus ... Le serveur est un :
Modèle: DELL® PowerEdge R210
Processeur: 1x Intel® Xeon® L3426
Architecture: 4x 1.86GHz, 64 Bits, VT
RAM: 16 Go DDR3 ECC
Disque dur: 2 x 2 To SATA2 RAID
Merci d'avance pour vos conseils.
Bonne fin de vendredi à tous !
Guillaume.
Hors ligne
La commande COPY est multi thread par défaut non ?
Hors ligne
non je me plante c'est que je lance plusieurs copy en meme temps de mon coté ^^
Hors ligne
Multi-thread surement, multi-core très bonne question kenrio !
A moins que mes logs serveur me jouent des tours, je n'ai qu'un cœur qui est utilisé pour le COPY ...
Hors ligne
je me suis fais avoir à a cause d'un modif sur slony qui a lancé une copy sur 17 tables et j'ai vu 10 coeurs à 100%.
d'où mon erreur, désolé de pas pouvoir t'aider ^^
Hors ligne
fsync = offà moins que vous vouliez corrompre votre instance, je vous conseille de le laisser à on. Sinon, postgres n'est pas multithreadé. Le moyen le plus simple serait de générer plusieurs fichiers et que vous lanciez plusieurs COPY en parallèle, mais passer le paramètre fsync à on changera la donne.
Julien.
https://rjuju.github.io/
En ligne
Merci rjuju pour ta réponse.
Lorsque tu dis "mais passer le paramètre fsync à on changera la donne", tu sous-entends quoi ? Que cela va ralentir le processus ?
Je vais voir pour faire plusieurs fichiers et les lancer parallèlement du coup.
Je suis toujours preneur de conseils et d'optimisation ! ;p
PS : n'y a t'il pas moyen (en C++) de sortir un format qui serait plus rapide à être inséré dans la base ?
Hors ligne
Pour être très clair, aucune requête n'est multihread. Même pas COPY.
Lorsque tu dis "mais passer le paramètre fsync à on changera la donne", tu sous-entends quoi ? Que cela va ralentir le processus ?
fsync à on ralentit le serveur. Mais c'est le seul moyen de ne pas avoir une base corrompue après un crash. Donc, de toute façon, la question ne se pose pas. fsync doit être à on, point.
Je vais voir pour faire plusieurs fichiers et les lancer parallèlement du coup.
Je suis toujours preneur de conseils et d'optimisation ! ;p
Ce sera à coup sûr la meilleure piste.
PS : n'y a t'il pas moyen (en C++) de sortir un format qui serait plus rapide à être inséré dans la base ?
COPY est le moyen le plus rapide pour insérer des données en masse dans la base.
Guillaume.
Hors ligne
Merci, j'avais également des problèmes pour insérer des données rapidement dans la base de données 
Je suis un passionné de guitare électrique.
Hors ligne
Une solution serait peut être d'avoir plusieurs grappes de disques.
Un ensemble où il y a la base, un autre où il y a les wal.
Après il faudrait gratter pour savoir quel est le facteurs limitant, est-ce les disques, le cpu?
Dernière modification par anayrat (03/02/2015 11:16:11)
Adrien Nayrat
DBA PostgreSQL Freelance
Hors ligne
Bonjour,
Merci à tous pour vos conseils. J'ai mise en place la technique de découpage des fichiers SQL en plusieurs bouts pour pouvoir lancer des COPY en parallèles. Ça prend encore du temps, mais c'est mieux.
Je viens redemander conseil, toujours pour les mêmes travaux, mais maintenant de façon générale.
Question : quel est la meilleure ligne à suivre pour l'environnement ci-dessous (la question est large volontairement) :
Sur un serveur "4 cores 16go ram sata 2 en disque", je dois enregistrer, à raison de 4 fois par jour, ~250 millions de lignes (données météorologiques, donc juste clé, valeur, lat, lng, date). Soit un total ~1 milliard de lignes / jour. Il faut que l'enregistrement soit le plus rapide possible, afin que les données soient à disposition de tous. La récupération de ces données, par la suite, (select) doit également être très rapide, car à destination d'applications mobiles.
Comment vous procéderiez ? Quelle architecture ? Etc.
Mon idée première : découper mes données, que ce soit aussi bien pour l'insert (permettant de paralléliser les COPY), que pour peupler ma base. Je serai partant pour peupler ma base dans de multiples tables (~2000), à l'aide de trigger par exemple, afin que lors de select, avec les latitudes et longitudes (chaque données est rattaché à ce couple) les valeurs soient directement prises dans la bonne table.
Qu'en pensez-vous ?
Un grand merci pour vos conseils en tout cas !
Bonne soirée à tous.
Hors ligne
Les triggers sont certainement le meilleur moyen de perdre en performance.
Guillaume.
Hors ligne
Merci pour ta réponse gleu.
Mais du coup, quelle serait la meilleure solution ? Car je comptais utiliser les triggers pour spliter mes données automatiquement. Ce qui, par la suite, permettrait d'accélérer les vitesses d'accès.
J'avoue que je n'ai plus de solutions. Dans un monde "tout rose", serait-il possible d'écrire directement depuis le C++ les données en "format" postgres ? Ce qui éviterait l'écriture dans un fichier préalable pour ensuite les insérer dans la base. Mais est-ce que le gain sera intéressant ?
Pour te répondre anayrat, je ne peux pas changer mon environnement matériel 
Merci à tous pour vos aides.
Hors ligne
Mais du coup, quelle serait la meilleure solution ?
Écrire directement dans la bonne partition.
Dans un monde "tout rose", serait-il possible d'écrire directement depuis le C++ les données en "format" postgres ?
Non, pas possible. Et ça ne serait pas un monde tout rose de toute façon
Si je comprends bien, vous avez un programme C++ qui écrit un fichier sur disque qu'il envoie à psql pour insérer les données ? autrement dit, vous écrivez deux fois les données ? si c'est bien ce que j'ai compris, pourquoi ne pas faire en sorte que le programme C++ se connecte à PostgreSQL et utilise le protocole COPY pour y insérer directement les données, sans passer par ce fichier ?
Guillaume.
Hors ligne
Merci gleu pour tes réponses.
Écrire dans la bonne partition, c'est déjà le cas je crois, car ma base et mon programme tourne sur la même partition (si j'ai bien compris ta question ).
Bien vu pour l'API C++ ! Je vais essayer ça oui.
Merci de tes conseils.
Hors ligne
Toujours pour le même environnement, avec l'outil ANALYSE et EXPLAIN j'ai ceci :
// Temps=# explain analyse select * from last_run_025 where latitude = -26 and longitude = 359.75;
// Index Scan using lat_lng_idx on last_run_025 (cost=0.00..918.66 rows=218 width=71) (actual time=0.050..1.284 rows=238 loops=1)
// Index Cond: ((latitude = (-26)::double precision) AND (longitude = 359.75::double precision))
// Total runtime: 1.340 ms
// Temps=# explain analyse select * from last_run_025 where latitude = -34 and longitude = 339.75;
// Index Scan using lat_lng_idx on last_run_025 (cost=0.00..1859.32 rows=451 width=71) (actual time=0.058..1.115 rows=238 loops=1)
// Index Cond: ((latitude = (-34)::double precision) AND (longitude = 339.75::double precision))
// Total runtime: 1.162 ms
Sur ma table, j'ai un index btree(latitude, longitude).
Pourquoi je n'ai pas le même coût entre les deux requêtes ? Serait-il plus performant d'avoir une clé de hash (lat+lng, mais non unique du coup) et de l'indexer seule ?
Idem, ci-dessous (les deux requêtes n'ont jamais été effectuées auparavant) :
// Temps=# explain analyse select * from last_run_025 where latitude = -34 and longitude = 300.75;
// Index Scan using lat_lng_idx on last_run_025 (cost=0.00..1224.16 rows=293 width=71) (actual time=12.852..1497.365 rows=238 loops=1)
// Index Cond: ((latitude = (-34)::double precision) AND (longitude = 300.75::double precision))
// Total runtime: 1497.595 ms
// Temps=# explain analyse select * from last_run_025 where latitude = -34 and longitude = 200.75;
// Index Scan using lat_lng_idx on last_run_025 (cost=0.00..1742.74 rows=422 width=71) (actual time=16.308..1560.096 rows=238 loops=1)
// Index Cond: ((latitude = (-34)::double precision) AND (longitude = 200.75::double precision))
// Total runtime: 1560.307 ms
Pourquoi le coût diffère ? Pourquoi le temps d’exécution est-il aussi important d'un coup ? Il est alors assez difficile de mesurer les performances pour les améliorer ...
A raison de 250 millions de lignes indexées sur une seule table, je me demande le gain de performances à la spliter entre plusieurs tables en fonction des lat/lng ...
Merci pour vos futures réponses qui m'aident grandement !
Bonne fin de journée à tous.
Hors ligne
Pourquoi je n'ai pas le même coût entre les deux requêtes ?
Le nombre de lignes estimées est doublé, le coût est doublé. Ça semble cohérent.
Serait-il plus performant d'avoir une clé de hash (lat+lng, mais non unique du coup) et de l'indexer seule ?
Je ne vois rien qui laisse supposer que ce serait mieux. Mais testez, c'est le meilleur moyen de savoir.
Pourquoi le coût diffère ?
Pareil, plus de lignes, donc plus de coût.
Pourquoi le temps d’exécution est-il aussi important d'un coup ?
Il peut y avoir plus de données mortes au niveau de l'index, qui font que PostgreSQL doit lire plus de blocs. Il peut y avoir plus d'activités de PostgreSQL (un autovacuum au hasard) comme d'autres processus. Difficile à dire sur une exécution.
Guillaume.
Hors ligne
Réponse ultra rapide, c'est top ! Merci à toi gleu
Ci-dessous, un count des deux premières requêtes.
//select count(*) from last_run_025 where latitude = -26 and longitude = 359.75;
// count 238
//select count(*) from last_run_025 where latitude = -34 and longitude = 339.75;
// count 238
Et comme indiqué dans mon précédent post, j'obtiens :
// (cost=0.00..918.66 rows=218 width=71) (actual time=0.050..1.284 rows=238 loops=1)
et
// (cost=0.00..1859.32 rows=451 width=71) (actual time=0.058..1.115 rows=238 loops=1)
Le premier rows est bien doublé comme tu l'as indiqué gleu, mais pas le "real" (si j'ai bien compris).
En tout état de cause, le nbre de lignes retourné est bien identique pour les deux requêtes. Du coup, où se fait la différence de coût ? ...
Mes excuses si j'ai du mal à tout comprendre et un grand merci pour ton aide.
=> A raison de 250 millions de lignes indexées sur une seule table, je me demande le gain de performances à la spliter entre plusieurs tables en fonction des lat/lng ...
Au passage, penses-tu que le gain serait intéressant ?
Encore merci gleu !
Hors ligne
En tout état de cause, le nbre de lignes retourné est bien identique pour les deux requêtes. Du coup, où se fait la différence de coût ? ...
Le coût est calculé en fonction du nombre de lignes estimés (et de plein d'autres paramètres/statistiques). Le coût est indiqué par la partie "cost=". Je vois 918 pour le premier et 1859 pour le deuxième (on va s'épargner les chiffres après la virgule...). Donc un facteur 2. Comme la ratio dans les lignes entre les deux EXPLAIN. Ces deux coûts sont des estimations.
Ensuite, les deux durées (actual time) sont pratiquement identiques parce que même nombre de lignes et coup de bol aussi.
=> A raison de 250 millions de lignes indexées sur une seule table, je me demande le gain de performances à la spliter entre plusieurs tables en fonction des lat/lng ...
Au passage, penses-tu que le gain serait intéressant ?
Tout dépend de ce qu'on appelle splitter ? si c'est partitionner, c'est pas les 250 pauvres millions de lignes qui sont un argument suffisant pour ça.
Il faut plutôt causer purge, maintenance, ressources matérielles.
Si on en revient au 1 milliard de lignes par jour, qu'on soit clair. Le problème ne sera pas partitionnement ou pas. Le problème sera les ressources disques.
Guillaume.
Hors ligne
Bonjour gleu et encore merci,
Ne pouvant pas changer mes ressources matériels pour le moment, l'idée du partitionnement m'était venue à la tête. Car une requête brute sur cette table (non partitionnée) donne un temps d'exécution de :
Temps=# select * from last_run_025 where latitude = 57.50 and longitude = 33.25;
Temps : 1552,946 ms
Brute = le première fois, la fois suivante, il organise (normal et heureusement) les données pour me les ressortir beaucoup plus rapide (avec les mêmes lat/lng).
Le soucis vient du fait que jamais ces lat/lng ne seront les mêmes. Elles sont rattachées à la localisation des utilisateurs, et les utilisateurs seront partout (app mobile).
Les données couvrent la surface terrestre, partitionner cette zone en sous-zones et créer une table pour chaque zone permettrait de diviser les temps d'exécution par autant non ? Imaginons 100 zones lat/lng, lors d'un select user, la requête ne sera effectuée que sur la table de la zone, donc sur 2,5 millions de lignes (250 millions / 100). Est-ce que le gain est trop faible par rapport à une unique table ? La création de 100 ou 1000 tables, une par zone, m'ennuie aussi, car je trouve ça un peu "sale" ... Mais plus d'1,5 s pour un select c'est long, très long. Même pour 250 millions de lignes ça me parait long avec un btree sur lat/lng ... Surtout après ta remarque "250 pauvres millions de lignes"
Que de questions, j'en conviens, désolé gleu
Dernière modification par gchanteloube (11/02/2015 11:16:11)
Hors ligne
Brute = le première fois, la fois suivante, il organise (normal et heureusement) les données pour me les ressortir beaucoup plus rapide (avec les mêmes lat/lng).
Soyons précis Il n'organise rien. Elles sont en mémoire et il est plus rapide de lire en mémoire que sur disque.
Les données couvrent la surface terrestre, partitionner cette zone en sous-zones et créer une table pour chaque zone permettrait de diviser les temps d'exécution par autant non ?
Non. Tout dépend si cette donnée est déjà en mémoire ou pas. Vu que vous ne pouvez pas changer les ressources matérielles, votre mémoire restera aussi peu importante, ce qui fait que, si vos utilisateurs passent leur temps à lire des données différentes, vos performances seront toujours aussi mauvaises.
Est-ce que le gain est trop faible par rapport à une unique table ?
Vous faites un parcours d'index. La volumétrie réelle de la table a peu d'importance dans ce cas. Donc une table ou 20000, pas de différence réelle.
Mais plus d'1,5 s pour un select c'est long, très long.
Il sort d'où, ce 1;5 seconde ? tous les temps que vous nous avez montré sont inférieures à 2 millisecondes. (je viens de voir que c'est l'exemple du post... dommage pour moi ) C'est 1,5s à froid.
Pour les "250 pauvres millions de lignes", je préfère préciser que, pour PostgreSQL, 250 millions de lignes, c'est rien. Peanuts. Nada. Évidemment, il faut du matériel qui va bien. Mais en soi, il sait traiter rapidement des volumétries plus importantes.
À moi de poser une question, serait-il possible d'avoir le résultat de la requête suivante :
EXPLAIN (ANALYZE, BUFFERS) select * from last_run_025 where latitude = 57.50 and longitude = 33.25;
Merci.
Guillaume.
Hors ligne
Bonsoir gleu,
Réponse plus que rapide (réponse à froid, sans recherche préalable), j'ai une erreur de syntaxe sur ta requête.
ERREUR: erreur de syntaxe sur ou près de « ANALYZE »
LIGNE 1 : EXPLAIN (ANALYZE, BUFFERS) select * from last_run_025 where ...
J'éditerai après recherche si je trouve pourquoi
Edit 1 : version postgres 8.4 (okay, ma version est assez vieille j'en conviens )
Edit 2 : les buffers ne sont disponibles que depuis la version 9 ... Le serveur tournant sur la version 6 de Debian je ne peux mettre à jour automatiquement postgres (seule la version 8.4 est dans les dépôts de cette version) sans le compiler et le processus de compilation implique de transférer les anciennes bases. Ce processus est plus que lourd. Je vais reconsidérer le problème et reviens dès que j'ai une solution convenable.
Dernière modification par gchanteloube (11/02/2015 22:31:26)
Hors ligne
La version 8.4 n'est pas vieille. Elle n'est plus maintenue. La discussion est terminée (je rigole, hein... ceci dit, c'est pas totalement faux... chercher des performances sur une version datant de 2009... comment dire... bon courage...).
La seule solution convenable est apt.postgresql.org.
Guillaume.
Hors ligne
Bonsoir,
Après un très fort crash du serveur en prod, les fichiers de journalisation de la version 8.4 ont été perdu ...
Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2015-02-12 19:06:55 CET LOG: le système de bases de données a été arrêté à 2015-02-12 18:22:32 CET 2015-02-12 19:06:55 CET LOG: n'a pas pu ouvrir le fichier « pg_xlog/00000001000001280000000F » (journal de transactions 296, segment 15) : Aucun fichier ou dossier de ce type 2015-02-12 19:06:55 CET LOG: enregistrement du point de vérification primaire invalide 2015-02-12 19:06:55 CET LOG: n'a pas pu ouvrir le fichier « pg_xlog/00000001000001280000000F » (journal de transactions 296, segment 15) : Aucun fichier ou dossier de ce type 2015-02-12 19:06:55 CET LOG: enregistrement du point de vérification secondaire invalide 2015-02-12 19:06:55 CET PANIC: n'a pas pu localiser un enregistrement d'un point de vérification valide 2015-02-12 19:06:55 CET LOG: processus de lancement (PID 16539) a été arrêté par le signal 6 : Aborted 2015-02-12 19:06:55 CET LOG: annulation du démarrage à cause d'un échec dans le processus de lancement ... failed!
failed!
Existe t-il un moyen pour relancer la base sans eux ?
La version 9.4 a été installée, la restauration est à cette heure finie pour le problème de ce topic. Il me reste à poser l'INDEX btree. Cependant, avant toutes autres choses, j'aimerai pouvoir refaire partir le 8.4 ... rhaaa (j'ai bien peur que ce ne soit pas possible ...)
Edit : je mets la solution au problème de fichiers de journalisation corrompu
pg_resetxlog /var/lib/postgresql/8.4/main/
Cela ré-initialise ces fichiers, la base peut alors se relancer. (En tant qu'admin gleu, n'hésites pas à me demander d'ouvrir un autre topic pour poster ce bug particulier, si cela peut aider plus de monde).
Dernière modification par gchanteloube (12/02/2015 22:31:54)
Hors ligne
Attention, pg_resetxlog permet de faire redémarrer l'instance, mais avec une très forte probabilité de données incohérentes voire corrompues. Je ne sais pas ce que vous comptez faire des données de l'instance en 8.4, mais vous devriez les vérifier avant toute utilisation.
Julien.
https://rjuju.github.io/
En ligne