ZABBIX Forums  
  #11  
Old 26-06-2008, 11:32
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi, I'v modify my "/etc/my.cnf" to be under 2 Go (size of my physical RAM)
Code:
[--] Total buffers: 11.2M per thread and 907.0M global
[!!] Maximum possible memory usage: 2.0G (100% of installed RAM)
Code:
max_allowed_packet      = 16M   # 1Mo 06/06/2008
table_cache             = 512
thread_cache_size       = 8
query_cache_size        = 100M  # 32 Mo 06/06/2008 / 256Mo 25/06/2008
query_cache_limit       = 512M  # 16Mo 24/06/2008 / 128 Mo 25/06/2008
thread_concurrency      = 8     # Try number of CPU's*2
max_heap_table_size     = 100M  # 16 Mo 20/06/2008
tmp_table_size          = 100M  # 34 Mo / 100 Mo

innodb_buffer_pool_size = 700M  # 1 Go 25/06/2008 / 70-80% of memory
innodb_log_buffer_size  = 2M    # 1M 25/06/2008

# === Per connection parameters ====================================
read_buffer_size        = 2M
read_rnd_buffer_size    = 2M    # 8M 25/06/2008
sort_buffer_size        = 2M    # 25/06/2008
thread_stack            = 256K  # 64K 25/06/2008 / 1Mo 26/06/2008
join_buffer_size        = 5M    # 2Mo 25/06/2008 / 128 Ko 20/06/2008
# -----------------------------------------------------------------

# === MyISAM parameters ===========================================
myisam_sort_buffer_size = 1M    # 8Mo 25/06/2008
key_buffer_size         = 4M    # 2Mo 25/06/2008
bulk_insert_buffer_size = 1M    # 8Mo 26/06/2008
# -----------------------------------------------------------------
But i'v allways this warnings:

Code:
[!!] Key buffer hit rate: -1779.9%
[!!] Query cache efficiency: 14.6%
[!!] Joins performed without indexes: 756
[!!] Temporary tables created on disk: 49%
[...]
    query_cache_limit (> 512M, or use smaller result sets)
    join_buffer_size (> 5.0M, or always use indexes with joins)
    tmp_table_size (> 100M)
    max_heap_table_size (> 99M)
    innodb_buffer_pool_size (>= 17G)
Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________

Last edited by pierre-hoffmann; 07-07-2008 at 17:22.
Reply With Quote
  #12  
Old 26-06-2008, 12:04
xs- xs- is offline
Senior Member
Zabbix certified specialist
 
Join Date: Dec 2007
Location: Netherlands
Posts: 391
Send a message via MSN to xs-
Default

The output of the tool is not holy, its just an aid which indicated which configuration items could produce a performance gain.
Reply With Quote
  #13  
Old 09-07-2008, 09:43
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi,

Another related question :

How often "HouseKeeper" run,
is there a method to run it manualy ??

(i'm changing a lots of history and trends values / to reduce database size)

Regards,
Pierre..
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________
Reply With Quote
  #14  
Old 09-07-2008, 10:40
xs- xs- is offline
Senior Member
Zabbix certified specialist
 
Join Date: Dec 2007
Location: Netherlands
Posts: 391
Send a message via MSN to xs-
Default

if you havent turned it off (it's on by default), every hour.

--------------------------8<-----------------------------
# How often ZABBIX will perform housekeeping procedure (in hours)
HousekeepingFrequency=1

# Uncomment this line to disable housekeeping procedure
#DisableHousekeeping=1
--------------------------8<-----------------------------
Reply With Quote
  #15  
Old 09-07-2008, 10:59
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Yes, you're right :-)

In my zabbix_server.conf: (so i use 1 hour default value)
Code:
# How often ZABBIX will perform housekeeping procedure
# (in hours)
# Default value is 1 hour
# Housekeeping is removing unnecessary information from
# tables history, alert, and alarms
# This parameter must be between 1 and 24

#HousekeepingFrequency=1
And in zabbix_server.log:
Code:
# cat zabbix_server.log | grep housekeeper
 14673:20080709:045803 Executing housekeeper
 14673:20080709:060121 Executing housekeeper
 14673:20080709:070430 Executing housekeeper
 14673:20080709:080808 Executing housekeeper
So i hope my dabase size decrease

For this moment it's better : Mysql tablespace doesn't grow anymore but Innodb Free size reduce ...




Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________

Last edited by pierre-hoffmann; 09-07-2008 at 12:15.
Reply With Quote
  #16  
Old 09-07-2008, 12:15
xs- xs- is offline
Senior Member
Zabbix certified specialist
 
Join Date: Dec 2007
Location: Netherlands
Posts: 391
Send a message via MSN to xs-
Default

Are you using the innodb_file_per_table option?

The innodb space will always grow, because it doesn't clean / re-use free-space areas within the innodb files very well (if at all).

Now Mysql has a the option to 'optimize tables' which, among other things, shrinks the innodb files (but only if you are using the innodb_file_per_table). It will not shrink the default 1/big innodb file.

PS
Keep in mind that you can't just 'turn on innodb_file_per_table'. You need to dump everything, delete all mysql files, enable the option and restore the dumps.
Reply With Quote
  #17  
Old 09-07-2008, 12:36
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Quote:
Originally Posted by xs- View Post
PS
Keep in mind that you can't just 'turn on innodb_file_per_table'. You need to dump everything, delete all mysql files, enable the option and restore the dumps.
Yes it's a big operation for 18Go of data mySql database so i don't make it for this moment.
But i've don't understand that, with innoDb per file Table option enable, the OPTIMIZE shrink the datafile ... it's Great news for me !

Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________
Reply With Quote
  #18  
Old 09-07-2008, 12:39
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Oups another problem:

today, i've try to clean my database with make an OPTIMIZE table,
and i've this error; do you know it ?

Code:
mysql> optimize table history_uint;
+---------------------+----------+----------+------------------------------------+
| Table               | Op       | Msg_type | Msg_text                           |
+---------------------+----------+----------+------------------------------------+
| zabbix.history_uint | optimize | error    | The table '#sql-194f_f2a9' is full |
| zabbix.history_uint | optimize | status   | Operation failed                   |
+---------------------+----------+----------+------------------------------------+
2 rows in set, 1 warning (1 hour 7 min 38.16 sec)
[EDIT]
OPTIMIZE have create an TMP table (copy of history_uint) that has auto-extend ibdata1 tablespace to full filesystem and Operation failed ...
Code:
080709 11:34:01  InnoDB: Error:
   Write to file /db/mysql/ibdata1 failed at offset 9 563085312.
InnoDB: 1048576 bytes should have been written, only 942080 were written.
InnoDB: Operating system error number 0.
Code:
/dev/mapper/system-dblv
                      40151648  38520196         0 100% /db
So i've now a 40 Go datafile ... not better

Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________

Last edited by pierre-hoffmann; 09-07-2008 at 15:17.
Reply With Quote
  #19  
Old 09-07-2008, 14:21
xs- xs- is offline
Senior Member
Zabbix certified specialist
 
Join Date: Dec 2007
Location: Netherlands
Posts: 391
Send a message via MSN to xs-
Default

yeah you dont want to do that with one big ibdata file.
Reply With Quote
  #20  
Old 12-11-2008, 09:56
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi,

it's the day; i want migrate Zabbix 1.4.6 to 1.6.1; so i want migrate my database too. (for instant it's only a test)

Now with all optimizations i've make; database size was stable at 20 Gb.

So, i've make export (about 40 minutes)
Code:
mysqldump --extended-insert --quick --single-transaction
 --default-character-set=latin1 -t zabbix > ${export_data}
i've make import (about 21 hours and 45 minutes)
Code:
(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat $export_data
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql -u root -p zabbix
Now i've some question:
  • How to reduce import time ?
  • Why export file is only 6 Go ?? (Source 20 Go and on destination 14 Go)
  • My database is in "latin1_swedish_ci" character set so when import i lose all french specific chars (éèà ...);
    solution is to force utf-8 import or something else ??

Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________

Last edited by pierre-hoffmann; 12-11-2008 at 10:01.
Reply With Quote
Reply

Tags
fsfgsfgsgf

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 14:57.