Storing InnoDB tables on RAM disk

MySQL & MariaDB Other MySQL® Questions
#1

Greetings

Currently I’m using MySQL 5.0 and InnoDB to store small database (1.1-1.8GB). I need extremely fast read access and moderate insert/update speed. On my dedicated server there is 4 GB RAM and I use 2GB as RAM drive where InnoDB bases are stored. Also InnoDB memory usage is set to minimal settings and 128MB log file (also placed on RAM disk).
Such combination gives me required speed but limits database size and requires extended backup features, etc.
My question is simple - is it possible to implement similar performance without RAM drive, just giving MySQL RAM that is now wasted on virtual drive.
Please, make comparative tests and post the results instead of just telling to “increase log size, may be it would help”.
Russian version of my question can be found here: http://www.mysqlperformanceblog.com/about/#comment-178034

#2

It depends.
As I understand it, MySQL caches query results, not data itself. So if you have repeated similar queries, maybe increasing memory size for query cache will help. If queries are all different, RAM disk is better. BTW, you can also use in-memory tables.

If you need fast SELECTs, why are you using InnoDB, not MyISAM ?

#3

All queries are different and retrieved data is cached on app side, so we don’t need to increase query cache. I’ve tried increasing it but nothing happened.
About InnoDB vs MyISAM. I still think that InnoDB is somewhat faster in complex queries and it give better result on my 4-CPU server with multi-threaded client applications.

#4

How did you really end up with the RAM disk solution?

Yes, MySQL has a query cache that caches the results of queries and uses a hashing mechanism to return the result without the need to actually execute the query again.

But InnoDB also has a caching to avoid disk reads and that is what the innodb_buffer_size variable is controlling.
Recommended is that you set this to about 80% of the available RAM on a dedicated server.
And if data in the tables are smaller than this MySQL won’t even touch the disk and perform all queries internally in RAM.

Writes is another matter.
Here you have the flushing of log to disk after each query(commit) that slows things down. Which is needed to be ACID compliant.
But that is just a default setting.

If you need more speed and are prepared to sacrifice a bit for it then you set:
innodb_flush_log_at_trx_commit = 0

Which configures InnoDB to not perform the flushing of the log after each write and that speeds things up significantly.

#5

innodb_flush_log_at_trx_commit = 0
already used.
You see, I’m not going to make experiments on working server just because “setting cache size to 80% RAM would help”, etc. May be someone could test different options and configurations to compare performance difference to RAM disk solution.
RAM drive solution came from using Firebird - with DB on RAM disk it really rocks on select operations.

#6
[B]Nomad wrote on Wed, 17 October 2007 22:00[/B]
innodb_flush_log_at_trx_commit = 0 already used.
Good
[B]Nomad wrote on Wed, 17 October 2007 22:00[/B]

You see, I’m not going to make experiments on working server just because “setting cache size to 80% RAM would help”, etc.

Well the whole reason why the innodb_buffer_pool exist in the first place is to cache data to avoid the disk reads which is what you want to avoid. And it is the single most important configuration variable to get speed out of a normal InnoDB database setup.
[B]Nomad wrote on Wed, 17 October 2007 22:00[/B]
May be someone could test different options and configurations to compare performance difference to RAM disk solution.
You are free to perform the homework.
[B]Nomad wrote on Wed, 17 October 2007 22:00[/B]

RAM drive solution came from using Firebird - with DB on RAM disk it really rocks on select operations.

That is actually a bit surprising because the OS cache usually does a very good job avoiding disk reads and on cache aggressive OS like Linux the speed difference is about 5%. And 5% penalty compared to the flexibility it offers is usually a very easy choice.

The only time when there really is a difference is if an application is using fsync like in the innodb_flush_log_at_trx_commit case.
But at some times the HW/OS is faking this anyway and at those cases there isn’t a speed difference anyway.

Out of curiousity, how many queries per second do you have and how many of them are writes?

#7
[B]Nomad wrote on Wed, 17 October 2007 16:00[/B]
May be someone could test different options and configurations to compare performance difference to RAM disk solution.

I’m sorry - do you mean someone would test it using YOUR queries ?

#8
[B]Nomad wrote on Wed, 17 October 2007 07:15[/B]
Greetings

Currently I’m using MySQL 5.0 and InnoDB to store small database (1.1-1.8GB).

First of all, if you use in-memory db, you don’t really need innodb because the most useful feature there is reliability and in your case it has no advantages at all. So, I’d suggest you to try myisam tables (they’d use less “disk” space because of compression) or MEMORY(HEAP) storage engine, which is in-memory by default.

As for tests, your approach is REALLY strange and I personally don’t see any reasons to test such configuration because even if we’d be able to show that it is really fast, no one would use such approach because mysql has its own in-memory db implementation for such needs.

#9
[B]scoundrel wrote on Fri, 19 October 2007 00:58[/B]

First of all, if you use in-memory db, you don’t really need innodb because the most useful feature there is reliability and in your case it has no advantages at all.

Earlier he explained his reasons for using InnoDB.

[B]scoundrel wrote on Fri, 19 October 2007 00:58[/B]

As for tests, your approach is REALLY strange and I personally don’t see any reasons to test such configuration because even if we’d be able to show that it is really fast, no one would use such approach because mysql has its own in-memory db implementation for such needs.

The only reason for using RAM disc instead of in-memory tables
I see is faster warm-up. Initialization. Data load. In case of
memory table there should be a great number of insert statements
(even if packed into one dump file). In case of RAM disc just copy few files.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.