How to make the mysql MEMORY ENGINE store more data?
You should adjust the way you make and load the table
CREATE TABLE sns_memory SELECT * FROM sns WHERE 1=2;
ALTER TABLE sns_memory ENGINE=MEMORY;
INSERT INTO sns_memory SELECT * FROM sns;
DROP TABLE sns;
ALTER TABLE sns_memory RENAME sns;
This will get around any imposed limits by tmp_table_size and max_heap_table_size.
Just the same, you need to do two things:
Add this to /etc/my.cnf
[mysqld]
tmp_table_size=2G
max_heap_table_size=2G
this will cover mysql restarts. To set these values in mysqld right now without restarting run this:
SET GLOBAL tmp_table_size = 1024 * 1024 * 1024 * 2;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 2;
If you are checking the above variables with
SELECT @@max_heap_table_size;
or
SHOW VARIABLES LIKE 'max_heap_table_size';
you may notice that they don't seem to change following the SET GLOBAL...
statements. This is because the settings only apply to new connections to the server. Make a new connection, and you'll see the values update or you could change it within your session by running:
SET tmp_table_size = 1024 * 1024 * 1024 * 2;
SET max_heap_table_size = 1024 * 1024 * 1024 * 2;
max_heap_table_size is what you are looking for