slow_launch_threads

Hi,

I’ve noticed that my slow_launch_threads is somewhat high (Slow_launch_threads 338) and I tried to google for it.

I now understand what this status variable is but I don’t know how to optimize it.

Any tips on what should I do to reduce that number or that’s not important?

±---------------------------±-----------+| Variable_name | Value |±---------------------------±-----------+| Aborted_clients | 20852 || Aborted_connects | 132 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 3014728789 || Bytes_sent | 2798518386 || Com_admin_commands | 6010 || Com_alter_db | 0 || Com_alter_table | 12 || Com_analyze | 1 || Com_backup_table | 0 || Com_begin | 3 || Com_change_db | 1243858 || Com_change_master | 0 || Com_check | 0 || Com_checksum | 0 || Com_commit | 1 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 3864 || Com_dealloc_sql | 0 || Com_delete | 211917 || Com_delete_multi | 1 || Com_do | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 2679 || Com_drop_user | 0 || Com_execute_sql | 0 || Com_flush | 3 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 318836 || Com_insert_select | 12629 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 3 || Com_optimize | 126 || 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 | 10072 || 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 | 4931474 || Com_set_option | 1076 || Com_show_binlog_events | 0 || Com_show_binlogs | 7 || Com_show_charsets | 52 || Com_show_collations | 50 || Com_show_column_types | 0 || Com_show_create_db | 7 || Com_show_create_table | 143 || Com_show_databases | 29 || Com_show_errors | 0 || Com_show_fields | 183 || Com_show_grants | 6 || Com_show_innodb_status | 3170 || Com_show_keys | 37 || Com_show_logs | 0 || Com_show_master_status | 4 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_privileges | 0 || Com_show_processlist | 47 || Com_show_slave_hosts | 2 || Com_show_slave_status | 0 || Com_show_status | 3194 || Com_show_storage_engines | 0 || Com_show_tables | 754 || Com_show_variables | 116 || Com_show_warnings | 0 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_truncate | 4164 || Com_unlock_tables | 2 || Com_update | 1421434 || Com_update_multi | 0 || Connections | 2250939 || Created_tmp_disk_tables | 30987 || Created_tmp_files | 22617 || Created_tmp_tables | 308388 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 2 || Handler_commit | 0 || Handler_delete | 276921 || Handler_discover | 0 || Handler_read_first | 148573 || Handler_read_key | 309526294 || Handler_read_next | 2572335379 || Handler_read_prev | 6877680 || Handler_read_rnd | 9174977 || Handler_read_rnd_next | 3440247175 || Handler_rollback | 6 || Handler_update | 214255498 || Handler_write | 38552596 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 127511 || Key_blocks_used | 105948 || Key_read_requests | 438621420 || Key_reads | 886862 || Key_write_requests | 10918003 || Key_writes | 963502 || Max_used_connections | 36 || Not_flushed_delayed_rows | 0 || Open_files | 484 || Open_streams | 0 || Open_tables | 256 || Opened_tables | 42162 || Qcache_free_blocks | 968 || Qcache_free_memory | 3662088 || Qcache_hits | 14724632 || Qcache_inserts | 4800027 || Qcache_lowmem_prunes | 1238238 || Qcache_not_cached | 119059 || Qcache_queries_in_cache | 2872 || Qcache_total_blocks | 8358 || Questions | 51921877 || Rpl_status | NULL || Select_full_join | 67569 || Select_full_range_join | 430 || Select_range | 319376 || Select_range_check | 1018 || Select_scan | 1866453 || Slave_open_temp_tables | 0 || Slave_running | OFF || Slave_retried_transactions | 0 || Slow_launch_threads | 338 || Slow_queries | 62 || Sort_merge_passes | 11318 || Sort_range | 379840 || Sort_rows | 190553812 || Sort_scan | 580587 || Table_locks_immediate | 10350296 || Table_locks_waited | 2349 || Threads_cached | 5 || Threads_connected | 8 || Threads_created | 10402 || Threads_running | 1 || Uptime | 1623940 |±---------------------------±-----------+

Slow launch threads are just threads which took long to launch. This generally happens in case of general system overload as it is pretty simple operations.

In your case you probably need to run EXPLAIN for your queries, stats variables show you have a lot of suboptimal queries.

Example:

| Select_full_join | 67569 |

Meaning many joins are done without indexes.

Enable slow query log, set long_query_time=2 and enable log_queries_not_using_indexes and run explain for queries you see in the log.

Great tips, thanks )

Actually I never noticed that stats before but I will start logging queries not using indexes.

But first I will try to increase my table_cache to 768 (from 256) and thread_cache_size to 40 (from cool:.

Do you know any way to “profile” sql queries besides explain? I need something more detailed to find out if the query is slow because the table it uses needed to be open, if something wasn’t in cache, if the thread creation was slow, etc.

The problem is that I have queries that are perfect (index and not very large < 1M) and they sometimes take 0.2s and sometimes 0.002s (not real values, just to show the diference in magnitude).

I suspect it can be because of opening tables and of course stuff that isn’t in cache but if I can get some help from a profiling tool, that would be great )

Increasing table_cache is good idea, however your opened_tables are not high enough for your uptime to hope it will make dramatic improvement.

To profile queries you can use SHOW STATUS, especially if you use MySQL 5.0. Do something like:

FLUSH STATUS;

SHOW STATUS;

And you’ll see statistics increments by this query. I would especially look at Handler_XX values.

The different times for query are normal on loaded system as load changes plus there can be locks as well as different amount of IO operations to resolve the query.