Je dois mettre en place un service Web Analytics sur mesure pour un grand nombre de sites Web. Les entités clés ici sont:
Chaque visiteur unique aura une seule ligne dans la base de données avec des informations telles que la page d'atterrissage, l'heure de la journée, le système d'exploitation, le navigateur, le référent, la propriété intellectuelle, etc.
Je devrai effectuer des requêtes agrégées sur cette base de données, telles que «Compter tous les visiteurs qui ont Windows comme OS et venaient de Bing.com '
J'ai des centaines de sites Web à suivre et que le nombre de visiteurs pour ces sites Web varie de quelques centaines d'un million de dollars par jour. Au total, je m'attends à ce que cette base de données augmente d'environ un million de lignes par jour.
Mes questions sont:
1) est MySQL une bonne base de données à cette fin?
2) Quelle pourrait être une bonne architecture? Je pense à créer une nouvelle table pour chaque site web. Ou peut-être commencer par une seule table puis reprogrammer une nouvelle table (quotidienne) si le nombre de lignes dans une table existante dépasse 1 million (mon hypothèse est-elle correcte). Mon seul souci est que si une table pousse trop grosse, les requêtes SQL peuvent être considérablement lentes. Alors, quel est le nombre maximum de lignes que je devrais stocker par table? De plus, existe-t-il une limite sur le nombre de tables que MySQL peut gérer.
3) est-il conseillé de faire des requêtes agrégées sur des millions de lignes? Je suis prêt à attendre quelques secondes pour obtenir des résultats pour de telles questions. Est-ce une bonne pratique ou y a-t-il une autre façon de faire des requêtes agrégées?
En bref, J'essaie une conception un type de configuration d'entrepôt de données à grande échelle qui sera écrit lourd . Si vous connaissez des études de cas ou des rapports publiés, ce sera génial!
4 Réponses :
Quelques suggestions dans une base de données agnostique.
La rationnelle la plus simple est de distinguer les tables intensives de lecture et d'écriture. Il est probablement une bonne idée de créer deux schémas parallèles quotidiens / schémas hebdomadaires et un schéma d'historique. Le partitionnement peut être fait de manière appropriée. On peut penser à un travail de lot pour mettre à jour le schéma d'historique avec des données du schéma quotidien / hebdomadaire. Dans l'historique Schema à nouveau, vous pouvez créer des tables de données distinctes par site Web (en fonction du volume de données).
Si tout ce que vous êtes intéressé, c'est dans les statistiques d'agrégation
Suggestion intéressante de garder les tables de lecture et d'écriture séparées. Toute suggestion spécifique Pourquoi cela sera utile (par opposition à l'utilisation d'une file d'attente pour faire des écrires par lots)?
La plupart des bases de données fournissent une disposition d'importation à l'exportation hors ligne. SQLLoader pour Oracle, DB2EXPORT / Import pour DB2. Je pense que c'est mysqldump pour mysql
Si vous parlez de plus grands volumes de données, regardez MySQL partitionnement . Pour ces tables, une partition de données / temps aiderait certainement la performance. Il y a un article décent sur la partitioninging ici .
Recherchez sur la création de deux bases de données distinctes: une pour toutes les données brutes pour les écritures avec une indexation minimale; une seconde pour signaler en utilisant les valeurs agrégées; Avec un processus de lot pour mettre à jour la base de données de rapports de la base de données de données brutes ou utiliser la réplication pour le faire pour vous.
Si vous voulez être vraiment intelligent avec vos rapports d'agrégation, créez un ensemble de tables d'agrégation («aujourd'hui», «semaine à ce jour», «mois à ce jour», «par année»). Agrégat des données brutes à "aujourd'hui" quotidiennement ou en "temps réel"; total de «de jour» à «Semaine à ce jour» tous les soirs; de "Semaine à ce jour" à "Mois à ce jour" sur une base hebdomadaire, etc. Lors de l'exécution de requêtes, rejoindre (Union) Les tableaux appropriés pour les gammes de date qui vous intéressent.
edit # 2
plutôt qu'une table par client, nous travaillons avec un schéma de base de données par client. Selon la taille du client, nous pourrions avoir plusieurs schémas dans une seule instance de base de données ou une instance de base de données dédiée par client. Nous utilisons des schémas séparés pour la collecte de données brutes et pour l'agrégation / la déclaration pour chaque client. Nous exécutons plusieurs serveurs de base de données, restreignant chaque serveur à une seule instance de base de données. Pour la résilience, les bases de données sont répliquées sur plusieurs serveurs et de la charge équilibrée pour améliorer les performances.
En fait, l'agrégation se produira sur des colonnes arbitraires. Donc, il ne s'agit pas uniquement de nombre de visiteurs uniques ou de visiteurs répétés, mais un utilisateur peut sélectionner n'importe quelle combinaison de variables (OS, navigateur, référent, heure de la journée) pour effectuer la segmentation. C'est ce qui rend cela difficile parce que j'ai besoin d'avoir accès aux données brutes pour cela.
Mon entreprise fournit exactement ce type d'information (et beaucoup plus que la page d'atterrissage, telle que la valeur des dépenses, l'abandon du panier) pour de très grands clients (l'AA, plusieurs grandes banques et compagnies d'assurance, grands voyagistes), Nous obtenons donc des volumes de données similaires (millions de lignes par jour). Nous courons sur Oracle plutôt que MySQL, mais bon nombre des principes sont les mêmes. Nous préférons fournir des rapports sur les forets, ce qui permet d'utiliser des données agrégées pour un rapport de haut niveau, avec "forage" sélectif aux données brutes sous-jacentes.
Est-ce que vous stockez des données historiques pour toujours? Ou avez-vous une stratégie de purge (par exemple supprimer toutes les données de plus de 100 jours)?
Nous maintenons réellement toutes les données à une limite de 7 ans, après quoi il est archivé. Sept ans probablement excessif, compte tenu du taux auquel le Web évolue; 2 ou (éventuellement) 3 ans devraient être plus que suffisants; Mais nous avons quelques clients de 7 ans et il est plus facile d'appliquer cette règle dans la base de la clientèle complète. Il est utile de faire plusieurs années pour la gestion des rapports «comparatifs» (par exemple, comparer ces chiffres de juillet aux chiffres précédents de juillet) et pour les rapports de tendance.
Addendum à 7 ans ... En outre, nous pouvons facturer le maintien des volumes de données supplémentaires (s'il a été explicitement demandé)
@MarkBaker Pouvez-vous partager votre conception et vos processus de base de données?
Vous devez vraiment tester votre voie à suivre simulera les envioments aussi proches que possible de l'environnement vivant, avec des données «vraies» (format et longueur correctes). Queries de référence et variantes de structures de table. Depuis que vous semblez connaître MySQL, commencez-y. Cela ne devrait pas vous prendre si longtemps pour configurer quelques scripts bombardant votre base de données avec des requêtes. Étudier les résultats de votre votre avec votre type de données vous aidera à réaliser où se produiront les goulots d'étranglement.
Pas une solution, mais j'espère que certaines aident sur le chemin, bonne chance :)
Vous devez absolument envisager de fractionnement des données par site sur des bases de données ou des schémas - cela facilite non seulement la sauvegarde, la chute, etc. Un site / client individuel, mais élimine également une grande partie des tracas de ne pas avoir à voir aucun client. Données des clients par accident ou codage médiocre, etc. Cela signifie également qu'il est également plus facile de faire des choix sur la partitionAing, au-dessus et au-dessus de la partitionnement de la table de la table de table pour le temps ou le client, etc.
Aussi, vous avez dit que le volume de données est de 1 million de lignes par jour (ce qui n'est pas particulièrement lourd et ne nécessite pas d'énorme pouvoir grognant de se connecter / stocker, ni de signaler si vous générerez 500 rapports à minuit, vous pourriez Logjam). Cependant, vous avez également dit que certains sites avaient des visiteurs de 1M quotidiennement pour que vous puissiez peut-être que la figure est trop conservatrice?
Enfin, vous n'avez pas dit si vous souhaitez signaler en temps réel un rapport de la ChartBeat / Opentracker, etc. ou un rafraîchissement cyclique comme Google Analytics - Cela aura une incidence majeure sur ce que votre modèle de stockage est du premier jour.
m
Mark, merci de répondre. Le rapport doit être fait en temps réel. Et c'est l'un des défis. Vous dites qu'un million de lignes par jour n'est pas lourde. Dans les 3 ans, la capacité totale de la DB sera d'environ 1 milliard de lignes. N'est-ce pas énorme? Ce que je suis particulièrement inquiet de la nature toujours croissante des données. Nous ne pouvons pas potentiellement stocker toutes les données de l'éternité?
Bien sûr, vous devez faire un peu de dimensionnement pour vous assurer que vous avez à la fois la capacité de stockage et la puissance grogneuse, mais avec une partition sensible, vous devriez être en mesure de séparer les performances des mises à jour et de rendre compte de la vulnérable aux problèmes que vous augmentez. Vous devrez peut-être faire des choix judicieux autour de la construction de tables d'agrégats et avoir le bon modèle pour soutenir les aspects de la BI de ce que vous essayez de fournir.
Si vous avez déjà conçu votre base de données. Pouvez-vous partager la conception de la base de données?