Please do report back and let us know how you fare.
Dan Christian Hammers wrote:
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 over120 million operations (Opens: 120224674) to open tables in 10 days of uptime, so yourtable_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 versionUptime: 898475 Threads: 16 Questions: 684283929 Slow queries: 7335 Opens: 120224674 Flush tables: 11 Open tables: 64 Queries per second avg: 761.606mysqladmin 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 id160_, 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 | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l || 3216644 | XXXXX | web1.intern:40148 | XXXXX | Query | 1 | Opening tables | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l || 3216677 | XXXXX | web3.intern:45182 | XXXXX | Query | 1 | Opening tables | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l || 3216694 | XXXXX | web3.intern:45191 | XXXXX | Query | 3 | closing tables | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l || 3216819 | XXXXX | web3.intern:45257 | XXXXX | Query | 4 | closing tables | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l || 3216830 | XXXXX | web3.intern:45263 | XXXXX | Query | 3 | closing tables | SELECTdata.id, data.xms_created created, data.xms_modified modified, data.xms_l |-- Dan Buettner
-- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
