hi, Taiyo >| innodb_buffer_pool_size | 8388608 >| key_buffer_size | 8388600
Try to increase the innodb_buffer_pool_size if you use the innodb storage or key_buffer_size if MyISAM storage is used or both. You can get more information about innodb_buffer_pool_size and key_buffer_size from this: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards, Leo Huang 2006/2/28, Taiyo <[EMAIL PROTECTED]>: > Greetings, > > We are running a server and the CPU is at %99.99 at all times, after about > 2-3 hours of processing queries just hang, sounds like our hardware is weak > but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS. > > I was hoping someone could look at our settings and would help us analyze > this issue: > > Please advise. > > Here are the stats: > > Some version information: > > mySQL version: 5.0.16-standard > Uname: Linux db.example.com > 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64 > GNU/Linux > RAM: 4GB > SWAP 1GB > HD: 2 SCSI 10k RPM on 2 separate > controllers. > > Some information about the load: > Queries per second avg: 16.346 (about) > Our biggest table is 3.5 million records and we index 3 of the columns for > fulltext search > We do a lot of join queries on 2 tables. > > Some mySQL variables: > [mysqld] > tmpdir=/db.example.com/tmp > query_cache_size=1048576 > query_cache_limit = 33554432 > query_cache_size = 33554432 > myisam_sort_buffer_size = 33554432 > sort_buffer_size = 33554432 > max_connections=500 > table_cache = 1000 > max_tmp_tables = 256 > > Here is all of my mysql -e 'SHOW VARIABLES': > +---------------------------------+----------------------------------------- > -+ > | Variable_name | Value > | > +---------------------------------+----------------------------------------- > -+ > | auto_increment_increment | 1 > | > | auto_increment_offset | 1 > | > | automatic_sp_privileges | ON > | > | back_log | 50 > | > | basedir | / > | > | 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 > | > | completion_type | 0 > | > | concurrent_insert | 1 > | > | 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 > | > | div_precision_increment | 4 > | > | engine_condition_pushdown | OFF > | > | expire_logs_days | 0 > | > | flush | OFF > | > | flush_time | 0 > | > | ft_boolean_syntax | + -><()~*:""&| > | > | ft_max_word_len | 84 > | > | ft_min_word_len | 2 > | > | ft_query_expansion_limit | 20 > | > | ft_stopword_file | (built-in) > | > | group_concat_max_len | 1024 > | > | have_archive | YES > | > | have_bdb | NO > | > | have_blackhole_engine | NO > | > | have_compress | YES > | > | have_crypt | YES > | > | have_csv | NO > | > | have_example_engine | NO > | > | have_federated_engine | NO > | > | have_geometry | YES > | > | have_innodb | YES > | > | have_isam | NO > | > | have_ndbcluster | NO > | > | have_openssl | NO > | > | have_query_cache | YES > | > | have_raid | NO > | > | have_rtree_keys | YES > | > | have_symlink | YES > | > | init_connect | > | > | init_file | > | > | init_slave | > | > | innodb_additional_mem_pool_size | 1048576 > | > | innodb_autoextend_increment | 8 > | > | innodb_buffer_pool_awe_mem_mb | 0 > | > | innodb_buffer_pool_size | 8388608 > | > | innodb_checksums | ON > | > | innodb_commit_concurrency | 0 > | > | innodb_concurrency_tickets | 500 > | > | innodb_data_file_path | ibdata1:10M:autoextend > | > | innodb_data_home_dir | > | > | innodb_doublewrite | ON > | > | innodb_fast_shutdown | 1 > | > | 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 | 1048576 > | > | innodb_log_file_size | 5242880 > | > | innodb_log_files_in_group | 2 > | > | innodb_log_group_home_dir | ./ > | > | innodb_max_dirty_pages_pct | 90 > | > | innodb_max_purge_lag | 0 > | > | innodb_mirrored_log_groups | 1 > | > | innodb_open_files | 300 > | > | innodb_support_xa | ON > | > | innodb_sync_spin_loops | 20 > | > | innodb_table_locks | ON > | > | innodb_thread_concurrency | 20 > | > | innodb_thread_sleep_delay | 10000 > | > | interactive_timeout | 28800 > | > | join_buffer_size | 131072 > | > | key_buffer_size | 8388600 > | > | key_cache_age_threshold | 300 > | > | key_cache_block_size | 1024 > | > | key_cache_division_limit | 100 > | > | language | /usr/share/mysql/english/ > | > | large_files_support | ON > | > | large_page_size | 0 > | > | large_pages | OFF > | > | license | GPL > | > | local_infile | ON > | > | locked_in_memory | OFF > | > | log | OFF > | > | log_bin | OFF > | > | log_bin_trust_function_creators | OFF > | > | log_error | > | > | log_slave_updates | OFF > | > | log_slow_queries | 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 | 1048576 > | > | max_binlog_cache_size | 18446744073709551615 > | > | max_binlog_size | 1073741824 > | > | max_connect_errors | 10 > | > | max_connections | 500 > | > | max_delayed_threads | 20 > | > | max_error_count | 64 > | > | max_heap_table_size | 16777216 > | > | max_insert_delayed_threads | 20 > | > | max_join_size | 18446744073709551615 > | > | max_length_for_sort_data | 1024 > | > | max_relay_log_size | 0 > | > | max_seeks_for_key | 18446744073709551615 > | > | max_sort_length | 1024 > | > | max_tmp_tables | 256 > | > | max_user_connections | 0 > | > | max_write_lock_count | 18446744073709551615 > | > | multi_range_count | 256 > | > | myisam_data_pointer_size | 6 > | > | myisam_max_sort_file_size | 9223372036854775807 > | > | myisam_recover_options | OFF > | > | myisam_repair_threads | 1 > | > | myisam_sort_buffer_size | 33554432 > | > | myisam_stats_method | nulls_unequal > | > | net_buffer_length | 16384 > | > | net_read_timeout | 30 > | > | net_retry_count | 10 > | > | net_write_timeout | 60 > | > | new | OFF > | > | old_passwords | OFF > | > | open_files_limit | 2510 > | > | optimizer_prune_level | 1 > | > | optimizer_search_depth | 62 > | > | pid_file | /var/lib/mysql/db.example.com.pid | > | port | 3306 > | > | preload_buffer_size | 32768 > | > | protocol_version | 10 > | > | query_alloc_block_size | 8192 > | > | query_cache_limit | 33554432 > | > | query_cache_min_res_unit | 4096 > | > | query_cache_size | 33554432 > | > | 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_compressed_protocol | OFF > | > | slave_load_tmpdir | /db.example.com/tmp/ | > | slave_net_timeout | 3600 > | > | slave_skip_errors | OFF > | > | slave_transaction_retries | 10 > | > | slow_launch_time | 2 > | > | socket | /var/lib/mysql/mysql.sock > | > | sort_buffer_size | 33554424 > | > | sql_mode | > | > | sql_notes | ON > | > | sql_warnings | ON > | > | storage_engine | MyISAM > | > | sync_binlog | 0 > | > | sync_frm | ON > | > | sync_replication | 0 > | > | sync_replication_slave_id | 0 > | > | sync_replication_timeout | 10 > | > | system_time_zone | CST > | > | table_cache | 1000 > | > | table_lock_wait_timeout | 50 > | > | table_type | MyISAM > | > | thread_cache_size | 0 > | > | thread_stack | 196608 > | > | time_format | %H:%i:%s > | > | time_zone | SYSTEM > | > | timed_mutexes | OFF > | > | tmp_table_size | 33554432 > | > | tmpdir | /db.example.com/tmp | > | transaction_alloc_block_size | 8192 > | > | transaction_prealloc_size | 4096 > | > | tx_isolation | REPEATABLE-READ > | > | updatable_views_with_limit | YES > | > | version | 5.0.16-standard > | > | version_comment | MySQL Community Edition - Standard (GPL) > | > | version_compile_machine | x86_64 > | > | version_compile_os | unknown-linux-gnu > | > | wait_timeout | 28800 > | > +---------------------------------+----------------------------------------- > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
