Bonjour,
Je rencontre depuis quelques temps des blocages sur une requête SQL particulière.
La plupart du temps, cette requête se remet à fonctionner lorsque je fais une optimisation de mes tables (elle aboutit en général en moins d'une minute) mais depuis quelques jours, cela ne suffit plus. La requête se bloque en cours d'exécution (en fait, elle tourne dans le vide pendant des
heures) et je ne comprends pas pourquoi.
Voici le résultat de show processlist sur le serveur montrant que la requête tourne toujours mais est bloquée :
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 814
User: GSYS
Host: localhost:3961
db: gsysreport
Command: Query
Time: 24722
State: Sending data
Info: select articles.ClefFournisseur as 'ClefFournisseur',EXTRACT(YEAR_MONTH FROM caisse.DatePiece) as 'D
La requête est donc lancée depuis 24722 secondes, et le compteur continue de tourner mais elle n'aboutit pas et peut rester dans cet état pendant plusieurs jours...
Ma base de données est très grosse (plus de 4.5 Go en tout), et les tables concernées par cette requête ont beaucoup d'enregistrements.
table articles : 130 320 enreg
table caisse : 3 146 137 enreg
table caisselignes : 12 092 002 enreg
J'utilise MySQL 5.0.21. Mes tables sont toutes en InnoDB.
Voici la requête complète et la structure des tables utilisées.
Requête :
=======
select articles.ClefFournisseur as 'ClefFournisseur',
EXTRACT(YEAR_MONTH FROM caisse.DatePiece) as 'DatePiece',
SUM(caisselignes.PrixTotalHorsTaxe) as 'CAHT',
count(distinct(caisse.ClefCaisse)) as 'NbTickets',
SUM(caisselignes.Quantite) as 'Qte',
(SUM(caisselignes.PrixTotalHorsTaxe))-(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite))) as 'Marge',
((SUM(caisselignes.PrixTotalHorsTaxe))-(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite))))/(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite)))*100 as 'MargePourc'
from caisse,caisselignes,articles
where (caisse.ClefCaisse=caisselignes.ClefCaisse) and
(caisselignes.ClefArticle=articles.ClefArticle) and
(caisse.NumeroSite='1') and
(EXTRACT(YEAR_MONTH FROM caisse.DatePiece)='200805')
group by articles.ClefFournisseur,EXTRACT(YEAR_MONTH FROM caisse.DatePiece)
Tables utilisées :
===========
structure des tables exportée via phpmyadmin :
CREATE TABLE caisse (
ClefCaisse varchar(22) NOT NULL default '',
ClefClient varchar(15) NOT NULL default '',
NomClient varchar(35) default NULL,
NumeroSite varchar(5) default NULL,
DatePiece date NOT NULL default '0000-00-00',
PrixTotalHT decimal(19,4) default NULL,
NetAPayer decimal(19,4) default NULL,
ArticlesVendus decimal(19,4) default NULL,
PRIMARY KEY (ClefCaisse),
KEY ClefClient (ClefClient),
KEY DatePiece (DatePiece)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE articles (
ClefArticle varchar(13) NOT NULL default '',
ClefFournisseur varchar(8) NOT NULL default '',
ClefFamille varchar(8) NOT NULL default '',
DesignationArticle varchar(50) default NULL,
CodeFamille varchar(8) default NULL,
DateCreation date default NULL,
DateModification date default NULL,
CodeFournisseur varchar(8) default NULL,
PrixUnitaireAchat decimal(19,4) default NULL,
PrixUnitaireVente_1 decimal(19,4) default NULL,
PRIMARY KEY (ClefArticle),
KEY ClefFournisseur (ClefFournisseur),
KEY ClefFamille (ClefFamille)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE caisselignes (
ClefPrimaire bigint(20) NOT NULL auto_increment,
ClefCaisse varchar(22) NOT NULL default '',
ClefArticle varchar(13) NOT NULL default '',
Quantite decimal(19,4) default NULL,
PrixUnitaireHorsTaxe decimal(19,4) default NULL,
PrixTotalHorsTaxe decimal(19,4) default NULL,
PRIMARY KEY (ClefPrimaire),
KEY ClefCaisse (ClefCaisse),
KEY ClefArticle (ClefArticle)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Je me pose la question de savoir si ce ne sont pas les fonctions spéciales EXTRACT(YEAR_MONTH FROM ...) qui lui posent problème. Avez-vous déjà utilisé ces fonctions ? Sont-elles très gourmandes en mémoire ?
J'ai aussi essayé de modifier ma requête en remplaçant EXTRACT(YEAR_MONTH FROM .caisse.DatePiece) par DATE_FORMAT(caisse.DatePiece,'%Y%m'), mais cela ne change rien.
Avez-vous une autre idée sur ce qui pourrait bloquer cette requête ?
Merci d'avance pour votre aide car je ne sais vraiment pas comment résoudre ce problème récurrent.
Pour info :
=======
explain ajouté au début de ma requête indique :
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: caisse
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2735156
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: caisselignes
type: ref
possible_keys: ClefCaisse,ClefArticle
key: ClefCaisse
key_len: 24
ref: gsysreport.caisse.ClefCaisse
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 15
ref: gsysreport.caisselignes.ClefArticle
rows: 1
Extra:
donc apparemment ma requête n'est pas optimisée pour la table caisse puisque le type retourné est ALL mais je ne vois pas comment faire autrement...
mysql> show variables;
+---------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | C:\Program Files\MySQL\MySQL Server 5.0\ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| 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:\Program Files\MySQL\MySQL Server 5.0\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 | E:\MYSQL\V5-0\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 |
| 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 | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 49283072 |
| 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 | ON |
| 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 | 1048576 |
| innodb_log_file_size | 25165824 |
| 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_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 10 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 26214400 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | C:\Program Files\MySQL\MySQL Server 5.0\share\english\ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | .\HTTPDIN2.err |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| 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 | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| 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 | 36700160 |
| 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 | 622 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | E:\MYSQL\V5-0\data\HTTPDIN2.pid |
| prepared_stmt_count | 0 |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| 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 |
| shared_memory | OFF |
| shared_memory_base_name | MYSQL |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | F:\__Temp\ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| sort_buffer_size | 262136 |
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | InnoDB |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | Paris, Madrid (heure d' |
| table_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_type | InnoDB |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 18874368 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.21-community-nt-log |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------------------------------+
211 rows in set (0.08 sec)
mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 16 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 460686 |
| Bytes_received | 1042 |
| Bytes_sent | 17796 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 3 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 14 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 6 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 2 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 960 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 36 |
| Handler_read_next | 15368616 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 424 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 552 |
| Innodb_buffer_pool_pages_data | 3007 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 826745 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 4 |
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 3008 |
| Innodb_buffer_pool_read_ahead_rnd | 130718 |
| Innodb_buffer_pool_read_ahead_seq | 29803 |
| Innodb_buffer_pool_read_requests | 588037026 |
| Innodb_buffer_pool_reads | 39475318 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 112983605 |
| Innodb_data_fsyncs | 967949 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 472190976 |
| Innodb_data_reads | 46690332 |
| Innodb_data_writes | 1792602 |
| Innodb_data_written | 2271550464 |
| Innodb_dblwr_pages_written | 826745 |
| Innodb_dblwr_writes | 10598 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 10208274 |
| Innodb_log_writes | 941779 |
| Innodb_os_log_fsyncs | 938277 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 949083648 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 251834 |
| Innodb_pages_read | 46690324 |
| Innodb_pages_written | 826745 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 15167 |
| Innodb_rows_inserted | 19028152 |
| Innodb_rows_read | 169490217 |
| Innodb_rows_updated | 361217 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 22484 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 7 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3 |
| Open_streams | 0 |
| Open_tables | 6 |
| Opened_tables | 4 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 928756 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 6 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 928700 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 4 |
| Threads_connected | 3 |
| Threads_created | 7 |
| Threads_running | 3 |
| Uptime | 308902 |
+-----------------------------------+------------+
245 rows in set (0.08 sec)
Merci pour votre aide.
Cordialement,
Delphine