Une très grosse table ou plusieurs petites

[fred078]

Bonjour,

Nous avons des problèmes de lenteurs, en particulier avec les requêtes faites sur une table.

Cette table a environ 500 000 enregistrements et pèse environ 3 Go (et ça va doubler/tripler dans les mois a venir).

Il y a sur cette table des millions de requêtes chaque jour (requêtes PHP, table mysql).

Les requêtes sont plutôt optimisées, et la table est dotée d'index.

Je me pose la question suivante :

Le fait de diviser cette grosse table en 26 petites tables (en divisant nos clients par ordre alphabétique : les clients dont l'identifiant commence par A auraient leurs données dans la table "table_a" par exemple) améliorerait les choses ?

Pour vous donner une idée de l'architecture, cette table a peu de colonnes (une 10ene), 2 de ces colonnes contiennent de données lourdes et représentent l'essentiel du poids total de la table.

Merci d'avance pour votre aide,

Fred

[K-Ola]

C'est certain que plusieurs petites tables en nombre raisonnable améliorera les performances.

Au niveau architecture serveur, il y a aussi des choses à faire: un ou plusieurs serveurs de cache pour limiter les requêtes ou encore du load balancing pour répartir la charge.

Modifié 15 Juin 2010 par K-Ola

[fred078]

Bonjour,

Merci pour ta réponse.

En fait nous avons déjà une gestion du cache pour cette table. et plusieurs serveurs avec load balancing et tout le bazar (c'est pas moi qui gère tout ca mais je sais qu'on a !) Après peut-etre qu'il y a des optimisations a faire la dessus, ils y travaillent aussi.

[K-Ola]

Ok, dans ce cas, je pense que tu peux sans problème diviser ta table en plusieurs petites, en faisant bien attention à prévoir la maintenance (si tu as besoin de diviser un jour table_a en table_aa table_ab etc)

[captain_torche]

Avant de scinder la base en plusieurs tables, ne faudrait-il pas regarder s'il y a autre chose qui cloche ?

Sur quels champs effectues-tu tes recherches ? Sont-ils indexés ?

Utilises-tu le joker (*) lors de tes requêtes, ou ne cibles-tu que les champs qui te seront utiles ?

[fred078]

Bonjour,

Il y a bien des index sur les champs recherchés, et nous avons banni les * pour toujours extraire uniquement le nécessaire.

[captain_torche]

Les recherches s'effectuent-elles sur les gros champs, ou d'autres champs plus "simples" ?

[fred078]

Non, la recherche s'effectue sur deux champs simple type idendifiant (alphanumérique, 50 caractères max dans un varchar)

c'est du genre :

SELECT gros_chp_1, gros_chp_2 WHERE id_1='blabla' AND id_2='toto'

[captain_torche]

Est-il possible de faire en sorte que les champs soient en "unique" ?

[jcaron]

Perso, je ne vois pas bien en quoi la découpe en plusieurs tables ferait gagner quoi que ce soit: le volume final de données étant le même, à condition de les bons index soient en place, ça ne changera rien, le premier niveau de l'index (qui fait grosso-modo l'équivalent de la découpe) va être en cache en permanence... Le partitionnement c'est utile dans de nombreux cas, mais ce n'est pas une recette miracle non plus, il faut qu'il y ait une bonne raison pour que ça apporte quoi que ce soit (et dans de nombreux cas ça va au contraire réduire les perfs, sans parler de la complication du code).

Les bonnes questions sont plutôt:

- as-tu bien un index sur (id_1,id_2) (un seul index qui contient les deux colonnes), et toute autre combinaison de colonnes déjà utilisée?

- que donne un explain sur les requêtes en question?

- combien y a-t-il de RAM sur ce serveur, et quel est la taille totale de la base (et éventuellement la partie "utile", i.e. celle qui est lue régulièrement)?

- que donne la charge CPU, RAM, et accès disques de la machine?

- mysql est-il correctement configuré au niveau buffers etc?

- y a-t-il beaucoup d'accès en écriture sur cette table?

- de quel type de table s'agit-il (myisam / innodb)?

Je pars évidemment du principe que le serveur de bdd est dédié à cette tâche.

Au delà, s'il y a plusieurs frontaux, avez-vous mis en place un cache partagé (memcache)?

Jacques.

[fred078]

Bonjour,

Merci pour toutes ces pistes. Nous allons les étudier une à une.

Nous avons commencé par créer un index (id_1,id_2). J'ai pas l'impression que l'amélioration soit perceptible.

EXPLAIN nous donne :

------------

id : 1

select_type : SIMPLE

table : matable

type : ref

possible_keys : id_1,id_2,id_1_2 **id_1_2 correspond à l'index (id_1,id_2)

key : id_1_2

key_len : 514

ref : const,const

rows : 2

Extra : Using where

------------

RAM en place : 2048Mo sur chaque serveur.

y a-t-il beaucoup d'accès en écriture sur cette table?

Beaucoup oui (INSERT et UPDATE), mais par rapport au nombre total de requetes, c'est surement pas plus de 10-15%

(pour les autres questions, je vais regarder.)

Par contre je viens de tomber sur quelque chose qui m'interroge :

Nous retrouvons dans la page qui réalise 90% des affichages 3 requetes de ce type :

SELECT champ1,champ2 FROM matable WHERE id_1='truc' AND id_2='chouette' LIMIT 1

[...]

SELECT champ3,champ4 FROM matable WHERE id_1='truc' AND id_2='chouette' LIMIT 1

[...]

SELECT champ5,champ6 FROM matable WHERE id_1='truc' AND id_2='chouette' LIMIT 1

Est ce que si on regroupe tout dans une seule et même requete nous aurons un gain significatif potentiel ?

(ce sont des requetes séparées car certaines sortent un contenu mis en cache, d'autres non).

Merci pour votre aide.

Fred

[jcaron]

- le nouvel index devrait mécaniquement améliorer les choses. Mais tu y gagneras probablement à virer les index inutiles maintenant

- tu n'as clairement pas assez de RAM. Tu as quoi comme disque(s)? Il est vraisemblable que tu les satures en I/O

- oui, regrouper les 3 requêtes en une seule te fera gagner, mais pas forcément significativement

- une requête avec un LIMIT 1 sans ORDER BY c'est très bizarre...

Jacques.