Menu
For the purposes of running a large number of tests that interact with the database, I want to do two things:
I would like to copy the schema of a database without copying its data. I can do this with a script that grabs the CREATE TABLE statements from each table in the database.
Upon creating this database, I would like to force it to be 100% in memory.
I'm stuck on how to do part 2 - Is there an easier way to do this other than specifying each table's engine? Somehow that seems like a poor way of doing it.
952
Create the MEMORY database and recreate the tables you'll be using with this syntax: CREATE TABLE tablename (...) ENGINE = MEMORY; . You can then import your data using LOAD DATA INFILE 'table_filename' INTO TABLE tablename for each table.
MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately 512MB of RAM.
First of all, there are 3 major cases when MySQL will crash due to running out of memory: MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix.
MySQL allocates buffers and caches to improve performance of database operations. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify these variables to run MySQL on systems with limited memory.
Create the database in /dev/shm (ubuntu|debian) and it will be in RAM. It can grow up to 0.5 of available memory.
161
As dtmilano said, you can put it on a tmpfs mounted filesystem. It doesn't have to be /dev/shm, but that is one place where tmpfs is usually mounted.
You can create a new one anywhere, though:
mount none -t tmpfs /path/to/dir
If it fills all your available RAM, it will use swap as a backup.
Put it in /etc/fstab to re-mount on boot. Just remember, it's a ram disk, so it starts out empty every time you reboot. See: http://www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs
Alternately, as suggested by yuxhuang you can create a table of type MEMORY. It also empties on restart, though the table definition remains. The MEMORY table type has a few restrictions, though. It uses fixed-size rows, for example, so text and blob columns are not allowed, and varchar isn't variable length. See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
