MySQL Bugs: #62536: partitions use too much memory

Bug #62536	partitions use too much memory
Submitted:	25 Sep 2011 15:44	Modified:	30 Aug 2012 13:04
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5, 5.1, etc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Currently an open partitioned table consumes too much resources per-invocation-per-thread.

An example table:
create table ptest (
  a int primary key auto_increment,
  b varchar(64000)
) 
partition by key(a) partitions 1000 ;

will consume ~193MB on simple SELECT as seen in http://domasmituzas.files.wordpress.com/2011/09/partitions-memory-usage.png

Combined with another bug I opened (#44626) it allows to blow up memory entirely (e.g. 10 mentions in UNION already uses over 2G).

Also, 10 parallel queries to this table will use 2G.
Needless to say, that 1000 parallel queries (in case of a server stall), will use 200G, making partitioning unusable in high concurrency situations. 

How to repeat:
create table ptest (
  a int primary key auto_increment,
  b varchar(64000)
) 
partition by key(a) partitions 1000 ;

INSERT INTO ptest VALUES (NULL, NULL);
SELECT SLEEP(3600) FROM ptest;

Suggested fix:
Reuse share buffers.

Thank you for the bug report. Verified just as described with current 5.5.17 on Mac OS X.

I've just monitored memory usage with top, and initially I had this:

 PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  VSIZE
...
78520 mysqld       0.0%  1:04.33  17    65     80 4048K   188K  9152K   199M 

Then I created partitioned table and got:

78520 mysqld       0.0%  1:08.34  17    65     82   34M   188K    39M   201M

Then I inserted one row and executed SELECT from it:

78520 mysqld       0.0%  1:08.58  17    65   1087   40M   188K    45M   264M

What's even worse, memory usage remain the same even when I closed session that executed SELECT. Only dropping table helped a bit:

78520 mysqld       0.0%  1:11.56  17    65     83   39M   188K    44M   202M

Related to bug#36613 and bug#20877.

This is a duplicate of Bug#57480 Memory leak when having 256+ tables

Domas, and that duplicate is good news for you. While you can't see progress in the public bug tracker yet there's been very extensive work on the problem that should address much of what you wrote in your blog post at http://dom.as/2011/09/25/blowing-up-in-memory/ and more.

This shouldn't be a duplicate, there's still plenty of bad allocation done outside of InnoDB, I'm attaching 7GB usage by single query from a partitioned table

instruments analysis of single query on 5.5.25

Attachment: Screen Shot 2012-06-18 at 1.20.09 PM.png (image/png, text), 425.54 KiB.

Domas, can you see where in ha_partition those malloc occur? Also which table and query did you use?

Mattias, looks like the biggest chunk (6G in my test) is coming from this:

  alloc_len= m_tot_parts * (m_rec_length + PARTITION_BYTES_IN_POS);
  alloc_len+= table_share->max_key_length;
  if (!m_ordered_rec_buffer)
  {
    if (!(m_ordered_rec_buffer= (uchar*)my_malloc(alloc_len, MYF(MY_WME))))

Table definition is something  like this:
CREATE TABLE `ptest` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(64000) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
PARTITIONS 1000 */

Query is just simple UNION*100 query to showcase  what happens when stall of any kind happens.
Do note, afterwards it is busy sorting locks for next extended period of time too :)

Re-opening this bug now, as it's still repeatable with 5.5.26.

Test case is simple (checked on Mac OS X):

Initial top result:

#  PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  VSIZE
# 29516 mysqld       0.0%  0:00.57  17    65     88   44M   188K    50M   206M 

CREATE TABLE `ptest` (
 `a` int(11) NOT NULL AUTO_INCREMENT,
 `b` varchar(64000) DEFAULT NULL,
 PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
PARTITIONS 1000 */

# 29516 mysqld       0.0%  0:03.09  17    65     92   68M   188K    74M   215M

SELECT COUNT(*) FROM test.ptest UNION ALL
SELECT COUNT(*) FROM test.ptest UNION ALL
SELECT COUNT(*) FROM test.ptest UNION ALL
SELECT COUNT(*) FROM test.ptest UNION ALL
SELECT COUNT(*) FROM test.ptest UNION ALL
SELECT SLEEP(60); 

# 29516 mysqld       0.0%  0:03.41  17    66    513  136M   188K   142M   580M
# both during the query above and after it

flush tables;

# 29516 mysqld       0.0%  0:03.51  17    66     97   89M   188K    95M   232M

So, even with 5 references to partitioned table we have notably increased memory use, and even flush tables do not help to release all the memory back. Nothing like that happens with non-partitioned table.

Fixed in 5.1.66, 5.5.28, 5.6.7.