Bug #46618 Key buffer usage 50%
Submitted: 8 Aug 2009 16:51 Modified: 10 Sep 2009 7:22
Reporter: Gianpiero Cicoira Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.0.77 OS:Microsoft Windows (2003)
Assigned to: CPU Architecture:Any
Tags: Key buffer usage, Key Efficiency

[8 Aug 2009 16:51] Gianpiero Cicoira
Description:
Good evening,
sorry for my english, it's very poor.
I have this problem, I use a Virtual Server with Windows 2003 and 2GB of Ram where 1 GB it's Free.
In the virtaul Machine Disk I/O is slow and I'd like load all Index in RAM. 
Mysql Administrator in the Key efficiency indicates: Key Buffer Usage 138,920,512s and Key Buffer 268,435,436s. 
Key buffer usage doesn't go over 138,920,512s even if I increasedKey buffer size to 384MB

The Db Data len is: 643MB and Index Len 237.4MB of which 190 MB for Fulltext Index.

What is the problem? Can I load entire Index in RAM?

Best regards

How to repeat:
SHOW VARIABLES
"Variable_name","Value"
"auto_increment_increment","1"
"auto_increment_offset","1"
"automatic_sp_privileges","ON"
"back_log","50"
"basedir","C:\Program Files\MySQL\MySQL Server 5.0\"
"binlog_cache_size","32768"
"bulk_insert_buffer_size","33554432"
"character_set_client","utf8"
"character_set_connection","utf8"
"character_set_database","latin1"
"character_set_filesystem","binary"
"character_set_results","utf8"
"character_set_server","latin1"
"character_set_system","utf8"
"character_sets_dir","C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\"
"collation_connection","utf8_general_ci"
"collation_database","latin1_swedish_ci"
"collation_server","latin1_swedish_ci"
"completion_type","0"
"concurrent_insert","0"
"connect_timeout","30"
"datadir","C:\Program Files\MySQL\MySQL Server 5.0\Data\"
"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"
"keep_files_on_create","OFF"
"engine_condition_pushdown","OFF"
"expire_logs_days","0"
"flush","OFF"
"flush_time","1800"
"ft_boolean_syntax","+ -><()~*:""""&|"
"ft_max_word_len","84"
"ft_min_word_len","3"
"ft_query_expansion_limit","20"
"ft_stopword_file","C://Program Files//MySQL//MySQL Server 5.0//stopwords.txt"
"group_concat_max_len","1024"
"have_archive","YES"
"have_bdb","NO"
"have_blackhole_engine","YES"
"have_compress","YES"
"have_crypt","NO"
"have_csv","NO"
"have_dynamic_loading","YES"
"have_example_engine","NO"
"have_federated_engine","DISABLED"
"have_geometry","YES"
"have_innodb","DISABLED"
"have_isam","NO"
"have_merge_engine","YES"
"have_ndbcluster","NO"
"have_openssl","DISABLED"
"have_ssl","DISABLED"
"have_query_cache","YES"
"have_raid","NO"
"have_rtree_keys","YES"
"have_symlink","YES"
"hostname","WIN4834"
"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",""
"innodb_data_home_dir",""
"innodb_adaptive_hash_index","ON"
"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_rollback_on_timeout","OFF"
"innodb_support_xa","ON"
"innodb_sync_spin_loops","20"
"innodb_table_locks","ON"
"innodb_thread_concurrency","8"
"innodb_thread_sleep_delay","10000"
"interactive_timeout","28800"
"join_buffer_size","131072"
"key_buffer_size","268435456"
"key_cache_age_threshold","300"
"key_cache_block_size","1024"
"key_cache_division_limit","100"
"language","C:\Program Files\MySQL\MySQL Server 5.0\share\english\"
"large_files_support","ON"
"large_page_size","0"
"large_pages","OFF"
"lc_time_names","en_US"
"license","GPL"
"local_infile","ON"
"log","OFF"
"log_bin","OFF"
"log_bin_trust_function_creators","OFF"
"log_error","C:\Program Files\MySQL\MySQL Server 5.0\Data\WIN4834.err"
"log_queries_not_using_indexes","ON"
"log_slave_updates","OFF"
"log_slow_queries","ON"
"log_warnings","1"
"long_query_time","10"
"low_priority_updates","OFF"
"lower_case_file_system","ON"
"lower_case_table_names","1"
"max_allowed_packet","33554432"
"max_binlog_cache_size","4294963200"
"max_binlog_size","1073741824"
"max_connect_errors","10"
"max_connections","200"
"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_prepared_stmt_count","16382"
"max_relay_log_size","0"
"max_seeks_for_key","4294967295"
"max_sort_length","1024"
"max_sp_recursion_depth","0"
"max_tmp_tables","32"
"max_user_connections","0"
"max_write_lock_count","4294967295"
"multi_range_count","256"
"myisam_data_pointer_size","6"
"myisam_max_sort_file_size","137438953472"
"myisam_recover_options","OFF"
"myisam_repair_threads","1"
"myisam_sort_buffer_size","67108864"
"myisam_stats_method","nulls_unequal"
"named_pipe","OFF"
"net_buffer_length","16384"
"net_read_timeout","30"
"net_retry_count","10"
"net_write_timeout","60"
"new","OFF"
"old_passwords","OFF"
"open_files_limit","1000"
"optimizer_prune_level","1"
"optimizer_search_depth","62"
"pid_file","C:\Program Files\MySQL\MySQL Server 5.0\Data\WIN4834.pid"
"plugin_dir",""
"port","3307"
"preload_buffer_size","32768"
"profiling","OFF"
"profiling_history_size","15"
"protocol_version","10"
"query_alloc_block_size","8192"
"query_cache_limit","1048576"
"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","4096"
"read_buffer_size","2097152"
"read_only","OFF"
"read_rnd_buffer_size","8388608"
"relay_log",""
"relay_log_index",""
"relay_log_info_file","relay-log.info"
"relay_log_purge","ON"
"relay_log_space_limit","0"
"rpl_recovery_rank","0"
"secure_auth","OFF"
"secure_file_priv",""
"shared_memory","OFF"
"shared_memory_base_name","MYSQL"
"server_id","0"
"skip_external_locking","ON"
"skip_networking","OFF"
"skip_show_database","OFF"
"slave_compressed_protocol","OFF"
"slave_load_tmpdir","C:\WINDOWS\TEMP\"
"slave_net_timeout","3600"
"slave_skip_errors","OFF"
"slave_transaction_retries","10"
"slow_launch_time","2"
"sort_buffer_size","33554432"
"sql_big_selects","ON"
"sql_mode",""
"sql_notes","ON"
"sql_warnings","OFF"
"ssl_ca",""
"ssl_capath",""
"ssl_cert",""
"ssl_cipher",""
"ssl_key",""
"storage_engine","MyISAM"
"sync_binlog","0"
"sync_frm","ON"
"system_time_zone","W. Europe Daylight Time"
"table_cache","256"
"table_lock_wait_timeout","50"
"table_type","MyISAM"
"thread_cache_size","16"
"thread_stack","262144"
"time_format","%H:%i:%s"
"time_zone","SYSTEM"
"timed_mutexes","OFF"
"tmp_table_size","33554432"
"tmpdir","C:\WINDOWS\TEMP\"
"transaction_alloc_block_size","8192"
"transaction_prealloc_size","4096"
"tx_isolation","REPEATABLE-READ"
"updatable_views_with_limit","YES"
"version","5.0.77-community-nt-log"
"version_comment","MySQL Community Edition (GPL)"
"version_compile_machine","unknown"
"version_compile_os","Win64"
"wait_timeout","28800"

[mysql]
default-character-set=latin1

[mysqld]
port=3307
skip-locking
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=myisam
mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=200
query_cache_size=32M
table_cache=256
tmp_table_size=32M
thread_cache_size=16
myisam_sort_buffer_size=64M
key_buffer_size=256M
sort_buffer_size=32M
read_buffer_size=2M
read_rnd_buffer_size=8M
bulk_insert_buffer_size=32M
ft_min_word_len=3
connect_timeout=30
wait_timeout=6000
skip-federated
max_allowed_packet=32M
thread_concurrency=16
query_cache_type=1
[10 Aug 2009 7:22] Sveta Smirnova
Thank you for the report.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

This key_buffer_usage graph in MySQL Administrator could show not accurate value in bytes, because there is no status variable which shows Index usage in bytes. Please examine status variables Key_blocks_unused, Key_blocks_used,  Key_read_requests , Key_reads, Key_write_requests, Key_writes as described at http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size and http://dev.mysql.com/doc/refman/5.1/en/myisam-key-cache.html, then provide repeatable test case showing tables you use and how you load index in cache.
[10 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".