housekeeper processe al 100% y no finaliza correctamente

[flako]

Hello
I am confused with the issue of optimizing the housekeeper (I never went), seeing "Zabbix busy housekeeper Processes, in%" can be seen that each time it runs (each 5hs) is set at 100% until the query fails :

mysql> select housekeeperid,tablename,field,value from housekeeper order by tablename;
ERROR 3 (HY000): Error writing file '/tmp/mysqltmp/MYTLd1LH' (Errcode: 28)

from mysql.log can see a "120724 11:02:27 [ERROR] /usr/sbin/mysqld: Sort aborted" for each execution of housekeeper


The error "(Errcode: 28)" I think because you run out of space (the /tmp/mysqltmp/ has 600MB, defined as: "tmpfs /tmp/ tmpfs mysqltmp rw,uid=mysql,gid=mysql,size=600M, nr_inodes=10k, mode=0700 0 0")


At other times (when the DB was less than 15GB) I have tried to define /tmp/mysqltmp as a physical partition of 30GB, but the same query generates a log such that the partition was full and ends up missing the housekeeper.

I tried to follow the documentation of performance of mysql and apache and mysql and zabbix recommended, but I am confused with this so if you can guide me where I should focus, they are grateful.


my configuration is:

The Zabbix 1.8.7 running on SLES 10 SP1 with kernel 2.6.32.12-0.7-xen (in a virtual xen)

Status of Zabbix
Number of hosts (monitored/not monitored/templates)	      = 325	250 / 2 / 73
Number of items (monitored/disabled/not supported)	      = 18220	8640 / 9281 / 299
Number of triggers (enabled/disabled)[problem/unknown/ok] =	3689	2467 / 1222  [151 / 720 / 1596]
Required server performance, new values per second	      = 85.5



zabbixsvr:~ # free -m
             total       used       free     shared    buffers     cached
Mem:          2012       1693        319          0         10        352
-/+ buffers/cache:       1330        681
Swap:         2047          0       2046



du -h /var/lib/mysql/*
125M	/var/lib/mysql/ib_logfile0
125M	/var/lib/mysql/ib_logfile1
1.0G	/var/lib/mysql/ibdata1
63G	        /var/lib/mysql/zabbix

# du -h /var/lib/mysql/zabbix/*| grep G
40G	    /var/lib/mysql/zabbix/history.ibd
19G	    /var/lib/mysql/zabbix/history_uint.ibd
2.7G   /var/lib/mysql/zabbix/housekeeper.ibd


/etc/zabbix/zabbix_server.conf
  HousekeepingFrequency=5
  MaxHousekeeperDelete=500

  CacheSize=32M
  HistoryCacheSize=16M
  HistoryTextCacheSize=16M
  ListenPort=10051
  LogSlowQueries=1000
  PidFile=/tmp/zabbix_server.pid
  SenderFrequency=30
  StartDBSyncers=8
  StartDiscoverers=3
  StartIPMIPollers=1
  StartPollers=25
  StartPollersUnreachable=4
  StartTrappers=2
  Timeout=30
  TrendCacheSize=8M


/etc/my.cnf
[mysqld]
   skip-locking
   key_buffer = 16M
   max_allowed_packet = 1M
   table_cache = 256
   sort_buffer_size = 512K
   net_buffer_length = 8K
   read_buffer_size = 256K
   read_rnd_buffer_size = 512K
   myisam_sort_buffer_size = 8M
   transaction-isolation = REPEATABLE-READ
   skip-federated
   server-id	= 1
   tmpdir = /tmp/mysqltmp

   innodb_buffer_pool_size = 750M
   innodb_log_file_size = 125M
   innodb_log_buffer_size = 4M
   innodb_flush_log_at_trx_commit = 2

   innodb_file_per_table = 1
   innodb_data_file_path=ibdata1:1024M
   innodb_flush_method=O_DIRECT
   innodb_thread_concurrency=8

   thread_cache_size=4
   log_slow_queries=/var/log/mysql.slow.log

   query_cache_limit=1M
   query_cache_size=128M
   tmp_table_size=256M 
   max_heap_table_size=256M
   max_connections = 400
   join_buffer_size=256k


   [mysql]
   no-auto-rehash

   [isamchk]
   key_buffer = 20M
   sort_buffer_size = 20M
   read_buffer = 2M
   write_buffer = 2M

   [myisamchk]
   key_buffer = 20M
   sort_buffer_size = 20M
   read_buffer = 2M
   write_buffer = 2M

   [mysqlhotcopy]
   interactive-timeout

Thanks for reading so far down and sorry for using translate.

[flako]

processe housekeeper 100% and does not complete successfully

Hello,
I solved half creating an index
CREATE INDEX ON housekeeper_tablename housekeeper (tablename ASC);

now fails with the error, "[ERROR] / usr / sbin / mysqld: Sort aborted" , but I run the query kills the server (runs out of resources)

But that's for another post