I’m using Percona 5.5.16 on a server with > 160k tables spread throughout 1500 databases, which results in the data dictionary size continually increasing.
I have innodb_dict_size_limit set to 512MB:
root@use-shard1a [(none)]>show global variables like ‘innodb_dict%’;±-----------------------±----------+| Variable_name | Value |±-----------------------±----------+| innodb_dict_size_limit | 536870912 |±-----------------------±----------+1 row in set (0.00 sec)
However, the size displayed in show engine innodb status continues to exceed that number and the memory of the instance continues to grow:
----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 5515509760; in additional pool allocated 0Internal hash tables (constant factor + variable factor) Adaptive hash index 97144368 (84999608 + 12144760) Page hash 5313416 (buffer pool 0 only) Dictionary cache 930283019 (21251312 + 909031707) File system 493420352 (82672 + 493337680) Lock system 13282728 (13281976 + 752) Recovery system 0 (0 + 0)Dictionary memory allocated 909031707
I’m at a loss for why dictionary size continues to grow, despite the limit being set. What would cause this behavior?
I noticed something to that effect once I flushed tables and saw the dictionary drop and respect the limit. I have table cache set to 2000 & innodb_open_files set to 8000.
I’m still running into extreme memory growth, even without the dictionary size increasing. Buffer pool is set to 5G, dictionary size limit set to 1G, query cache off, and the data set is completely InnoDB (140k tables across 1500 databases w/ a number of those tables partitioned). It’s currently a slave, so there are no active connections aside from replication.
48 hours of uptime & top is reporting the Virt at 14G & Res at 13G on a 15G instance. That will grow until it consumes all physical memory & swap space. I’d like to be able to up the buffer_pool but I’m having to restart the instance 3 times a week as is just to control the memory usage.
This probably needs a hands-on diagnosis. You could go back and forth here forever: “what is the output of pmap -x?” “what is the output of…” If you are restarting 3x a week I’d suggest getting in touch with our sales people. We can probably diagnose this pretty quickly with access to the server.