Cache sql bon ratio entre insert et hits

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par JeunZ, 5 Avril 2006.

  1. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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?
     
  2. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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
     
  3. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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?
     
  4. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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 ?
     
  5. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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).
     
  6. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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.
     
  7. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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
     
  8. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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:
     
  9. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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.
     
  10. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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.
     
  11. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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.
     
  12. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    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 ^^ ;-)
     
  13. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    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:
     
  14. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
    Oh non je ne dirai pas que c'est ça vient de moi, mais je vais les informer.
     
  15. JeunZ
    JeunZ WRInaute accro
    Inscrit:
    18 Février 2004
    Messages:
    3 942
    J'aime reçus:
    0
  16. spidetra
    spidetra WRInaute passionné
    Inscrit:
    7 Juillet 2003
    Messages:
    1 215
    J'aime reçus:
    0
    J'ai mis à jour mon profil... ne jamais raté un coup offert :wink:
     
Chargement...
Similar Threads - Cache sql ratio Forum Date
cache mysql maison Développement d'un site Web ou d'une appli mobile 18 Février 2019
Panorama des systèmes de cache : PHP, MySQL, HTTP Administration d'un site Web 16 Août 2010
comment creér les Query Cache en mysql Développement d'un site Web ou d'une appli mobile 12 Avril 2010
Système de cache directement dans la base de données Mysql Développement d'un site Web ou d'une appli mobile 15 Mars 2010
Mise en cache d'une sortie mysql Développement d'un site Web ou d'une appli mobile 29 Décembre 2006
Combien de cache pour mysql? Administration d'un site Web 29 Juillet 2005
Requete sql en cache Administration d'un site Web 18 Avril 2005
un mdp mysql, ca se cache comment ? Administration d'un site Web 28 Septembre 2003
Stratégie de cache PWA : timestamp ? Développement d'un site Web ou d'une appli mobile 2 Septembre 2022
Cache Google sans police de caractères Débuter en référencement 9 Juillet 2022
Outil pour déterminer délai mise en cache navigateur Développement d'un site Web ou d'une appli mobile 8 Juin 2022
problème avec la mise en cache des images de mon site Développement d'un site Web ou d'une appli mobile 19 Décembre 2021
Apparence d'une page en cache et positionnement Crawl et indexation Google, sitemaps 24 Novembre 2021
Site à cacher dans l'historique de navigation Administration d'un site Web 5 Octobre 2021
Javascript API cache query non url ? Développement d'un site Web ou d'une appli mobile 18 Août 2021
Cache Etag nginx et PHP ? Administration d'un site Web 7 Août 2021
Fetch quelles techniques de cache ? Développement d'un site Web ou d'une appli mobile 1 Août 2021
Supprimer la version en cache de dizaines de PDF Crawl et indexation Google, sitemaps 11 Janvier 2021
Le contenu « caché » par un accordéon a-t-il autant de poids SEO que du texte « normal » ? Référencement Google 6 Janvier 2021
Cache avec contenu variable ? Développement d'un site Web ou d'une appli mobile 2 Décembre 2020