We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB table (e.g. inside temperature of building 42 is stored in table building__inside_temperature). There are about 100 time series tables and each has three columns:

  • timestamp in milliseconds as bigint
  • ID of object (as char(22))
  • numerical value (as double)

The primary key is (timestamp, ID). We use range partitioning on the timestamp column with one partition per day for 2.5 years and key sub-partitioning on the ID with 8 sub-partitions. This results in about 800 partitions with 8 sub-partitions each per table. We use innodb_file_per_table=OFF since there would be too many open files otherwise. The other MySQL parameters are left untouched.

When connecting with 100 threads (and connections) in parallel and opening a time series table, the MySQL instance runs out of memory. The process list shows all threads in 'Opening table' state before crashing. With less partitions or less parallel connections opening the table, the problem disappears. We use the following mysqlslap command for that: mysqlslap --create=create_timeseries.sql --delimiter=";" --query=insert_datapoint.sql --number-of-queries=1 --concurrency=100

Content of the files is here: http://pastebin.com/x4YMMF3h

The error is the following:

2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1208.

2014-08-12 06:25:53 2b119160d7002014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1248.

2014-08-12 06:25:53 2b11913c4700 InnoDB: Assertion failure in thread 47354451085056 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:25:53 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=102
max_threads=604
thread_count=102
connection_count=102
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 333908 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x13aeb5a80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 3784.

2014-08-12 06:25:53 2b1192035700 InnoDB: Assertion failure in thread 47354464130816 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

http://dev.mysql.com/doc/refman/5.6/en/table-cache.html just tells, that each MySQL thread opens a table independently.

  1. Why is opening a table with many partitions so memory-intensive?
  2. How can I see how many times a table is opened and how much memory this consumes? show open tables from mysqlslap; does not give me per-thread information and no memory consumption.
up vote 1 down vote accepted

I found out that due to this bug, MySQL 5.6.17 uses 6kb memory per open subpartition. This would result in about 500 GB of memory usage for 100 connections each having 100 tables with 800 partitions each, with 8 subpartitions each.

  • 1
    +1 for finding this out yourself. CAVEAT: You should experiment with MyISAM for table partitioning and see if you have the same issues. For my former employer, I have a daily MyISAM table with 4179 partitions, holding past data and empty partitions for future days. Never had an issue. Of course, I raised the machine's ULIMIT to 102400. I have a separate post on raising innodb_open_files, open_files_limit, and the OS file limit (dba.stackexchange.com/a/60194/877) BTW Welcome to the DBA StackExchange !!! – RolandoMySQLDBA Aug 14 '14 at 12:03
  • Ever considered installing 1TB of RAM ??? HA HA – RolandoMySQLDBA Aug 14 '14 at 12:05

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.