how to reduce mysql overhead?

Question

My machine has 2GB RAM, I have some cron job run every minute insert into some data.

My mysql always overhead. I think it is very frequently, if some cron job mysql insert run, 5-10 minutes, the db will become overhead. 12 hours later, the overhead data will increase into 1-2MB.

how to reduce mysql overhead? this is my my.cnf

key_buffer_size = 256M
key_buffer = 128M
back_log = 3000
max_allowed_packet = 4M
table_cache = 2048
table_open_cache = 512
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
thread_cache = 512
query_cache_size = 384M
set-variable =wait_timeout=60
thread_concurrency = 4
log-slow-queries = slow.log
long_query_time = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 384M
ft_min_word_len=3
# log-bin=mysql-bin

here is some print in phpmyadmin.

Table    Action  Records  Type    Collation    Size       Overhead
table_nam        101,399  MyISAM  utf8_unicode_ci  112.7 MiB  1.2 MiB

Answer 1

You use mytop and mysqltuner to analyze using mysql resources (RAM and CPU).

Too enable the option to log slow queries:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

And check out this post about ntpd service:

http://stackoverflow.com/questions/1282232/mysql-high-cpu-usage

I have a dedicated server for a high rate of transactions:

max_allowed_packet=16M
key_buffer_size=8M
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=512M
join_buffer_size=40M
table_open_cache=1024
query_cache_size=40M
table_definition_cache=256
innodb_additional_mem_pool_size=10M
key_buffer_size=16M
max_allowed_packet=32M
max_connections = 300
query_cache_limit = 10M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

Regards.

Answer 2

You can eliminate the overhead by frequently running OPTIMIZE TABLE on the table in question. I don't think you've got enough overhead to worry about; it's right around 1%, and shouldn't be giving you much trouble. Are you finding that a particular query is running slowly, or that you can't execute something as fast as you'd like to? If you can show the query that you're having trouble with, then you should post that query and describe the symptoms (as well as the table itself and the pattern of inserts you're doing).

I note that you're using MyISAM. Have you considered using InnoDB instead? InnoDB has several advantages over MyISAM, and you might find it more appropriate for your application.