Editing mySql code so it won\'t write HD

Anon

I am in the process of trying to create a mud. From what I found mySql uses hash tables, which makes it very, very fast, however the fact that it has to write to the database every time it writes to it, will make it very very slow (if it\'s doing it oh say a few hundred times a second). So I thought of doing something totally different.

I would have to different sql installations. one that only exists in memory, the other which exists on HD. When the server boots up, everything which is used very actively in the world (people, monsters, ect..) will be loaded into the sql database which resides in memory only. When I go to shut down the server, it copies the tables to the database which resides on the HD.

I know that I could tweak the HD so that is uses as much memory as I need, however it will still write to the HD as it keeps it in memory. All I want is to have it stay in memory.

I suppose it would be ok if when I initially create the tables, they are on the HD, but after that, when I add items, they need to stay in memory.

If my computer were to crash or whatnot, I would lose everything in that mysql server, but that would be ok.

Any thoughts on this? I don\'t program in c/c++ though, and I would have a hard time taking out the writing to HD code, so if someone can do that and send me the recompiled executable, that would be great.

I imagine it would only be a line or two of code?
Thanks

Anon

Easy, if you are using a Linux server (I guess other Unices would work too...), just create a decent sized ramdisk, and then move your database files to it. Next, create a symlink in the mysql directory in /home/ and point it to your ramdrive. You could also set up an hourly Cron job to back up the state of the ram drive back to HD. Add a script to your init.d to automatically copy the last good state of the database into ram before you start mysql.

Anon

Here comes the flames.

Sounds kinda like a bad idea.

If you are keeping everything in memory until you shutdown your "Ram DB server", and MySQL or your server crashes, you lose all recently inserted data. You said that would be OK. That is unacceptable. To say that you improved MySQL performance, but at the cost of possibly losing data is no improvement. That one fact alone makes your idea REALLY BAD.

Secondly, to say the MySQL becomes "very,very slow" when making a few hundred inserts a second is ridiculous. A properly configured and properly maintained MySQL database coupled with good hardware will hold its own. The product is a viable database suited for serious development, something that you probably never do.

Thirdly, you think that this would only take "a line or two of code" added to MySQL source to implement. Sounds a little naive.

Anon

Have you researched this at all? MySQL supports an in memory table, check out HEAP tables.

Another thing you might want to do, is test the preformance and see if you really need to use a non HD solution.

Anon

Hmm, you guys gave some good tips. As for J. Hilton, you have some good points, however I think that you need to help, instead of just flame 🙁 Point me to some resources that inforce what you are telling me.

Your post was not there to help me, it was there to flame me. You can argue with me and say you were helping me by flaming me, when really you offered no other help, you just flamed away...

It never occurred to me to use a ram drive, which should have been totally obvious, I am going to look into that. Thanks a ton for the help though Jesse and Jason, you did help open my eyes to good solutions.

Anon

No problem. As with most things in a *nix environment, there is more than one way to accomplish something. I am personally unfirmiliar with the HEAP functions of MySQL, however it seems that a ramdrive would be the easiest way to go. Especially for backups.

I have used symlinks in the Mysql dir to move databases to users home directory for the purposes of managing the disk space they use, and it worked perfectly. As long as your ramdrive is large enough, there shouldn't be a problem with storing a db there.