Intereting Posts

Essayer de joindre des tables en utilisant des champs datetime MS Access affiche des copys d'une première ligne à la place des valeurs correctes Grouper par et agréger la fonction La clause ORDER BY n'est pas valide dans les vues, les fonctions inline, les tables dérivées et les sous-requêtes Interrogation d'un tableau similaire à la requête SQL – PHP Comment puis-je join une table deux fois et referencer un nom de colonne différemment à chaque fois? En utilisant SQL, comment mettre à jour des lignes, en utilisant leurs propres valeurs? Puis-je installer SQL Server avec SQL Server Express? La logique personnalisée SQL dans les contraintes Problème avec Entity Framework après l'ajout d'une colonne à la table DB Comment puis-je find la plage de dates manquante dans sql server 2008? Envoi d'e-mails en masse dans ASP.NET Sélectionnez Où à l'intérieur si sinon dans SQL Comment créer une requête sur une table existante et build une table (vue) avec des données booleannes en utilisant une condition logique? Obtenir la quantité entre plusieurs mois à partir de 2 parameters de date

Recommandations pour la structure de database avec un grand set de données

Il me semble que cette question sera sans réponse précise puisqu'elle nécessite une parsing trop complexe et une plongée profonde dans les détails de notre système.

Nous avons dissortingbué net de capteurs. Informations rassemblées dans une database et traitées ultérieurement.

La design actuelle de DB consiste à avoir une énorme table partitionnée par mois. Nous essayons de le maintenir à 1 milliard (en général 600-800 millions d'loggings), de sorte que le taux de remplissage est de 20 à 50 millions d'loggings par jour.

Le server de database est actuellement MS SQL 2008 R2, mais nous avons commencé à partir de 2005 et mis à jour au cours du développement du projet.

La table elle-même contient SensorId, MessageTypeId, ReceiveDate et le champ de données. La solution actuelle consiste à conserver datatables du capteur dans le champ de données (binary, longueur fixe de 16 octets) en décodant partiellement son type et à l'save dans messageTypeId.

Nous avons différents types de messages envoyés par des capteurs (le courant est d'environ 200) et il peut être augmenté à la request.

Le traitement principal est effectué sur le server d'application qui récupère les loggings à la request (par type, sensorId et plage de dates), le décode et effectue le traitement requirejs. La vitesse actuelle est suffisante pour une telle quantité de données.

Nous avons demandé d'augmenter la capacité de notre système en 10-20 fois et nous nous inquiétons que notre solution actuelle soit capable de cela.

Nous avons aussi 2 idées pour "optimiser" la structure dont je veux discuter.

Les données du capteur peuvent être divisées en types, j'utiliserai 2 primaires pour plus de simplicité: données de niveau (valeur) (données analogiques avec plage de valeurs), données d'état (quantité fixe de valeurs)

Nous pouvons donc redessiner notre table pour en faire de petites en utilisant les règles suivantes:

pour chaque valeur de type fixe (type d'état) créez sa propre table avec SensorId et ReceiveDate (donc nous évitons le type de magasin et le blob binary), tous les états dépendants seront stockés dans la propre table Foreign Key similaire, donc si nous avons valeurs A et B , et les états dépendants (ou supplémentaires) pour cela 1 et 2 nous StateA_1 avec les tables StateA_1 , StateA_2 , StateB_1 , StateB_2 . Ainsi, le nom de la table est constitué d'états fixes qu'elle représente.
pour chaque donnée analogique, nous créons un tableau séparé qui sera similaire au premier type mais qui contient un champ supplémentaire avec une valeur de capteur;

Avantages:

Stocker seulement la quantité requirejse de données (actuellement notre données binarys blob contient de l'espace à la plus grande valeur) et réduire la taille de la database;
Pour get des données de type particulier, nous obtenons l'access à la table de droite au lieu du filter par type;

Les inconvénients:

AFAIK, il viole les pratiques recommandées;
Nécessite le développement de frameworks pour automatiser la gestion des tables car ce sera l'enfer de DBA de le maintenir manuellement;
Le nombre de tables peut être considérablement important car il nécessite une couverture complète des valeurs possibles;
Les modifications du schéma de database lors de l'introduction de nouveldatatables de capteur ou même d'une nouvelle valeur d'état pour des états déjà définis peuvent donc nécessiter des modifications complexes;
La gestion complexe conduit à l'erreur sujette;
Peut-être que le moteur de DB enfoncerait des valeurs dans une telle table d'organisation?
La structure de la database n'est pas fixe (constamment modifiée);

Probablement tout surpoids quelques avantages mais si nous obtenons des gains de performance significatifs et / ou (less appréciés mais précieux aussi) de l'espace de stockage peut-être nous suivons ce path.

2 Peut-être juste split la table par capteur (ce sera environ 100 000 tables) ou mieux par plage de capteurs et / ou passer à différentes bases de données avec des servers dédiés mais nous voulons éviter l'empan matérielle si possible.

3 Laissez tel quel.

4 Passer à un type de SGBD différent, par exemple un SGBD orienté colonne (HBase et similaire).

Qu'est-ce que tu penses? Peut-être que vous pouvez suggérer des ressources pour une lecture ultérieure?

Mise à jour: La nature du système que certaines données de capteurs peuvent arriver même avec un retard de mois (généralement 1-2 semaines de retard), certains toujours en ligne, une sorte de capteur a la memory à bord et aller en ligne finalement. Chaque message de capteur est associé à la date de triggersment de l'événement et à la date de réception du server, de sorte que nous pouvons distinguer datatables récentes des données collectées il y a quelque time. Le traitement inclut des calculs statistics, la détection de déviation de parameters, etc. Nous avons construit des rapports agrégés pour une visualisation rapide, mais lorsque nous recevons des données de capteurs anciennes données (déjà traitées), nous devons rebuild certains rapports à partir de zéro. datatables et les valeurs agrégées ne peuvent pas être utilisées. Nous conservons donc généralement des données sur 3 mois pour un access rapide et d'autres archivées. Nous nous efforçons de réduire les besoins de stockage des données, mais nous avons décidé que nous avions besoin de tout pour que les résultats soient précis.

Update2:

Voici une table avec des données primaires. Comme je le mentionne dans les commentaires, nous supprimons toutes les dependencies et les contraintes lors de "besoin de vitesse", donc il est utilisé uniquement pour le stockage.

 CREATE TABLE [Messages]( [id] [bigint] IDENTITY(1,1) NOT NULL, [sourceId] [int] NOT NULL, [messageDate] [datetime] NOT NULL, [serverDate] [datetime] NOT NULL, [messageTypeId] [smallint] NOT NULL, [data] [binary](16) NOT NULL )

Exemple de données provenant de l'un des servers:

 id sourceId messageDate serverDate messageTypeId data 1591363304 54 2010-11-20 04:45:36.813 2010-11-20 04:45:39.813 257 0x00000000000000D2ED6F42DDA2F24100 1588602646 195 2010-11-19 10:07:21.247 2010-11-19 10:08:05.993 258 0x02C4ADFB080000CFD6AC00FBFBFBFB4D 1588607651 195 2010-11-19 10:09:43.150 2010-11-19 10:09:43.150 258 0x02E4AD1B280000CCD2A9001B1B1B1B77

Je vais juste jeter quelques idées là-bas, j'espère qu'elles seront utiles – ce sont quelques-unes des choses sur lesquelles je réfléchirais / je réfléchirais.

Partitionnement – vous mentionnez que la table est partitionnée par mois. Est-ce que vous êtes partitionné manuellement ou utilisez-vous la fonctionnalité de partitionnement disponible dans Enterprise Edition? S'il est manuel, envisagez d'utiliser la fonctionnalité de partitionnement embeddede pour partitionner davantage vos données, ce qui devrait vous donner une évolutivité / performance accrue. Cet article " Partitioned Tables and Indexes " sur MSDN par Kimberly Tripp est génial – beaucoup de bonnes infos là-dedans, je ne vais pas faire une injustice en paraphrasant! Il vaut la peine d'en tenir count en créant manuellement une table par capteur, ce qui pourrait être plus difficile à maintenir / mettre en œuvre et donc append de la complexité (simple = bon). Bien sûr, seulement si vous avez Enterprise Edition.

Filtered Indexes – consultez cet article MSDN

Il y a bien sûr l'élément matériel – il va sans dire qu'un server charnu avec des tas de RAM / disques rapides etc. jouera un rôle.

Une technique, pas tellement liée aux bases de données, consiste à passer à l'logging d'un changement de valeurs – avec un minimum de n loggings par minute. Ainsi, par exemple si le capteur n ° 1 envoie quelque chose comme:

 Id Date Value ----------------------------- 1 2010-10-12 11:15:00 100 1 2010-10-12 11:15:02 100 1 2010-10-12 11:15:03 100 1 2010-10-12 11:15:04 105

alors seulement le premier et le dernier logging se termineraient dans la database. Pour s'assurer que le capteur est "live", il faut entrer au less 3 loggings par minute. De cette façon, le volume de données serait réduit.

Je ne sais pas si cela aide, ou si ce serait faisable dans votre application – juste une idée.

MODIFIER

Est-il possible d'archiver des données en fonction de la probabilité d'access? Serait-il correct de dire que les anciennes données sont less susceptibles d'être accessibles que les nouveldatatables? Si c'est le cas, vous pouvez jeter un oeil à l' architecture DW 2.0 de Bill Inmon pour la prochaine génération d'entreposage de données où il discute du model de déplacement des données à travers différentes zones DW (Interactif, Intégré, Near-Line, Archival) basé sur probabilité d'access. Les time d'access varient de très rapide (zone interactive) à très lent (archivage). Chaque zone a des exigences matérielles différentes. L'objective est d'empêcher de grandes quantités de données d'obstruer le DW.

Au niveau du stockage, vous allez probablement être bien. SQL Server le gérera.

Ce qui m'inquiète, c'est la charge que votre server va prendre. Si vous recevez constamment des transactions, vous auriez environ ~ 400 transactions par seconde aujourd'hui. Augmentez cela d'un facteur de 20 et vous regardez environ 8 000 transactions par seconde. Ce n'est pas un petit nombre count tenu que vous faites des rapports sur les mêmes données …

Btw, est-ce que je vous comprends bien dans le fait que vous jetez datatables du capteur quand vous l'avez traité? Donc, votre set de données sera un "roulant" 1 milliard de lignes? Ou vous venez d'append datatables?

Vous pouvez stocker les horodatages en tant qu'entiers. Je crois que les tampons datetime utilisent 8 octets et les entiers n'utilisent que 4 dans SQL. Vous devez quitter l'année, mais comme vous partitionnez par mois, cela ne pose peut-être pas de problème.

Donc '12 / 25/2010 23:22:59 'serait stocké en tant que 1225232259 -MMDDHHMMSS

Juste une pensée…