H Aßdøµ
H Aßdøµ

Reputation: 3055

MySQL Out of memory when copy Innodb Table to memory table

I am getting this error when copying table to table, even there is plenty of space ( 36 GB) and memory ( 09 GB).

insert into time_series_funds_mem select * from time_series_funds;
MySQL said: Documentation
#5 - Out of memory (Needed 260748 bytes) 

The table time_series_funds has about 16 million row, and total size of ~3.2 Gb, the storage engine is InnoDb. And this is my my.cnf file:

# The following options will be passed to all MySQL clients
[client]
port        = 3300
socket      = "${path}/binaries/mysql/mysql.sock"

# Here follows entries for some specific programs

# The MySQL server
[mysqld]

tmp_table_size=7096M
max_heap_table_size=7096M


# The TCP/IP Port the MySQL Server will listen on
port = 3300

#Path to installation directory. All paths are usually resolved relative to this.
basedir = "${path}/binaries/mysql/"

#Path to the database root
datadir = "${path}/binaries/mysql/data/"

# The default storage engine that will be used when create new tables
default-storage-engine = MYISAM

bind-address    = 127.0.0.1
socket          = "${path}/binaries/mysql/mysql.sock"
log_error       = "${path}/binaries/mysql/data/mysql_error.log"
skip-external-locking
key_buffer_size = 160M
max_allowed_packet = 100M
table_open_cache = 64
net_buffer_length = 8K


skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M


log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed
server-id   = 1


innodb_data_home_dir = "${path}/binaries/mysql/data/"
innodb_data_file_path = ibdata1:200M:autoextend
innodb_log_group_home_dir = "${path}/binaries/mysql/data/"
innodb_buffer_pool_size = 3024M
innodb_log_file_size = 512M
innodb_log_buffer_size = 200M
innodb_flush_log_at_trx_commit = 1
#innodb_flush_method = normal

innodb_lock_wait_timeout = 50

innodb_write_io_threads = 16
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer_size = 5000M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

This is the structure of table:

CREATE TABLE `time_series_funds_mem` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `tickerid` int(10) unsigned NOT NULL,
 `ticker` varchar(6) COLLATE utf32_bin DEFAULT NULL,
 `fund_ticker` varchar(8) COLLATE utf32_bin NOT NULL,
 `date` date DEFAULT NULL,
 `open` decimal(13,4) unsigned DEFAULT NULL,
 `high` decimal(13,4) unsigned DEFAULT NULL,
 `low` decimal(13,4) unsigned DEFAULT NULL,
 `close` decimal(13,4) unsigned DEFAULT NULL,
 `adjusted_close` decimal(13,4) unsigned DEFAULT NULL,
 `volume` bigint(20) unsigned DEFAULT NULL,
 `dividend_amount` decimal(13,4) unsigned DEFAULT NULL,
 `split_coefficient` decimal(13,4) unsigned DEFAULT NULL,
 `return_perc` decimal(13,4) DEFAULT NULL,
 `has_return_perc` bit(1) NOT NULL DEFAULT b'0',
 `has_return_calc` bit(1) NOT NULL DEFAULT b'0',
 `return_YTD` decimal(13,6) NOT NULL,
 `return_1Y` decimal(13,6) NOT NULL,
 `return_3Y` decimal(13,6) NOT NULL,
 `return_5Y` decimal(13,6) NOT NULL,
 `return_all` decimal(13,6) NOT NULL,
 `var_95` decimal(13,6) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `tickerid` (`tickerid`),
 KEY `ticker` (`ticker`)
) ENGINE=MEMORY DEFAULT CHARSET=utf32 COLLATE=utf32_bi

This is the file I am trying to import: http://167.99.242.73/funds.tar.gz.

Upvotes: 1

Views: 331

Answers (0)

Related Questions