Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
@Mat, the overhead is showed in phpmyadmin –  fish man Dec 8 '12 at 13:07
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.