Using thread_statistics does not clear state when a thread goes away

[colin]

When running Percona Server 5.6.28-76.1 with thread_statistics=1 and userstat=1, I notice that memory constantly increases with each new connection (from basic calculations, ~400 bytes per connection).
In our case, applications recycle connections every 10 minutes or after N number of usages. Over time this will result in a large number of connections being used.

IMO the hash table (global_thread_stats) and the thread_statistics table should both remove the entry for a thread once the thread has died/gone away.

I reproduced this locally by continuously reconnecting a client and observed the RSS of mysqld.

[Laurynas Biveinis (laurynas-biveinis)]

There are use cases for historical data in THREAD_STATISTICS, i.e. not all the users would appreciate data for disconnected connections disappearing at the same moment. On the other hand, keeping everything results in unbounded memory growth and is not desirable neither as you found. We could introduce an option to keep 10000 or a different number of most recent entries, but I'm not sure the implementation is suitable for such limit at the moment.

Does regular FLUSH THREAD_STATISTICS work for you as a workaround?

[colin (coldeasy-hotmail)]

Based a quick local test doing a FLUSH THREAD_STATISTICS reduces the memory growth substantially though not entirely - I guess due to the in-memory hash table?

[Laurynas Biveinis (laurynas-biveinis)]

Each FLUSH destroys and re-creates the in-memory hash table. If you observe a memory use growth despite the periodic FLUSH, the root cause of that growth must be further researched.

Have you checked if there is no (small) continuous growth with your patch that clear thread data on disconnect?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3430