Speeding Up MySQLDUMP Imports

I quite often need to import large dumps of data quirky. Using these command will speed up the dump but can cause some problems


WARNING THIS COMMANDS ARE DANGEROUS – They will break replication and cause problems it used incorrectly.

set global innodb_flush_log_at_trx_commit = 2;
SET SQL_LOG_BIN = 0;
SET @@foreign_key_checks = 0;

Advertisements
Speeding Up MySQLDUMP Imports

Getting Query Cache Stats

select
hits.variable_value as ‘QueryC Hits’,
csel.variable_value as ComSelect,
ins.variable_value as ‘QueryC Inserts’,
p.variable_value as ‘QueryC Prunes’,
round(hits.variable_value / (hits.variable_value + csel.variable_value),3) as ‘Hit Rate’,
round(ins.variable_value / (hits.variable_value +  csel.variable_value),3) as ‘Insert Rate’,
round( p.variable_value /ins.variable_value,3) as ‘Prune Rate’
from information_schema.global_status hits,
information_schema.global_status csel,
information_schema.global_status ins,
information_schema.GLOBAL_STATUS p
where hits.variable_name = ‘QCACHE_HITS’
and   csel.variable_name = ‘Com_Select’
and   ins.variable_name = ‘QCACHE_INSERTS’
and   p.VARIABLE_NAME = ‘Qcache_lowmem_prunes’;

Getting Query Cache Stats