MySQL Bugs: #65074: MySQL Server memory usage grew up and never shrunk

Bug #65074	MySQL Server memory usage grew up and never shrunk
Submitted:	23 Apr 2012 12:32	Modified:	13 Nov 2014 14:59
Reporter:	Seunguck Lee	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.23	OS:	Linux (CentOS release 5.6 (Final))
Assigned to:		CPU Architecture:	Any

Description:
I have just a few tables and these tables have  about 1000 partitions.
Just after restart mysql server, mysql server memory usage is only 20GB (including 18GB innodb buffer pool).
But after two or three weeks, mysql server used over 30 GB 
and finally mysql server used swap space.

Mysql server has about 4000 connection and 10 tables (internally over 1000 partitions).
And during the peak time, mysql server handle about 2000 query per second (1600 select and 400 update queries).
Mysql server memory usage never shrunk.

And after "FLUSH TABLES" command, mysql server's memory usage is down.
But, actually "FLUSH TABLEs" command takes very long time and 
all queries hang on until "FLUSH TABLES" command is done.
So, we can't execute "FLUSH TABLES" command on our production server.

max_connections=5000
table_open_cache = 1024
sort_buffer_size = 512K
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 128K

(There is no sort operation and a lot of disk temporary table for "DESCRIBE tb_a".)

Mysql server's memory usage grew up even though I set "table_open_cache=200".

What is the problem ?
Is there any work-around for this (Is there any way to memory usage shrunk without FLUSH TABLES) ?

How to repeat:
** MySQL Server ----------------------------------------
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` text NOT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=60000001 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (id)
PARTITIONS 1024 */

** Insert 50000000 records to sbtest

** Sysbench Client 1 ----------------------------------------
./bin/sysbench --test=oltp --oltp-table-size=50000000 --num-threads=2000 --max-requests=999990000 --max-time=0 \
 --mysql-host=192.168.80.49 --mysql-user=sysbench --mysql-password=sysbench \
 --mysql-db=sysbench --mysql-table-engine=innodb --mysql-create-options= \
 --mysql-socket=/tmp/mysql.sock \
 --mysql-port=3306 \
 --oltp-test-mode=complex \
 --oltp-read-only=off \
 --oltp-user-delay-min=1000000 \
 --oltp-user-delay-max=2000000 \
 --db-ps-mode=disable \
 run;

** Sysbench Client 2 ----------------------------------------
./bin/sysbench --test=oltp --oltp-table-size=50000000 --num-threads=2000 --max-requests=999990000 --max-time=0 \
 --mysql-host=192.168.80.49 --mysql-user=sysbench --mysql-password=sysbench \
 --mysql-db=sysbench --mysql-table-engine=innodb --mysql-create-options= \
 --mysql-socket=/tmp/mysql.sock \
 --mysql-port=3306 \
 --oltp-test-mode=complex \
 --oltp-read-only=off \
 --oltp-user-delay-min=1000000 \
 --oltp-user-delay-max=2000000 \
 --db-ps-mode=disable \
 run;

Start mysql and warmping up data : mysql server use only 8~10GB
Just after running sysbench client : mysql server use up to 20GB ~ 25GB
But mysql server's memory usage never shrunk

Do you real tables also have TEXT/BLOB columns? Please, send the output of SHOW TABLE STATUS for any of tables that demonstrate the problem, and the output of SHOW ENGINE INNODB STATUS when you see memory growth.

show engine innodb status

Attachment: show_engine_innodb_status.txt (text/plain), 9.45 KiB.

Yes, tables of real service has TEXT column.
Actually our real service mysql is 5.5.20, So I tested mysql 5.5.23 with sysbench.
And I found mysql 5.5.23 show the same symptoms.

Attached file is MySQL 5.5.20's innodb engine status.

Thanks.

See bug #57480 also about fixes for similar cases that are made in 5.5.21+.

Yes, I've seen the bug report.

And I already tested this case with mysql 5.5.23.
But MySQL Server's memory usage is too high. 
MySQL Server memory usage is over 36GB (Innodb buffer pool 18GB + ?), when sysbench is running with connection 4000 and 1000 tables.

I don't understand why mysql server need another 18GB memory.
And memory usage is increasing during test. (It could be because table cache is not warmed-up fully)
Anyway, to shrink mysql server's memory usage, we need to run "FLUSH TABLES".
But "FLUSH TABLEs" command need a lot of time, furthermore "FLUSH TABLEs" make another session's query waiting.

I'm wondering Is there any way to shrink memory usage without "FLUSH TABLES".
Thanks.

I do not see huge memory use for InnoDB dictionary, so this may have something to do with table cache (as FLUSH TABLES helpes). Can you try to run the same test and check memory usage with, say:

table_open_cache = 128

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can experience a similar behavior on rel. 5.6.17.
Still I have many partitions (2000) and a longblog field in my table.
Any fix for this? Any workaround?

For the 5.6.17 case, you should see if this solves it in 5.6.21:
http://bugs.mysql.com/bug.php?id=70641

Please see Shane comment. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".