Optimisation BDD

[villagedefrance]

Opencart utilise "utf8_general_ci", qui est un format international et qui fonctionne trés bien en Francais.

Mon conseil serait de convertir toutes tes tables "utf8_bin" et "latin1_swedish_ci", en "utf8_general_ci". Cela ne devrait pas poser aucun problème, mais fait quand même un backup (Export Tables) avant, pour ne rien perdre, juste au cas ou.

Pour répondre a ta question, Oui, bien souvent les extensions (modules) installent leur propre tables et selon leur provenance, on se retrouve avec toute sorte de collations. Mes plus anciennes extensions par exemple, ajoutaient des tables en "utf8_bin", car cela était l'ancien défaut pour OC, mais c'est maintenant "utf8_general_ci", et je suis en train de tout mettre à jour.

[Canou83]

Bine je vinde basculer tout ce qu'il y avait à basculer dans la BDD en "utf8_general_ci"

Question. Est-il normale, si je prends un exemple au hasard de table, que certaines colonnes n'aient pas d'indication "Interclassement" ?

Faut-il en mettre ?

[villagedefrance]

Oui, c'est normal car ce sont des chiffres : INT (integer) et decimal.
Seul les valeures texte, VARCHAR (variable characters) ont besoin d'une collation .
Surtout n'ajoute pas de collation si il n'y en a pas initiellement ! Remplace uniquement !

[Canou83]

Et bien merci villagedefrance, c'estfait et cela fonctionne bien.

j'en ai aussi profiter dans mon fichier my.cnf de rajouter :

Code: Tout sélectionner

collation_server=utf8_general_ci
character_set_server=utf8

L'ensemble de mes solutions tournent maintenant sour le même charset

Bon et bien il me reste plus qu'à essayer de comprendre l'histoire des indexes.... Et là c'est une autre histoire.

Malgré toute la bonne volonté de slwsu, je n'y pige rien du tout. je ne sais même pas si fondamentalement cela changerai quelque chose, j'imagine que oui.

[slwsu]

Malgré toute la bonne volonté de slwsu, je n'y pige rien du tout. je ne sais même pas si fondamentalement cela changerai quelque chose, j'imagine que oui.

Imagine que tu cherche un mot dans un dictionnaire, tu n'a pas le choix, tu l'ouvre et tu tourne page par page pour trouver le bonne page puis le bon mots...
A la fin de ce dictionnaire, il y a un index, si tu cherche via cet index, tu retrouve ta page plus vite car il y a moins de données a parcourir, tu n'as pas besoin de chercher la page, c'est via l'index que tu trouve le n° de page...

hé bien le moteur fonctionne un peut de la même façon...

Bien sur ce n'est qu'une image

[Canou83]

Oui pardon ça j'ai compris, mais c'est la manière de le mettre en place que je n'ai pas compris

[slwsu]

Je viens de regarder un truc, le moteur utilisé est MyISAM et je pense me souvenir que MySIAM ne gère pas les contraintes de clés étrangères, a vérifier !

[Canou83]

Bonsoir slwsu, bon je n'ai pas vraiment eu le temps aujourd'hui de me plonge dans tout ça, mais depuis 24 h (je crois) j'ai fait mouliner les logs slow query sur mon serveur pour regarder de prêt.

Il y a de la lecture

Quelques exemples :

Code: Tout sélectionner

# Time: 140121 11:39:04
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.037662  Lock_time: 0.020092 Rows_sent: 683  Rows_examined: 1366
use web160aquamag;
SET timestamp=1390300744;
SELECT * FROM oc_setting WHERE store_id = '0' OR store_id = '0' ORDER BY store_id ASC;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.018007  Lock_time: 0.012500 Rows_sent: 2  Rows_examined: 4
SET timestamp=1390300744;
SELECT * FROM oc_weight_class wc LEFT JOIN oc_weight_class_description wcd ON (wc.weight_class_id = wcd.weight_class_id) WHERE wcd.language_id = '2';
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.014257  Lock_time: 0.009847 Rows_sent: 3  Rows_examined: 6
SET timestamp=1390300744;
SELECT * FROM oc_length_class mc LEFT JOIN oc_length_class_description mcd ON (mc.length_class_id = mcd.length_class_id) WHERE mcd.language_id = '2';

Code: Tout sélectionner

# Time: 140121 11:39:05
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.031734  Lock_time: 0.028122 Rows_sent: 1  Rows_examined: 27
SET timestamp=1390300745;
SELECT tr2.tax_rate_id, tr2.name, tr2.rate, tr2.type, tr1.priority FROM oc_tax_rule tr1 LEFT JOIN oc_tax_rate tr2 ON (tr1.tax_rate_id = tr2.tax_rate_id) INNER JOIN oc_tax_rate_to_customer_group tr2cg ON (tr2.tax_rate_id = tr2cg.tax_rate_id) LEFT JOIN oc_zone_to_geo_zone z2gz ON (tr2.geo_zone_id = z2gz.geo_zone_id) LEFT JOIN oc_geo_zone gz ON (tr2.geo_zone_id = gz.geo_zone_id) WHERE tr1.tax_class_id = '9' AND tr1.based = 'shipping' AND tr2cg.customer_group_id = '3' AND z2gz.country_id = '74' AND (z2gz.zone_id = '0' OR z2gz.zone_id = '1197') ORDER BY tr1.priority ASC;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.014801  Lock_time: 0.004122 Rows_sent: 0  Rows_examined: 676
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '1314' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.016393  Lock_time: 0.013073 Rows_sent: 0  Rows_examined: 44
SET timestamp=1390300745;
SELECT * FROM oc_tabs t LEFT JOIN oc_tabs_description td ON (t.tab_id = td.tab_id) LEFT JOIN oc_tabs_to_product t2p ON (t.tab_id = t2p.tab_id) WHERE t2p.product_id = '1314' AND td.language_id = '2' AND t.status = '1' ORDER BY t.sort_order, t.tab_id ASC;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.002742  Lock_time: 0.001962 Rows_sent: 1  Rows_examined: 14
SET timestamp=1390300745;
SELECT * FROM oc_layout_route WHERE 'product/product' LIKE CONCAT(route, '%') AND store_id = '0' ORDER BY route DESC LIMIT 1;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.007640  Lock_time: 0.006949 Rows_sent: 32  Rows_examined: 53
SET timestamp=1390300745;
SELECT * FROM oc_extension WHERE `type` = 'module';
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000851  Lock_time: 0.000060 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '89' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000691  Lock_time: 0.000062 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '1409' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000673  Lock_time: 0.000060 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '92' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000623  Lock_time: 0.000059 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '139' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000628  Lock_time: 0.000058 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '128' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000645  Lock_time: 0.000058 Rows_sent: 1  Rows_examined: 678
SET timestamp=1390300745;
SELECT SUM( quantity ) AS quantity_purchased FROM oc_order_product WHERE product_id = '83' GROUP BY product_id;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.007534  Lock_time: 0.006660 Rows_sent: 0  Rows_examined: 676
SET timestamp=1390300745;
SELECT p.product_id FROM oc_order_product opa, oc_order_product opb, `oc_order` o, oc_product p LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE opa.product_id=1314 and opa.order_id=opb.order_id AND opb.product_id!= 1314 and opb.product_id = p.product_id and opb.order_id = o.order_id and p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' GROUP BY p.product_id ORDER BY o.date_added DESC LIMIT 4;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.022012  Lock_time: 0.020898 Rows_sent: 1  Rows_examined: 6
SET timestamp=1390300745;
SELECT COUNT(*) AS total FROM oc_LatestNews n LEFT JOIN oc_LatestNews_to_store n2s ON (n.LatestNews_id = n2s.LatestNews_id) WHERE n2s.store_id = '0' AND n.status = '1';
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.013201  Lock_time: 0.004365 Rows_sent: 3  Rows_examined: 15
SET timestamp=1390300745;
SELECT * FROM oc_LatestNews n LEFT JOIN oc_LatestNews_description nd ON (n.LatestNews_id = nd.LatestNews_id) LEFT JOIN oc_LatestNews_to_store n2s ON (n.LatestNews_id = n2s.LatestNews_id) WHERE nd.language_id = '2' AND n2s.store_id = '0' AND n.status = '1' ORDER BY n.date_added DESC LIMIT 3;
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.006761  Lock_time: 0.006094 Rows_sent: 0  Rows_examined: 0
SET timestamp=1390300745;
DELETE FROM `oc_customer_online` WHERE (UNIX_TIMESTAMP(`date_added`) + 3600) < UNIX_TIMESTAMP(NOW());
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.002709  Lock_time: 0.001732 Rows_sent: 1  Rows_examined: 244
SET timestamp=1390300745;
SELECT SUM(points) AS total FROM oc_customer_reward WHERE customer_id = '0';
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000367  Lock_time: 0.000085 Rows_sent: 1  Rows_examined: 244
SET timestamp=1390300745;
SELECT SUM(points) AS total FROM oc_customer_reward WHERE customer_id = '0'   AND DATE(date_added) > DATE_SUB( DATE(NOW()) , INTERVAL 365 DAY);
# Time: 140121 11:39:06
# User@Host: web160aquamag[web160aquamag] @  [XX.X.XX.XXX]
# Query_time: 0.000415  Lock_time: 0.000126 Rows_sent: 10  Rows_examined: 53
SET timestamp=1390300746;
SELECT * FROM oc_extension WHERE `type` = 'total';

Comment j'analyse ça pour commencer ? Quelle lecture dois-je en faire et comment l'interpréter ?

Merci.

[slwsu]

Ok, je regarde mais faut que je cherche un peut, en attendant, quel est la valeur de long_query_time ?

[Canou83]

J'en ai pas pour le moment...

Code: Tout sélectionner

[mysqld]

collation_server=utf8_general_ci
character_set_server=utf8

max_connections = 50
max_user_connections = 50
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 32M
max_heap_table_size = 32M

########################
##Configuration Innodb##
##Uncomment the next line to disable Innodb

#skip-innodb

innodb_buffer_pool_size         = 16M
innodb_additional_mem_pool_size = 2M

innodb_log_file_size            = 10M
innodb_log_buffer_size          = 4M

innodb_flush_log_at_trx_commit=2


[slwsu]

Salut, prend par exemple cette requête:

Code: Tout sélectionner

SELECT *
FROM test_user u
LEFT JOIN test_user_group ug ON ( u.user_id = ug.user_group_id )
WHERE u.user_id =  '1'

si tu regarde bien elle compare des ids de deux tables, il faut que ces ids deviennent des index dans phpmyadmin, par exemple, ces deux tables donne ca:

Code: Tout sélectionner

-- -----------------------------------------------------
-- Table `mydb`.`test_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`test_user` ;

CREATE TABLE IF NOT EXISTS `mydb`.`test_user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_group_id` INT(11) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
  `salt` VARCHAR(9) NOT NULL,
  `firstname` VARCHAR(32) NOT NULL,
  `lastname` VARCHAR(32) NOT NULL,
  `email` VARCHAR(96) NOT NULL,
  `code` VARCHAR(40) NOT NULL,
  `ip` VARCHAR(40) NOT NULL,
  `status` TINYINT(1) NOT NULL,
  `date_added` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`test_user_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`test_user_group` ;

CREATE TABLE IF NOT EXISTS `mydb`.`test_user_group` (
  `user_group_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `permission` TEXT NOT NULL,
  PRIMARY KEY (`user_group_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = utf8;


et avec une clé indexé:

Code: Tout sélectionner

-- -----------------------------------------------------
-- Table `mydb`.`test_user_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`test_user_group` ;

CREATE TABLE IF NOT EXISTS `mydb`.`test_user_group` (
  `user_group_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `permission` TEXT NOT NULL,
  PRIMARY KEY (`user_group_id`))
ENGINE = MyISAM
AUTO_INCREMENT = 11;


-- -----------------------------------------------------
-- Table `mydb`.`test_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`test_user` ;

CREATE TABLE IF NOT EXISTS `mydb`.`test_user` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_group_id` INT(11) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
  `salt` VARCHAR(9) NOT NULL,
  `firstname` VARCHAR(32) NOT NULL,
  `lastname` VARCHAR(32) NOT NULL,
  `email` VARCHAR(96) NOT NULL,
  `code` VARCHAR(40) NOT NULL,
  `ip` VARCHAR(40) NOT NULL,
  `status` TINYINT(1) NOT NULL,
  `date_added` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`),
  INDEX `user_group_id` (`user_group_id` ASC),
  CONSTRAINT `fk_test_user_test_user_group`
    FOREIGN KEY (`user_group_id`)
    REFERENCES `mydb`.`test_user_group` (`user_group_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2;


En fait, j'ai tous simplement dis que dans le table "test_user": user_group_id = user_group_id de la table user_group...

Tu dois identifier les différentes requête et vois ou tu peux faire autre chose mais attention, mes explications restent asses simpliste, renseigne toi plus et surtout, fait des tests avec mysql workbeanch, pour ma part je ne passe presque jamais par phpmyadmin, la version graphique de la base de données, c'est quand même génial

[Canou83]

Merci slwsu pour toutes ces explications !

Je suis pas au niveau pour aller mettre les mains la dedans. Je ne prendrai donc aucun risque et me cantonnerai à ce que je sais faire lol mais en tout cas merci beaucoup d'avoir prie le temps de m'expliquer.

[slwsu]

Bien tu sais, je te répond parce que ca me fait plaisir et que j'ai un peut de temps mais tu as raison d'abandonner, il y a l'optimisation c'est sur mais la tu es sur le truc le plus compliqué, avant ca (sur un cms) il y a pas mal de choses a faire.
Tu as fait le choix de prendre un truc fonctionnel, derrière il y a des développeurs qui ont pensé a ces choses la avant toi mais pour des raisons qui sont les leurs ils prennent des routes qui sont les leurs. Dans l'ensemble je suis sur que les requêtes du système de base sont propre...
Je te conseil de voir avec les temps de chargement ce chaque éléments de chaque pages du site...
Regarde tes css si tu en a plusieurs et combine les en 1 seul puis utilise une version compressé, fait pareil avec le js...
Travail sur le poids des images...
Cherche tous ce qui met du temps mais coté clients, ya du travail j'en suis sur , y'en a toujours !

[Canou83]

OK Sympa.

Bon il me reste juste alors l'optimisation du fichier my.cnf.
C'est un serveur sqlprivé de 256 Mo de RAM.

La seule véritable variable qui ne fait que vouloir augmenter (en fonction des recommandations du l'outil d'OVH) c'est la variable join_buffer_size qu'il me veut faire augmenter x 2 à chaque fois, saut que évidement, cela me fait dépasser les capacité du serveur.

En fait toute ma reflexion est basée depuis le début sur join_buffer_size.

[Canou83]

Bonjour, j'ai fait tourner quelques jour sur mon serveur sql :

slow_query_log=1
slow_query_log_file=/home/mysql/sqlprive.log
long_query_time=2

Avec donc long_query_time=2 à 2 secondes. Ci dessous le résultat.

Code: Tout sélectionner

/opt/mysql/mysql/bin/mysqld, Version: 5.1.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /bdd/mysql.sock
Time                 Id Command    Argument
# Time: 140207  8:35:05
# User@Host: OVH[OVH] @ localhost []
# Query_time: 4.626255  Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 542
SET timestamp=1391758505;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
# Time: 140207 19:57:06
# User@Host: web160aquamag[web160aquamag] @  [10.0.89.125]
# Query_time: 3.906145  Lock_time: 0.005166 Rows_sent: 1  Rows_examined: 2
use web160aquamag;
SET timestamp=1391799426;
SELECT *, (SELECT os.name FROM `oc_order_status` os WHERE os.order_status_id = o.order_status_id AND os.language_id = o.language_id) AS order_status FROM `oc_order` o WHERE o.order_id = '1012';
# Time: 140208 21:39:03
# User@Host: OVH[OVH] @ localhost []
# Query_time: 2.631124  Lock_time: 0.000156 Rows_sent: 2  Rows_examined: 542
SET timestamp=1391891943;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;

Une amélioration à envisager ?