List:General Discussion« Previous MessageNext Message »
From:mos Date:July 30 2004 5:23pm
Subject:RE: Installing MySQL Databases on RAM Drive
View as plain text  
Stephen,

At 11:16 AM 7/30/2004, Stephen Rasku wrote:
>Flash drives have a limited number of writes that can be done before the
>drive starts to fail:
>
>         http://www.kingston.com/tools/bits/bit17.asp
>         http://www.diskonkey.com/documents/Performance_reliability.pdf
>         (Look under "Flash Reliability")

Thanks for the info. I learned something today.<g>


>If I can write the database to a RAM disk and just archive to the flash
>drive periodically, I can greatly reduce the number of writes to the
>flash drives and extending its life.  These drives are going into
>transmitter stations in remote locations that are only accessible in the
>summer.  If we have a failure in the winter the system could be unusable
>for 6 months.

Ok, then it makes sense to use compact flash just for backup and not the 
primary drive. But you still have to put the MySQL tempdir someplace and 
that may still get some read/write activity. Of course if you are just 
doing data collection then this should be minimal. MySQL is pretty 
elaborate for something like this. So I'm wondering if MySQL because of its 
disk activity is the right database for the job? There are memory based 
databases that are excellent for this type of application. They are small 
and extremely fast and can load/save the table to disk and you can 
eliminate all disk activity. The one I'm thinking of is called TkbmMemTable 
and runs in Delphi and I think there is a Kylix version for Linux. It has 
built in compression, indexing, blob support, and it is free (they have a 
pay version) and there are no 
royalties.http://www.components4programmers.com/products/kbmmemtable/

This means there is zero disk activity and the "database" (which is a flat 
file system) and it gets compiled into the .exe file so there is a zero 
footprint install. You can then export the data to a CSV file for import 
into another database. It is also possible to access the database over a 
webserver too so you can access the data remotely.  Send me an email if you 
want me to send you a compiled TkbmMemTable demo app if you want to see 
what it can do. (for Windows) -I'm not associated with their company but 
I've been using it for years and it's great.


>The system is protected by a monitored UPS.  If the power fails, part of
>the shutdown procedure would be to save the database to flash.
>
>There is no user per se for this system but there are multiple processes
>accessing the database.  There are actually two databases but one gets
>backed up much more frequently than the other.  The whole operating
>system and application is stored on the flash drive.
>
>HEAP tables might be the solution.  How would I archive these to disk on
>shutdown?

You can use SQL on a heap table like any other MySQL table.
Example:

insert into save_table select * from ram_table;

The ram_table should have the same columns as save_table.
BTW, you can now use a "Create ram_table like save_table".

Mike



>...Stephen
>
>-----Original Message-----
>From: mos [mailto:mos99@stripped]
>Sent: Jul 30, 2004 7:38 AM
>To: mysql@stripped
>Subject: Re: Installing MySQL Databases on RAM Drive
>
>
>At 04:45 PM 7/28/2004, you wrote:
> >Our customers are running MySQL 4.0.17 on QNX 6.2.1.
> >
> >We are currently using a flash drive to store our database but we want
> >to store it in a RAM disk to prolong the life of the drive
>
>Flash drives are solid state (no moving parts) so you can't break them.
>I've never heard of anyone wearing them out. Compact Flash unless they
>are
>too slow or consume too much power, I'd recommend sticking with a flash
>drive. With a RAM drive you need to handle times when the power fails.
>
>Someone else mentioned HEAP tables which solves the speed problem and
>you
>could write everything to flash memory every 5 minutes or so as a back
>up.
>I assume  this is for a single user right? You're not running a database
>
>server off of Compact Flash, right? If so you may want to try the
>embedded
>MySQL version which will speed things up a bit.
>http://dev.mysql.com/doc/mysql/en/libmysqld_overview.html
>
>Mike
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Installing MySQL Databases on RAM DriveStephen Rasku28 Jul
  • Re: Installing MySQL Databases on RAM DriveEgor Egorov29 Jul
    • Re: Installing MySQL Databases on RAM DriveBrent Baisley30 Jul
  • Re: Installing MySQL Databases on RAM Drivemos30 Jul
    • RE: Installing MySQL Databases on RAM DriveStephen Rasku30 Jul
      • RE: Installing MySQL Databases on RAM Drivemos30 Jul
        • RE: Installing MySQL Databases on RAM DriveStephen Rasku30 Jul
    • Re: Installing MySQL Databases on RAM DriveEamon Daly30 Jul