ZABBIX Forums  
  #21  
Old 12-11-2008, 10:20
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

* How to reduce import time ?
More mem, faster disks. The autocommit and foreignkeycheck tricks are the most common way to speed things up (which you are already doing)

* Why export file is only 6 Go ?? (Source 20 Go and on destination 14 Go)
Indexes

* 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 ??
Hmm not really. It should stay that way. On table reation you can pass a charset (which mysqldump already should do) but you negated with --default-character-set=latin1. If you dump without this, mysqldump should dump the with the original charset.
Reply With Quote
  #22  
Old 12-11-2008, 10:39
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi,
Quote:
Originally Posted by xs- View Post
* How to reduce import time ?
More mem, faster disks.
  • 2 Go physical RAM (perhaps i can extend this...)
  • EMC Clariion FC 15K Disks (no more ...)


Quote:
Originally Posted by xs- View Post
Hmm not really. It should stay that way. On table reation you can pass a charset (which mysqldump already should do) but you negated with --default-character-set=latin1. If you dump without this, mysqldump should dump the with the original charset.
I' ve added "--default-character-set=latin1" cause of this problem of charset but it doesn't resolve it !!

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; 13-11-2008 at 12:33.
Reply With Quote
  #23  
Old 13-11-2008, 12:32
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi,

No idea for my charset problem ??

Regards
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________
Reply With Quote
  #24  
Old 05-03-2009, 10:13
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Unhappy

Hi,

I'm go migrate Zabbix 1.4.6 to 1.6.2 and "per_table_file";
so make mysql export and import; and i've this result:
  • Existing database : 26 Go / Export 1h20
  • Imported database : 20 Go /import 31h00

Ooops 31h .... it's too long; what's doing to reduce that ...

My export batchs:
Code:
export_struct=/db/mysql/export/zabbix_struct.dmp
export_data=/db/mysql/export/zabbix_data.dmp

# Export de la structure
display_msg "Exporting structure [$(date)]"
mysqldump --no-data --complete-insert zabbix > ${export_struct}
display_ok
display_msg "-> $(ls -lap ${export_struct})\n"
display_msg "End export [$(date)]\n"

# Export des data
display_msg "Data export [$(date)]"
mysqldump --no-autocommit=TRUE --default-character-set=latin1 -t zabbix > ${export_data} # ${tables_list} > ${export_data}
display_ok
display_msg "-> $(ls -lap ${export_data})\n"
display_msg "End  export [$(date)]\n"

My import script:
Code:
# Export des data
print "+ Begin Import [$(date)]"
(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
echo "SET SQL_LOG_BIN=0;"
cat $export_data
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
echo "SET SQL_LOG_BIN=1;"
) | mysql -u root -p zabbix
print "+ End l'import [$(date)]"
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; 05-03-2009 at 10:20.
Reply With Quote
  #25  
Old 05-03-2009, 10: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

Well, for starters, these should give some performance increase

innodb_flush_method = O_DIRECT # only use for local disks, not SAN
innodb_buffer_pool_size = # (80% of total mem)
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0

But what kind of storage hardware are you using? Local disk + raid card, NAS, SAN, DAS?
If DAS or local disks + raid card, try playing around with the read ahead and write policy.
Reply With Quote
  #26  
Old 05-03-2009, 10:38
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Hi,

I'm Using EMC2 Clariion SAN with FC Disks ...

so i can add this options; only for import ??
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0

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

Last edited by pierre-hoffmann; 05-03-2009 at 10:50.
Reply With Quote
  #27  
Old 05-03-2009, 11:22
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

Well, is you are using FC SAN storage, you should be looking at optimizing the system itself, doesn't sound like mysql is the bottleneck.
There's plenty of information on how to optimize disk access via FC (plus which innodb_flush_method is best).

Dont know how to set those parameters for dump only, but imo you can use these in general for a zabbix db. Turning these off (worst case) would cause you to lose some transactions after crash. No biggie for measurement data.
Reply With Quote
  #28  
Old 05-03-2009, 16:42
pierre-hoffmann pierre-hoffmann is offline
Senior Member
 
Join Date: Jan 2008
Location: France
Posts: 133
Default

Ooops,

I think i've found the probleme, import was made on test machine (not true server); and on it i've default value for "innodb_buffer_pool_size" ...

I've se in process_list ; an insert take about 9/11 seconds ....
and with good value of innodb_buffer_pool size an import take about 1 second


... import in progress

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

Yes it work : only 3h40 for importing 20 Go
I go migrate to zabbix 1.6.2 and new Linux SLES version.

Thanks for your help !

Regards,
Pierre.
__________________
P.Hoffmann
System & Network Admin.
__________________________
Zabbix version 1.8.1
Hosts monitored 1300
OS Novell SLES 10 SP2
__________________________
Reply With Quote
  #30  
Old 14-08-2009, 23:55
alj alj is offline
Senior Member
 
Join Date: Aug 2006
Posts: 188
Default

Quote:
Originally Posted by pierre-hoffmann View Post
Hi,


-> I'm affraid by
Code:
[!!] Key buffer hit rate: -1224.5%
But don't know why ...
Its because key buffer is not being used for innodb (only for myisam)
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 19:23.