Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » slow_launch_threads
slow_launch_threads [message #99] Wed, 23 August 2006 10:37 Go to next message
cifroes is currently offline cifroes
Messages: 2
Registered: August 2006
Junior Member
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    |
+----------------------------+------------+
Report message to a moderator
Re: slow_launch_threads [message #100 is a reply to message #99 ] Wed, 23 August 2006 10:54 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: slow_launch_threads [message #104 is a reply to message #99 ] Wed, 23 August 2006 12:10 Go to previous messageGo to next message
cifroes is currently offline cifroes
Messages: 2
Registered: August 2006
Junior Member
Great tips, thanks Smile

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 Smile
Report message to a moderator
Re: slow_launch_threads [message #107 is a reply to message #99 ] Wed, 23 August 2006 16:12 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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;
<run query>
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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:Queries are slowing down as table grows
Next Topic:Data size
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Sun Jul 5 17:23:10 EDT 2009

Total time taken to generate the page: 0.00614 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.