You should also up your thread cache, currently it's set to 0. MySQL won't reuse threads if this is set to 0. MySQL has created
13781740 new threads so far.
You can change the value while the server is running (example below), but may
want to also add it to your conf file.
set global thread_cache_size=30
----- Original Message -----
From: "Christian Hammers" <[EMAIL PROTECTED]>
To: "Dan Buettner" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Thursday, June 22, 2006 1:02 PM
Subject: Re: Lots of threads in "opening tables" and "closing tables" state
Hello
On Thu, Jun 22, 2006 at 11:28:41AM -0500, Dan Buettner wrote:
Christian, can you post the output of
SHOW VARIABLES;
and
SHOW STATUS;
please?
Ok, is below. We're only using MyISAM although InnoDB is activated.
I see you're servicing 761 queries per second on average, which is
pretty good. However, it appears to me your server has performed over
120 million operations (Opens: 120224674) to open tables in 10 days of
uptime, so your
table_cache or some other related variable may be too low for your
situation. As a result your server is perhaps having to close tables
only to re-open them very soon, introducing a lot of overhead.
Good hint!
I raised table_cache from 64 to 1500 now.
open_files_limit was already is at 10010 but has now been set explicitly
to 999999 to be sure that ulimit is invoked in mysqld_safe.
bye,
-christian-
mysql> SHOW STATUS;
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 14621 |
| Aborted_connects | 46 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1116549455 |
| Bytes_sent | 4247046330 |
| Com_admin_commands | 22 |
| Com_alter_db | 0 |
| Com_alter_table | 6 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 22443782 |
| 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 | 64987 |
| Com_dealloc_sql | 0 |
| Com_delete | 393983 |
| Com_delete_multi | 7 |
| 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 | 21 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 37524371 |
| Com_insert_select | 491 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 27873 |
| 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 | 101809 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 1488592 |
| Com_savepoint | 0 |
| Com_select | 264552897 |
| Com_set_option | 3185250 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 10 |
| Com_show_charsets | 0 |
| Com_show_collations | 50660 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 28822 |
| Com_show_databases | 63 |
| Com_show_errors | 0 |
| Com_show_fields | 60687 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 7841 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 15185 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 57003 |
| Com_show_variables | 50686 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 27870 |
| Com_update | 4998234 |
| Com_update_multi | 0 |
| Connections | 13781741 |
| Created_tmp_disk_tables | 13908301 |
| Created_tmp_files | 357 |
| Created_tmp_tables | 25556398 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 11 |
| Handler_commit | 172 |
| Handler_delete | 441765 |
| Handler_discover | 0 |
| Handler_read_first | 8843583 |
| Handler_read_key | 3555263777 |
| Handler_read_next | 2460992409 |
| Handler_read_prev | 2 |
| Handler_read_rnd | 364084608 |
| Handler_read_rnd_next | 734358289 |
| Handler_rollback | 991 |
| Handler_update | 4139918 |
| Handler_write | 3324995673 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 462389 |
| Key_blocks_used | 269084 |
| Key_read_requests | 2177928092 |
| Key_reads | 646663798 |
| Key_write_requests | 134750556 |
| Key_writes | 17604377 |
| Max_used_connections | 709 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 83 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 120233868 |
| Qcache_free_blocks | 11124 |
| Qcache_free_memory | 212475728 |
| Qcache_hits | 338146801 |
| Qcache_inserts | 22031468 |
| Qcache_lowmem_prunes | 305572 |
| Qcache_not_cached | 242822244 |
| Qcache_queries_in_cache | 35193 |
| Qcache_total_blocks | 81683 |
| Questions | 688260340 |
| Rpl_status | NULL |
| Select_full_join | 201 |
| Select_full_range_join | 104217 |
| Select_range | 259660 |
| Select_range_check | 0 |
| Select_scan | 1359197 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 7342 |
| Sort_merge_passes | 0 |
| Sort_range | 29843569 |
| Sort_rows | 527144541 |
| Sort_scan | 14043825 |
| Table_locks_immediate | 972928780 |
| Table_locks_waited | 12738164 |
| Threads_cached | 0 |
| Threads_connected | 21 |
| Threads_created | 13781740 |
| Threads_running | 2 |
| Uptime | 901110 |
+----------------------------+------------+
157 rows in set (0.00 sec)
mysql> SHOW VARIABLES;
+---------------------------------+----------------------------------------------------------+
| Variable_name | Value
|
+---------------------------------+----------------------------------------------------------+
| back_log | 50
|
| basedir | /usr/
|
| bdb_cache_size | 8388600
|
| bdb_home |
|
| bdb_log_buffer_size | 0
|
| bdb_logdir |
|
| bdb_max_lock | 10000
|
| bdb_shared_data | OFF
|
| bdb_tmpdir |
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | /usr/share/mysql/charsets/
|
| collation_connection | latin1_swedish_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /var/lib/mysql/
|
| 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
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| 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 | DISABLED
|
| have_blackhole_engine | NO
|
| have_compress | YES
|
| have_crypt | YES
|
| have_csv | YES
|
| have_example_engine | NO
|
| have_geometry | YES
|
| have_innodb | YES
|
| have_isam | YES
|
| have_ndbcluster | DISABLED
|
| have_openssl | NO
|
| have_query_cache | YES
|
| have_raid | YES
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_additional_mem_pool_size | 10485760
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 419430400
|
| innodb_data_file_path |
/var/lib/mysql/tablespace1.innodb:1024M:autoextend |
| innodb_data_home_dir |
|
| innodb_fast_shutdown | ON
|
| 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 | 8388608
|
| innodb_log_file_size | 268435456
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | /var/lib/mysql/
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 8
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 536870912
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language | /usr/share/mysql/english/
|
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_bin | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | ON
|
| log_update | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 16776192
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 2000
|
| 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_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| myisam_data_pointer_size | 4
|
| myisam_max_extra_sort_file_size | 2147483648
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 67108864
|
| ndb_autoincrement_prefetch_sz | 32
|
| ndb_force_send | ON
|
| ndb_use_exact_count | ON
|
| ndb_use_transactions | ON
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | ON
|
| open_files_limit | 10010
|
| pid_file | /var/run/mysqld/mysqld.pid
|
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 8388608
|
| 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 | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log_purge | ON
|
| relay_log_space_limit | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| server_id | 0
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_net_timeout | 3600
|
| slave_transaction_retries | 0
|
| slow_launch_time | 2
|
| socket | /var/run/mysqld/mysqld.sock
|
| sort_buffer_size | 2097144
|
| sql_mode |
|
| storage_engine | MyISAM
|
| sql_notes | ON
|
| sql_warnings | ON
|
| sync_binlog | 0
|
| sync_replication | 0
|
| sync_replication_slave_id | 0
|
| sync_replication_timeout | 0
|
| sync_frm | ON
|
| system_time_zone | CEST
|
| table_cache | 64
|
| table_type | MyISAM
|
| thread_cache_size | 0
|
| thread_stack | 131072
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| tmp_table_size | 33554432
|
| tmpdir | /tmp
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| version | 4.1.11-Debian_4sarge4-log
|
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24:
(April 1, 2005) |
| version_comment | Source distribution
|
| version_compile_machine | i386
|
| version_compile_os | pc-linux-gnu
|
| wait_timeout | 28800
|
+---------------------------------+----------------------------------------------------------+
196 rows in set (0.00 sec)
Christian Hammers wrote:
>Hello
>
>We have the problem that on one of our database server
>(Quad-Dualcore-Hyperthreading Xeon system with 8GB RAM) the database
>performance suddenly goes down and stays so for a while.
>
>There is no significant memory or CPU activity but the Load goes up to
>200 which indicates to me that there must be many mysql threads (hidden
>in the process list) which are calculated in the Load value but are
>effectively doing nothing as CPU/RAM stays.
>
>The mysqladmin processlist output looks suspicious, *lots* of queries in
>the opening+closing tables state which should, according to the docs,
>be happen in fractions of a second.
>
>There is no visible disc activety during that time so definetly no I/O
>bottleneck (8GB RAM is enough to hold all data).
>
>The system is running Debian stable, further infos below.
>
>Thanks in advance for any ideas!
>
>bye,
>
>-christian-
>
>
>
>db:~# mysqladmin status version
>Uptime: 898475 Threads: 16 Questions: 684283929 Slow queries: 7335
>Opens: 120224674 Flush tables: 11 Open tables: 64 Queries per second
>avg: 761.606
>mysqladmin Ver 8.41 Distrib 4.1.11, for pc-linux-gnu on i386
>Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
>This software comes with ABSOLUTELY NO WARRANTY. This is free software,
>and you are welcome to modify and redistribute it under the GPL license
>
>Server version 4.1.11-Debian_4sarge4-log
>Protocol version 10
>Connection Localhost via UNIX socket
>UNIX socket /var/run/mysqld/mysqld.sock
>Uptime: 10 days 9 hours 34 min 35 sec
>
>
>| 93702 | XXXXX | localhost:41097 | XXXXX | Query | 0
>| Opening tables | select mt_votinga0_.id as id1
>60_, mt_votinga0_.votingId as votingId160_, mt_votinga0_.answer as answ |
>| 93704 | XXXXX | localhost:40998 | XXXXX | Query | 3
>| Opening tables | SELECT SUM(e.count) AS votes FROM MT_Vote AS e
>WHERE e.answerId = 29 GROUP by e.answerId |
>| 115252 | XXXXX | localhost:33701 | XXXXX | Query | 0
>| Opening tables | select mt_voting0_.id as col_
>0_0_ from MT_Voting mt_voting0_ where mt_voting0_.id=36 and mt_voting0_ |
>| 306188 | XXXXX | localhost:39702 | XXXXX | Query | 0
>| Opening tables | select mt_voting0_.id as col_
>0_0_ from MT_Voting mt_voting0_ where mt_voting0_.id=36 and mt_voting0_ |
>| 306194 | XXXXX | localhost:43074 | XXXXX | Query | 0
>| Opening tables |
>...
>| 3216567 | XXXXX | web1.intern:40079 | XXXXX | Query | 1 |
>Opening tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>| 3216644 | XXXXX | web1.intern:40148 | XXXXX | Query | 1 |
>Opening tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>| 3216677 | XXXXX | web3.intern:45182 | XXXXX | Query | 1 |
>Opening tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>| 3216694 | XXXXX | web3.intern:45191 | XXXXX | Query | 3 |
>closing tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>| 3216819 | XXXXX | web3.intern:45257 | XXXXX | Query | 4 |
>closing tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>| 3216830 | XXXXX | web3.intern:45263 | XXXXX | Query | 3 |
>closing tables | SELECT
> data.id,
> data.xms_created created,
> data.xms_modified modified,
> data.xms_l |
>
>
--
Dan Buettner
--
Christian Hammers WESTEND GmbH | Internet-Business-Provider
Technik CISCO Systems Partner - Authorized Reseller
Lütticher Straße 10 Tel 0241/701333-11
[EMAIL PROTECTED] D-52064 Aachen Fax 0241/911879
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
