In memory database error: table is full

Question

I got hit by error "Tale is full" on my in memory database.

My database is 4.5GB in size and has 300 partitions.

I run MySql 5.6.17 version_compile_machine=x86_64 on 64 bit Windows 10, 16GB ram. Disk are NTFS and have 100GB spare place.

I've setup tmp_table_size and max_heap_table_size to 12GB.
Those values ware 30 MiB untill "table is full" error, so it seems that they ware and are ignored (changing to 12GB did not help.)

If it has to do something with this setting innodb buffer pool size is 12GiB also.

System runs on 50% of RAM memory.

What else can do to increase allowed size of in memory DB?

Answer 1

I was changing tmp_table_size and max_heap_table_size both via PhpMyAdmin and via SET GLOBAL ... in my scripts. And it didn't work.

Changing those varaibles via my.ini, restarting server and then loading DB into memory allowed for bigger DB.

• a_vlad: drop all partitions, reset heap_size, leave memory settings to innodb (or decrease it to 8192). MySQL 4.5G database on 16Gb machine will work fine (bad queries can kill and real InMemory database)
• Rick James: @a_vlad - 8192 what?
• a_vlad: 8192M of course == 8G, originally -4.5GB, innodb buffer pool size original = 12G, with 4.5G database and 8G it also more than necessary ,but I prefer give server memory from start, for not worry about this in feature. Most of my servers - dedicated for tasks, so why not make settings from start.

Answer 2

"I've setup tmp_table_size and max_heap_table_size to 12GB." -- TERRIBLE!

One query that has an ORDER BY and needs a tmp table could allocate up to that much space. 10 connections... Well, it is mind boggling.

I recommend no more than 1% of RAM (160M in your case) for both of those VARIABLES.

"System runs on 50% of RAM memory." -- I'd say you have a "ticking bomb".

"300 partitions" -- as in "CREATE TABLE ... PARTITION BY ..."? A single table with 300 partitions is inefficient; the practical limit is about 50.

"memory DB" -- You are trying to "CREATE TABLE ... ENGINE=MEMORY"? And fill it with lots of data? Why? It is usually quite adequate to use ENGINE=InnoDB and let the caching of that engine give you about as much performance as MEMORY.

• Mike: I did setup of 12GB just to make it work , i'll try make lower and see if they will allow bigger database. At "50% of ram" - thank you for reminder. Server is not a client facing and is run on demand so basically i oversee it while it works.At "300 partitions" - its the setup that i practically tested to max out previous SSD server. Lower values lead to less queries per second. At "Memory DB" - i work now on older disk (HDD) now and i'm more worried about it being being physically harmed by load so thats why i load everything into RAM. Haven't tested performance of RAM vs Inno though.
• Rick James: Another point -- MEMORY does only table-level locking. InnoDB may be more scalable due to row-level locking.