It's difficult for me to say whether MyISAM or InnoDB would be faster for you. It will depend on exactly how you'll be using it. "MyISAM tables are really fast for select-heavy loads, while table level locks limit their scalability for write intensive multi-user environments. ... INNODB tables ... have row level locking and consistent reads, which allow to reach excellent read write concurrency." <http://www.mysql.com/news-and-events/newsletter/2002-12/a0000000091.html> You may find Jeremy Zawodny's book, "High Performance MySQL" <http://www.oreilly.com/catalog/hpmysql/> useful.
Michael
roland wrote:
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 (about 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]
