Optimisation de Dotclear 2 Beta 7 : La face cachée de la base de données

Ludovic Toinel — Sun, 18 Nov 2007 18:39:00 +0100

Après mon précédent article concernant quelques pistes d'optimisations de Dotclear 2, voici un nouvel article qui liste quelques pistes d'optimisations pour la base de données de Dotclear2 beta 7.

Remarque : Je tiens à mettre l'accent sur le "beta 7" car ces optimisations ne seront pas forcément applicables à la beta 8 ...

1) Tracker les requêtes SQL les plus lentes

Dotclear2 étant développé très proprement (merci à l'équipe de développement de Dotclear2), toutes les requêtes SQL passent par un composant qui se nomme "dblayer", qui est une couche d'abstraction à l'accès de la base de données.

inc/clearbricks/dblayer/

Cette couche permet à Dotclear2 de communiquer proprement avec sa base de données, celle-ci est composée d'une classe php "dblayer.php" et de plusieurs autres classes spécifiques aux bases de données que Dotclear 2 supporte.

Après une étude rapide du fichier dblayer.php, on se rend très vite compte que les requêtes de consultation "select" passent par la méthode "select()" de la classe dbLayer présent dans le fichier.

Pour connaitre la liste des requêtes SQL les plus longue, il suffit donc de tracer les requêtes depuis cette méthode

Voici la méthode "select()" avant patch:

public function select($sql)

        {

                $result = $this->db_query($this->__link,$sql);



                $this->__last_result =& $result;



                $info = array();

                $info['con'] =& $this;

                $info['cols'] = $this->db_num_fields($result);

                $info['rows'] = $this->db_num_rows($result);

                $info['info'] = array();



                for ($i=0; $i<$info['cols']; $i++) {

                        $info['info']['name'][] = $this->db_field_name($result,$i);

                        $info['info']['type'][] = $this->db_field_type($result,$i);

                }



                return new record($result,$info);

        }

Et la voici après patch :

public function select($sql)

        {

                $starttime = microtime();

                $result = $this->db_query($this->__link,$sql);

                $endtime = microtime();



                $tracesql = false;

                $totaltime = $endtime - $starttime;



                // Si la requête est plus longue que 0.5 secondes

                if ($totaltime > 0.5 ){

                        $tracesql = true;

                }



                // On trace la requête SQL

                if ($tracesql){



                        $fp = fopen("/tmp/db.log", "a");



                        $data = $totaltime . ";" . $sql . "\n";



                        // Write the data to the file

                        fwrite($fp, $data);



                        // Close the file

                        fclose($fp);

                }



                $this->__last_result =& $result;



                $info = array();

                $info['con'] =& $this;

                $info['cols'] = $this->db_num_fields($result);

                $info['rows'] = $this->db_num_rows($result);

                $info['info'] = array();



                for ($i=0; $i<$info['cols']; $i++) {

                        $info['info']['name'][] = $this->db_field_name($result,$i);

                        $info['info']['type'][] = $this->db_field_type($result,$i);

                }



                return new record($result,$info);

        }

Cette modification devrait nous permettre de tracer les requêtes select qui répondent en plus de 0.5 secondes dans un fichier qui se nomme "db.log" et qui sera créé dans le répertoire temporaire "/tmp".

2) Analyse de requêtes SQL

Après un peu de trafic le blog, vous devriez obtenir des requêtes SQL dans le fichier "/tmp/db.log". Si ce n'est pas le cas, soit votre blog ne contient pas beaucoup d'articles, soit vous avez un serveur avec pas mal de CPU

Note: Le premier chiffre qui se trouve devant la requête est le temps d'exécution de celle-ci.

Voici les trois requêtes SQL les plus redondantes qui ont été tracées sur mon blog. Les requêtes tracées dépendent énormément de la configuration de votre blog, de la quantité d'articles que contient votre blog, et de la qualité de votre hébergement Web :

SELECT meta_id, meta_type, COUNT(M.post_id) AS count FROM dc_meta M LEFT JOIN dc_post P ON M.post_id = P.post_id WHERE P.blog_id = 'default'  AND meta_type = 'tag' AND ((post_status = 1 AND post_password IS NULL ) ) GROUP BY meta_id,meta_type,P.blog_id ORDER BY count DESC LIMIT 50 



SELECT P.post_id, P.blog_id, P.user_id, P.cat_id, post_dt, post_tz, post_creadt, post_upddt, post_format, post_password, post_url, post_lang, post_title, post_excerpt, post_excerpt_xhtml, post_content, post_content_xhtml, post_notes, post_type, post_meta, post_status, post_selected, post_open_comment, post_open_tb, nb_comment, nb_trackback, U.user_name, U.user_firstname, U.user_displayname, U.user_email, U.user_url, C.cat_title, C.cat_url FROM dc_post P INNER JOIN dc_user U ON U.user_id = P.user_id LEFT OUTER JOIN dc_category C ON P.cat_id = C.cat_id WHERE P.blog_id = 'default' AND ((post_status = 1 AND post_password IS NULL ) ) AND post_type = 'post' AND (       (post_dt = '2006-03-29 22:49:37' AND P.post_id < 315)        OR post_dt < '2006-03-29 22:49:37' )  ORDER BY post_dt DESC, P.post_id DESC  LIMIT 1 



SELECT C.comment_id, comment_dt, comment_tz, comment_upddt, comment_author, comment_email, comment_site, comment_content,  comment_trackback, comment_status, comment_spam_status, comment_spam_filter, comment_ip, P.post_title, P.post_url, P.post_id, P.post_password, P.post_dt, P.user_id, U.user_email, U.user_url FROM dc_comment C INNER JOIN dc_post P ON C.post_id = P.post_id INNER JOIN dc_user U ON P.user_id = U.user_id WHERE P.blog_id = 'default' AND ((comment_status = 1 AND P.post_status = 1 AND post_password IS NULL ) ) ORDER BY comment_dt DESC  LIMIT 10

La première chose à vérifier est que les éléments présents après le "WHERE" des requêtes possèdent bien un index en base :

Sur la première requête, il manque des index sur meta_type, post_status et post_password.
Sur la deuxième requête, il manque un index sur post_type.
Sur la dernière requête, il manque un index sur comment_status et comment_dt

Voici les requêtes SQL pour créer ces index en base :

ALTER TABLE `dc_post` ADD INDEX ( `post_status` , `post_password` )  ;

ALTER TABLE `dc_post` ADD INDEX ( `post_type` )  ;



ALTER TABLE `dc_meta` ADD INDEX ( `meta_type` )  ;



ALTER TABLE `dc_comment` ADD INDEX ( `comment_status` )  ;

ALTER TABLE `dc_comment` ADD INDEX ( `comment_dt` )  ;

Après ces modifications, les requêtes que vous trouverez dans votre fichier db.log devraient être moins nombreuses. J'ai ouvert un post sur le forum de Dotclear2, mais je n'ai pas encore de réponse quand à mes optimisations.

Je souhaite remercier l'équipe Dotclear2 pour leur travail et les féliciter de cet merveilleux outil qu'est Dotclear2 !

Blog de Geeek, le blog geek par excellence par Ludovic Toinel ... - Balise - base de données

Optimisation de Dotclear 2 Beta 7 : La face cachée de la base de données

1) Tracker les requêtes SQL les plus lentes

2) Analyse de requêtes SQL