[Table Enormes] Demande de renseignements / 'Best practices'

blietaer · 04/10/2011 10:15:05

Bonjour,

Je m’attelle tout doucement à un projet de monitoring réseau.

En très bref: il s'agit de sniffer des paquets à différents endroits et de les stocker, consolider, agréger et présenter.
Cette dernière présentation se fait de manière passive (dashboard), interactive (graphes clickables/zoomables) et automatisée (rapport PDFs émis daily, weelky, nightly,..)
Cela fait bcp de données (=paquets IP), mais pas très lourdes (on ne garde qu'une signature du header+timestamp+endroit où il a été vu)

Je commence par me demander la validité de 'LAMP' ici, (ou plutôt 'LAPP', puisque PostgreSQL est présenti ici plutôt que MySQL)

L'idée est qu'un utilisateur qui se connecte à l'interface arrive d'abord sur une page de type 'dashboard' avec un apperçu de l'état du réseau (latence, capacité, volume,..) assez générique, après, on peut imaginer des onglets avec des graphes plus interactif qui permettent de remonter dans le temps, zoomer, drill-down,...
La couche d'accès à la DB se ferait par quelque chose de propre, genre un beau Framework PHP/MVC, avec un DBAL/ORM (genre Doctrine2)

Le nerf de la guerre: les I/O et la fluidité de l'interface en général.
Les questions pour lesquelles je serais ravis de partager vos expériences/conseils/remarques:

I. La structure de la DB:
a.) Le choix de PostgreSQL jsutement: est-ce que les outils libres SQL sont valables?
On dit souvent que des recherches sont plus rapides sur des flat-files, la complexité ici est de permettre une dimension en plus: l’agrégation des données (stored/trigger procedure?) Nos tables sont clairement relativement maigres (une 20aine de champs) mais kilométrique (imaginons que l'on garde les données des paquets jusqu'il y a un mois!)

b.) L’agrégation justement: bien que je ne l'ai jamais fait, il semble que l'on peut rendre une DB 'vivante' avec des triggers et des nested routines, qui spontanément vont faire du 'house-keeping' des données, soit directement à l'insert, soit plus tard, selon une date/durée d'expiration.
Est-ce intéressant? efficace? Mieux vaut tout laisser en granularité maximale et laisser la partie 'query & display' faire les arrangements?
Est-ce franchement abérant de vouloir tout mettre dans une table? mieux vaut passer à une table par jour et possibilité de 'LEFT JOIN' si jamais on fait des recherches sur une nuit qui couvre 2 tables?

II. Le support HW:
c.) Bien que l'idée soit de partir d'un serveur "moyen" ici sous la main (raid-SAS, 10aine de Go RAM, 4/8 cores), je me demande s'il y a moyen de gagner en I/O autrement.
J'ai découvert (ici même) qu'il y avait moyen de balancer toute la DB en RAM au boot. Malin? Bricolage? limitation?
Est-il possible de ne mettre en RAM unquement qu'une partie (table des données du jour, par ex.) ?
Y a-t-il des nouvelles révolutions en la matière? (les fameux clouds? le faite de taper 2 de nos serveurs ensemble, en //?)

III. L'extraction des données:
d.) Ecrire sa propre petite librairie PHP pour faire des requêtes SQL semble un sport spécifique et pourtant rendu très automatique par les ORM/DBAL en PHP: je me dis que cette communautée a certainement plus d'expérience que moi (même si je fait du PHP/SQL depuis +/-8ans) et que leur requêtes générées seront plus optimisées. Mais tiendront-elles compte de la taille de mes tables?
Est-ce vraiment la panacée dans mon cas?

Merci beaucoup pour vos éclaircissements.

Bien à vous,
Bli

SAS · 04/10/2011 10:42:18

blietaer a écrit :

I. La structure de la DB:
a.) Le choix de PostgreSQL jsutement: est-ce que les outils libres SQL sont valables?

Oui.
Et vous pouvez également prévoir de partitionner vos tables pour éviter qu'elles ne grossissent démeusurément.

blietar a écrit :

b.) L’agrégation justement: bien que je ne l'ai jamais fait, il semble que l'on peut rendre une DB 'vivante' avec des triggers et des nested routines, qui spontanément vont faire du 'house-keeping' des données, soit directement à l'insert, soit plus tard, selon une date/durée d'expiration.
Est-ce intéressant? efficace? Mieux vaut tout laisser en granularité maximale et laisser la partie 'query & display' faire les arrangements?
Est-ce franchement abérant de vouloir tout mettre dans une table? mieux vaut passer à une table par jour et possibilité de 'LEFT JOIN' si jamais on fait des recherches sur une nuit qui couvre 2 tables?

Cela dépend grandement de la présentation que vous voulez faire de vos données. Si de gros calculs sont à prévoir pour les afficher, il sera préférable d'envisager de les calculer préalablement. Tiggers, cron... à vous de voir.

II. Le support HW:
c.) Bien que l'idée soit de partir d'un serveur "moyen" ici sous la main (raid-SAS, 10aine de Go RAM, 4/8 cores), je me demande s'il y a moyen de gagner en I/O autrement.
J'ai découvert (ici même) qu'il y avait moyen de balancer toute la DB en RAM au boot. Malin? Bricolage? limitation?
Est-il possible de ne mettre en RAM unquement qu'une partie (table des données du jour, par ex.) ?
Y a-t-il des nouvelles révolutions en la matière? (les fameux clouds? le faite de taper 2 de nos serveurs ensemble, en //?)

On peut envisager de nombreuses méthodes d'accélérer les accès. Si votre serveur est fourni en RAM et la volumétrie faible, le cache système jouera.
Ensuite, vous pouvez prévoir de travailler en RAM, mais un crash du serveur, et vos données se volatilisent...

III. L'extraction des données:
d.) Ecrire sa propre petite librairie PHP pour faire des requêtes SQL semble un sport spécifique et pourtant rendu très automatique par les ORM/DBAL en PHP: je me dis que cette communautée a certainement plus d'expérience que moi (même si je fait du PHP/SQL depuis +/-8ans) et que leur requêtes générées seront plus optimisées. Mais tiendront-elles compte de la taille de mes tables?
Est-ce vraiment la panacée dans mon cas?

Le sirop Typhon, en terme de requêtes, est probablement à éviter. Un oeil qui connaît les tables et leur volumétrie a toujours sa place dans le développement des requêtes.

Marc Cousin · 04/10/2011 10:45:18

a) Ça dépend vraiment de ce que vous souhaitez faire de la base de données. L'exemple des triggers plus bas est assez bon: programmer l'équivalent à la main sur des fichiers plats va être très long et pénible (et probablement plein de bugs au début). Par contre, les bases de données (SQL ou non d'ailleurs) ont de nombreux coûts… par exemple au niveau du stockage, on se paye des entêtes par bloc, par enregistrement… qui font grossir les données. Les fichiers plats peuvent être plus rapides, tant qu'il n'y a qu'une seule relation, qu'on n'a pas de critères complexes de recherche, qu'on n'a pas besoin de voir d'instantané des données, de forcer des contraintes d'intégrité… et d'expérience, on finit toujours par se rendre compte qu'on en a besoin

b) Pour ce qui est de calculer des agrégats au fur et à mesure de l'insertion des données, c'est évidemment à double tranchant: l'interrogation est plus rapide, l'insertion plus lente. Il y a évidemment aussi des solutions 'intermédiaires', soit en recalculant l'agrégat à intervalle régulier à partir de zéro, soit en le recalculant à intervalle régulier à partir de ce qui a bougé (en entretenant une log de ce qui a bougé par exemple). Évidemment, à chaque fois, ça fait un peu de programmation.

Ce n'est pas forcément aberrant de tout mettre dans une table, ça peut simplifier l'administration et le développement. Mais ça aura certainement un impact sur les performances si le volume devient important. Par ailleurs, si il y a plusieurs partitions, on ne les agrège pas avec des LEFT JOIN mais des UNION ALL habituellement. Ou sous PostgreSQL, on les déclare comme héritant toutes de la même table mère, avec des contraintes check (c'est bien expliqué dans la doc), et on laisse postgresql faire le boulot. Il vaut d'ailleurs mieux être en 9.1 si on part dans cette direction (il y a des améliorations importantes de l'optimiseur).
Pour ce qui est du partitionnement, on peut imaginer le faire par date, ou par sonde, objet, etc… suivant ce qu'on va le plus souvent rechercher, ou suivant ce qu'on va vouloir purger. Le partitionnement par date est très pratique pour dire qu'on veut purger des vieilles données: il suffit de détruire les vieilles partitions. Par contre, il peut être très inefficace si on cherche toutes les informations concernant un équipement sur une longue période de temps (beaucoup de partitions à lire). Le choix de la partition dépend vraiment de ce qu'on veut en faire.

Un dernier point: plus il y a de partitions, plus le temps de planification des requêtes devient élevé. Ça peut devenir très cher au dessus de quelques dizaines à quelques centaines de partitions à l'heure actuelle.

c) La base en ram est évidemment plus rapide: le temps d'accès d'un disque dur est de l'ordre de 1ms, celui de la ram de l'ordre de 1ns. Encore faut il avoir assez de ram, ce qui n'est évidemment pas souvent possible. Par ailleurs, les écritures ne seront pas tellement accélérées par cela, puisqu'il faudra bien que les données arrivent sur le disque à un moment ou l'autre.
Pour ce qui est de mettre en ram uniquement les données courantes, c'est déjà ce que fait le moteur de lui-même, et le système d'exploitation aussi d'ailleurs.

On peut faire de la parallélisation sur plusieurs bases avec PostgreSQL, avec GridSQL ou peut-être Postgres-XC (je n'ai pas suivi l'avancement du projet dernièrement). Mais ce n'est peut-être pas la peine de partir dans cette direction avant d'avoir mal…

d) Méfiez vous des ORM. La plupart du temps, les requêtes qu'ils fabriquent ne sont pas si bonnes que ça, et surtout vous perdez une bonne partie des leviers d'optimisation que vous pouvez avoir si vous écrivez vous-même vos requêtes. Les ORM fonctionnent assez bien quand il s'agit de reconstituer un objet avec quelques requêtes unitaires, mais montrent leurs limites quand on veut travailler sur des jeux de données plus importants.

gleu · 04/10/2011 10:56:54

Allez, je réponds moi-aussi

En très bref: il s'agit de sniffer des paquets à différents endroits et de les stocker, consolider, agréger et présenter.

Ça ressemble beaucoup à ce que faisait Edenwall avec le projet Immun et ulogd. Et ça tournait très bien sur du PostgreSQL.

I. La structure de la DB:
a.) Le choix de PostgreSQL jsutement: est-ce que les outils libres SQL sont valables?

Complètement valable, au moins en ce qui concerne PostgreSQL. C'est un outil utilisé par de nombreuses sociétés, dans des utilisations particulièrement critiques (échange bancaire, système de sécurité, ...). Il supporte très bien les grosses volumétries, et un grand nombre d'utilisateurs.

On dit souvent que des recherches sont plus rapides sur des flat-files, la complexité ici est de permettre une dimension en plus: l’agrégation des données (stored/trigger procedure?)

Oui, les triggers et les procédures stockées sont gérés par PostgreSQL depuis longtemps.

Nos tables sont clairement relativement maigres (une 20aine de champs) mais kilométrique (imaginons que l'on garde les données des paquets jusqu'il y a un mois!)

Vous avez une vraie idée de la volumétrie en lignes, comme en espace disque ?

b.) L’agrégation justement: bien que je ne l'ai jamais fait, il semble que l'on peut rendre une DB 'vivante' avec des triggers et des nested routines, qui spontanément vont faire du 'house-keeping' des données, soit directement à l'insert, soit plus tard, selon une date/durée d'expiration.
Est-ce intéressant? efficace? Mieux vaut tout laisser en granularité maximale et laisser la partie 'query & display' faire les arrangements?

C'est une possibilité qui est intéressante, mais pour un maximum d'efficacité, il est généralement préférable de lancer des outils régulièrement qui feront le nettoyage dont vous parlez ou du précalcul.

Est-ce franchement abérant de vouloir tout mettre dans une table? mieux vaut passer à une table par jour et possibilité de 'LEFT JOIN' si jamais on fait des recherches sur une nuit qui couvre 2 tables?

Tout dépend de votre volumétrie. Il est aussi possible d'utiliser du partitionnement. Dans ce cas, des tests de perfs sont à prévoir car c'est assez à double tranchant.

II. Le support HW:
c.) Bien que l'idée soit de partir d'un serveur "moyen" ici sous la main (raid-SAS, 10aine de Go RAM, 4/8 cores), je me demande s'il y a moyen de gagner en I/O autrement.
J'ai découvert (ici même) qu'il y avait moyen de balancer toute la DB en RAM au boot. Malin? Bricolage? limitation?
Est-il possible de ne mettre en RAM unquement qu'une partie (table des données du jour, par ex.) ?
Y a-t-il des nouvelles révolutions en la matière? (les fameux clouds? le faite de taper 2 de nos serveurs ensemble, en //?)

Encore une fois, tout dépend du contexte. Si vous avez beaucoup d'utilisateurs et des utilisateurs très consommateurs, 8 cores est un minimum. Si vous voulez des I/O, utilisez du RAID-10, le RAID5 ne fera que vous ralentir lors des écritures.

Concernant la RAM, PostgreSQL se repose beaucoup sur le système. Pour les grosses bases, généralement, tout ne tient pas. PostgreSQL s'assure que les données les plus utilisées sont en cache.

Quant aux clouds, certainement génial pour l'administration, et tout aussi certainement mauvais pour les performances, notamment si les applications ne sont pas dans le cloud.

III. L'extraction des données:
d.) Ecrire sa propre petite librairie PHP pour faire des requêtes SQL semble un sport spécifique et pourtant rendu très automatique par les ORM/DBAL en PHP: je me dis que cette communautée a certainement plus d'expérience que moi (même si je fait du PHP/SQL depuis +/-8ans) et que leur requêtes générées seront plus optimisées. Mais tiendront-elles compte de la taille de mes tables?
Est-ce vraiment la panacée dans mon cas?

Les ORM génèrent généralement des requêtes peu performantes. Personnellement, j'essaierai de m'en affranchir ou je m'assurerai que je puisse faire mes propres requêtes via l'ORM si nécessaire.

blietaer · 04/10/2011 12:41:09

Pardon de sonner un peu 'mieleux/démago', mais je voudrais vous remercier pour votre rapidité, professionalisme et votre courtoisie (se faire 'vouvoyer' sur un forum, cela commençait à me manquer sérieusement...)

Je vous réponds un peu en vrac:
- l'idée est donc vraiment d'aboutir, soit à des rapports générés spontanément (daily, weekly,..) en PDFs (donc la vitesse n'importe peu), soit via l'interface Web, relativement sobre, mais si possible des plus fluides, utilisant des technologies assez récente HTML/5, CSS/3 et JQuery pour faire des graphes vraiment interactifs (zoom, drill-down,....)
Les drill-down/zooms doivent justement permettre de gagner en granularité: on ne voit pas les points plus gros en zoomant, mais on en voit plus (= une autre définition), je trouve que c'est particulièrement là que le bas blesse.

- Je ne connaissais pas EdenWall/immun, très intéressant mais nous nous situons plutôt dans une démarche de monitoring/perf. que de sécurité ici.

- Oui, bien sûr, le lien entre les tables se fait par UNION et non pas des JOIN, pardonnez cet égaremment

- Merci de m'avoir éclairé sur le double tranchant des trigger-procedures: en effet, on a des heures pour faire rentrer tous les fichiers SQL et des minutes pour afficher des graphes...c'est pas vivable. Cela dit, les données en production arriveraient au fur et à mesure dans la DB, donc l'entrée pourrait être lente, ce n'est pas très grave. Mais la lecture des données (agrégées ou pas) doit Être vraiment immédiate.

- L'existant: nous avons un PostgreSQL configuré à la Warrior, avec des stored procedure et 25 tables de 100KB à 1MB, sauf une (les paquets) qui fait + d'une 20aine de GB (réparti 50-50% entre la Table Size et l'indexes Size), avec seulement 6 champs pourtant.
Les requêtes SQL sont actuellement faites maison avec PHP, une vilaine couche d'HTML et le tout servi en graphiques SVG (à abandonner au plus vite)

CREATE TABLE packets
(
pkid bigserial NOT NULL,
flid smallint,
pktime timestamp without time zone,
pksig bytea,
pksize smallint,
pkflag character(1),
CONSTRAINT packets_pkey PRIMARY KEY (pkid),
CONSTRAINT packets_flid_fkey FOREIGN KEY (flid)
REFERENCES flows (flid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT packets_flid_key UNIQUE (flid, pktime, pksig)
)

gleu · 04/10/2011 12:51:09

Je ne connaissais pas EdenWall/immun, très intéressant mais nous nous situons plutôt dans une démarche de monitoring/perf. que de sécurité ici.

Oui. Cela étant dit, vous stockez en gros les mêmes données, avec de grosses volumétries en jeu.

Concernant votre volumétrie, c'est tout à fait jouable avec PostgreSQL.

SQLpro · 10/10/2011 16:19:35

Bonjour,

dès que l'on commence à parler volumétrie et en particulier tables avec beaucoup de lignes (plusieurs millions, voire beaucoup plus), il faut penser à différentes techniques, parmis lesquelles :
1) hypernormaliser sa base : cela peut être fait quelque soit le choix du SGBDR. A lire : Base de données et performances... petites tables et tables obèses !
2) le partitionnement des données : vous pouvez stocker toutes vos données dans une seule et même table et faire vivre différemment vos partitions. La PostGreSQL n'est pas le plus adéquat notamment en terme de souplesse de mise en œuvre. A lire Partitionner une table. Comparaison PostGreSQL / MS SQL Server(Oracle, DB2, SQL Server le font proprement)
3) la mise en place de vues matérialisées ou indexées. Là c'est clair, cela n'existe pas sous PostGreSQL ou il faut tout faire à la main. Ce genre de technique est très intéressante sur des calcules d'agrégats (SUM, COUNT, AVG...) (Oracle ou SQL Server le fait)
4) une gestion efficaces des espaces de stockage. Là non plus PostGreSQL ne sait pas faire : pas de pré allocation, pas de parallélisme d'accès pour les IO... (Oracle, SQL Server, SYbase et DB2 le font).

En sus, si vous voulez faire des rapports avec du drill down et de présenter des données sous forme de tableaux croisés multidimensionnel et que vous voulez que vos utilisateurs puissent à leur gréé modifier les croisements de données, il faut vous orienter vers un SGBDD (décisionnel) plutôt que R. Et là PostGreSQL n'est pas du tout adapté à des forts volumes car il ne sait faire que du ROLAP. Avec un fort volume de données à brasser, il faut un stockage spécifique au décisionnel donc une base MOLAP en passant éventuellement par un DataWarehouse.
Si en sus vous voulez envoyer vos rapport de manière automatique il faut un gestionnaire de reporting permettant les abonnement et probablement une gestion des droits associés (n'importe qui ne peut pas s'abonner à n'importe quoi).

Bref, je pense que vous devriez vous orienter plus sur une solution de BI intégrant DW + cube décisionnel avec l'attirail serveur de rapport, outils de forage, ETL.... Et dans ce cas MS SQL Server est l'une des solutions les plus complète et les moins cher...http://www.legrandbi.com/2011/01/gartne … t-bi-2011/

Quelques remarques :

NON les recherches ne sont pas plus rapide dans un flat file. elle le sont beaucoup moins. L'indexation, le partitioneemnt et les vues indexeées sont l'apanage des SGBDR !
En revanche les insertions dans des fichiers plats sont plus rapide, sauf si les fichiers de la base sont multiplexés et en particulier le journal de transactions (point de passage obligé de toute mise à jour) => RAID 10 avec plus ou moins de disque...

Pour l'agrégation par vues indexées/matérialisées Oracle fonctionne en mode asynchrone tandis que SQL Server le fait en mode synchrone.

Pour les IO oui, vous pouvez gagner énormément en organisant correctement vos fichiers et vos disques si le SGBDR sait gérer correctement les storages. C'est le cas d'Oracle et SQL Server. A lire sur le sujet : Question sur les fichiers et le stockage des données dans les SGBDR C/S

Les SGBDR fonctionnent exclusivement en RAM. Toutes les manipulations de données se font en mémoire. Le disques n'étant en fait qu'un épiphénomène, car on ne sait toujours pas fabriquer des RAM non volatile. Les SGBDR commencent par travailler d'abord en mémoire et écrivent le minimum de données de manière synchrone (transaction uniquement en mode WAL) et repoussent l'écriture des données en rafales par sessions de manière optimisées (recherches du trajet minimal pour la tête de lecture du disque lorsque c'est possible - DB2, Oracle, SQL Server par exemple - pas PostGreSQL).
La mise en RAM se faisant en mode LRU (Last Recent Use) : plus une données à été récemment utilisée, plus il est probable qu'elle soit rapidement réutilisées. Pour cela les SGBDR (PG compris) maintiennent des statistiques d'exécution.

Le parallélisme des SGBDR par "scaling out" est une illusion, car il faudrait faire en sorte que les données soient simultanément écrites sur tous les serveurs. Et ce travail pénaliserait de beaucoup les lectures. La duplication des données doit donc être réservée à des cas précis et particulier...

L'utilisation des ORM est le plus sur moyen d'avoir les performances les plus catastrophiques possible. Lisez ce que j'ai écrit à ce sujet : Darwinisme et informatique : les ORM et les frameworks survivront-ils au concept de développement en base de données épaisse

Enfin, lorsque l'on doit travailler sur des masses de données importantes il faut qu'une même requête puisse être multithraadée. Ce n'est pas le cas de PG qui ne sait pas faire.

A +

Dernière modification par SQLpro (10/10/2011 16:27:25)

gleu · 10/10/2011 19:34:14

Encore une fois, je ne suis pas d'accord avec vous sur le partitionnement des données et sur ce que vous appelez la gestion du stockage. Le reste est un ensemble de buzzwords que vous associez à Oracle et SQL Server. Beau post de pub pour eux.

En fait, le seul point sur lequel on est d'accord est les ORM. Et encore...

Marc Cousin · 11/10/2011 07:12:03

Ah, et au passage, LRU, ça veut dire Least Recently Used. Last Recent Use ça ne veut rien dire.

SAS · 11/10/2011 10:49:03

blietaer a écrit :

- L'existant: nous avons un PostgreSQL configuré à la Warrior, avec des stored procedure et 25 tables de 100KB à 1MB, sauf une (les paquets) qui fait + d'une 20aine de GB (réparti 50-50% entre la Table Size et l'indexes Size), avec seulement 6 champs pourtant.
Les requêtes SQL sont actuellement faites maison avec PHP, une vilaine couche d'HTML et le tout servi en graphiques SVG (à abandonner au plus vite)

CREATE TABLE packets
(
pkid bigserial NOT NULL,
flid smallint,
pktime timestamp without time zone,
pksig bytea,
pksize smallint,
pkflag character(1),
CONSTRAINT packets_pkey PRIMARY KEY (pkid),
CONSTRAINT packets_flid_fkey FOREIGN KEY (flid)
REFERENCES flows (flid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT packets_flid_key UNIQUE (flid, pktime, pksig)
)

Vous pouvez probablement envisager un partitionnement au niveau du pktime. Voire un archivage des données vers une table qui ne serait accéder qu'en cas de besoin d'informations plus anciennes.

#1 04/10/2011 10:15:05

[Table Enormes] Demande de renseignements / 'Best practices'

#2 04/10/2011 10:42:18

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#3 04/10/2011 10:45:18

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#4 04/10/2011 10:56:54

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#5 04/10/2011 12:41:09

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#6 04/10/2011 12:51:09

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#7 10/10/2011 16:19:35

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#8 10/10/2011 19:34:14

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#9 11/10/2011 07:12:03

Re : [Table Enormes] Demande de renseignements / 'Best practices'

#10 11/10/2011 10:49:03

Re : [Table Enormes] Demande de renseignements / 'Best practices'

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux