Hi!
I already found an easy solution. Write to the file in 16 MB chunks
instead of 1 MB chunks. Then I got 5 MB/s on our Linux Xeon.
I do not think MyISAM uses fsync at all. I need fsync because in
recovery I must know that some database pages have been cleanly written
on disk.
These fsync things require experiments because some OSs may have
very poor implementation. On Windows NT I use non-buffered i/o,
and have had no performance problems.
Regards,
Heikki
At 04:42 PM 5/16/01 +0200, you wrote:
>What is the possibility of doing a quick test at startup of the mysql to
see which is quicker and then use the quickest method? Or make it configurable?
>
>What does MyISAM use?
>
>--
>Richard Ellerbrock
>[EMAIL PROTECTED]
>
>>>> Heikki Tuuri <[EMAIL PROTECTED]> 2001/05/15 07:43:23 >>>
>Richard,
>
>I made some measurements with fsync. If I create first the big data file
>without O_SYNC and after that make a single fsync, I get a write speed
>of 5 MB/s, which is 5 times more than with O_SYNC.
>
>On the other hand, opening a log file in the O_SYNC mode makes commits
>much faster than by calling a separate fsync after each write.
>
>I will change the initial file writing so that it first opens the file
>without O_SYNC, the does the writes, calls fsync, closes the file, and
>then opens the file with O_SYNC.
>
>These are Linux-specific optimizations. There is no real reason
>why the OS should do writes faster with these tricks. I realize that
>Linux developers have not got enough feedback from database implementors.
>
>Regards,
>
>Heikki
>
>>Richard,
>
>>thank you for the observations. Some comments:
>
>>>Just installed MaxSQL 3.23.38 on a test system to check out InnoDB. Here
>are my
>>>observations:
>>>
>>>1) Manual is not clear on permissions of directories - They should be
>mysql.root
>>>owned. The error in the error log just says cannot create file. This could
>be one
>>>of many things! I had to use my initiative, but newbie user will never
>pick this
>>>up.
>
>>Ok, I promised in a posting an hour ago that InnoDB will print the
>>OS error number in future. I will also update the manual.
>
>>>2) The error logs format is not consistent across InnoDB and normal Mysql:
>>>010514 17:11:32 mysqld started
>>>InnoDB: Error in creating or opening /data/ibdata/ibdata1
>>>InnoDB: Could not open data files010514 17:11:33 Can't init databases
>>>010514 17:11:33 mysqld ended
>>>This makes it almost impossible to do something useful (like parse) the
>log file.
>>>
>
>>Many people wish that it would print the time before the error message.
>>It is on the TODO list :).
>
>>3) Initial data file creation is VERY slow. I am still waiting after 15
>minutes
>>for the first file to complete - It is only 606megs now. I have two files.
>What
>>happens if you run out of table space and quickly need to create more
>extents? Shutting
>>down the database does not sound like a good option in a mission critical
>24/7 environment.
>>This is on a linux Redhat 6.2 (latest patches) box with Raid 5 disks on an
>IBM Serveraid
>>controller.
>
>>I am just running on our Linux Xeon, and the the initial writing to a file
>>proceeds only 1 MB/second. It seems to have slowed down a lot when
>>I added the fsync to all file writes in .38. It writes 1 MB chunks to the
>>file at a time, and there is no reason why it should be so slow. I guess
>>that the problem is in Linux. I have to do so that in the initial writing
>>fsync is not used, but the file is closed, and opened again. Or we could
>>try writing smaller chunks than 1 MB.
>
>>Maybe we should contact Linux developers. fsync should not cause
>>these kinds of problems.
>
>>4) Ok, datafiles created overnight. Now trying alter table ... type=innodb
>on a
>>file with 1.2mil records. Still waiting after about 12 hours. Dumping files
>this
>>size is going to be problematic. Machine pegs with load of around 12! I
>suggest
>>LOTS more testing on BIG tables. Small stuff normally works quite well, but
>HUGE
>>tables is where it counts.Thanks for the help.
>
>>It sounds like that the operation is disk bound. What is the CPU and
>>disk load?
>
>>How big is the table in MBs? What kind of indexes you have?
>>How much buffer pool you have configured in my.cnf?
>
>>PS: I am willing to test stuff on my development machine.
>
>>Good :).
>
>>Best regards,
>
>>Heikki
>>http://www.innobase.fi
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
