MySQL Bugs: #68514: 5.6 huge memory usage with max

Bug #68514	5.6 huge memory usage with max_connections parameter
Submitted:	27 Feb 2013 23:34	Modified:	7 Apr 2015 22:26
Reporter:	Jijo Varghese	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.6.10 GA	OS:	Any (Centos 5)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	5.6, max_connections, Memory

Description:
Using max_connections in my.cnf hogs up GB of RAM

How to repeat:
Very easy to reproduce for comparison with mysql 5.5 

only 1 line in my.cnf
# cat /etc/my.cnf
[mysqld]
max_connections = 30000

# rpm -Uvh MySQL-server-5.5.30-1.rhel5.x86_64.rpm
# /etc/init.d/mysql start

# top -b -n1 | grep -w mysqld
18447 mysql     19   0  337m  41m 4136 S  0.0  0.3   0:00.10 mysqld

Now switch to mysql 5.6

# rpm -e MySQL-server
# rpm -Uvh MySQL-server-5.6.10-1.rhel5.x86_64.rpm
# /etc/init.d/mysql start

# top -b -n1 | grep -w mysqld
18681 mysql     19   0 5739m 5.3g 5744 S  0.0 35.1   0:07.28 mysqld

workaround add
performance_schema = 0

Explicitly setting a lower "performance_schema_max_thread_instances" cures the huge memory usage.

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_per...

That is not a bug.

The performance schema allocates memory once at server start-up based on
server sizing, and does not allocate memory later during the server
execution.

It might be surprising to see all the memory allocated at start-up,
but the memory consumption is fixed and will not grow with load,
since all is already accounted for.

About the amount of memory allocated itself, details can be found in:
  SHOW ENGINE PERFORMANCE_SCHEMA STATUS
which indicate how much memory is used for each internal buffer.

If the DBA wants to keep statistics for all possible instrumentation,
then yes, the performance schema needs memory for it.
That is expected.

If the DBA wants to keep statistics for only some type of instrumentation,
for example statements, but not for others, for example mutexes,
the overall memory consumption can be trimmed down, by not allocating
statistics buffer for things that are never used.

For example:

performance-schema-max-mutex-classes = 0
performance-schema-max-mutex-instances = 0

will avoid allocating memory for mutexes in general,
and also avoid allocating memory for mutex statistics by thread,
which grows linearly with max_connection, which can become expensive.

The same principles apply to all types of instrumentation.

Other memory buffers that grows with max_connection are the per thread
history for statements, stages and waits, these are also good candidate to 
trim when not needed.

The complete list of tuning parameters can be seen in:
  SHOW GLOBAL VARIABLES LIKE "performance_schema%";

Overall, the performance schema is:
- very transparent, providing a way to inspect memory consumption with SHOW
  ENGINE PERFORMANCE_SCHEMA STATUS,
- very predictable for capacity planning, with a fixed allocation,
- very controllable, with fine grained configuration parameters for each
  type of instrumentation (SHOW GLOBAL VARIABLES)

Regards,
-- Marc Alff.

Thank you for detailed explanation, Marc!

I've checked 5.6.10 on Linux started with --no-defaults, and got P_S.memory=52061696 (this is for max_connections=151, default there). Then I started it with additional --max-connections=251 and got max_connections=214 (why?) and P_S.memory=103829496. Simple calculations with MySQL shows:

mysql> select (103829496 - 52061696)/(214 - 151);
+------------------------------------+
| (103829496 - 52061696)/(214 - 151) |
+------------------------------------+
| 821711.1111 |
+------------------------------------+
1 row in set (0.08 sec)

that is, 800K of P_S-related memory per connection just after startup, without any history or non-default settings. Surely this is documented somewhere I assume. Any URL?

Hi Valeriy.

The heuristics used by the performance schema to estimate the size to allocate are just that: heuristics, used when the DBA provides no sizing at all.

The code is located in storage/perfschema/pfs_autosize.cc

The function itself is linear *by part*, and *not* continuous when going from one part to the next, so assuming you are interested in knowing the incremental memory cost per connection at scale, you need to measure with max_connection > 2 * 151, otherwise any math trying to second guess PFS will be just wrong.

Assuming the code operates in the only segment that matters (large_data), the incremental cost per increment in max_connection is around 176 Kb by my estimates (and this can be trimmed down, the real cost is half of that).

176 Kb per connection, not 800 Kb per connection ...

As for max_connection being lowered by the server, this is related to the limits on max open files, which are constraining max_connections.

folks, we did find that the performance schema status output wraps at 4G. Internal bug was filed and fixed yesterday :)

Isn't 176kb per increment too much? I think it is.

Jijo,

If you have max_connections set that high it probably means one of these things:

1. Configuration mistake, happens a lot.
2. You have connection pools on clients that are configured to open too many connections, grow them too fast and or close them too slowly.
3. You should be using a thread pool.
4. Both 2 and 3.

Peak server throughput because of non-PS things is unlikely to be much higher than with 64 connections simultaneously doing work. Above that it's likely to start dropping and by 1000 trying to do work it's likely to be well into severe throughput degradation. A thread pool is the primary way to limit this, combined perhaps with innodb_thread_concurrency. The per-connection overhead isn't just from PS, lots of idle connections slow down other parts of the server as well.

For the autosizing you really need to ensure that you do not set any of these values to values much higher than you will really use:

max_connections (uncommonly high is higher than 1500)
table_open_cache (uncommonly high is higher than 10000)
table_definition_cache (uncommonly high is higher than 10000)
open_files_limit (uncommonly high is higher than 30000)

Each of those is normally, not just with the performance schema, there as both a request to allocate more resources and a request to cap resource usage at this level, with only limited excursions (like the table cache size not being strictly enforced).

If you set any of those to uncommonly high values and want to use performance schema auto-sizing you might experiment with no more than the uncommonly high values in my.cnf and setting to the higher values in the server init file ( http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_init-file ).

At some point Marc might adjust the resource allocation per value to start decreasing above the uncommonly high values, or cap some calculations at the uncommonly high value I mentioned.

The reason such caps can be useful is that:

max_connections over 1500: the server throughput is likely to peak below 64 active connections, so any server that is using more than 1500 connections simultaneously is going to have its performance destroyed already. Higher values normally mean connection pools, not connections all doing work at the same time. Benchmarks would be a possible exception to this but defaults shouldn't really be tuned just for benchmarks.

table_open_cache, table_definition_cache: likely to be shared hosting or other large number of tables, low access rates per table situations. So some table-related counts will be higher but the rates overall won't be.

open_files_limit: really this is a proxy for the things it caps, not a value of its own. I wouldn't use this for PS autosizing except if it's low enough to force the other values to be limited, then would be best to work out how those will be capped and use the capped values rather than the raw ones.

Maybe Marc can find some way to tune based on the use case assumptions I've given. Some of them would probably mean that the growth rate per unit should start to slow down after a certain point, for some allocations. Queries per connection, say, would drop as connection count grows above the uncommonly high level, so there's limited value in having per-query allocations just continue to increase at the same rate rather than slowing down.

Why is this not a bug? On my x86-64 with 5.6.12 when performance_schema=1 then the extra RAM is: max_connections * 196kb. So that is ~4GB for max_connections=20000. I have many servers that use max_connections=5000 and I am unwilling to waste 1GB of RAM on the PS.

 We can't afford to waste memory and electricity.

James - I guess my values are uncommonly high. I suspect many other high profile customers also use values that are uncommonly high. Don't you want them to have the option for using the PS? They are the ones who will say nice things about features at conferences.

> max_connections (uncommonly high is higher than 1500)
> table_open_cache (uncommonly high is higher than 10000)
> table_definition_cache (uncommonly high is higher than 10000)
> open_files_limit (uncommonly high is higher than 30000)

Mark,

I agree with you. After it was ruled not a bug I advocated changing it anyway.

I think that it would be sensible to have caps on the calculated values for the reasons I gave, using the "uncommonly high" level as the cap for automatic allocation calculations. That would limit the RAM cost when it appears likely that automatic calculation isn't prudent. If you have any better thoughts on whether or when to set caps on the values used they would be interesting.

Alternatively, if you think that the connections don't really need as much RAM as implied by the calculation rules, maybe Marc could adjust that calculation based on your data for actual need.

I think that this is an area where we need to try to do better, if we can get better data on what's actually needed for the higher values. You saying what PS actually needs to do its job for your systems without adverse performance effects could be very useful in that adjusting.

James Day, MySQL Senior Principal Support Engineer, Oracle

A more detailed description of how PS decides how much memory to allocate is at bug #68287. To check how much memory it's using see bug #69665.

Hi Jijo & Mark,

This is technically not a bug, but an intentional part of the design (meant to limit/lessen transactional throughput and latency overhead, at the obvious cost of greater memory usage overhead). 

That being said, it is something that we're looking at addressing in upcoming work.

Due to these factors, I've moved this to a verified feature request.

Thank you for all of the helpful feedback!

Best Regards,

Matt

Design reviews must be interesting over there if using this much memory per connection was described in the review and accepted as OK.

Jijo,
The advice from James Day is wrong for many web-scale MySQL users. Disabling the PS is what I do. MariaDB, Percona and WebScale variants of MySQL have an alternative to the PS that is much more careful about consuming memory and CPU cycles -- user & table stats.

Jijo,
The advice from James Day is wrong for many web-scale MySQL users. Disabling the PS is what I do. MariaDB, Percona and WebScale variants of MySQL have an alternative to the PS that is much more careful about consuming memory and CPU cycles -- user & table stats.

Mark, yes, you're right that it's going to be wrong for quite a lot of web-scale users and not a small number of others as well.

Hopefully you'll like the 5.7 changes more. We'll see, once we have some code, not just words.

One observation that comes out of this is perhaps for Oracle to package their MySQL binaries in 2 forms, one with P_S enabled and one without it, and both packages configured to be easily switchable.  For rpm type packaging that probably shouldn't too difficult to achieve, and in many ways provides more flexibility to both camps.

Yes, we can build our own and the src rpms are available to build from. So it is not much work but it does require that for every update you then maintain this. I would rather Oracle did that and I spent my time on other things.  I wonder though how many people really get bitten by this over memory consumption and whether there are enough people to warrant building the 2 versions.

Also other MySQL memory issues which will be easier to see with P_S's new memory_summary_* tables in 5.7 have for me been much more of an issue than those caused by P_S' high but constant footprint.  Certain query types can easily gobble up an extra 20GB of RAM if you do not pay attention and that far outweighs the overhead being described here.

So if you don't need P_S then it's good to not have it, and the best way I would  see to achieve that would be to do as suggested above. That may not be realistic .

Simon, we're looking to make the PS part a non-issue in 5.7. We'll see once we have released code that people can experiment with. We know it's a substantial issue that we have to do something about.

You're right that some queries can consume a lot of RAM. If you catch one doing that please file a bug report so we can do something about it. One I know that we fixed a while back involved lots of constants in an IN clause but there have been others.

This is addressed in MySQL 5.7.6 with recent work that implements autoscaling for several Performance Schema sizing parameters.

The Performance Schema now allocates memory incrementally, scaling its memory use to actual server load, instead of allocating all the memory it needs during server startup. Consequently, configuration of the Performance Schema is easier; most sizing parameters need not be set at all. A server that handles a very low load will consume less memory without requiring explicit configuration to do so.

These system variables are autoscaled:

performance_schema_accounts_size
performance_schema_hosts_size
performance_schema_max_cond_instances
performance_schema_max_file_instances
performance_schema_max_index_stat
performance_schema_max_metadata_locks
performance_schema_max_mutex_instances
performance_schema_max_prepared_statements_instances
performance_schema_max_program_instances
performance_schema_max_rwlock_instances
performance_schema_max_socket_instances
performance_schema_max_table_handles
performance_schema_max_table_instances
performance_schema_max_table_lock_stat
performance_schema_max_thread_instances
performance_schema_users_size

There are new instruments named with the prefix memory/performance_schema/ that expose how much memory is allocated for internal buffers in the Performance Schema. These instruments are displayed in the memory_summary_global_by_event_name table.

For more information about how Performance Schema allocates memory and how to assess the amount currently in use, see http://dev.mysql.com/doc/refman/5.7/en/performance-schema-memory-model.html.