MySQL Memory Engine vs InnoDB on RAMdisk

Question

I am writing some software that should smooth data from a hierarchical format type to a table format. Instead of doing all this in a programming language every time and serving it, I want to cache the results for a few seconds and use SQL to sort and filter. When we use, we speak 400,000 records and 1 or 2 readings during these few seconds.

Each table will contain from 3 to 15 columns. Each line will contain from 100 bytes to 2,000 bytes of data, although it is possible that in some cases some lines can receive up to 15,000 bytes. I can freeze the data, if necessary, in order to keep working.

The main parameters that I consider:

MySQL memory engine

A good option, almost specially written for my use! But ... "MEMORY tables use a fixed-length row storage format. Variable-length types such as VARCHAR are stored with a fixed length. MEMORY tables cannot contain BLOB or TEXT columns." - Unfortunately, I have text fields up to 10,000 characters long - and even this is a number that is not specifically limited. I could adjust the varchar length based on the maximum length of the text columns when I go through my anti-aliasing, but this is not entirely elegant. Also, for my random row of 15,000 characters, does this mean that I need to allocate 15,000 characters for each row in the database? If there were 100,000 lines, then this is 1.3 gb, not including overhead!

InnoDB on RAMDisk

This is designed to work in the cloud, and I could easily deploy a server with 16 GB of RAM, configure MySQL to write to tmpfs, and use full-featured MySQL. My concern for this is space. Although I'm sure the engineers wrote a memory mechanism to prevent the consumption of all temporary storage and server crashes, I doubt that this solution will know when to stop. How much actual space will my 2000 bytes of data in database format consume? How can I control this?

Bonus questions

Indexes I know in advance which columns need to be filtered and sorted. I can tune the index before inserting it, but what kind of performance improvement could I honestly expect from a disk with a disk? How much extra index overhead do you add?

Insertion I assume that inserting multiple rows with a single query is faster. But one request or a series of large requests is stored in memory, and we write to memory, so if I did this, for a moment I would need to double the memory. So, we are talking about doing one or two or a hundred at a time, and waiting for it to complete before processing more. InnoDB does not lock the table, but I'm worried that you are sending two requests too close together and confusing MySQL. Is this a serious problem? With the MEMORY engine, I would definitely have to wait for completion due to table locks.

Temporary Are there any benefits to temporary tables besides the fact that they are deleted when the db connection is closed?

Answer 1

I suggest you use MyISAM. Create a table with the appropriate indexes for your query. Then disable the keys, load the table and enable the keys.

I suggest you develop a discipline like this for your system. I used this discipline very well.

Save two copies of the table. Name one table_active , and the second table_loading .

When it is time to download a new copy of your data, use these commands.

  ALTER TABLE table_loading DISABLE KEYS; /* do your insertions here, to table_loading */ /* consider using LOAD DATA INFILE if it makes sense. */ ALTER TABLE table_loading ENABLE KEYS; /* this will take a while */ /* at this point, suspend your software that reading table_active */ RENAME TABLE table_active TO table_old; RENAME TABLE table_loading TO table_active; /* now you can resume running your software */ TRUNCATE TABLE table_old; RENAME TABLE table_old TO table_loading; 

Alternatively, you can DROP TABLE table_old; and create a new table for table_loading instead of the last rename.

This two-table strategy (with two buffers) should work very well. This will create some delay because your software that reading the table will work on the old copy. But you will avoid reading from a partially loaded table.

I suggest MyISAM because you will not end RAM and will not explode, and you will not have overhead with a fixed line length or transaction overhead. But you can also consider MariaDB and the Aria storage engine, which does a good job of using RAM buffers.

If you use the MEMORY storage engine, be sure to configure the max_heap_table_size system variable. If your read requests will use index range scanning (sequential index access), be sure to specify BTREE style indexes. See here: http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html