> As soon as I open a database management tool (So far MySQL Workbench
> and Navicat) the database halts leaving behind logs about InnoDB data
> dictionary problems, and the like…
What are the exact error messages?
> I have tuned my.cnf with MyISAM and InnoDB buffers (4G each) and various other tunings…
What's important is how those settings relate to demand, load and resources.
There's stress, we need to find where, so please ...
(i) post the result of ...
select engine,data,indexes,total
from (
select
ifnull(engine,'TOTALS') as engine,
concat(data,' GB') as data,
concat(indexes,' GB') as indexes,
concat(tot,' GB') as total,
if(engine is null,-1,tot) as ord
from (
select
engine,
round( sum(data_length)/1024/1024/1024, 2 ) as data,
round( sum(index_length)/1024/1024/1024, 2 ) as indexes,
round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
from information_schema.tables
where engine is not null and engine not in('information_schema','performance_schema')
group by engine with rollup
) sums
) list
order by list.ord desc;
(ii) post the results of running these queries in the mysql client program ...
show variables;
show global status;
(iii) post the result of this OS cmd ...
free -m