Forum : Gros timeout sur mysql

Base de données MySQL

Bonjour à tous,
je fais la migration vers un serveur dédié du site d'un ami, tout c'est bien passé mais il y a encore quelques bugs au niveau sql qui me fait des 100% de charge de CPU (jusqu'à ce que je le redemarre)
notamment quand je fais cette requete :

SELECT qte FROM lignefacture AS lf INNER JOIN facture AS f USING(factureid) INNER JOIN lignefacturecaracteristique AS lfc1 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc2 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc3 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc4 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc5 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc6 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc7 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc8 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc9 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc10 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc11 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc12 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc13 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc14 USING(lignefactureid) INNER JOIN lignefacturecaracteristique AS lfc15 USING(lignefactureid) WHERE lf.artid = 452 AND lfc1.caractvalid = '0' AND lfc2.caractvalid = '1' AND lfc3.caractvalid = '12' AND lfc4.caractvalid = '175' AND lfc5.caractvalid = '438' AND lfc6.caractvalid = '802' AND lfc7.caractvalid = '804' AND lfc8.caractvalid = '889' AND lfc9.caractvalid = '982' AND lfc10.caractvalid = '1001' AND lfc11.caractvalid = '1181' AND lfc12.caractvalid = '1218' AND lfc13.caractvalid = '1221' AND lfc14.caractvalid = '1654' AND lfc15.caractvalid = '3310' AND f.dateexpedition = '0000-00-00' AND f.datedecrementation != '0000-00-00' AND f.dateincrementation = '0000-00-00' AND f.deleted = '0''

Elle est vraiment dég. je vous l'accorde mais je n'ai pas fait le site.
Bref cette commande fait planter mon serveur alors qu'elle marche sans aucun soucis sur l'ancien(le résultat est instantané), auriez-vous des idées/pistes? sachant que les versions de mysql diffèrent, Merci d'avance.

Oki, effectivement j'ai du faire un mic mac lors de l'export ! Merci beaucoup pour ton aide ;)

Si l'export a été fait correctement au format SQL, les index sont dedans

c'est de l'Innodb ou du MyIsam ?

Les index sont ils bien créés ?

MyIsam, merci beaucoup ! Effectivement ,les index ne sont pas créés, mais comment faire cela? je pensais qu'ils étaient conservés lors de l'export/import via phpmyadmin, je dois tous les rentrer à la main?

Désolé , je reviens la queue entre les jambes, mais les indexs étais bien là, ma version de Phpmyadmin les caches juste par défaut, J'ai tout réimporté, la requête à fonctionner sur le coup, mais après diverses insertions dans la table, ça ne fonctionne plus comme si l'index devenait corrompu, j'ai beau analyser/optimiser rien n'y fait, si tu as une nouvelle piste, je suis preneur !
Merci d'avance !

Je n'utilise jamais le USING
tu peux le remplacer par ON(lfc1.lignefactureid = lf.lignefactureid)
le fait d'utiliser des INNER JOIN fait que s'il manque la moinde valeur, tu n'auras pas de résultat
essaye avec des LEFT JOIN

Pour voir comment la requête est interprétée, tu peux lancer la commande EXPLAIN devant le SELECT
ça indique quels index sont utilisés, s'il y a des tables temporaires créées ...

Ca me fait la même chose, en réduisant le nombre de join à 9 ça fontionne, mais à 10 il en veux plus de ma requete. Ce qui m'énerve le plus, c'est que sur l'ancienne base, ça fonctionne et pas sur la mienne, elle se trouve pourtant sur un serveur plus puiisant :o .
la seule chose qui pourrait être différent, c'est la config mysql mais j'ai pas accès à l'ancienne malheureusement.

dans le my.ini, tu peux augmenter le join_buffer_size qui est à 256k par défaut, essaye 1024k (sans garantie)

Si tu as acces à phpmyadmin, tu peux voir les variables système

Ah, merci, j'ai rien trouvé de probant voici la liste de l'ancienne db :

auto increment increment 1 auto increment offset 1 autocommit ON automatic sp privileges ON back log 50 basedir /usr big tables OFF binlog cache size 32 768 binlog direct non transactional updates OFF binlog format STATEMENT binlog stmt cache size 32 768 bulk insert buffer size 8 388 608 character set client utf8 (Valeur globale) latin1 character set connection utf8 (Valeur globale) latin1 character set database latin1 character set filesystem binary character set results utf8 (Valeur globale) latin1 character set server latin1 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci (Valeur globale) latin1_swedish_ci collation database latin1_swedish_ci collation server latin1_swedish_ci completion type NO_CHAIN concurrent insert AUTO connect timeout 10 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default storage engine MyISAM default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1 000 div precision increment 4 engine condition pushdown ON error count 0 (Valeur globale) 0 event scheduler OFF expire logs days 10 external user (Valeur globale) flush OFF flush time 0 foreign key checks ON ft boolean syntax + -><()~*:""&| ft max word len 84 ft min word len 4 ft query expansion limit 20 ft stopword file (built-in) general log OFF general log file /var/lib/mysql/ns353272.log group concat max len 1 024 have compress YES have crypt YES have csv YES have dynamic loading YES have geometry YES have innodb YES have ndbcluster NO have openssl DISABLED have partitioning YES have profiling YES have query cache YES have rtree keys YES have ssl DISABLED have symlink YES hostname ns353272.ovh.net identity 0 (Valeur globale) 0 ignore builtin innodb OFF init connect init file init slave innodb adaptive flushing ON innodb adaptive hash index ON innodb additional mem pool size 8 388 608 innodb autoextend increment 8 innodb autoinc lock mode 1 innodb buffer pool instances 1 innodb buffer pool size 134 217 728 innodb change buffering all innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file format Antelope innodb file format check ON innodb file format max Antelope innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force load corrupted OFF innodb force recovery 0 innodb io capacity 200 innodb large prefix OFF innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log buffer size 8 388 608 innodb log file size 5 242 880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 75 innodb max purge lag 0 innodb mirrored log groups 1 innodb old blocks pct 37 innodb old blocks time 0 innodb open files 300 innodb print all deadlocks OFF innodb purge batch size 20 innodb purge threads 0 innodb random read ahead OFF innodb read ahead threshold 56 innodb read io threads 4 innodb replication delay 0 innodb rollback on timeout OFF innodb rollback segments 128 innodb spin wait delay 6 innodb stats method nulls_equal innodb stats on metadata ON innodb stats sample pages 8 innodb strict mode OFF innodb support xa ON innodb sync spin loops 30 innodb table locks ON innodb thread concurrency 0 innodb thread sleep delay 10 000 innodb use native aio OFF innodb use sys malloc ON innodb version 5.5.41 innodb write io threads 4 insert id 0 (Valeur globale) 0 interactive timeout 28 800 join buffer size 131 072 keep files on create OFF key buffer size 16 777 216 key cache age threshold 300 key cache block size 1 024 key cache division limit 100 large files support ON large page size 0 large pages OFF last insert id 0 (Valeur globale) 0 lc messages en_US lc messages dir /usr/share/mysql/ lc time names en_US license GPL local infile ON lock wait timeout 31 536 000 locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error log output FILE log queries not using indexes OFF log slave updates OFF log slow queries ON log warnings 1 long query time 2 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 16 777 216 max binlog cache size 18446744073709547520 max binlog size 104 857 600 max binlog stmt cache size 18446744073709547520 max connect errors 10 max connections 151 max delayed threads 20 max error count 64 max heap table size 16 777 216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1 024 max long data size 16 777 216 max prepared stmt count 16 382 max relay log size 0 max seeks for key 18446744073709551615 max sort length 1 024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 18446744073709551615 metadata locks cache size 1 024 min examined row limit 0 multi range count 256 myisam data pointer size 6 myisam max sort file size 9223372036853727232 myisam mmap size 18446744073709551615 myisam recover options BACKUP myisam repair threads 1 myisam sort buffer size 8 388 608 myisam stats method nulls_unequal myisam use mmap OFF net buffer length 16 384 net read timeout 30 net retry count 10 net write timeout 60 new OFF old OFF old alter table OFF old passwords OFF open files limit 4 161 optimizer prune level 1 optimizer search depth 5 optimizer switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on performance schema OFF performance schema events waits history long size 10 000 performance schema events waits history size 10 performance schema max cond classes 80 performance schema max cond instances 1 000 performance schema max file classes 50 performance schema max file handles 32 768 performance schema max file instances 10 000 performance schema max mutex classes 200 performance schema max mutex instances 1 000 000 performance schema max rwlock classes 30 performance schema max rwlock instances 1 000 000 performance schema max table handles 100 000 performance schema max table instances 50 000 performance schema max thread classes 50 performance schema max thread instances 1 000 pid file /var/run/mysqld/mysqld.pid plugin dir /usr/lib/mysql/plugin/ port 3 306 preload buffer size 32 768 profiling OFF profiling history size 15 protocol version 10 proxy user (Valeur globale) pseudo slave mode OFF (Valeur globale) pseudo thread id 1 790 628 (Valeur globale) 0 query alloc block size 8 192 query cache limit 1 048 576 query cache min res unit 4 096 query cache size 16 777 216 query cache type ON query cache wlock invalidate OFF query prealloc size 8 192 rand seed1 0 (Valeur globale) 0 rand seed2 0 (Valeur globale) 0 range alloc block size 4 096 read buffer size 131 072 read only OFF read rnd buffer size 262 144 relay log relay log index relay log info file relay-log.info relay log purge ON relay log recovery OFF relay log space limit 0 report host report password report port 3 306 report user rpl recovery rank 0 secure auth OFF secure file priv server id 0 skip external locking ON skip name resolve OFF skip networking OFF skip show database OFF slave compressed protocol OFF slave exec mode STRICT slave load tmpdir /tmp slave max allowed packet 1 073 741 824 slave net timeout 3 600 slave skip errors OFF slave transaction retries 10 slave type conversions slow launch time 2 slow query log ON slow query log file /var/log/mysql/mysql-slow.log socket /var/run/mysqld/mysqld.sock sort buffer size 2 097 152 sql auto is null OFF sql big selects ON sql big tables OFF sql buffer result OFF sql log bin ON sql log off OFF sql low priority updates OFF sql max join size 18446744073709551615 sql mode sql notes ON sql quote show create ON sql safe updates OFF sql select limit 18446744073709551615 sql slave skip counter 0 sql warnings OFF ssl ca ssl capath ssl cert ssl cipher ssl key storage engine MyISAM stored program cache 256 sync binlog 0 sync frm ON sync master info 0 sync relay log 0 sync relay log info 0 system time zone CEST table definition cache 400 table open cache 2 000 thread cache size 8 thread concurrency 10 thread handling one-thread-per-connection thread stack 196 608 time format %H:%i:%s time zone SYSTEM timed mutexes OFF timestamp 1 437 408 021 (Valeur globale) 0 tmp table size 16 777 216 tmpdir /tmp transaction alloc block size 8 192 transaction prealloc size 4 096 tx isolation REPEATABLE-READ unique checks ON updatable views with limit YES version 5.5.41-0ubuntu0.12.04.1-log version comment (Ubuntu) version compile machine x86_64 version compile os debian-linux-gnu wait timeout 28 800

j'ai essayé de passer les tables en innoDB car apparement c'est plus rapide mais sans résultat, j'obtiens exactement le même temps pour 10 join (sur les 15) a savoir 2.8 secondes (sachant que pour 9 join c'est quasi instantanée)

que donne le explain ?
Si ta requête utilise des tables temporaires, essaye d'augmenter ceci :

# pour les table Memory et temp en memoire max_heap_table_size=1024M tmp_table_size=1024M

en fonction de ta mémoire disponible

+----+-------------+-------+--------+-------------------------+----------------+---------+--------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------+----------------+---------+--------------------------------+------+-------------+ | 1 | SIMPLE | lf | ref | PRIMARY,factureid,artid | artid | 4 | const | 1 | | | 1 | SIMPLE | f | eq_ref | PRIMARY,dateexpedition | PRIMARY | 4 | mouvements.lf.factureid | 1 | Using where | | 1 | SIMPLE | lfc1 | ref | lignefactureid | lignefactureid | 4 | mouvements.lf.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc2 | ref | lignefactureid | lignefactureid | 4 | mouvements.lf.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc3 | ref | lignefactureid | lignefactureid | 4 | mouvements.lf.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc4 | ref | lignefactureid | lignefactureid | 4 | mouvements.lf.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc5 | ref | lignefactureid | lignefactureid | 4 | mouvements.lf.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc6 | ref | lignefactureid | lignefactureid | 4 | mouvements.lfc1.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc7 | ref | lignefactureid | lignefactureid | 4 | mouvements.lfc6.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc8 | ref | lignefactureid | lignefactureid | 4 | mouvements.lfc7.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc9 | ref | lignefactureid | lignefactureid | 4 | mouvements.lfc7.lignefactureid | 29 | Using where | | 1 | SIMPLE | lfc10 | ref | lignefactureid | lignefactureid | 4 | mouvements.lfc9.lignefactureid | 29 | Using where | +----+-------------+-------+--------+-------------------------+----------------+---------+--------------------------------+------+-------------+

Voila ce que donne le explain, ça me parle pas du tout,

c'est parfait, les clés sont utilisées (possible keys)
du coup je ne vois plus trop
quelle version de Mysql ?

C'est la version 5.5.44, en tous cas je te remercie pour le temps que tu consacre à mon problème ;)

tu dis bien que c'est le nombre de jointures qui bloque, et non pas certaines jointures ?
Pourtant le nombre limite donné dans la doc est de 61 jointures.

Oui peut importe les jointures, j'ai essayé les premières avec les dernières et inversements tout fonctionne en dessous de 10 jointure. Mais c'est marrant car à 9 la requete prend quelques miniseconde et à 10 c'est 3s et à 11 c'est la mort :o

Gros timeout sur mysql

18 réponses