Let’s say when you have to run a batch process monthly, you can survive with times like 10 minutes. I can imagine a lot of seasoned DBAs right now ROFL about my insignificant 10 minutes. The point here is I was developing this process and some test cases, so my usual trial/error methodology doesn’t scale very well with 10 minute offsets.
So I borrowed an idea from a colleague: why not moving all the database to memory? It’s not so big and I have 2G of ram. But, could I change all tables (~20) to MEMORY in one line or so?
Since this was a Ruby on Rails project, I used the rails console to be able to mix SQL and Ruby. My first try:
conn.tables.each do |t|
conn.execute "ALTER TABLE #{t} ENGINE=MEMORY"
end
First error: foreign keys couldn’t be migrated from InnoDB to MEMORY
Maybe there is a more MySQL-esque way of dropping all the foreign keys on a database but this one worked quite well:
>> res = conn.execute "SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
WHERE table_schema = 'app_development'
AND REFERENCED_TABLE_NAME IS NOT NULL"
>> res.each_hash do |h|
conn.execute "ALTER TABLE #{h['TABLE_NAME']}
DROP FOREIGN KEY #{h['CONSTRAINT_NAME']}"
end
>> conn.tables.each do |t|
conn.execute "ALTER TABLE #{t} ENGINE=MEMORY" rescue nil
end
Still I had some problems with a few tables using BLOBs, but they were not used on the process/tests so I ignored them. That’s what the rescue nil is for.
Jorge,
instead of changing the engine type, there is an easier way.
Move the data directory to an in-memory filesystem.
In Linux, it’s usually in /dev/shm.
1. stop the server
2. cd /usr/local/mysql
3. mv data data1
4. cp -R data1 /dev/shm/data
5. ln -s /dev/shm/data /usr/local/mysql/data
6. start the server
Now the data is in memory, without any limitation.
(Warning: the data is in memory only! If your server shuts down, you lose all)
When you are finished, copy the data from the in-memory filesystem to the hard disk.
Cheers
Giuseppe
I thought about a ramdisk for this, but I’m using OSX and I don’t know how to do it by now
A RAM disk on Mac OS X is quite easy, actually:
http://osxdaily.com/2007/03/23/create-a-ram-disk-in-mac-os-x/
Cheers
Giuseppe
Wow, that’s easy! Thanks
If you have enough memory to hold the entire database in memory, why not just make innodb_buffer_pool_size large enough to do so. The speed should be the same (or similar) as moving the tables themselves into the MEMORY engine, without the data loss problems.
In my experience, MEMORY hasn’t been all that useful.
Great site!0
@gari <– COMMENT SPAM
Note the lame comment and the website link.
Do the ‘Net a favour and purge stupid posts like that, thanks.
Many thanks just what I was looking for, innodb_buffer_pool_size large it is.