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
