Out of memory sur une requête

Bonsoir,

j'ai une requête à exécuter plusieurs fois consécutivement, avec des paramètres différents.
Et au bout d'une quinzaine d'exécution, j'ai le message suivant "Out of memory (Needed 1048548 bytes)". J'ai modifié la variable 'query_cache_limit' pour la passer à 20 Mo mais j'ai toujours le même message.

Quelqu'un a une idée, peut être sur le paramètre MySQL à modifier ?

Merci d'avance !

Complément d'informations:
Je rajoute la liste des variables globales.
Il s'agit là d'une instance sur windows, mais j'ai exactement les mêmes versions que sur un serveur linux, et donc avec exactement le même problème.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228

auto_increment_increment 1 auto_increment_offset 1 automatic_sp_privileges ON back_log 50 basedir C:\\zdata\\Logiciels\\wamp\\wamp\\bin\\mysql\\mysql5.0.51a\\ binlog_cache_size 32768 bulk_insert_buffer_size 100000000 character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_filesystem binary character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir C:\\zdata\\Logiciels\\wamp\\wamp\\bin\\mysql\\mysql5.0.51a\\share\\charsets\\ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type 0 concurrent_insert 1 connect_timeout 5 datadir C:\\zdata\\Logiciels\\wamp\\wamp\\bin\\mysql\\mysql5.0.51a\\data\\ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 keep_files_on_create OFF engine_condition_pushdown OFF expire_logs_days 0 flush OFF flush_time 1800 ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) group_concat_max_len 1024 have_archive YES have_bdb NO have_blackhole_engine YES have_compress YES have_crypt NO have_csv NO have_dynamic_loading YES have_example_engine YES have_federated_engine YES have_geometry YES have_innodb YES have_isam NO have_merge_engine YES have_ndbcluster NO have_openssl DISABLED have_ssl DISABLED have_query_cache YES have_raid NO have_rtree_keys YES have_symlink YES hostname LPDSI6XP1233 init_connect init_file init_slave innodb_additional_mem_pool_size 16777216 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 33554432 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_io_threads 4 innodb_file_per_table OFF innodb_flush_log_at_trx_commit 1 innodb_flush_method innodb_force_recovery 0 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir innodb_log_archive OFF innodb_log_buffer_size 33554432 innodb_log_file_size 10485760 innodb_log_files_in_group 2 innodb_log_group_home_dir .\\ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 9437184 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language C:\\zdata\\Logiciels\\wamp\\wamp\\bin\\mysql\\mysql5.0.51a\\share\\english\\ large_files_support ON large_page_size 0 large_pages OFF lc_time_names en_US license GPL local_infile ON log OFF log_bin OFF log_bin_trust_function_creators OFF log_error C:\\zdata\\Logiciels\\wamp\\wamp\\logs\\mysql.log log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 1 long_query_time 10 low_priority_updates OFF lower_case_file_system ON lower_case_table_names 1 max_allowed_packet 33553408 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10 max_connections 512 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 4294967295 max_length_for_sort_data 2048 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 2048 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 107374182400 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal named_pipe OFF net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old_passwords OFF open_files_limit 2048 optimizer_prune_level 1 optimizer_search_depth 62 pid_file C:\\zdata\\Logiciels\\wamp\\wamp\\bin\\mysql\\mysql5.0.51a\\data\\LPDSI6XP1233.pid port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 268435456 query_cache_min_res_unit 4096 query_cache_size 268435456 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 61440 read_only OFF read_rnd_buffer_size 258048 relay_log_purge ON relay_log_space_limit 0 rpl_recovery_rank 0 secure_auth OFF secure_file_priv shared_memory OFF shared_memory_base_name MYSQL server_id 0 skip_external_locking ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_load_tmpdir C:\\WINDOWS\\TEMP\\ slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 sort_buffer_size 207864 sql_big_selects ON sql_mode sql_notes ON sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDB sync_binlog 0 sync_frm ON system_time_zone Paris, Madrid table_cache 510 table_lock_wait_timeout 50 table_type InnoDB thread_cache_size 12 thread_stack 196608 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF tmp_table_size 67108864 tmpdir C:\\WINDOWS\\TEMP\\ transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ updatable_views_with_limit YES version 5.0.51a-community-nt version_comment MySQL Community Edition (GPL) version_compile_machine ia32 version_compile_os Win32 wait_timeout 28800

---

Un SGBDR à besoin de BEAUCOUP de ressources de toute nature et en particulier de RAM. Lui limiter la RAM est un non sens absolu, vu qu'un SGBDR travaille exclusivement en cache pour manipuler ses données.

A +

Bonjour et merci de votre réponse,

je ne suis pas spécialement un expert dans le paramétrage des SGBD, et j'aurai voulu savoir ce que vous appeliez "limiter la RAM", i.e. quels sont les paramètres qui permettent d'étendre ou supprimer cette limite.

Merci bien.

Citation:

---

Envoyé par SQLpro

Un SGBDR à besoin de BEAUCOUP de ressources de toute nature et en particulier de RAM. Lui limiter la RAM est un non sens absolu, vu qu'un SGBDR travaille exclusivement en cache pour manipuler ses données.

A +

---

Le query_cache n'est pas la taille de la mémoire allouée aux requêtes. C'est un cache qui conserve le résultat des requêtes SELECT récentes, càd que si on passe deux fois la même requête (avec les mêmes paramètres), MySQL renverra le résultat précédemment obtenu plutôt que de ré-exécuter la requête.

Juste pour vérifier que le problème n'est pas là, peux-tu ajouter SQL_NO_CACHE juste après ton SELECT et retester ?

Cf doc http://dev.mysql.com/doc/refman/5.0/...iguration.html

Désolé, aucun effet, même message pour la même requête.
Concrètement j'exécute une vingtaine de requêtes (similaires aux valeurs des critères près), et c'est toujours sur la 15ème avec le même message...
Merci.

Peux-tu nous donner cette requête ? Combien de lignes te renvoie-t-elle ?

Citation:

---

Combien de lignes te renvoie-t-elle ?

---

ben ça, ça va être difficile de répondre si elle s'exécute pas ! ;-)

A +

Citation:

---

Envoyé par SQLpro

ben ça, ça va être difficile de répondre si elle s'exécute pas ! ;-)

A +

---

Si, elle s'exécute 14 fois et plante à la quinzième.

Citation:

---

Envoyé par Antoun

Si, elle s'exécute 14 fois et plante à la quinzième.

---

C'est vraiment curieux ça. Normalement toutes les ressources utilisées par les requêtes doivent être libérées. Je ne sais pas trop dans quelles situations ce message peut sortir, mais il se pourrait que :

• La requête est trop grosse et ne peut pas être traitée. Dans ce cas c'est la 15ème en particulier qui est plus grosse/touche plus de données et coince ; Même lancée seule ou en premier elle ne devrait pas passer. En soi ça me semble louche : Si elle est si moche que ça c'est probablement qu'elle a un problème.
• Jointures mal faites -> table temporaire énorme -> plus de place sur le disque. Ça rejoint un peu le cas précédent.
• Il y a des ressources non libérées, par exemple des tables temporaires trop petites pour passer sur le disque. Arrivé à la 15ème couche ça coince.
• Tout est fait dans une transaction et les requêtes changent beaucoup de données. Peut-être que mysql sature.

Les 2 premières options me semblent les meilleures candidates.

max_binlog_cache_size me semble très gros. Je me demande s'il peut monopoliser les 2Go de mémoire disponibles sur un windows 32bits...

query_cache_size 268435456
Plus de 200Mo ça fait quand même beaucoup.

Sinon, pendant se temps, le serveur est-il chargé ? Y a-t-il d'autres connexions qui monopolisent des ressources ?

Bonsoir, et désolé de ne pas être revenu plus tôt sur mon sujet... assez occupé.

Je donne les réponses, peut-être dans le désordre...

1) Ca renvoit entre zéro et 80 lignes. Et la plus grosse table possède 400.000 lignes.
2) Je n'ai toujours pas de solution, si ce n'est que j'ai changé le traitement et scindé en 2 requêtes. Donc ca fonctionne mais bon j'aimerais trouver une explication pour ce souci...
3) Pour la requête, elle est un peu longue donc je la simplifie pour y mettre je pense le morceau le plus intéressant (du moins c'est celui que j'ai modifié).

Code:

---

1 2 3 4

SELECT * FROM Table1 T1 INNER JOIN Table2 T2 on T1.id=T2.idt1 WHERE IFNULL (SELECT SUM(DISTINCT IF(lib='toto',1,0)) FROM Table1 WHERE ...))

---

La Table1 contient 400.000 lignes.
J'ai demandé pourquoi il avait fallu passer par "IFNULL (...SUM(DISTINCT...)". Bon effectivement en une requête, difficile de faire autrement.
Après quelques tests, j'ai l'impression que c'est le traitement du SUM DISTINCT IF qui passe mal... même sur une requête plus petite, MySQL n'aime pas...

Bon j'accorde quand même que l'utilisation est tordue... Mais je ne comprends pas pourquoi MySQL plante sur une requête et pas sur les autres alors que certaines d'entre elles retournent un plus grand nombre de lignes...

Merci en tout cas pour votre "mobilisation" ;)

Si je comprends bien ton SUM(DISTINCT IF...), il pourrait être remplacé avantageusement par un EXISTS. Pour ce qui est de la performance globale, je suppose que ta sous-requête est corrélée, ce que MySQL optimise très mal... ça pourrait être intéressant de voir s'il est possible de la remplacer par une jointure ou par une sous-requête non corrélée.

Ce qui a été fait. J'ai viré le SUM(DISTINCT IF...) par l'utilisation d'un EXISTS et d'un NOT EXISTS...
Les résultats sont identiques mais bien sûr pas les performances.
Pour ceux qui ont le même problème, solution : revoir la requête même si ca ne semble pas venir de la requête au début...

Ce que je ne comprends toujours pas c'est ce côté aléatoire dans le plantage... c'est-à-dire que la requête seule fonctionne mais quand on en passe plusieurs les unes après les autres, au bout d'un moment ca ne fonctionne plus... Et ceci même en augmentant de manière drastique les caches, buffer, etc. Comme tu le dis, certainement un pb d'optimisation... il y en a qquns comme ça, qd on tombe dessus ... :?

Merci en tout cas, et bonnes fêtes :king:

Citation:

---

Envoyé par Antoun

Si je comprends bien ton SUM(DISTINCT IF...), il pourrait être remplacé avantageusement par un EXISTS. Pour ce qui est de la performance globale, je suppose que ta sous-requête est corrélée, ce que MySQL optimise très mal... ça pourrait être intéressant de voir s'il est possible de la remplacer par une jointure ou par une sous-requête non corrélée.

---