Re: Memory usage increasing constantly
Ok sorry for my late reply, I took some time to troubleshoot and test. This is so much time consuming...
So the good news are that in the meanwhile I have isolated the problem with a single query.
That is, the application invokes around 500 of the following queries:
SELECT Pt.* FROM Pt WHERE (CId=1199) AND (StartTime BETWEEN <t1> AND <t2>) ORDER BY StartTime DESC LIMIT 0,1;
I don't know if this is relevant, but some (around 60) of these queries may be executed in parallel threads, hitting on different partitions (each parallel query hits on a single partition).
When I give these queries, the RAM jumps up of 3 GB.
Then I give the command: "FLUSH TABLES" and 3 GB are released.
Note: this 3 GB are added to the innodb_buffer pool: the system in a steady state runs with 3.9 GB of RAM, with one run of these queries it goes to 6.8 GB.
If I continue to give some more queries of that kind, the RAM keeps increasing at a slower pace (and is never released until I give FLUSH TABLES) until mysql goes out-of-memory.
Now, the question are:
- is it expected that all this RAM is allocated? Maybe I'm wrong, but it looks like all my big table get cached in memory, exceeding innodb_buffer_pool.
- if the anwer is yes, how can I avoid that?
- how can I force MySQL to free the RAM when the query is done (apart from manual FLUSH)?
Below the answers to your previous posts (in case they still apply to my scenario).
---------------
indexes: I agree that some indexes may speedup a little bit, but I'd like to avoid adding more indexes on such a big table.
Moreover from what I see using EXPLAIN PARTITION I always go on a single/few partitions and the indexes look efficient enough.
Partitioning: yes I did some testing, and my current schema (which is legal - I didn't find any documentation that discourages using 2000 partitions) appeared to be the better tradeoff between performances and maintenance costs. Also, I have tested with few partitions (like 200) and the system is slower (as I expected) and still I got the memory issue.
table_open_cache: it is exactly 2000
Monitor memory usage versus Threads_connected: well Threads_connected in my case is quite constant since I have connection pooling. As I wrote above, the issue appears e.g. on a single burst of parallel queries.
Decreasing thread_cache_size to 1 did not change the behavior.