Optimisation de ma base

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 :aie:

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:

---

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:

---

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 ? :mrgreen:

Merci, d'avance ;)