Requetes SQL parfois lente : Selon show profile -> Pb opening table

WRInaute accro
Bonjour

Depuis la mise en ligne d'un de mes nouveaux projets, je constate que parfois des requêtes (ca peut etre n'importe laquelle) mettent beaucoup de temps à s'exécuter pouvant aller jusqu'à plusieurs secondes. (problème jamais identifié en local sur mon pc)

Ma Bdd est presque vide (quelques dizaines d'enregistrements éparpillés sur toutes les tables)
Et ce problème peut toucher n'importe quelle requête, même de simples select sur des tables avec quasiment aucun enregistrement.
Donc à priori il ne s'agit pas d'un problème de requêtes spécifiques gourmandes ou de soucis d'index etc.

Je me tire les cheveux depuis plusieurs jours sans trouver le problème.

J'ai donc décidé de travailler un peu plus méthodiquement et j'ai créé une petite page PHP très simple avec la connexion à la bdd et l’exécution de 2 requêtes simples. je vérifie le temps d’exécution en actualisant plusieurs fois la page et je fait également un show profile.

Résultat de ce test je constate que lorsque une des requêtes met plusieurs secondes le problème semble venir d'opening tables

J'ai d'ailleurs eu droit hier dans la nuit à l'erreur suivante :

Code:
SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

Maintenant à partir de cette analyse je n'ai strictement aucune idée de ce que cela signifie exactement et quel autre test devrais-je effectuer pour déterminer exactement le problème et surtout le régler une bonne foie.

PS : je suis OVH en mutualisé. J'utilise Mysql et le moteur Innodb
 
WRInaute accro
Voici un exemple de show profile obtenu lorsqu'une requête met du temps :

Code:
temps requete : 1.2502820491791

starting => 0.000007
Waiting for query cache lock => 0.000003
checking query cache for query => 0.000026
checking permissions => 0.000003
Opening tables => 1.249692
System lock => 0.000013
Waiting for query cache lock => 0.000040
init => 0.000014
optimizing => 0.000032
statistics => 0.000041
preparing => 0.000024
Waiting for query cache lock => 0.000004
preparing => 0.000003
storing result in query cache => 0.000014
executing => 0.000003
Sending data => 0.000038
end => 0.000005
query end => 0.000004
closing tables => 0.000012
freeing items => 0.000055
logging slow query => 0.000003
cleaning up => 0.000043

On voit bien ici le Opening tables => 1.249692

Sur le site mysql.com on peut lire ceci :

Opening tables, Opening table

The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_cache value is large enough.

Le problème, c’est comment trouver dans quel cas de figure je suis et comment faire les tests?

Voici également le show status si ca peut vous aider

Code:
Aborted_clients => 45528
Aborted_connects => 411072
Binlog_cache_disk_use => 0
Binlog_cache_use => 0
Binlog_stmt_cache_disk_use => 0
Binlog_stmt_cache_use => 0
Bytes_received => 895
Bytes_sent => 5466
Com_admin_commands => 0
Com_assign_to_keycache => 0
Com_alter_db => 0
Com_alter_db_upgrade => 0
Com_alter_event => 0
Com_alter_function => 0
Com_alter_procedure => 0
Com_alter_server => 0
Com_alter_table => 0
Com_alter_tablespace => 0
Com_analyze => 0
Com_begin => 0
Com_binlog => 0
Com_call_procedure => 0
Com_change_db => 0
Com_change_master => 0
Com_check => 0
Com_checksum => 0
Com_commit => 0
Com_create_db => 0
Com_create_event => 0
Com_create_function => 0
Com_create_index => 0
Com_create_procedure => 0
Com_create_server => 0
Com_create_table => 0
Com_create_trigger => 0
Com_create_udf => 0
Com_create_user => 0
Com_create_view => 0
Com_dealloc_sql => 0
Com_delete => 0
Com_delete_multi => 0
Com_do => 0
Com_drop_db => 0
Com_drop_event => 0
Com_drop_function => 0
Com_drop_index => 0
Com_drop_procedure => 0
Com_drop_server => 0
Com_drop_table => 0
Com_drop_trigger => 0
Com_drop_user => 0
Com_drop_view => 0
Com_empty_query => 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_install_plugin => 0
Com_kill => 0
Com_load => 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_release_savepoint => 0
Com_rename_table => 0
Com_rename_user => 0
Com_repair => 0
Com_replace => 0
Com_replace_select => 0
Com_reset => 0
Com_resignal => 0
Com_revoke => 0
Com_revoke_all => 0
Com_rollback => 0
Com_rollback_to_savepoint => 0
Com_savepoint => 0
Com_select => 1
Com_set_option => 6
Com_signal => 0
Com_show_authors => 0
Com_show_binlog_events => 0
Com_show_binlogs => 0
Com_show_charsets => 0
Com_show_collations => 0
Com_show_contributors => 0
Com_show_create_db => 0
Com_show_create_event => 0
Com_show_create_func => 0
Com_show_create_proc => 0
Com_show_create_table => 0
Com_show_create_trigger => 0
Com_show_databases => 0
Com_show_engine_logs => 0
Com_show_engine_mutex => 0
Com_show_engine_status => 0
Com_show_events => 0
Com_show_errors => 0
Com_show_fields => 0
Com_show_function_status => 0
Com_show_grants => 0
Com_show_keys => 0
Com_show_master_status => 0
Com_show_open_tables => 0
Com_show_plugins => 0
Com_show_privileges => 0
Com_show_procedure_status => 0
Com_show_processlist => 0
Com_show_profile => 3
Com_show_profiles => 0
Com_show_relaylog_events => 0
Com_show_slave_hosts => 0
Com_show_slave_status => 0
Com_show_status => 1
Com_show_storage_engines => 0
Com_show_table_status => 0
Com_show_tables => 0
Com_show_triggers => 0
Com_show_variables => 0
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_reprepare => 0
Com_stmt_reset => 0
Com_stmt_send_long_data => 0
Com_truncate => 0
Com_uninstall_plugin => 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 => 68472163
Created_tmp_disk_tables => 0
Created_tmp_files => 337376
Created_tmp_tables => 3
Delayed_errors => 0
Delayed_insert_threads => 0
Delayed_writes => 4436
Flush_commands => 51
Handler_commit => 1
Handler_delete => 0
Handler_discover => 0
Handler_prepare => 0
Handler_read_first => 0
Handler_read_key => 1
Handler_read_last => 0
Handler_read_next => 0
Handler_read_prev => 0
Handler_read_rnd => 0
Handler_read_rnd_next => 43
Handler_rollback => 0
Handler_savepoint => 0
Handler_savepoint_rollback => 0
Handler_update => 0
Handler_write => 40
Innodb_buffer_pool_pages_data => 61957
Innodb_buffer_pool_bytes_data => 1015103488
Innodb_buffer_pool_pages_dirty => 610
Innodb_buffer_pool_bytes_dirty => 9994240
Innodb_buffer_pool_pages_flushed => 48661657
Innodb_buffer_pool_pages_free => 0
Innodb_buffer_pool_pages_misc => 3578
Innodb_buffer_pool_pages_total => 65535
Innodb_buffer_pool_read_ahead_rnd => 0
Innodb_buffer_pool_read_ahead => 4985792
Innodb_buffer_pool_read_ahead_evicted => 144717
Innodb_buffer_pool_read_requests => 333880554215
Innodb_buffer_pool_reads => 27599255
Innodb_buffer_pool_wait_free => 0
Innodb_buffer_pool_write_requests => 2002248381
Innodb_data_fsyncs => 105836925
Innodb_data_pending_fsyncs => 1
Innodb_data_pending_reads => 1
Innodb_data_pending_writes => 0
Innodb_data_read => 537667817472
Innodb_data_reads => 32896520
Innodb_data_writes => 148840711
Innodb_data_written => 1775761521664
Innodb_dblwr_pages_written => 48661657
Innodb_dblwr_writes => 1122545
Innodb_have_atomic_builtins => ON
Innodb_log_waits => 5
Innodb_log_write_requests => 280452084
Innodb_log_writes => 96072663
Innodb_os_log_fsyncs => 96497773
Innodb_os_log_pending_fsyncs => 1
Innodb_os_log_pending_writes => 0
Innodb_os_log_written => 181007337472
Innodb_page_size => 16384
Innodb_pages_created => 5188762
Innodb_pages_read => 32816372
Innodb_pages_written => 48661657
Innodb_row_lock_current_waits => 0
Innodb_row_lock_time => 1036650
Innodb_row_lock_time_avg => 25
Innodb_row_lock_time_max => 36535
Innodb_row_lock_waits => 40031
Innodb_rows_deleted => 73225444
Innodb_rows_inserted => 358818694
Innodb_rows_read => 309789950011
Innodb_rows_updated => 107299906
Innodb_truncated_status_writes => 0
Key_blocks_not_flushed => 3
Key_blocks_unused => 1501761
Key_blocks_used => 620838
Key_read_requests => 95410656371
Key_reads => 25884602
Key_write_requests => 2675923921
Key_writes => 816605597
Last_query_cost => 10.499000
Max_used_connections => 301
Not_flushed_delayed_rows => 0
Open_files => 30572
Open_streams => 0
Open_table_definitions => 80000
Open_tables => 40000
Opened_files => 205614208
Opened_table_definitions => 0
Opened_tables => 0
Performance_schema_cond_classes_lost => 0
Performance_schema_cond_instances_lost => 0
Performance_schema_file_classes_lost => 0
Performance_schema_file_handles_lost => 0
Performance_schema_file_instances_lost => 0
Performance_schema_locker_lost => 0
Performance_schema_mutex_classes_lost => 0
Performance_schema_mutex_instances_lost => 0
Performance_schema_rwlock_classes_lost => 0
Performance_schema_rwlock_instances_lost => 0
Performance_schema_table_handles_lost => 0
Performance_schema_table_instances_lost => 0
Performance_schema_thread_classes_lost => 0
Performance_schema_thread_instances_lost => 0
Prepared_stmt_count => 0
Qcache_free_blocks => 33990
Qcache_free_memory => 100019296
Qcache_hits => 2577660920
Qcache_inserts => 659239377
Qcache_lowmem_prunes => 212396617
Qcache_not_cached => 97788079
Qcache_queries_in_cache => 71900
Qcache_total_blocks => 192354
Queries => 4833519321
Questions => 13
Rpl_status => AUTH_MASTER
Select_full_join => 0
Select_full_range_join => 0
Select_range => 0
Select_range_check => 0
Select_scan => 3
Slave_heartbeat_period => 0.000
Slave_open_temp_tables => 0
Slave_received_heartbeats => 0
Slave_retried_transactions => 0
Slave_running => OFF
Slow_launch_threads => 25
Slow_queries => 0
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 => 2118047661
Table_locks_waited => 1843473
Tc_log_max_pages_used => 0
Tc_log_page_size => 0
Tc_log_page_waits => 0
Threads_cached => 13
Threads_connected => 19
Threads_created => 87797
Threads_running => 11
Uptime => 4317859
Uptime_since_flush_status => 4317859
ovh_audit_calls => 1075685346
ovh_audit_version => 1.0
 
WRInaute accro
Personne n'aurait vraiment aucune piste à me suggérer? :?

Pourquoi l'opening tables met parfois plusieurs secondes? Que dois je tester pour identifier le problème?

Ça peut toucher n'importe quelles requêtes (simples select avec ou sans jointures, update...) et tables.

J'atteins des fois 6 secondes voir même le time limit, juste à cause du opening tables (constaté via SHOW PROFILE)

- Le explain de mes requêtes sont bons
- Ma base est quasi vide (1Mo, soit quelques dizaines d'enregistrements)
- je n'ai qu'une dizaine de tables.
- je n'ai pas plus de 5-6 requêtes SQL exécutée par page
- je ferme ma connexion en fin de script ($bdd=null;).
- j’exécute toutes mes requêtes en début de script (dans mes controleurs)

Le côté aléatoire rend vraiment le problème très difficile à déterminer.

est-ce qu'il y a un point particulier à vérifier dans la structure de mes tables ou de ma Bdd qui pourrait poser un tel problème?

Je ne vois aucune explication à de telles performances même sur un mutualisé.
Et je suis incapable de déterminer si le problème vient du côté serveur ou de mon côté.

Je suis vraiment à court d'idées et de tests :(

C’est peut être important, mais lorsque j'ai mis mon site en ligne OVH venait de mettre à jour le mysql sur la version 5.5 sur les mutualisés.
Pourrait-il y avoir des soucis de configuration de leur côté? (si oui pourquoi ne l'auraient-ils toujours pas constaté?).

La version mysql 5.5 est-elle connu pour un problème similaire?

J'ai posté ma question sur plusieurs forums spécialisés et aucune réponse :/
Même le support ovh aucune réponse après 3 jours d'attente.
 
Discussions similaires
Haut