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]
