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.
- Why is opening a table with many partitions so memory-intensive?
- 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.
