Discussion :

[Julie95220]

Bonjour,

J'utilise actuellement une base de données MySQL pour stocker mes données et étant donné que je commence à avoir des problèmes de lenteurs, je cherche une solution de remplacement. J'ai un unique serveur hébergeant à la fois MySQL et le serveur web (apache + PHP), mes problèmes venant de la partie MySQL. (Bon je ne suis pas du tout sure d'être dans la bonne catégorie..)

La solution actuelle

Serveur OVH avec Debian GNU/Linux 7.4
Processeur : Intel® Xeon® CPU E5-1620 v2 @ 3.70GHz 4 coeurs 8 threds
64Go de RAM
mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2
Tables innodb uniquement.

Configuration MySQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
 
table_cache=200
thread_cache_size=264
query_cache_size=256M
query_cache_limit=256M
join_buffer_size=256K
key_buffer_size=256M
bulk_insert_buffer_size=256M
read_buffer_size=10M
sort_buffer_size=64M
myisam_sort_buffer_size=256M
 
low_priority_updates=1
 
myisam-recover         = BACKUP
 
query_cache_limit       = 2M
query_cache_size        = 16M
query_cache_type        = 1
 
expire_logs_days        = 10
max_binlog_size         = 100M
 
innodb_lock_wait_timeout=100
innodb_buffer_pool_size=25G
 
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=0
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_buffer_pool_instances=5

Les besoins

Ma solution n'est pas destinée à avoir beaucoup d'utilisateurs, ni beaucoup de requêtes. (En gros, si on atteint 1000 utilisateurs "inscrits", c'est énorme déjà).

Ce que je dois faire, c'est récupérer un fichier de log de plus d'un millions de lignes par jour (on en est aujourd'hui à 1.5millions par jour, et ça devrait augmenter au fur et à mesure du temps), et les stocker dans ma base de données. Chaque ligne de log est traitée afin d'associer certaines valeurs à des ID ou autre. Chaque ligne de log ressemble à quelque chose comme ça :
date;site;utilisateur;pays;nombre;argent
Les données site, utilisateurs, pays, correspondent à des dimensions, il y a en a bien plus que ça (8 actuellement mais il est fort probable que ça évolue. De plus, des dimensions sont liées à ces dimensions. Par exemple, une thématique est liée à chaque site), et nombre et argent correspondent à des métriques (là encore, j'en ai plus que ça, mais il y a peu de chance que ça évolue beaucoup).

Le but est d'obtenir des données pour des périodes et des dimensions données. Les plus simples vont être par exemple, la somme de nombre et d'argent (SUM(nombre), SUM(argent)) pour le mois dernier, pour tel site (ou pour d'autres filtres). Mais il faut aussi pouvoir obtenir des données plus "compliquées" comme les 50 sites qui ont fait le plus d'argent sur une période, et pour chacun de ces sites, les 20 thématiques qui ont fait le plus de "nombre" et pour chaque thématique, tous les pays triés par nom. (Et évidemment, cela avec une modularité assez élevée... On peut éventuellement se retrouver avec 20 dimensions à vouloir afficher...)
Ces requêtes très compliquées peuvent prendre du temps sans que ce ne soit un problème, mais les plus simples (en gros, récupérer les données sur une période avec des filtres sur chaque dimensions) doivent pouvoir se faire rapidement (30 secondes, c'est déjà trop long).

Il y a aussi une notion de taux de change. Je récupère des données en dollar, et je dois les afficher en euro, selon le taux de change en vigueur actuellement. Cependant, à la fin du mois, un taux de change est choisi pour le mois précédent, et je dois donc appliquer ce taux à toutes les données du mois précédent. (Sachant qu'en fonction de certaines dimensions, je récupère des données directement en euro, donc il ne faut pas que je les mette à jour)

Ma solution actuelle

Actuellement, j'ai donc une table innodb rassemblant toutes les dimensions, du type :
Date, SiteId, UtilisateurId, PaysId, Nombre, ArgentDollar, ArgentEuro
Des tables Site, Utilisateur, avec leur dimensions associés (par exemple thématique pour un site, age, nationalité par un utilisateur) et les tables associées à ces dimensions.
Afin de diminuer les temps d'affichage de données simple, j'ai aussi créer des "sous tables" du type :
Date, SiteId, Nombre, ArgentDollar, ArgentEuro
Date, UtilisateurId, PaysId, Nombre, ArgentDollar, ArgentEuro
En gros, les métriques restent les mêmes, mais il y a un plus petit nombre de dimensions. Comme cela, j'utilise toujours la table la plus petite en fonction des filtres que les utilisateurs demandent (s'il n'y a qu'un filtre sur le Site, je vais pouvoir utiliser la table avec seulement le Site comme dimension, qui est bien plus petite que la table avec toutes les dimensions).

Ces sous tables sont générées "en cascade" (j'utilise toujours la plus petite table contenant toutes les dimensions nécessaires).

Pour les requêtes trop compliquées (voir l'exemple plus haut, qui peut avoir beaucoup de dimensions), elles sont faites en background et requêter via une API, donc leur temps d'execution n'est pas critique.

Pour le taux de change, je fais passer une update en fin de mois, pour regénérer la valeur ArgentEuro à partir de la valeur ArgentDollar (c'est d'ailleurs pour ça que je garde toujours la métriques argentDollar dans ma base, elle n'est pas utilisé à part pour ça..) sur la plus grosse table, puis je regénère les "sous tables" avec les bonnes valeurs en euro. Je ne peux pas faire passer l'update sur toutes les tables étant donné que pour certaines lignes, en fonction des dimensions, sont déjà en euro (la valeur argentDollar et ArgentEuro sont les mêmes) et ces dimensions doivent donc être disponibles pour que je puisse filtrer dessus.

J'ai ajouté des index sur mes tables de rapports sur les champs de type Date, SiteId, UtilisateurId, PaysId, etc.. (les dimensions en gros).

J'ai aussi ajouté du "pruning" pour certaines grosses dimensions. Par exemple pour les pays, je ne garde que les 25 pays qui ont fait le plus d'argent chaque jour, et je met tout le reste dans un pays "Autre", afin de diminuer la quantité de données..

Mes problèmes

Alors j'ai plusieurs problèmes. Le premier, c'est que même des rapports simple (2 dimensions, dont une petite), quand il y a une "grosse" dimension (=beaucoup de données pour cette dimensions. Genre utilisateur, on peut en avoir 20k par jour), ça peut prendre du temps à charger (5-10 secondes, parfois plus, ce qui est beaucoup pour une page web, pour des données assez simples). De plus, ces problèmes risquent de s'intensifier avec le remplissage des tables et le fait que j'ai des données de plus en plus grosse chaque jour).

Mon deuxième problème, c'est lorsque j'ai 2 requêtes qui s’exécutent à la fois, ça prend BEAUCOUP trop longtemps. Par exemple, 2 requêtes qui prennent une minute chacune, elles vont prendre 2 minutes l'une à la suite de l'autre, mais si elles s’exécutent en même temps, il y en aura pour 15 minutes. Cela va surtout poser problème en début de mois, quand je fais passer l'update du taux de change, et que je regénère toutes les sous tables (j'ai 22 tables de rapport de ce type en fait...) pour tout le mois. Ces regénérations de sous table prennent très longtemps (dans les 24h), et quand d'autres scripts se mettent en parallèle, ces scripts sont TRES ralentis (par exemple, j'en ai un qui met 1h habituellement et qui a mis 10h ce week-end...) et cela ralenti aussi la regénération des tables, qui ralenti les autres trucs etc... Donc ça provoque des "problèmes" en chaine et je ne vois pas comment gérer ça à part en désactivant mes traitements qui peuvent se faire en parallèle pendant 24h (et quand j'aurais encore plus de données et que ça prendra plus longtemps, il faudra que ce soit désactivé encore plus longtemps... Enfin ça ne me parait pas être une solution...) Je pense que ce problème peut venir de l'accès disque, mais je ne suis même pas sure...

Donc voilà, j'aimerais bien savoir vers quoi m'orienter pour améliorer tout ça sur le long terme. Je suis ouverte à toutes les solutions (que ce soit des changements de serveurs, de SGBD, de langages de développement, regarder du côté de la Big Data avec Hadoop, NoSQL et compagnie, etc... Enfin disons que tout est vraiment possible (dans la limite du raisonnable, on ne va pas se faire construire un data center ! )). Et je prend aussi les communautés/forums qui pourraient aussi m'aider sur ce sujet, je ne connais pas très bien les communautés spécialisées là-dedans.


Merci !

[aieeeuuuuu]

Bonjour,

Vos "petites" tables, copies de "grandes" tables avec moins de données est une fausse bonne idée. Il aura été bien préférable de créer des index à la place, qui aurait eu l'effet escompté, mais avec les performances en plus.
Je dirais donc que la première chose à faire est de réécrire vos requêtes pour qu'elles ne s'appuient que sur les "grosses" tables principales et de voir ensuite quels seraient les index nécessaires. Il faut aussi bien soigner la modélisation, peut-être pourriez vous nous présenter la votre.

Quant à changer de SGBDR, certains permettent en effet de créer des index sur des vues, ce qui est parfois très utile sur les requêtes faisant des grands agrégats de données. Cela semble être votre cas...

[Julie95220]

Bonjour,

Les index sont bien créés sur mes plus grandes tables (sur les plus petites aussi d'ailleurs !).
Mais j'ai du mal à voir en quoi c'est une fausse bonne idée... Rien que le fait que MySQL n'ai à faire un SUM sur 1000 fois moins de lignes devraient aider pas mal non ?

Au niveau de la modélisation, tout est fait de la même façon, mais je n'ai pas d'outil pour la visualiser facilement. Mais si je prend un sous ensemble de mon système, ça pourrait ressembler à ça :
(J'ai un PRIMARY index sur tous les Id)
full_report : id (int), date (date), siteId (int), countryId (int), userId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de full_report de type unique sur les colonnes : date, siteId, countryId, userId
site_country_report : id (int), date (date), siteId (int), countryId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de site_country_report de type unique sur les colonnes : date, siteId, countryId
site_report : id (int), date (date), siteId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de site_report de type unique sur les colonnes : date, siteId
country_report : id (int), date (date), countryId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de country_report de type unique sur les colonnes : date, countryId

Puis pour les tables de modeles :
site : id (int), name (varchar), thematicId (int), ownerId (int)
thematic : id (int), name (varchar)
owner : id (int), name (varchar)
country : id (int), name (varchar), continentId (id)
continent : id (int), name (varchar)
user : id (int), name (varchar), age (int), genderId (int)
gender : id (int), name (varchar)

[DotNetMatt]

MySQL n'a pas les épaules pour une volumétrie conséquente et laisse à désirer dans de nombreux domaines. SQLPro a rédigé un billet listant les malfaçons du produit : MySQL ? Un SGBDR poudre aux yeux !. En cherchant un peu sur Google tu pourras également trouver des infos sur les problématiques rencontrées par YouTube par exemple, qui l'utilise à très grande échelle.

Personnellement j'ai aussi pu le constater sur un projet pro : à la base une application servant de base documentaire sous MySQL. La base était relativement petite (on parle de 30 Go) pourtant malgré un serveur puissant on était confronté à des lenteurs telles que l'application n'était que très peu utilisée : une requête moyenne durait environ 20 minutes. Après une migration vers SQL Server (2008 R2 à l'époque), sur le même serveur avec les mêmes données et le même modèle, la même requête moyenne (ou presque) ne prenait plus qu'environ 10 millisecondes...

Le passage à un SGBDR digne de ce nom sera donc sûrement bénéfique. On peut recommander SQL Server ou Oracle. SQL Server a l'avantage d'être beaucoup moins cher et d'avoir une courbe d'apprentissage bien plus abordable qu'Oracle, qui souffre de certaines limitations historiques et très étonnantes. Mais ça ne fera pas tout, il faudra également :
- Une bonne modélisation (respect des formes normales)
- Une bonne stratégie d'indexation comme indiqué par aieeeuuuuu
- Un bon paramétrage du support de stockage (choix du RAID adapté ou si SAN, choix d'une connectique réseau adaptée)

[SQLpro]

Au niveau de la modélisation, tout est fait de la même façon, mais je n'ai pas d'outil pour la visualiser facilement. Mais si je prend un sous ensemble de mon système, ça pourrait ressembler à ça :
(J'ai un PRIMARY index sur tous les Id)
full_report : id (int), date (date), siteId (int), countryId (int), userId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de full_report de type unique sur les colonnes : date, siteId, countryId, userId
site_country_report : id (int), date (date), siteId (int), countryId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de site_country_report de type unique sur les colonnes : date, siteId, countryId
site_report : id (int), date (date), siteId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de site_report de type unique sur les colonnes : date, siteId
country_report : id (int), date (date), countryId (int), impressions (int), dollarRevenue (decimal), revenue (decimal)
Index de country_report de type unique sur les colonnes : date, countryId

Puis pour les tables de modeles :
site : id (int), name (varchar), thematicId (int), ownerId (int)
thematic : id (int), name (varchar)
owner : id (int), name (varchar)
country : id (int), name (varchar), continentId (id)
continent : id (int), name (varchar)
user : id (int), name (varchar), age (int), genderId (int)
gender : id (int), name (varchar)

Apparemment aucune de vos tables n'a de clef primaire. Juste des contraintes d'unicité ?
Commencez par faire un modèle correct.

Postez le DDL de vos table et non pas un style télégraphique. Nous avons besoin de savoir quelles sont TOUTES les contraintes et les TYPES exact de chaque colonne.
De même le DDL des index.

A +

[Julie95220]

Apparemment aucune de vos tables n'a de clef primaire. Juste des contraintes d'unicité ?
Commencez par faire un modèle correct.

Postez le DDL de vos table et non pas un style télégraphique. Nous avons besoin de savoir quelles sont TOUTES les contraintes et les TYPES exact de chaque colonne.
De même le DDL des index.

A +

J'ai bien précisé que toutes mes tables ont une clé primaire sur le champ ID.
Les seules contraintes sont celles que j'ai donné (clé unique sur les champs date + tous les champs de dimensions). J'ai bien précisé les types des colonnes (sans les précisions de taille qui vont dire int(11), mais ce sont des valeurs que j'ai mise en fonction des données que j'ai dans ma table, et de ce que je peux avoir, et ce n'est donc pas à ce niveau-là que je vais pouvoir optimiser).

Mais pour donner un exemple, les DDL des tables full_report et site sont les suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE IF NOT EXISTS `full_report` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `siteId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `countryId` int(11) NOT NULL,
  `impressions` int(11) NOT NULL,
  `dollarRevenue` decimal(12,6) NOT NULL DEFAULT '0.000000',
  `revenue` decimal(12,6) NOT NULL DEFAULT '0.000000',
  PRIMARY KEY (`id`),
  UNIQUE KEY `Search` (`date`,`siteId`,`userId`,`countryId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
 
CREATE TABLE IF NOT EXISTS `site` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `thematicId` int(11) NOT NULL,
  `ownerId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Toutes mes tables sont faites sur ce model

@DotNetMatt : Merci, je vais lire ça ! (Par contre, j'avoue que tout ce qui doit passer par du Microsoft Server... Ça ne va pas passer ! )

[punkoff]

quid des indexs sur la table full_report par exemple ?

[StringBuilder]

PRIMARY KEY = Clé primaire
UNIQUE KEY = Clé unique

Ça n'a rien à voir !

C'est donc bien des clés primaires que vous avez, ce qui est bien mieux que des clés unique, ce que laissait penser votre description (comme quoi la DDL c'est vital pour que tout le monde se comprenne).

Sinon, un truc que laisse perplexe... SiteId ne référence pas... site(id).

Commencez par déclarer convenablement vos clés étrangères, cela aidera grandement !

[Julie95220]

PRIMARY KEY = Clé primaire
UNIQUE KEY = Clé unique

Ça n'a rien à voir !

C'est donc bien des clés primaires que vous avez, ce qui est bien mieux que des clés unique, ce que laissait penser votre description (comme quoi la DDL c'est vital pour que tout le monde se comprenne).

Sinon, un truc que laisse perplexe... SiteId ne référence pas... site(id).

Commencez par déclarer convenablement vos clés étrangères, cela aidera grandement !

Ça me paraissait pourtant clair quand même en fait... J'ai dit que j'avais une clé primaire (PRIMARY KEY) sur les champs id de toutes mes tables. Et j'avais aussi une clé unique (UNIQUE KEY) pour chaque table de rapport sur la date et chaque colonne de dimensions. Enfin je connais bien la différence entre une clé primaire et une clé unique en fait !

Par ailleurs, au niveau performance, qu'est-ce que cela va m'apporter de déclarer mes clés étrangères ? Cela me permettra d'améliorer la rapidité d'une requête de type SELECT SUM(revenue) FROM full_report WHERE date >= "2014-01-01" AND siteId IN (25,45,67) AND countryId = 2 AND userId IN (55,223,795) ? En effet, je gère entièrement l'intégrité de mes données via l'application, et j'ai l'impression que de déléguer ce travail à la base de données risque simplement de plus la charger (c'est en tout cas ce que je lis dans la documentation de MySQL).
Donc je ne vois vraiment pas en quoi cela peut aider ?

Merci !

@punkoff : j'ai bien un index UNIQUE sur les colonnes : date, siteId, countryId, userId pour la table full_report

[punkoff]

Perso pour ce type de schéma, et vu MySql, déclarer des FK c'est pas forcément util => on va me tirer dessus
(Mais je ne l'aurais pas fait pour les même raisons que vous, laisser l'applicatif gérer des contraintes d'intégrité quand on en a besoin c'est une mauvaise idée)

Par contre le fait de les déclarer pourrait avoir un effet bénéfique vu que vous ne l'avez pas fait : ca aurait créer des index sur chacune des colonnes.


La vu comme c'est partie, Mysql ne dispose que d'un seul index pour accéder aux données c'est votre contrainte d'unicité.

Selon les requêtes il va partir soit :
- index scan (de l'unique qui est assez conséquent)
- table scan

Avec plus d'index, il pourrait choisir d'autres plan d’exécution, surtout quand vous disposez de restriction comme "countryId = 2" ou "siteId IN (25,45,67)".

[Julie95220]

Mes contraintes d'unicité sont loin d'être critiques je dois l'avouer !

Cependant, pour le deuxième point, je suis d'accord. Là ou ça va me poser problème, c'est que dans cet exemple, pour optimiser toutes les combinaisons, il me faudrait les index suivants :
- date, siteId, countryId, userId
- date, siteId, countryId
- date, siteId, userId
- date, siteId
- date, countryId, userId
- date, countryId
- date, userId

Ce qui me fait déjà 7 index (sachant que j'ai pris un exemple simplifié avec 3 dimensions, mais j'en ai actuellement 8, et ce nombre a de fortes chances d'augmenter dans le futur). Donc il me faudrait un nombre d'index énorme pour gérer toutes les combinaisons et du coup je pense que cela doit limiter grandement l'efficacité des index, non ? (Ou alors, cela ne peut-il pas poser des problèmes dès qu'il s'agit d'écriture ? S'il faut mettre à jour les index à chaque fois...)
De plus, rien qu'une requête de type : select sum(impressions), sum(revenue) from full_report where date >= "2014-01-01"; prend énormément de temps... (Je l'ai lancé il y a 5 minutes et elle n'est pas terminée)

[StringBuilder]

Quelle est la volumétrie actuelle ? (vous parlez de 1,5 millions de lignes par jour, mais qu'en est-il actuellement ?)

Sinon, dans votre message, vous parliez de "primary index" et non de "primary key", ce qui laisse à penser la présence d'un index unique et non d'une clé primaire.

[aieeeuuuuu]

Bonjour,

Je pense que la première chose à faire est de prendre le temps de lire quelques articles concernant l'indexation, c'est certainement l'axe sur lequel vous aurez les meilleurs gains de performance. Et visiblement, vous n'en avez pas saisi tous les détails. Ce n'est pas une notion aussi simple qu'il y parait au premier abord. je vous suggère donc de lire quelques articles, et surtout de faire beacoup de tests pour bien comprendre leur fonctionnement, et leur utilité.

Car, par exemple vous dites (j'ai numéroté vos index):

il me faudrait les index suivants :
1/- date, siteId, countryId, userId
2/- date, siteId, countryId
3/- date, siteId, userId
4/- date, siteId
5/- date, countryId, userId
6/- date, countryId
7/- date, userId

- 4 est inutile car redondant avec 1,2 et 3
- 2 est inutile car redondant avec 1
- 6 est inutile car redondant avec 5

Quant aux autres, rien ne prouve qu'ils seront réellement utiles. des index se posent -entre autre - par rapport aux requêtes qui sont effectuées

De plus, rien qu'une requête de type : select sum(impressions), sum(revenue) from full_report where date >= "2014-01-01"; prend énormément de temps... (Je l'ai lancé il y a 5 minutes et elle n'est pas terminée)

et combien de temps prends-t-elle après avoir posé l'index suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX IX_FULL_REPORT_DATE ON full_report(date, impression, revenue)

[Julie95220]

Bonjour,

Oui en effet, pour les index vous avez raison je pense ! C'est vrai que je m'étais particulièrement renseignée sur les index il y a quelques mois, et que j'ai peut-être oublié 2-3 détails !
Il y a d'ailleurs un gros détails que je n'ai pas réussi à comprendre au niveau des index : est-ce que l'ordre des colonnes a une importance ? Est-ce qu'un index date, countryId, siteId revient à la même chose que date, siteId, countryId ? J'ai l'impression que non, mais je ne l'ai jamais vu écrit clairement... Et est qu'avec un index de type date, siteId, countryId, ça veut dire qu'il faut faire un WHERE date = "2014-01-01" AND siteId = 1 AND countryId = 1, ou est-ce que je peux inverser les deux conditions ?

J'ai quand même "peur" de générer trop d'index et que cela perturbe l'écriture... Enfin je ne sais pas si cela fait vraiment ça... Je pense que je vais me replonger dans la lecture d'infos sur les index, car je crois que j'ai oublié un certain nombre de choses...

Et je fais le test avec l'index. Je ne pensais pas que ça pouvait être utilisé pour faire des SUM sur des métriques ! Mais bon... J'ai besoin de la date ainsi que des métriques à tous les coups, mais il est obligatoire de pouvoir filtrer sur TOUTES les dimensions, comme on veut, et c'est la que ça se complique pour moi !

Enfin au final, ce qui m'embête, c'est que même en accélérant les requêtes, je garde mon problème lié aux requêtes simultanées... Enfin c'est juste des problèmes en plus

@StringBuilder : J'ai près de 300 millions de lignes sur mes plus grosses tables. Mais c'est important que même dans 3ans, quand il y a aura 5 millions de lignes/jour, ma solution reste possible et qu'il ne faille que upgrader les specs matérielles !

EDIT : Bon j'ai fait le test que tu proposais (rajouter un index sur la colonne date + les métriques, avec une requête qui SUM les métriques, et un WHERE sur la date), je passe de 1min08 à 39 secondes. Ça fait une amélioration significative, mais ce n'est clairement pas assez rapide par rapport à mes besoins...

[punkoff]

Pour le problème d'indexation, n'oubliez pas que vous pouvez les supprimer avant de faire votre import puis de les re-créer.

Faites attention tout de même ca n'est pas forcément la meilleur solution, vérifier les temps d'éxecution avant de la mettre en place.

lisez ceci pour l'indexation, c'est une bonne base : http://sqlpro.developpez.com/cours/quoi-indexer/

[aieeeuuuuu]

Il y a d'ailleurs un gros détails que je n'ai pas réussi à comprendre au niveau des index : est-ce que l'ordre des colonnes a une importance ?

Oui, l'ordre des colonnes dans un index a de l'importance.
Si vous devez chercher dans un dictionnaire les mots commençant par "C", ce sera très rapide (et, rapport à vos "petites tables", il sera inutile de copier tous les mots du dico dans 26 livres séparés, ça ne ferait qu'occuper de la place dans votre bibliothéque).
Si en revanche vous cherchez les mots dont la deuxième lettre est "C", le classement alphabétique du dictionnaire ne vous sera d'aucune utilité, et vous devrez vérifier page par page, mot par mot.

Et est qu'avec un index de type date, siteId, countryId, ça veut dire qu'il faut faire un WHERE date = "2014-01-01" AND siteId = 1 AND countryId = 1, ou est-ce que je peux inverser les deux conditions ?

En revanche, l'ordre des clauses WHERE n'a aucune importance, le SGBD commencera par appliquer les restrictions qui permettront de filtrer le plus possible. Toujours avec votre dictionnaire, vous n'aurez pas plus de difficulté à trouver [les mots dont la première lettre est le "C" et la deuxième lettre est le "A"] que [les mots dont la deuxième lettre est le "A" et la première lettre est le "C"]...

J'ai quand même "peur" de générer trop d'index et que cela perturbe l'écriture... Enfin je ne sais pas si cela fait vraiment ça...

Oui cela a un cout, car lors d'une mise à jour de la table, il faut mettre à jour l'index.
Cependant, même lors d'une mise à jour ou d'une suppression, l'index peut etre utilisé pour trouver les données à mettre à jour, avec souvent un gain bien supérieur au surcout de mise à jour de l'index...
Il faut bien sûr ne poser que les index utiles. prenez les requêtes les plus lentes, une par une, et voyez quels index pourraient être utiles. voyez ensuite quelles requêtes ils peuvent impacter.
Globalement, pour une bonne stratégie d'indexation, il faut bien comprendre le fonctionnement des index mais également connaitre
- la structure des tables
- le contenu des tables (la répartition des données)
- les requêtes exécutées et dans quel contexte (fréquence, est-ce qu'un utilisateur attends à l'autre bout, ou est-ce un script qui peut patienter un peu plus...)

EDIT : Bon j'ai fait le test que tu proposais (rajouter un index sur la colonne date + les métriques, avec une requête qui SUM les métriques, et un WHERE sur la date), je passe de 1min08 à 39 secondes. Ça fait une amélioration significative, mais ce n'est clairement pas assez rapide par rapport à mes besoins...

Un rapide calcul : vous parliez de 5 millions de lignes pas jour. depuis les 1er janvier, ça fait donc de l'ordre du milliard de lignes. 39 secondes pour sommer un milliard de lignes, c'est finalement peu... (quoique vous parliez d'une table de 300 millions de lignes !?!). pour information, combien de lignes sont concernées après restriction sur la date ?

Pour une requete aussi simple (syntaxiquement), on peut affirmer qu'il n'y aura pas meilleur index que celui proposé, qui est couvrant (donc aucun accès à la table ne devrait être fait). C'est donc la volumétrie elle-même qui est en cause. Et si vous avez beaucoup de requêtes de ce type, passer sur un SGBD proposant des vues indexées seraient effectivement utile : la réponse à cette requete pourrait être immédiate !

[Julie95220]

Merci pour toutes ces infos pour les index ! Ça parait hyper logique expliqué comme ça !

Au niveau de la volumétrie, je parlais d'1.5millions de ligne par jour (et d'éventuellement 5 millions par jour dans le futur). Mes plus grosses tables en prod ont 300 millions de ligne (environ hein )

J'ai fait mon test sur ma base de dev (un peu moins performante que la prod !) qui n'a que 50 millions de lignes sur cette condition. Ce serait largement pire en prod au niveau des temps de réponse !

Enfin pour l'instant, je m'oriente surtout vers un changement de système de base de données avec optimisation au niveau des index, partitionnement de tables, configuration. J'aimerais tester PostgreSQL, MariaDB ou encore voir ce que je pourrais avoir dans du NoSQL (même si ce serait de bien plus gros changements pour le coup ! )

Merci

[punkoff]

en prod vous avez combien de Go de données ?

[SQLpro]

Par ailleurs, au niveau performance, qu'est-ce que cela va m'apporter de déclarer mes clés étrangères ? Cela me permettra d'améliorer la rapidité d'une requête de type SELECT SUM(revenue) FROM full_report WHERE date >= "2014-01-01" AND siteId IN (25,45,67) AND countryId = 2 AND userId IN (55,223,795) ? En effet, je gère entièrement l'intégrité de mes données via l'application, et j'ai l'impression que de déléguer ce travail à la base de données risque simplement de plus la charger (c'est en tout cas ce que je lis dans la documentation de MySQL)

Toujours les mêmes stupidités....

Il n'est pas possible de gérer les IR dans l'applicatif. En effet d'un côté le système est ensembliste (le SGBDR) de l'autre il est itératif (l'application). Ce défaut d'impédance empêche de faire des contraintes ensemblistes côté applicatif. Donc, vous avez toutes les chances d'avoir en production des données orphelines. Dans tous les audits que j'ai fait venant d'application du même genre, il y avait TOUJOURS des lignes orphelines.
De plus un contrôle applicatif oblige à plus de boulot...
Pour être à eut près correct il faudrait faire la chose suivante :
1) démarrer une transaction
2) poser un verrou exclusif sur les tables visées
3) faire la mise à jour
4) lancez les requêtes de vérification d'IR
5) finaliser la transaction
soit une dizaine d'aller-retour réseau très couteux et pendant ce temps les tables sont bloquées !!!
C'est totalement débile, puisque avec une contrainte cela est fait en interne dans le SGBDR donc aucun aller-retour donc instantané !

A +

[Julie95220]

en prod vous avez combien de Go de données ?

J'en suis à 150Go de données. (Sachant que ça ne fait que 6 mois qu'on récupère de vraies données (en gros les données de 2013 ne représentent presque rien) et qu'on en a de plus en plus au fil du temps).

@SQLpro : Je comprend. Cependant, je ne pense pas avoir de données orphelines étant donné que la suppression au niveau des models de dimension est tout simplement impossible du côté applicatif (et je ne m'amuse pas à en supprimer au hasard en ligne de commande dans la base ) et comme je l'ai réécrit après, l'intégrité des données à ce niveau là n'est pas critique et même si je me retrouve à avoir des données orphelines, ce n'est pas grave non plus. Mon problème est vraiment au niveau des performances et pas de l'intégrité de mes données