Cache sql bon ratio entre insert et hits

JeunZ · 5 Avril 2006

Ma question est toute simple, je tente d'optimiser mon cache sql.

Il est dit partout qu'il est important de regarder le ratio entre les inserts dans le cache et les hits.

A partir de quand estimes t'on le ratio bon? 1 insert pour 2 hits? 1 pour 10?

Et si on est en dessous de ce ratio que faire?

spidetra · 5 Avril 2006

Plus ton ratio est élevé et plus ton système de cache est performant.
Un ratio de 1/2 me semble être un très mauvais ratio.
Cela veut dire que ton système de cache ne sert presque à rien.
J'essaye d'avoir un ratio >> 10

Il est de combien ton ratio ?

Il faut aussi regarder la variable Qcache_lowmem_prunes, cette variable doit tendre vers zéro. Si ça valeur est trop grande, c'est que la taille de ton cache est trop petit.

Ce ratio dépend aussi bc des requêtes que tu fait sur ta base.
Le ratio pour un site web devrait être assez grand ( bc de lectures des mêmes infos ).

Perso, j'utilise trois niveau de cache :
- SGBD : cache sql
- Couche DAO : cache niveau mapping O/R
- Couche Web : cache de page

JeunZ · 5 Avril 2006

Moi mon problème, c'est que c'est une plateforme de blog, et il y a énormément d'insert, d'update, peut être presque plus que de select.

Du coup j'ai un ratio qui à mon avis, et tu confirmes n'est pas génial 1 insert pour 2 hits.

Donc je me demandais à ce niveau si il fallait couper le cache ou si c'était quand même mieux que rien ou tenter de faire changer ce ratio?

spidetra · 5 Avril 2006

JeunZ a dit:
Donc je me demandais à ce niveau si il fallait couper le cache ou si c'était quand même mieux que rien ou tenter de faire changer ce ratio?

Tu me pose une colle.

Je ne comprend pas pourquoi les INSERT et les UPDATE entreraient en jeu dans ton ratio.
J'étais persuadé que seul les SELECT étaient mis en cache.
A conbien est ton Qcache_lowmem_prunes ?

Tu joue sur d'autres niveau de cache ?

JeunZ · 5 Avril 2006

Qcache lowmem prunes 0

Les update changent la valeur d'un champs donc je suppose que ça efface les requetes select qui étaient en cache qui retournaient ce champs? Et du coup on refait une insertion dans le cache lors du select suivant qui touche à ce champs.

Je n'ai pas d'autre cache.

J'ai fais des tonnes de recheches ils disent partout plus le ratio est grand mieux c'est, mais ils ne disent nul part à partir de quand il vaut mieux désactiver le cache.

Par exemple pour eacceleration (cache php) mon serveur est beaucoup plus rapide sans que avec, quelque soit la config de ce cache, et c'est du à une structure assez spéciale (du fait de la plateforme de blog).

spidetra · 6 Avril 2006

Le mieux c'est de faire des tests. Optimisez un SGBD c'est une question de recettes personnelles.
Tu verras si tu as de gros écarts entre avec/sans cache.
C'est vrai que ta situation est assezz atypique.

Question subsidiaire : Tu as bc d'index sur tes tables ?
Perso, j'ai plutôt tendance à sur-utiliser les index :
- Index clé primaire
- Index clé étrangère
- Recherche de colonne candidates pour des index supplémentaires.

C'est efficace lorsque tu as bc+ de SELECT que de INSERT ou de UPDATE.
Dans ton cas tu as peut-être intérêt à te limiter aux index sur les clés primaires et les clés étrangères.
Là encore, pas de règles miracles. Il faut tester et essayer d'optimiser ta base et tes requêtes.

JeunZ · 6 Avril 2006

Disons que ma plateforme tourne sous dotclear, et au vu du boulot réaliser sur dotclear on peut estimer que la structure et les requetes sont bonnes ;-)

En ayant laissé tourné un peu plus longtemps le cache je m'approche du 1 pour 3 mais bon je vais quand même vérifier toutes les requêtes etc...

De toute façon à priori j'ai aucun problème de ressources, c'est juste que je préfère optimiser maintenant plutôt que le faire quand ça ira mal ^^.

J'ai pas mal de slow query aussi, donc je dois de toute façon m'y pencher.

Enfin alexa me donne quand meme le résultat suivant:

Speed: Very Fast (88% of sites are slower), Avg Load Time: 0.7 Seconds

spidetra · 6 Avril 2006

JeunZ a dit:
Disons que ma plateforme tourne sous dotclear, et au vu du boulot réaliser sur dotclear on peut estimer que la structure et les requetes sont bonnes ;-)

Houlà ! Sur ce point on ne vas pas du tout être d'accord !
La modélisation du SGBD est souvent un des point faible de beaucoup de système OpenSource.

Par défaut, je ne fais jamais confiance à 100% au modèle d'un projet OpenSource.

J'analyse systématique la base pour voir si je peux l'optimiser ou pas.

Prenons le cas particulier de dotClear avec les tables User et Post.

Table User :

Code:

CREATE TABLE `{{PREFIX}}user` (
    `user_id` varchar(32) binary NOT NULL default '',
    `user_level` int(11) NOT NULL default '0',
    `user_pwd` varchar(32) binary NOT NULL default '',
    `user_nom` varchar(255) binary default NULL,
    `user_prenom` varchar(255) binary default NULL,
    `user_pseudo` varchar(255) binary default NULL,
    `user_email` varchar(255) default NULL,
    `user_post_format` varchar(5) NOT NULL default 'wiki',
    `user_edit_size` int(11) NOT NULL default '10',
    `user_pref_cat` int(11) default NULL,
    `user_lang` char(3) default NULL,
    `user_delta` int(1) NOT NULL default '0',
    `user_post_pub` int(1) NOT NULL default '0',
    PRIMARY KEY  (`user_id`)
    ) TYPE=MyISAM

Table Post :

Code:

 CREATE TABLE `{{PREFIX}}post` (
    `post_id` int(11) NOT NULL auto_increment,
    `user_id` varchar(32) binary NOT NULL default '',
    `cat_id` int(11) default NULL,
    `post_dt` datetime default NULL,
    `post_creadt` datetime default NULL,
    `post_upddt` datetime default NULL,
    `post_titre` varchar(255) default NULL,
    `post_titre_url` varchar(255) default NULL,
    `post_chapo` longtext,
    `post_chapo_wiki` longtext,
    `post_content` longtext,
    `post_content_wiki` longtext,
    `post_notes` longtext,
    `post_pub` int(1) NOT NULL default '0',
    `post_selected` int(1) NOT NULL default '0',
    `post_open_comment` int(1) NOT NULL default '0',
    `post_open_tb` int(1) NOT NULL default '0',
    `nb_comment` int(11) NOT NULL default '0',
    `nb_trackback` int(11) NOT NULL default '0',
    `post_lang` varchar(5) default NULL,
    PRIMARY KEY  (`post_id`)
    ) TYPE=MyISAM

Création des index :

Code:

    ALTER TABLE `{{PREFIX}}post` ADD INDEX `fk_post_categorie` (`cat_id`,`post_pub`)
    ALTER TABLE `{{PREFIX}}post` ADD INDEX `fk_post_user` (`user_id`,`post_pub`)

Sauf erreur de ma part ( je n'ai pas créé la database, donc j'ai peut-être raté un bout de code ), Il n'y a pas d'index sur la clé étrangère user_id dans la table post.

Pourquoi ?

Je ne connais pas toutes les requêtes de dotClear, mais je suis prêt à parier que des requêtes vont faire des jointures entre le user_id de la table user et celui de la table post.
Ces jointures seront toujours plus rapides et plus performantes avec des index.

Perso j'ai une règle de base que j'applique systématiquement , sans réfléchir, sur toutes mes bases :
- Index sur les clés primaires
- Index sur les clés étrangères.

Je ne fais jamais l'économie de ces deux types d'index. Je ne pose pas la question de savoir s'ils seront utiles ou pas.
Dans 95% des cas ils sont indispensables !

Donc tu as peut-être aussi par là une piste d'optimisation :wink:

JeunZ · 6 Avril 2006

Ah oui vu comme ça c'est vrai que tu es sans doute dans le vrai.

Moi, naif, je me disais le gars il passe tellement de temps sur son projet, il y a tellement de gens qui utilisent ce projet, y apportent des modifs, que forcément c'est optimisé.

EDIT: après reflexion, je pense qu'il n'a pas mit d'index sur user_id car on ne fait à ma connaissance jamais de select sur l'user_id, c'est toujours soit sur une catégorie, soit une date etc... Mais bon ça ne coute en effet rien de la créer.

spidetra · 6 Avril 2006

Je ne sais pas. Je n'ai pas regardé toutes les requêtes.

Pour ne pas focaliser sur dotClear.
J'ai eu récemment le cas d'un projet OpenSource qui faisait 50 SELECT pour retourner 50 lignes.
Au lieu de faire 1 SELECT avec une jointure entre les 2 tables.

Sur un serveur dédié , avec PostgreSQL comme SGBD, le système est tombé dans les choux avec une trentaine de user connecté en permanence.
On a passé des semaines à reprendre le code pour tout optimiser.

Donc j'ai décidé de ne plus faire confiance aveuglement.

Dans ton cas attention : Tu as plus d'INSERT et d'UPDATE que de SELECT. Donc tes requetes de modification de la DB seront légèrement plus lente.

spidetra · 6 Avril 2006

JeunZ a dit:
EDIT: après reflexion, je pense qu'il n'a pas mit d'index sur user_id car on ne fait à ma connaissance jamais de select sur l'user_id, c'est toujours soit sur une catégorie, soit une date etc... Mais bon ça ne coute en effet rien de la créer.

Tu veux dire des requêtes de ce type entre Post et User

Code:

$strReq = 'SELECT U.user_id,user_level,user_nom,user_prenom,'.
				'user_pseudo,user_email,user_post_format,user_edit_size,'.
				'user_pref_cat, user_lang, user_delta, user_post_pub, '.
				'count(P.post_id) AS nb_post '.
				'FROM '.$this->t_user.' U '.
				'	LEFT JOIN '.$this->t_post.' P ON U.user_id = P.user_id '.
				'WHERE 1 '.
				$reqPlus.
				'GROUP BY U.user_id '.
				'ORDER BY U.user_id ASC ';

LEFT JOIN '.$this->t_post.' P ON U.user_id = P.user_id '.

Tu vois j'ai passé 5mn sur le code source de dotClear et j'ai au moins trouvé une requete qui fait une jointure entre ces 2 champs.

JeunZ · 6 Avril 2006

Tu vas rire...

Mais j'ai ajouté deux index, un dans post sur user_id et un autre dans la table user, après avoir regardé les requetes qui faisaient du slow query.

Et je n'ai plus eu depuis de requete slow query alors que j'en avais une toutes les 5 secondes en moyenne.

Je me dois de t'inviter boire un coup si je passe dans ta région ^^ ;-)

spidetra · 6 Avril 2006

Regarde toutes tes tables et rajoutent des index sur toutes les clés étrangères.
Essayons d'être utile.
Un petit contact avec l'équipe de dev de dotClear pour leur proposer de patcher leurs scripts de création de DB.

Je te laisse faire et récolter les lauriers :lol:

JeunZ · 6 Avril 2006

Oh non je ne dirai pas que c'est ça vient de moi, mais je vais les informer.

JeunZ · 6 Avril 2006

On va voir la réponse:

http://www.dotclear.net/forum/viewtopic ... 396#p86396

mais en tout cas ça fait plaisir de ne plus avoir de slow query, merci encore une fois :wink:

spidetra · 6 Avril 2006

JeunZ a dit:
Je me dois de t'inviter boire un coup si je passe dans ta région ^^ ;-)

J'ai mis à jour mon profil... ne jamais raté un coup offert :wink: