Mysql - Myisam ou InnoDB

noren · 25 Août 2014

Bonjour

J'ai un dilemme sur le choix du moteur de stockage pour ma base de données : Myisam ou innoDB.

Après avoir lu plusieurs articles sur le sujet je pense connaitre les avantages et inconvénients de chacun mais je n'arrive pas à faire un choix.

MyIsam :
- Recherche Fulltext
- Plus rapide car moins de contrôles (select, insert), même si cette différence diminue de plus en plus
- Moins de ressources utilisés au niveau du stockage de la BDD et au niveau de la memoire lors de l’exécution des requetes
- Stable mais n'évolue plus

InnoDB
- gestion des transactions
- getsion des clés étrangères
- moteur par defaut de mysql depuis mysql 5.5
- Fulltext possible depuis la version 5.6.4
- Toujours en évolution

Alors voila les soucis :

J'ai besoin de faire un moteur de recherche sur mon site, mais sur mutualisé (en tout cas chez OVH), mysql 5.6.4 n’est pas encore implanté. maximum 5.5 avec SQL privé
Je ne peux pas utiliser Sphinx et autres API non plus sur mutualisé, et les moteurs de recherches PHP (lucène par exmeple, je crois) risquent vite de montrer leur limite lorsque le site contiendra plusieurs milliers d'articles.

De plus il semblerait qu'en innodb la BDD prend beaucoup plus de place et les ressources mémoires utilisées durant les traitement semblent être bien plus importantes d'après mes recherches. Possibilité donc de saturation plus rapide si le serveur ne suit pas (ram etc.).

Autre point, mysam bloque la table a chaque insert, update alors qu'Innodb bloque seulement la ligne. Même si je ne pense pas avoir un grand nombre d'insert et update sur mes sites, ce point me semble réellement important pour le choix du moteur. :|

En innodb est-on obligé de gérer les clés étrangères? peut-on par exemple utiliser uniquement les transactions?
Est-on d'ailleurs obligé d'itiliser les transactions, ou peut on coder avec le moteur innodb de la même façon qu'avec myisam? (ok ca serait dommage, mais c’est pour savoir si on peut améliorer ensuite son code au fur et à mesure (transactions...)

Différence entre ACID et transactions? pas sur de bien comprendre.

Les commandes mysqldump pour exporter ou importer une bdd sont les mêmes avec innodb et mysql?

Myisam va t-il mourir comme le moteur isam? ou peut on espérer qu'il restera encore présent dans 20 ans? :mrgreen:

Autre soucis actuellement je test en local sur une version mysql 5.6, si j'utilise le moteur innodb je risque d'avoir des surprises sur des versions précédentes étant donné qu'innodb est en pleine évolution.

Pour vous quel moteur est le plus performant? avez vous déjà fait des tests (benchmarks)? Que me conseillerez vous? Quelle solution existe pour mon moteur de recherche si j'utilise Innodb?

Une solution serait de créer une table myisam qui recopierait les données nécessaires pour la recherche. table que je mettrais a jour a chaque ajout, suppression ou mise à jour d'un article (via trigger ou non). cette solution pourrait être éventuellement provisoire le tant que le fulltext Innodb soit possible sur mutualisé.

Lorsqu'on passe de MyIsam à innoDB que faut il bien vérifier pour qu'il n'y ai pas de coquille?

je serais tenter de continuer d'utiliser MyIsam, mais je crains que dans quelques années ce moteur ne soit plus du tout utilisé et que ça me pose problème :/

Blount · 25 Août 2014

Ce n'est pas simple comme question.
Ce que je peux te dire :

En innodb est-on obligé de gérer les clés étrangères? peut-on par exemple utiliser uniquement les transactions?

Tu n'es obligé de rien. Mais pourquoi te passer d'une fonctionnalité plus qu'intéressante. Les clés étrangères permettent de conserver une bonne intégrité de ta base de données. Es-tu sur que ton article est associé à une catégorie toujours existante ? Que se passe t-il a la suppression d'une catégorie ? Les clés étrangères te permettent de valider un schéma directement via la base de données. Par exemple, c'est le serveur de base de données qui va empêcher de supprimer une catégorie si un article y est toujours attaché. De même, il va générer une erreur si tu essaies d'associer un article à une catégorie inexistante.
Je suis déjà passé derrière ce genre de cas, où le code PHP ne gérait pas (ou mal) certain de ces points, et c'est moche …

Les clés étrangères te permettent de faciliter l'accès concurrent à tes base de données. Bien que peu probable, au moment où tu crées un nouvel article. Tu as ta requête qui vérifie l’existence de la catégorie et celle qui insert le nouvel article. Entre les 2, tu as quelqu'un qui supprime la catégorie. Tu te retrouves avec un article orphelin.
Avec la clés étrangère, tu supprimes ta requête de vérification de la catégorie (SELECT) et tu fais juste l'insert. Si la catégorie n'existe pas, MySQL t'envoie bouler avec un problème de contrainte non respectée. Tu traites l'erreur qu'il te retourne et hop c'est réglé.

Est-on d'ailleurs obligé d'itiliser les transactions, ou peut on coder avec le moteur innodb de la même façon qu'avec myisam? (ok ca serait dommage, mais c’est pour savoir si on peut améliorer ensuite son code au fur et à mesure (transactions...)

Non, tu n'es pas obligé mais c'est toujours quelque chose qui permet d'éviter des corruptions de base de données.
Exemple un peu basique: tu supprimes une catégorie, mais avant, tu supprimes les articles attachés. Tes articles sont supprimés, et oups, une erreur lors de la suppression de la catégorie. Un rollback et tu remet en état avant la suppression des articles. Il te suffit de loguer l'erreur et de la traiter

Là, c'est pas bien grave, mais dans certain cas, ça peut tout casser (par exemple, quand tu fais de la représentation intervallaire).
Les transactions permettent aussi d'accélérer considérablement l'exécution de plusieurs requêtes de modification. En temps normale, innoDB utilises des transaction implicite, c'est à dire qu'il valide tout les changements tout de suite (vérification et tout). Or, en gérant les transactions, la validation final se fera au commit (ne me demande pas plus de détail ^^).
J'ai justement modifié le code d'importation dans Prestashop pour accélérer l'importation. Comme indiqué dans mon article, pour environ 1400 produits, je suis passé de 8 minutes à 12 secondes. Ce qui est énorme quand tu importes tous les jours

Perso, jusque maintenant, je n'ai utilisé que de l'innoDB et je serais incapable de te dire si pour toi ce sera le mieux ou pas.

Bon désolé s'il reste des fautes, mais je dois partir faire les courses. Il y a une vie en dehors de l'ordi il parait …

spout · 25 Août 2014

Semi HS: c'est dommage de pas avoir un dédié ou VPS pour tester ElasticSearch, ça supporte:
- Accent/ASCII folding
- Stemming
- Faceted search
- "More like this"
- Suggestions
- Stop words
- API REST
- ...

Blount · 25 Août 2014

spout a dit:
Semi HS: c'est dommage de pas avoir un dédié ou VPS pour tester ElasticSearch, ça supporte:
- Accent/ASCII folding
- Stemming
- Faceted search
- "More like this"
- Suggestions
- Stop words
- API REST
- ...

Bah c'est pas si HS que ça parce que ça répond quand même à sa demande.
Je suis d'avis aussi que si son site trouve un grand intérêt dans le moteur de recherche, alors il ne faut pas trop hésiter à se diriger vers les outils dédiés.
Surtout que les virtus sont de plus en plus accessibles. C'est toujours plus couteux qu'un mutu comme le propose OVH, mais au moins tu as les outils que tu veux.
Tu dois certes gérer ton serveur, mais bon tu échanges un stress par un autre parce qu'en mutu, t'as toujours le risque de te retrouver avec un site limité en ressource du jour au lendemain.

noren · 26 Août 2014

Merci Blount et Spout pour vos réponses.

Si je passe en InnoDB c'était prévu que j’effectue des transactions pour les inserts, update, delete :wink:
Pour les clés étrangères je ne sais pas trop, ca nécessiterait quelques essais pour voir ci cela ne me poserait pas de problèmes dans certains cas.

Passer sur un dédié n’est pas au programme pour plusieurs raisons :

- prix plus élevé
- pas assez de connaissance pour gérer et configurer un dédié
- en cas de pépin technique tu es quasi livré à toi même (ce qui n'est pas vraiment le cas avec les mutualisés)

De plus les offres mutualisés, en tout cas sur ovh, ont pas mal évolué : offres performances (moins limité en ressources et pas de partage, enfin normalement)

http://www.ovh.com/fr/hebergement-web/offres-performance.xml

Mais pour le moment il est vrai que je reste bien emme*dé avec ce moteur de recherche :

- Soit je reste avec MyIsam et je fais du FullText
- Soit je passe avec InnoDB et je crée une table MyIsam (avec Index FullText) qui me servira uniquement pour la recherche (c’est pas très propre). le temps de migrer sur une version mysql 5.6
- Soit j'utilise le moteur de recherche de Google (mais faut-il encore que toutes mes pages soient bien indexées et suffisamment rapidement, et ça crée une dépendance)
- Soit j'utilise les Like et Regex, mais là j'ai bien peur qu'avec plusieurs milliers d'articles je fasse péter le serveur :/
- Soit je crée un champ mot clé pour chaque article, et je fais ma recherche (like) uniquement sur ce champ (varchar). Mais c’est contraignant et bien moins efficace pour les utilisateur :/

Je n'ai pas trouvé d'autres solution pour un moteur de recherche sur mutualisé. Tous nécessite un dédié (sphinx etc.).
Il y aurait peut etre Zend_Search_Lucene mais je ne sais pas si c’est possible sur mutualisé et si c’est performant. :|

Après avec InnoDB il y a ce problème de taille de la BDD qui est bien plus importante qu'en MyIsam. Et innoDb nécessite bien plus de ressources (ram...). De plus ce moteur à pas mal évolué et peut donc engendrer quelques imprévus en fonction des changements de versions Mysql entre la prod et le dev :?

Quelle prise de tête tout ça. Rien n'est simple....

noren · 26 Août 2014

Wordpress utilisant InnoDB mais pas d'index Fulltext j'ai jeté un oeil sur son moteur de recherche par défaut :

Code:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%test%') OR (wp_posts.post_content LIKE '%test%'))) AND (wp_posts.post_password = '') AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_title LIKE '%test%' DESC, wp_posts.post_date DESC LIMIT 0, 10

ca me semble pas très performant comme solution, le moteur de wordpress ne risque pas de vite saturer lorsqu'il y a plusieurs milliers d'articles? A moins que Wordpress utilise un système de cache performant qui règlerait mes problèmes :roll: mais si oui lequel et comment?

PS : j'ai fait quelques tests avec une table de plus de 50 000 articles sur mon site et avec des like ça semble quand même tenir la route (0,50s) en local, rien de mortel et ça peut éventuellement m'éviter le FullText.

avantages :

- le choix vers InnoDB serait déjà plus facile
- et la taille de ma BDD serait réduite étant donné que l'index Fulltext ne serait plus utile

Inconvénient :

- Pas de pertinence et requête plus longue qu'avec l'index FullText

Rien n’empêcherait par la suite lorsque mysql 5.6 sera bien implanté de migrer sur cette version et d'ajouter un index Fulltext

Quand pensez vous?

Blount · 26 Août 2014

Perso, j'utilise la même technique que Wordpress.

La question est : ton moteur de recherche est-il la partie principale de ton site ?
Parce que s'il est utilisé 1 fois tous les jours, tu risques pas grand chose.
Pour un blog par exemple, en général, une personne arrivera sur le site via un moteur de recherche.

Dit nous concrètement ce que tu va rechercher.

noren · 26 Août 2014

Non la moteur de recherche ne sera pas l’élément principal. c’est pour ça qu'en fin de compte je pense pouvoir tolérer les "like" à la place des "matchs", après lorsque (si) le besoin s'en fera ressentir je pourrais toujours migrer sur du mysql 5.6 (ou plus) dans quelques années et gérer le fulltext ou éventuellement utiliser la technique d'une table myIsam uniquement pour la recherche :wink:

je pense donc que je vais me tourner sur du InnoDB ça me semble plus raisonnable pour le futur.
Il faut que je migres mes tables et petit a petit je mettrais en place les transactions etc. ça va être encore de bonnes heures de tests :/

mes sites sont des sites d'informations, le moteur de recherche sera aussi bien utilisé sur le front office pour aider les utilisateurs a trouver des articles selon les critères de leur recherche, mais également en backoffice pour que je puisse retrouver facilement un article afin de l'éditer et le modifier :wink:

Blount · 26 Août 2014

noren a dit:
je pense donc que je vais me tourner sur du InnoDB ça me semble plus raisonnable pour le futur.
Il faut que je migres mes tables et petit a petit je mettrais en place les transactions etc. ça va être encore de bonnes heures de tests :/

En théorie, rien d'inquiétant si tu as développé correctement.
Passer de MyISAM à innoDB est beaucoup plus simple que le contraire.

C'est surtout au moment de placer tes contraintes (clé étrangère) que tu peux avoir des surprises si tu as mal géré niveau PHP.
Le mieux, c'est de copier ta base et de tout faire via PHPMyAdmin, tu verras tout de suite les problèmes et ça ira vite.
Après, soit tu fais la même chose avec la vrai base soit tu mets toutes tes requêtes dans un fichiers SQL pour l'exécuter (ça permet de garder une trace).

Au niveau des requêtes, rien à changer globalement. Sauf le FULLTEXT évidemment.

spout · 26 Août 2014

noren a dit:
Passer sur un dédié n’est pas au programme pour plusieurs raisons :

- prix plus élevé
- pas assez de connaissance pour gérer et configurer un dédié
- en cas de pépin technique tu es quasi livré à toi même (ce qui n'est pas vraiment le cas avec les mutualisés)

- Un VPS chez DigitalOcean c'est 5 $ (= 3.8 €). C'est moins cher que le performance que tu cites.
- Pas besoin de grandes connaissances pr installer une des image [strike]LAMP[/strike] LEMP pré-configurée. De plus ça s'apprend non ?
- Pépin hardware sur un VPS, c'est eux qui gèrent. Si c'est un problème software tu réinstalles le snapshot en 2 minutes.

Utiliser LIKE pour faire les recherches c'est nul, comment tu gères les recherches à plusieurs mots ou avec des accents ?

Sinon il y'a aussi la soluce PostgreSQL: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/

noren · 26 Août 2014

@Blount : C’est ce que je pensais aussi, de toute façon je verrais et de toute façon c'est surement la meilleure chose à faire. Je ne vais pas me bloquer pour ce FullText et un moteur de recherche qui n'as pas forcément besoin d'être ultra performant en terme de pertinence.

Spout : Même si ça s'apprend je ne me sens pas prêt a passer sur VPS ou dédié et encore moins chez DigitalOcean que je ne connais ni d’Adam ni d’Eve et uniquement en anglais :wink:
Pour le like je sais que c’est loin d'être la meilleur solution pour un moteur de recherche, malheureusement en fonction des contraintes que je me suis fixé c'est la seule pour le moment d'acceptable et de réalisable (ça ne semble d'ailleurs pas gêner Worpress qui est pourtant le CMS le plus utilisé dans le monde). Après comme indiqué je pourrais toujours éventuellement utiliser une table dédiée uniquement pour la recherche et qui serait en MyIsam avec index FullText, ou attendre pour utiliser le FullText lorsque la version 5.6 sera disponible.
Donc on peut voir les Like comme du provisoire et m'évite de rester avec le moteur MyIsam qui n'a plus d'avenir, passer sur du dédié, VPS ou je ne sais quoi encore. :wink:
Le Like est donc une solution de secours provisoire. je pourrais toujours passer par le moteur de google lorsque les sites seront bien indexés et suffisamment rapidement :mrgreen:

Pour les accents j'ai testé il semblerait que le Like me retourne les mêmes résultats avec ou sans accents, je sais pas si c’est normal mais en tout cas ça semble être une bonne chose.
Ensuite pour plusieurs mots clés je n'ai pas encore défini si je met OR ou AND etc. Il y a surement toujours des moyens pour aiguiller les utilisateurs dans leur recherche.

PostgreSQL aurait pu être une solution si je l'avais choisi depuis le départ. Changer de SGBD peut devenir vite complexe. De plus PostegreSQl est certainement plus adapté pour les grosses bases de données, et hormis 2-3 requêtes ou je constate les faiblesses de l'optimiseur Mysql, le moteur de recherche impossible en InnoDB, MySql fait l'affaire.
Après MySql a été racheté par Oracle, on peut donc espérer qu'il tende réellement vers une réel SGBD. On peut le voir d'ailleurs avec ce passage en InnoDB par défaut et l'arrivé du FullText dans la dernière version. MySql a pas mal évolué dans le bon sens.

De toute façon mes sites seront modestes, inutile donc de sortir l'artillerie lourde :mrgreen:

Blount · 26 Août 2014

spout a dit:
Utiliser LIKE pour faire les recherches c'est nul, comment tu gères les recherches à plusieurs mots ou avec des accents ?

Pour les accents, il suffit d'utiliser le bon charset. Si tu met du utf8_bin, alors oui e != é mais E != e aussi.
Par contre, en utf8_general_ci (ci = case insensitive), alors E == e et e == é (pareil pour tous les autres caractères).

noren · 27 Août 2014

La prise en compte des accents avec les Like date de la version 5 de mysql je crois.
J'ai des bases avec mysql 4 et la gestion des accents n’est pas prise en compte, j'utilise REGEX mais ça rame sévère

D'ailleurs ou trouver une liste de mots courants que l'ont pourrait retirer des mots clés saisies par l'utilisateur pour améliorer le moteur avec Like?

exemple : de, par, pour, quand, pourquoi, etc.

noren · 27 Août 2014

Je viens de faire un test, j'ai dupliqué ma base, je l'ai passé en InnoDB et j'ai fait des tests avec quelques requêtes que j'ai comparé avec les résultats sur ma base en MyIsam. Et la grosse déception.
Les temps d’exécution en InnoDB par rapport à MyIsam sont tout simplement très mauvais!

C’est en moyenne 10-20% moins rapide pour de simples requêtes et dès qu'il y a des requête un poil plus complexes (selects avec jointures ou requêtes imbriquées, upadte etc.) c’est tout simplement catastrophique c'est 10 à 50 fois plus lent. ce qui est d'autant plus surprenant c'est que je fais ces tests sur mysql 5.6 qui utilise par défaut InnoDB et qui est censé avoir apporté beaucoup d’améliorations à ce moteur au niveau de l'optimiseur notamment. J'ose même pas imaginé sur des versions précédente ce que ça doit donner!

Ce moteur a beau utiliser des transactions etc. avec de telles performances ça fait franchement réfléchir :roll:

madri2 · 27 Août 2014

il y a plusieurs paramètres de conf spécifique a innodb, qu'il faut modifier

noren · 27 Août 2014

Voici mon fichier my.ini sur wampserver :

Code:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[wampmysqld]
port		= 3306
socket		= /tmp/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

#[mysqld]
#slow-query-log
#slow-query-log-file = c:/wamp/logs/slow-queries.log
#long-query_time=0

[mysqld]
port=3306

J'avais deja constaté ceci :

Code:

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

j'avais donc remplacé par ceci :

Code:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = c:/wamp/bin/mysql/mysql5.6.12/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = c:/wamp/bin/mysql/mysql5.6.12/data
innodb_log_arch_dir = c:/wamp/bin/mysql/mysql5.6.12/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

En gros j'ai dé-commenté toutes les lignes qui semblaient avoir un rapport avec la configuration d'InnoDB et je n'ai constaté aucune amélioration (j'avais bien sur redémarré wampserver).

Il y a peut être des trucs qui m'échappent. je ne comprend pas d'ailleurs pourquoi ces lignes étaient pas activées alors que la version que j'ai de wampserver utilise mysql 5.6 qui utilise par défaut innoDB :roll:

Petite question en passant, Cache de requêtes MySQL (http://dev.mysql.com/doc/refman/5.0/fr/query-cache.html) est elle utilisée sur les mutualisés? il me semblait avoir lu que non quelque part (mais j'ai surement halluciné).

madri2 · 27 Août 2014

wamp est pas censé être utilisé en prod donc ça influe peut être sur les perfs

noren · 27 Août 2014

Je sais pas trop, lorsque je fait des comparaisons de performances sur d’autres sites qui m'appartiennent les résultats sont meilleurs sur mon pc de développement via wampserver quand prod, ce qui m’inquiète d'autant plus :/

En prod c'était 3 ou 4 fois plus lent qu'en local

madri2 · 27 Août 2014

il faut modifier aussi la config

et là c'est du boulot ...

noren · 27 Août 2014

la config de quoi? je suis et serais sur du mutualisé donc je serais limité pour les configurations :wink:

madri2 · 27 Août 2014

bah toute la config que t'a décommenté

genre
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M

tu peux mettre à 50% de la ram comme marqué

noren · 28 Août 2014

Pas de difference notable j'ai essayé avec innodb_buffer_pool_size = 16M
J'ai également essayé avec
innodb_flush_log_at_trx_commit = 0
ou
innodb_flush_log_at_trx_commit = 2

Par contre j'arrive vraiment pas a comprendre comment fonctionne mysql et l'utilisation de la RAM, cache etc.

lorsque je fait cette commande :

Code:

SHOW VARIABLES LIKE '%query_cache%'

j'obtiens ceci :

Code:

Variable_name 	Value 	
have_query_cache 	YES
query_cache_limit 	1048576
query_cache_min_res_unit 	4096
query_cache_size 	1048576
query_cache_type 	OFF
query_cache_wlock_invalidate 	OFF

et si je fais cette commande après avoir un peu navigué sur mon site :

Code:

SHOW STATUS LIKE 'Qcache%'

J'ai ceci :

Code:

Variable_name 	Value 	
Qcache_free_blocks 	1
Qcache_free_memory 	1031448
Qcache_hits 	0
Qcache_inserts 	0
Qcache_lowmem_prunes 	0
Qcache_not_cached 	1277
Qcache_queries_in_cache 	0
Qcache_total_blocks 	1

j'en déduis que la cache des requêtes Mysql n’est pas activé ( query_cache_type OFF ) et (Qcache_queries_in_cache 0 )
Donc comment expliquer que certaines de mes requêtes à la 1ere exécution des pages de mon site (après redémarrage du serveur) mettent, par exemple, 0.8s etc. et qu'ensuite elle fasse 0.0020s voir 0.0003s
J’associais ça à la cache des requêtes :

http://dev.mysql.com/doc/refman/5.0/fr/query-cache.html
http://dev.mysql.com/doc/refman/5.0/fr/query-cache-status-and-maintenance.html
http://dev.mysql.com/doc/refman/5.0/fr/query-cache-configuration.html

Qu'elle cache est dans ce cas utilisé?

Petite précision si j'ajoute SQL_NO_CACHE sur mes requêtes ça ne change rien elle passe bien de 0.8s (par exmeple) à la 1ere exécution à 0.002 ensuite.

madri2 · 28 Août 2014

50% de la ram ça fait 16Mo chez toi ?

il y a les scripts tuning primer et mysqltuner (linux) qui permettent d'aider à configurer, mais je ne sais pas s'ils prennent (bien?) en compte innodb

noren · 28 Août 2014

pardon me suis trompé je voulais dire 60M je me suis basé sur une offre sql privé d'OVH qui n'ont que 128M :wink:

Aurais tu des infos concernant cette histoire de cache? :wink:

madri2 · 28 Août 2014

c'est peut être le cache du disque dur qui fais que c'est plus rapide ensuite

noren · 28 Août 2014

cache disque dur? connais pas, tu saurais ou je peux trouver des infos la dessus? j'ai cherché sur GG mais pas trouvé de réelles réponses la dessus
cette cache est active de partout même sur les mutualisés?

spout · 29 Août 2014

Linux is borrowing unused memory for disk caching

http://www.linuxatemyram.com/

Blount · 29 Août 2014

Quand un fichier est lu sur ton disque dur, il est en même temps écrit dans la mémoire vive (si possible, on ne met pas 2Go dans 1Go ^^).
Si par la suite, tu demandes à nouveau de lire ce fichier, il sera directement récupéré de la mémoire vive.
Ce qui signifie que la lecture sera instantanée et ne demandera plus un temps d'accès disque. Par la même occasion, tu économises physiquement le disque dur car il n'a à ce moment pas besoin de travailler. D'un autre coté, pendant la lecture du fichier en mémoire vive, le disque dur peut aller chercher un autre fichier qui serait demandé par une autre application.

C'est systématique sur les systèmes GNU/Linux, aucune configuration nécessaire. C'est pour cela qu'on a l'impression que la mémoire est toujours plein (ce qui est le cas en quelque sorte). Mais elle est là pour ça. Par contre, il est évident que la mémoire est toujours prioritaire au application. Plus les applications demanderont de mémoire, plus le cache se videra.

Perso, je suis d'avis que tu te fait trop de bile pour rien avec cette histoire de temps.
Combien de visite quotidienne tu as sur tes sites ?

noren · 29 Août 2014

je vous remercies, j'imagine que même sur mutualisé ça fonctionnera de la même façon :mrgreen:
Et je pourrais toujours avec le SQL privé augmenter la RAM :wink:

Actuellement j'ai 0 visiteur (les sites ne sont pas en ligne et sont toujours en phase de veleoppement), j'essaye de prévoir lorsqu'il y aura pas mal de données :mrgreen: , je veux m'éviter de désagréables surprises, surtout si en fin de compte je constate que c’est toute ma base de données qui est bancale.
Je sais qu'au pire des cas il y a toujours des solutions de dé-normalisation,ou l'utilisation de la cache, mais je souhaite déjà partir sur des bases saines en choisissant le bon moteur (myisam, innodb) et en optimisant au mieux mes requêtes. je souhaiterais éviter un éventuel goulot d'étranglement, ou même des temps de chargements de plus de 1s ou 2s, pour des sites d'informations ca serait bien trop long

Donc la si je comprend bien lorsque je navigue la première fois sur mes sites juste après le redémarrage du serveur, il est normal d'avoir des temps d’exécutions élevés, le temps que tout se mette bien en place dans la RAM?

Blount · 29 Août 2014

Le problème, c'est que sans visiteur, ce n'est pas toujours évident de trouver les futures problèmes.
En l'occurrence, je ne pense pas que le choix entre innoDB ou MyISAM ait une importance. Souvent, les problèmes viennent des requêtes. Et encore, faut vraiment être bourrin, du genre utiliser des LOCK à gogo ou des transactions bloquantes pendant 15 jours.

Mon conseil: fait ton site, écrit les requêtes qui te semblent justes et ne te pose pas trop de question pour le moment. Je ne connais pas ton secteur d'activité, mais je ne pense pas que tu passeras de 1 visite à 10000/jour du jour au lendemain, tu auras bien le temps d'analyser le comportement de ton site entre temps.
Souvent, les choses qui marchent super bien aujourd'hui étaient loin d'être optimisés au début (mode troll: Windows n'y est toujours pas aujourd'hui

), et c'est partout pareil. Ne perd pas trop de temps d'aller dans la sur optimisation avant même de savoir si cela sera nécessaire (je te le souhaite quand même ^^).

noren · 29 Août 2014

Tu as raison
D'autant plus que de toute façon je ne vois pas ce que je peux faire de plus pour le moment. :wink:

Bon c’est parti pour InnoDb ça ne sera que mieux pour l'intégrité de la BDD. Et sur MySql l'avenir est à InnoDB de toute façon :mrgreen:

Leonick · 29 Août 2014

par contre, dans le temps j'avais essayé les requêtes full text et je n'avais pas été convaincu : les résultats était souvent approximatifs.

noren · 30 Août 2014

De toute façon c’est plus d'activité le Full Text, l'index prend bien trop de place pour un intérêt minime, et de toute façon il n'est pas possible avec InnoDB, donc pour le moment je me contenterais du Like (je sais c’est pas top du tout), et je verrais par la suite :wink:

spout · 2 Septembre 2014

Un ptit slide intéressant sur lequel je suis tombé tt à l'heure: http://jolicode.github.io/elastica-conf/

noren · 3 Septembre 2014

Merci je vais regarder ça :wink:

PS : a priori Elasticsearch nécessite une installation sur le serveur (impossible sur mutualisé), mais je vais quand même regarder un peu plus en détail au cas ou je serais passé à côté de quelquechose