gaps in graphs

[ProTON]

Does anyone have the problem with housekeeper and graphs? It seems that when housekeeper is executing, MySQL stops receiving data and graphs have ugly gaps every hour. This is definetely because of housekeeper because if I try to run it every 4 hours, the gaps in graphs also changes to every 4 hours.

Anyone know how to solve this problem?

[nelsonab]

Look into tuning your MySQL server if you have not already.

[ProTON]

What exactly needs to be tuned? I have already converted tables to InnoDB and installed more RAM into server.

[wax66]

What exactly needs to be tuned? I have already converted tables to InnoDB and installed more RAM into server.

Tune your my.cnf

For example, here's mine:


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Added by me
key_buffer = 256M
query_cache_size = 128M
query_cache_limit = 8M
table_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
tmp_table_size = 128M
max_heap_table_size = 32M
thread_concurrency = 16
#innodb_data_file_path = /var/lib/mysql/ibdata1:64M:autoextend
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


I ran into the same issue today because I'm finally adding a ton of hosts into this node which had been previously inactive, and realized I had done that tuning on only the other 2.

:-)

-Ron

[nelsonab]

Wax is right. The key parameters I've found useful in tuning are the Key buffer and read buffer for innodb. You can pretty much ignore most of the ISAM options. There are a few posts for MySQL tuning and there's a script available for tuning, I can't remember the name right this moment but it's been referenced a few times in several of the posts. Adding more RAM is good, but you also need to make sure MySQL knows how much of it it can use.

[ProTON]

Thank you both of you. Indead increasing key_buffer and sort_buffer size helped.