MySQL Bugs: #69490: Memory usage of P

Bug #69490	Memory usage of P_S
Submitted:	17 Jun 2013 10:45	Modified:	10 Dec 2015 16:17
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
This may not be a 'bug report' in the traditional meaning.  Also not sure if it is a documentation reqeust or a feature request. 

The background is that we have experienced that enabling/disabling P_S sometimes makes a big difference of how much memory the mysqld process consumes.  The latest case was reported by a customer using a recent 5.6x version, and here the difference is close to 3 GB (I have no option to verify this case specically, though).

I was not able to find any meaningfull and related documentation.  The question is: "How can the memory overhead of enabling P_S and its various 'consumers' be predicted, and how can worst-case scenario in respect to memory usage for a given configuration be managed and calculated in advance?".

Without such information it is very difficult to manage the overall MySQL server configuration as regards server and per-client buffer settings if the aim is to allow the mysqld process a maximum (no less and no more) memory consumption. 

An estimate resulting in small inaccuracies are of course inside the tolerable - but it is not when we are talking of GB's.

How to repeat:
See above. 

Suggested fix:
Make P_S memory usage instrumentable/manageable and/or document.

Check http://bugs.mysql.com/bug.php?id=68514. Even though it was set to "Not a bug" eventually, there is some useful explanations of how much additional memory is used and how it s allocated depending on server settings.

Thanks, Valery. I/We will study this. 

But should such information not be in MySQL docs?

Change it to document category?. Thanks.

OK .. no issue! Category changed!

For the records, personally I had never agreed that allocation of these amounts of memory the way it is done is "OK" or "Not a bug". 

But it seems like a kind of tradeoff, by design, and surely it is a cost of PERFORMANCE_SCHEMA in 5.6 that users with hundreds of connections should know about.

Thank you for the feedback.

To clarify:

1)

The performance schema allocates memory once at startup,
and never allocates memory during the server operation.

This is by design.

2)

The total amount of memory allocated, and the details about how much memory is allocated for each internal buffer, depends entirely on the sizing parameters.

For example, a server started with max_connection = 2000 will consume twice as much memory to keep statistics per connection compared to a server started with max_connection = 1000, and the memory consumption for this buffer has nothing to do with how many connections currently exists.

If the memory usage is considered too high, review the sizing parameters,
to make sure they are appropriate for the expected load.

3)

The full details about how much memory is used by which buffer are exposed with
SHOW ENGINE PERFORMANCE_SCHEMA STATUS

---

Leaving this opened as a documentation bug, for the doc team to evaluate.

-- Marc Alff

OK, Marc. Understood.

But the weakness/problem is that in order to estimate P_S memory requirements, you will first need to enable it with specific 'sizing parameters'.  This is a 'post mortem approach'.

I'd like to know *BEFORE* configuring it and even before enabling it at all.

The Performance Schema memory model has changed so that in most cases it autosizes itself, or autoscales as needs change during runtime. (One beneficial effect is that less memory is used.) This is described at:

http://dev.mysql.com/doc/refman/5.7/en/performance-schema-memory-model.html

However, the more dynamic approach to memory allocation means that this wish is unlikely to be attained:

"I'd like to know *BEFORE* configuring it and even before enabling it at all."

To query Performance Memory use at runtime, the memory_xxx summary tables can be used:

http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html

For this purpose, the memory instruments for the Performance Schema itself will be of most interest. For example:

SELECT * FROM memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';