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