Thanks for this. Very useful. In particular your idea of using a RAM disk sounds interesting but I am not sure how you imagine I use it? I presume that on power down everythign on a ramdisk is lost so on every power=up I would have to put my data directory on the RAM-disk while the settings in mysql have the path to the data directory on the RAM-disk as the datadir?
Would there be any advice on how to set up the tables for maximum performance given the usage specifications as described in the earlier mail? Regards, Roland On Sunday 17 October 2004 17:50, Michael Stassen wrote: > Those 2 variables are explained in the manual > <http://dev.mysql.com/doc/mysql/en/Server_system_variables.html>. > > flush > This is ON if you have started mysqld with the --flush option. This > variable was added in MySQL 3.22.9. > > flush_time > If this is set to a non-zero value, all tables will be closed every > flush_time seconds to free up resources and sync unflushed data to > disk. We recommend this option only on Windows 9x or Me, or on systems with > minimal resources available. This variable was added in MySQL 3.22.18. > > The --flush startup option is also explained in the manual > <http://dev.mysql.com/doc/mysql/en/Server_options.html>. > > --flush > Flush all changes to disk after each SQL statement. Normally MySQL > does a write of all changes to disk only after each SQL statement and lets > the operating system handle the synching to disk... > > Both seem to do the opposite of what you want. > > Let me start by saying that I've never done anything like what you are > trying, so you may get better advice from others. That said, I'm not > surprised you're having trouble finding a way to get mysql to do what you > want, because some of your requirements (data not written to disk, loss of > data OK) are the exact oppposite of what one usually looks for in a db and > what mysql was designed for. > > Note also that the question of writes is not just a matter of tables. > Changing data often involves updating indexes, which are also written to > disk. > > I'm wondering if the description of --flush doesn't hold the key, "Normally > MySQL does a write of all changes to disk only after each SQL statement and > lets the operating system handle the synching to disk." This led me to > think an alternative might be to solve this at the OS/filesystem level, > instead of in mysql. That is, find an OS/filesystem combination which > behaves as you require: writes in memory, flush to disk only when asked. > This, in turn, led me to wonder if you've considered putting the db > (mysql's data directory) on a RAM disk, which you back up to flashdisk as > required. In the event of a power failure, you would restore the RAM disk > from the flashdisk and continue. In that case, no modifications to mysql > would be required. > > Michael > > roland wrote: > > Hello, > > > > I am using a MySQL database for a somewhat odd application. In this > > application I will have relatively small tables and relatively few > > tables. > > > > I would like to set the variables so that all the operations on the > > database takes place in RAM and that it does not write to disk unless > > explicily given a command to do so. Both on select and update operations > > which will be what most operations will consist of. the reason for this > > is firstly speed but almost more importantly I am using flashdisk which > > has a limited life (baout 2000000) write actions and my application will > > performing update operations on a single entry at anything up to 1000 > > times per second. It is absolutely not critical should I lose the values > > in case of a powerdown. In fact it would suffice to not use the tables on > > the disk at all except to provide default values. > > > > From the documentation I gathered that MySQL refers to writing to disk as > > a flush operation. Doing a SHOW VARIABLES reveals that there are > > variables called > > > > flush OFF > > flush_delay 0 > > > > Since I have made no modifications yet these are default values. I must > > misunderstanding something because this would imply that automatic > > writing to disk is already inhibited and that seems unlikely. > > > > My question is thus whether and if yes how i can control (minimize at > > least) the writing to disk. > > > > Also given the smallness of my tables what would be the best way of > > setting up the database (what table type?) for maximum performance? > > > > Thanks for any help. > > > > Regards, > > > > Roland. -- -"I have not failed. I've just found 10,000 ways that won't work." -(Thomas Alva Edison 1847-1931) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
