Bonjour à tous,
je bosse sur une appli de gestion de données bibliographique qui commence à me poser des problèmes.
En gros, je gère des documents numériques et pour chacun un certain nombre de données associées (appelées métadonnées).
L'appli est munie d'un moteur de recherche évolué, de fonctionnalités de navigation au sein du corpus et pour chaque document elle peut afficher l'ensemble des métadonnées sur une page (la "notice")
Pour ceux que cela intéresse : http://www.enssib.fr/bibliotheque-numerique/

Tout cela fonctionnait bien jusqu'à présent avec environ un bon millier de documents et une dizaine de millier de métadonnées, grâce aux algos que j'ai optimisés autant que j'ai pu.

Problème : je vais y ajouter d'un coup un gros volume de documents, on va passer de 1100 à près de 20 000 documents, et je pense que la BD ne va pas supporter le choc.
Sur mon serveur de développement, avec 15 000 docs pour 80 000 métadonnées, c'est déjà trop : le moteur de recherche est complètement HS, la navigation considérablement ralentie ... bref le service est inutilisable

Donc je me demandais s'il y avait des pros de mysql qui pouvaient m'aider à optimiser tout ça.
Voilà les pistes auxquelles j'ai pensé, mais il y en a peut être d'autres :
- optimisation de la structure de la BD (tables, indexes etc ...)
- optimisation des requêtes SQL
- optimisation des fonctions utilisateur dans mysql (j'en ai une ou deux que je suspecte de plomber les perfs mais je ne sais pas comment les améliorer)
- optimisation du paramétrage du serveur mysql ?
- augmentation de la conf materielle du serveur ?
- déménagement de la BD sur un serveur dédié (actuellement sur le serveur web)
- migration vers un autre SGBD (oracle ? postgresql ?) mais je ne sais pas si ça serait mieux
- un peut tout ça à la fois ? ...
- vous avez d'autres idées ?

Si vous conaissez ce genre de problématiques et que vous avez des conseils à me donner, ça serait hyper cool de votre part, parceque là mes compétences en BD et en administration système atteignent leurs limites ....

Pour ceux qui suivent toujours, voici plus de détails techniques sur mon appli et la conf de mon serveur :

Serveur de production :
CPUs : 2 Intel Xeon à 2.8GHz chacun
RAM : 2.5 Go
Disques durs en RAID 0
SWAP : 4Go (très peu utilisé)
Système : Linux Fedora Core 6
Apache 2, PHP 5, MySQL 5, le tout à peu près avec les confs par défaut

Bref ... un serveur pas trop mal, quand même
Ce serveur héberge un site web qui délivre dans les 10 000 pages par jour.

Structure (partielle) de la base de données :
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
--
-- Structure de la table `collection`
--
 
CREATE TABLE IF NOT EXISTS `collection` (
  `id_collection` int(10) unsigned NOT NULL auto_increment,
  `nom` varchar(128) NOT NULL,
  `description` varchar(512) default NULL,
  `visible` tinyint(1) NOT NULL default '0',
  `collection_mere` int(10) unsigned default NULL,
  `ordre_affichage` int(2) unsigned default NULL,
  `nbDocs` int(11) default NULL,
  `nbTotalDocs` int(11) default NULL,
  PRIMARY KEY  (`id_collection`),
  UNIQUE KEY `nom` (`nom`,`collection_mere`),
  UNIQUE KEY `collec_ordre_unique` (`collection_mere`,`ordre_affichage`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 3072 kB; (`collection_mere`) REFER `bibnum/coll' AUTO_INCREMENT=1503 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `document`
--
 
CREATE TABLE IF NOT EXISTS `document` (
  `id_document` int(10) unsigned NOT NULL auto_increment,
  `nom_fichier` varchar(128) default NULL,
  `collection` int(10) unsigned NOT NULL default '0',
  `deposant` varchar(64) NOT NULL default '0',
  `idLicence` int(8) default NULL,
  `dateDepot` date NOT NULL,
  `dateDerniereModification` date default NULL,
  `idStatut` int(2) NOT NULL default '1',
  `motifStatut` text,
  `dateStatut` date default NULL,
  `idLabel` int(10) default NULL,
  PRIMARY KEY  (`id_document`),
  KEY `FK_document_2` USING BTREE (`collection`),
  KEY `deposant` (`deposant`),
  KEY `idLicence` (`idLicence`),
  KEY `idStatut` (`idStatut`),
  KEY `FK_label` (`idLabel`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB; (`collection`) REFER `bibnum/collectio' AUTO_INCREMENT=126257 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `metadonnee`
--
 
CREATE TABLE IF NOT EXISTS `metadonnee` (
  `idMetadonnee` int(8) NOT NULL auto_increment,
  `identifiant` varchar(64) NOT NULL default '',
  `nom` varchar(64) NOT NULL default '',
  `definition` varchar(256) default NULL,
  `commentaire` varchar(512) default NULL,
  `version` varchar(16) default NULL,
  `autorite` varchar(64) default NULL,
  `langue` varchar(8) NOT NULL default 'fr',
  `obligation` enum('Optionnel','Obligatoire') NOT NULL default 'Optionnel',
  `type` varchar(64) default NULL,
  `occurence` int(10) unsigned default NULL,
  `est_raffinement_de` int(8) default NULL,
  `afficher_utilisateur` enum('oui','non','dequalif') NOT NULL default 'oui',
  `exporter_dc_simple` enum('oui','non') NOT NULL default 'oui',
  `exporter_dc_enrichi` enum('oui','non','dequalif') NOT NULL default 'oui',
  `interrogeable_moteur_recherche` enum('oui','non') NOT NULL default 'oui',
  `saisissable_deposant` enum('oui','non') NOT NULL default 'oui',
  `saisieSemiAutomatiqueActivee` tinyint(1) NOT NULL default '0',
  `typeSaisie` enum('libre','personne','texte','date','format','langue','document','typeRessource') NOT NULL default 'libre',
  PRIMARY KEY  (`idMetadonnee`),
  UNIQUE KEY `identifiant` (`identifiant`),
  UNIQUE KEY `nom` (`nom`),
  KEY `est_raffinement_de` (`est_raffinement_de`),
  KEY `langue` (`langue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Palette des metadonnées disponibles' AUTO_INCREMENT=98 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `notice`
--
 
CREATE TABLE IF NOT EXISTS `notice` (
  `id_document` int(10) unsigned NOT NULL default '0',
  `valeur_meta` text NOT NULL,
  `idMetadonnee` int(8) NOT NULL,
  PRIMARY KEY  (`id_document`,`idMetadonnee`,`valeur_meta`(767)),
  KEY `idMetadonnee` (`idMetadonnee`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 9216 kB; (`identifiant_meta`) REFER `bibnum/met';
 
--
-- Contraintes pour les tables exportées
--
 
--
-- Contraintes pour la table `collection`
--
ALTER TABLE `collection`
  ADD CONSTRAINT `collection_ibfk_1` FOREIGN KEY (`collection_mere`) REFERENCES `collection` (`id_collection`) ON DELETE CASCADE ON UPDATE CASCADE;
 
--
-- Contraintes pour la table `document`
--
ALTER TABLE `document`
  ADD CONSTRAINT `document_ibfk_45` FOREIGN KEY (`collection`) REFERENCES `collection` (`id_collection`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_ibfk_46` FOREIGN KEY (`deposant`) REFERENCES `utilisateur` (`login`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_ibfk_47` FOREIGN KEY (`idLicence`) REFERENCES `Licence` (`idLicence`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_ibfk_48` FOREIGN KEY (`idStatut`) REFERENCES `statutDocument` (`idStatut`) ON UPDATE CASCADE,
  ADD CONSTRAINT `document_ibfk_49` FOREIGN KEY (`idLabel`) REFERENCES `label` (`idLabel`) ON UPDATE CASCADE;
 
--
-- Contraintes pour la table `metadonnee`
--
ALTER TABLE `metadonnee`
  ADD CONSTRAINT `metadonnee_ibfk_1` FOREIGN KEY (`est_raffinement_de`) REFERENCES `metadonnee` (`idMetadonnee`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `metadonnee_ibfk_2` FOREIGN KEY (`langue`) REFERENCES `langue` (`abreviation_langue`) ON UPDATE CASCADE;
 
--
-- Contraintes pour la table `notice`
--
ALTER TABLE `notice`
  ADD CONSTRAINT `FK_notice_doc` FOREIGN KEY (`id_document`) REFERENCES `document` (`id_document`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `notice_ibfk_1` FOREIGN KEY (`idMetadonnee`) REFERENCES `metadonnee` (`idMetadonnee`) ON UPDATE CASCADE;
La table "document", répertorie les documents (environ 1000 enregistrements) mais ne les contient pas (ils sont sur le disque dur), la table "métadonnée" répertorie les champs de description disponibles pour les documents (environ 70 enregistrements), et la table "notice" (la plus grosse : environ 15 000 enregistrements) associe des valeurs aux documents pour des métadonnées.
Quand à la table "collection", elle range les collections de façon hiérarchique (une collection peut être dans une autre collection et ainsi de suite ...) chaque document étant associé à une et une seule collection.

Comme je le disais plus haut, j'ai aussi défini quelques fonctions utilisateurs et j'ai peur qu'elles fassent ramer le tout :

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
36
37
38
39
40
41
42
43
 
CREATE DEFINER=`root`@`localhost` FUNCTION `document_dans_collection`(idDocument INT(10), idCollection INT(10)) RETURNS BOOL
SQL SECURITY INVOKER
RETURN (
        (idCollection = (SELECT collection FROM document WHERE id_document = idDocument)) 
        OR est_collection_descendante_de((SELECT collection FROM document WHERE id_document = idDocument), idCollection)
    )
 
 
 
CREATE DEFINER=`root`@`localhost` FUNCTION `est_collection_descendante_de`(idCollection INT(10), idCollectionAncetre INT(10)) RETURNS BOOL
    READS SQL DATA
    SQL SECURITY INVOKER
RETURN (est_sous_collection_de(idCollection,idCollectionAncetre) OR est_sous_collection_de((SELECT collection_mere FROM collection WHERE id_collection = idCollection),idCollectionAncetre))
 
 
 
CREATE DEFINER=`root`@`localhost` FUNCTION `est_sous_collection_de`(idCollection INT(10), idCollectionMere INT(10)) RETURNS BOOL
    READS SQL DATA
    SQL SECURITY INVOKER
RETURN ((SELECT collection_mere FROM collection WHERE id_collection = idCollection) = idCollectionMere)
 
 
 
-- cette fonction retourne TRUE si le document est "sous une notice chapeau"
CREATE DEFINER=`root`@`localhost` FUNCTION `document_est_fils`(idDocument INT(10)) RETURNS BOOL
RETURN (
            idDocument IN (
                SELECT id_document 
                FROM notice 
                WHERE idMetadonnee = 33 
                UNION 
                SELECT valeur_meta AS id_document 
                FROM notice 
                WHERE idMetadonnee = 28
            )
);
 
 
CREATE DEFINER=`root`@`localhost` FUNCTION `extention`(nom_fichier VARCHAR(128)) RETURNS varchar(8)
    DETERMINISTIC
    SQL SECURITY INVOKER
RETURN CONCAT('.',SUBSTRING_INDEX(nom_fichier, '.', -1))
Est-ce que vous voyez des énormités dans mon code SQL ?
Est-ce que vous pensez que je peux améliorer la structure de mes tables tout en conservant la consistance des données et sans re-développer la couche métier de mon appli (genre en optimisant les indexes ?) ?
Est-ce que vous pensez que je devrais avoir plus de ressources materielles pour mon serveur ?

Est-ce que passer ma BD sous Oracle ou PostgreSQL pourrait m'aider ? (ma couche métier est basée sur une couche d'abstraction de base de donnée, donc ça ne devrait pas poser de problème).

Est-ce que vous avez déjà été confronté à ce genre de problème et comment vous en êtes-vous sorti ?

Bref, est-ce que vous pouvez me filer un coup de main SVP ?

Merci, d'avance