There are many things that are wrong with your configuration and running status:
With your configuration, before running no query, you need 1.5 GB of static memory assignment- before the buffer pool is even used an no connection is performed. Most of it (1GB) goes to performance_schema, but there is still 500MB that are allocated (vs the 42k for the default configuration).
You also have quite some high number of temporary tables created (180 tables/s), half of them in memory -I would bet that the other 90 are created in memory, then on disk. That can account, in your setup, to up to 1.8 GB of temporary tables per second.
Your queries have a non-negligible amount of full joins and full table scans, reading randomly 26K rows per second and sorting 3K per second. In summary- you end up wasting lots of resources in read buffers, join buffers and sort buffers. And that is for 90 simultaneous connections. 90 is not a high number for MySQL, but you need more resources for the type of queries that you are executing- which examine lots of rows.
You say that you have 100K tables, which usually affects the InnoDB dictionary size (146M) and the
open_table_cache. You have increased some of the caches, but I do not agree that that is the right way to proceed.
Aside from that, you have lots of connections using SHOW STATUS commands, typical from an ORM/framework. Not worrying in 5.6, where metadata commands do not update the InnoDB statistics automatically, but from that I can derive that the queries you are executing are not the most optimal ones.
So, my recommendations:
- Turn down most of the memory-related, session-only or query-only variables (e.g. tmp_table_size)
- Turn down table-cache related variables
- Reduce the concurrency by setting up a pool of connections (or reconfigure the current one). This will allow you to turn down max_connections without affecting the application throughput.
These ones will increase latency, but that is needed in order to increase the concurrency. If you want more concurrency with the same memory resources you will have to split your tables on several servers (aka sharding). In general, there are many things that I do not like from your configuration file, but I am not bold enough to tell you to change them without evaluating them myself.
- Turn off performance_schema (it will save you 1GB of memory for your configured number of connections)
- Fix your queries and start using indexes. Or create the appropriate indexes. This is probably the first step.
- Avoid using MySQL- Without seeing your queries, something tells me that most of them could be easily cached at application or middleware layer. 5.6 even comes with a free memcached server!
- Watch for the rollbacks you are executing (1 was running at the time of the statistics gathering). Rollbacks are very expensive resource-wise (memory and CPU).
For more detailed information, I would need to perform a query audit.