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:[EMAIL PROTECTED] Sent: Jul 30, 2004 7:38 AM To: [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]