Re: Open Table Cache (1024 limit vs. 100 mil. opened tables)

[Peter Brawley]

Usage needs to be analysed in relation to load & resources. What is the MySQL version? What is innodb_buffer_pool_size? How much RAM does the machine have, how much is used by other processes?

Then please ...

(i) post the result of this query ...

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 full results of running these queries in the mysql client program ...

show variables;

show global status;