InnoDB Data Dictionary Exceeds Memory Limits with Many Tables or Partitions (Doc ID 1384260.1)

Last updated on MAY 09, 2016

Applies to:

MySQL Server - Version: 5.5 and later   [Release: 5.5 and later ]
Information in this document applies to any platform.

Symptoms

On : 5.5 version where schema has many tables that each have large columns (500+) and a large number of partitions, the follow issue results.

ACTUAL BEHAVIOR
---------------
When creating a large amount of partitions (350 per table across 78 tables) the memory allocation for MySQL went up dramatically (48G based on example figures).

EXPECTED BEHAVIOR
-----------------------
When creating a large amount of partitions, it is expected that some memory will be used for handling the new partitions, but not that amount.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create 100 tables with 500 columns each.
2. Partition each table by day to create 365 partitions.
3. Access the tables and watch the memory disappear.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot run the MySQL service without it running out of memory. It also reduces the system memory available for other processes.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms