I have one giga for my key buffer. I am only adding data to a table and it takes around 2 seconds only to insert this new values. The ratio key_write_request/key_write is too high and the mysqladministrators shows I am 100 % all the time. I dont know what else to do, I already updated the key buffer size, the tables open are 0 since I am have a big space for the cache.


I have a script running to fill a table. The script I am runnnig insert 40 rows in a matter of 0.5 secs. after a while, the timing passed to to 2 secs and now when I runned a parallel script it becames 4 secs. The table was increasing in size and started to slow down. I have to fill 67420 times this 40 rows and it seem instead of 6 hours now I have to wait more thatn 24.

I am not sure if is it a problem with my small table MyISAM (which has 6 keys and 18 cols) and growed to aroung 100M and the index size is 27M. Still I think I should have a problem in the configuration, since the hitrate shouldnt be so high. I dont know what to do.

Can someone give me hint?





skip-locking

#max_allowed_packet = 100M
#If this value is too big, no tables are opened.
table_cache=512


#MySQL memory =
#key_buffer +
#max_connections * (join_buffer + record_buffer
# + sort_buffer + thread_stack + tmp_table_size
key_buffer = 1G
sort_buffer_size = 1G
#max_connections=20

#######################
net_buffer_length = 50M
read_buffer_size = 200M
read_rnd_buffer_size = 500M
thread_concurrency = 50


# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
#tmp_table_size=500M

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=1G

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=512M

#used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=512M

thread_cache = 512
query_cache_limit = 1G
query_cache_type = 1
query_cache_size = 1G

#myisam_sort_buffer_size = 128M
log_error="mysql_error.log"



# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
# commented in by lampp security
#skip-networking
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
# log-bin deactivated by default since XAMPP 1.4.11
#log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 1G
sort_buffer_size = 1GM
read_buffer = 512M
write_buffer = 512M

[myisamchk]
key_buffer = 1G
sort_buffer_size = 1G
read_buffer = 512M
write_buffer = 512M

[mysqlhotcopy]
interactive-timeout