October 29, 2010: Don't Partition your InnoDB Tables!

Earlier in this series:
Okay, MySQL. What the hell.
MySQL, what the hell, part 2

Long story short: My MySQL database, supporting a write-heavy, gigabytes-large, partitioned one-file-per-table InnoDB tableset, had a memory leak. It was difficult to find any information about this issue.

In my implentation, I had:
  • MySQL 5.1.50 on Linux 2.6.34.7-56.fc13.x86_64
  • 1 table with 3 million rows, 3 columns, 2 indexes (including primary), 150MB data, 140MB index, 20 partitions by linear key of the first column in the primary key.
  • 1 table with 6 million rows, all unique, 2 columns, 240MB data, 20 partitions by linear key of the first column. Over the course of an hour, this entire table is rewritten (chunks removed, new chunks inserted).
  • 1 table with 110 million rows, 10 columns, 3 indexes (including primary), 7.3GB data, 5.7GB index, 20 partitions by linear key of first column in primary key.
  • 8.0GB of system memory. 6.0GB innodb_buffer_pool_size. innodb_file_per_table=1. 1 hard drive.
  • Constant upserts with uniform frequency, and only 4-5 read connections on average at any given time.

I wrote a script that would watch system memory, and restart mysql when the amount of free RAM (+ buffers/cache) dropped below 256MB. Here's how that looked for the past few days:
2010-10-27 23:25:16: Free memory: 248024k, less than 262144k. Restarted mysqld.
2010-10-27 23:40:01: Free memory: 2805392k.
2010-10-27 23:55:01: Free memory: 724648k.
2010-10-28 00:10:01: Free memory: 621828k.
2010-10-28 00:25:01: Free memory: 595784k.
2010-10-28 00:40:01: Free memory: 513324k.
2010-10-28 00:55:01: Free memory: 481636k.
2010-10-28 01:10:01: Free memory: 449344k.
2010-10-28 01:25:01: Free memory: 415300k.
2010-10-28 01:40:01: Free memory: 368328k.
2010-10-28 01:55:01: Free memory: 283548k.
2010-10-28 02:10:24: Free memory: 258308k, less than 262144k. Restarted mysqld.
2010-10-28 02:25:01: Free memory: 1980012k.
2010-10-28 02:40:01: Free memory: 629564k.
2010-10-28 02:55:02: Free memory: 523104k.
2010-10-28 03:10:01: Free memory: 370304k.
2010-10-28 03:25:01: Free memory: 344388k.
2010-10-28 03:40:01: Free memory: 275412k.
2010-10-28 03:55:01: Free memory: 262368k.
2010-10-28 04:10:28: Free memory: 232576k, less than 262144k. Restarted mysqld.
2010-10-28 04:25:01: Free memory: 5354260k.
2010-10-28 04:40:01: Free memory: 3194412k.
2010-10-28 04:55:01: Free memory: 957212k.
2010-10-28 05:10:01: Free memory: 676196k.


MySQL is the only thing of consequence running on this box. Last night, I paused data updates for about 90 minutes as I removed the partitioning on those 3 tables. Here's how that went:

2010-10-28 22:56:53: Free memory: 253616k, less than 262144k. Restarted mysqld.
2010-10-28 23:10:01: Free memory: 4984468k.
2010-10-28 23:25:01: Free memory: 3403416k.
2010-10-28 23:40:01: Free memory: 1381036k.
2010-10-28 23:55:01: Free memory: 622360k.
2010-10-29 00:10:01: Free memory: 440564k.
2010-10-29 00:25:01: Free memory: 394656k.
2010-10-29 00:40:01: Free memory: 329512k.
2010-10-29 00:55:23: Free memory: 243328k, less than 262144k. Restarted mysqld.
2010-10-29 01:10:01: Free memory: 5767448k.
2010-10-29 01:25:01: Free memory: 3727708k.
----- stopped script here, removed partitions, restarted mysqld and mem script -----
2010-10-29 02:55:01: Free memory: 3994588k.
2010-10-29 03:10:01: Free memory: 836104k.
2010-10-29 03:25:01: Free memory: 818416k.
2010-10-29 03:40:01: Free memory: 793408k.
2010-10-29 03:55:01: Free memory: 813048k.
2010-10-29 04:10:01: Free memory: 813344k.
2010-10-29 04:25:01: Free memory: 815596k.
2010-10-29 04:40:02: Free memory: 816080k.
2010-10-29 04:55:01: Free memory: 816000k.
2010-10-29 05:10:01: Free memory: 815396k.
2010-10-29 05:25:01: Free memory: 815912k.
2010-10-29 05:40:01: Free memory: 815360k.
2010-10-29 05:55:01: Free memory: 816152k.
2010-10-29 06:10:01: Free memory: 767904k.
2010-10-29 06:25:01: Free memory: 814852k.
2010-10-29 06:40:01: Free memory: 813524k.
2010-10-29 06:55:01: Free memory: 813936k.
2010-10-29 07:10:01: Free memory: 814380k.
2010-10-29 07:25:01: Free memory: 813636k.
2010-10-29 07:40:01: Free memory: 812888k.
2010-10-29 07:55:01: Free memory: 785412k.
2010-10-29 08:10:01: Free memory: 813148k.


So, magically, the memory leak is gone. Credit goes to this guy for spotting it at the end of 2008. Apparently nobody listened or could duplicate it. Well, buddy, I'm right there with ya.

Category: Technical | Posted by: admin

Comments

October 29, 2010, 18:47:56 Nuhvok01 wrote:

This frightens me. Even computer majors have an issue with software where to problem magically fixes itself. So is it still trying to exponentially eat more RAM or is it behaving now because it felt like it?

Add Comment

This item is closed, it's not possible to add new comments to it or to vote on it