Just my two cents. That's why it is Oracle. Oracle is (almost) an operating system, with its advanced implementation of device/file system management, up to a logical volume management just consider ASM for example. MySQL is quite simpler.
May be Oracle gurus could bring some key benefit to MySQL now removing some historical bottlenecks. Cheers Claudio 2011/3/18 Zhu,Chao <zhuc...@gmail.com> > Thanks Guys; > The reason I was seeking RAW/AIO, is mostly about non-blocking write; > Which i mean: > Even though single write is not faster on RAW, if it supports raw and > Asynch IO write, then MySQL can continue to submit write request to disk > without waiting for the previous write to complete, and then submit a > second > write request; > In this case, the commit(write throughput) can be enhanced greatly, > without > blocking /keeping user wait; In our current test, we are using SAN with > huge cache and each single write only takes 0.3ms(yes very fast, close to > ramdisk i guess); But the sequential/blocking fsync call is the bottleneck: > and it can't be parallized; > > That's the reason i was seeking for such option; > > I was an oracle DBA before and oracle has such kind of capability(aio > write) > so lgwr can have very high throughput(tens of thousands of commit per > second, and it does group commit); > > Sample Trace in Unix/Oracle lgwr: > /1: semtimedop(105, 0xFFFFFFFF7FFFC914, 1, 0xFFFFFFFF7FFFC900) = 0 > /1: kaio(AIOWRITE, 261, 0x390D3CE00, 8704, 0x0F5FB0007BB2B218) = 0 > /1: kaio(AIOWRITE, 261, 0x390C80000, 253952, 0x0F5FD2007BB2B4A8) = 0 > /1: kaio(AIOWRITE, 261, 0x390D60400, 211456, 0x0F63B2007BB2B738) = 0 > /1: kaio(AIOWRITE, 261, 0x390E8EC00, 182272, 0x0F66EC007BB2B9C8) = 0 > /1: kaio(AIOWRITE, 261, 0x390F10A00, 230912, 0x0F69B4007BB2BC58) = 0 > /1: kaio(AIOWRITE, 261, 0x391024A00, 91648, 0x0F6D3A007BB2BEE8) = 0 > > Thx > > > On Fri, Mar 18, 2011 at 6:00 AM, Karen Abgarian <a...@apple.com> wrote: > > > Hi, > > > > For the actual question, I agree with the points Johan mentioned. > MySQL, > > to my knowledge, does not have an option to use raw devices for binary > logs. > > Even if it had it, it would not have the benefits Chao is seeking. > There > > is indeed a tradeoff between losing transactions and performance. If > the > > goal is performance, the raw device would be slower since every write > would > > have to actually complete, instead of leaving the block in the OS cache. > > The best is probably achieved by the battery backed cache: the server > could > > be configured to not lose transactions and at the same time perform the > work > > fast. > > > > For the question of tweaking the sync_binlog, I find difficult to use > > values other than 0 and 1. With 0, it just ignores fsyncs, and the > amount > > of transactions lost is at the mercy of OS cache. With 1, all > transactions > > will always be on disk before returning to the user. I cannot make sense > > out of the doco's remark about that this would lose 'at most one > > transaction' and I assume it is a mistake. > > > > With the value of 10, say, however, what I expect to happen, is the > server > > will attempt to do fsync every 10 statements. Say 10 transactions are > in > > the binary log buffer, and the server does an fsync. What is to happen > with > > the other transactions that keep coming? If they commit in memory and > > return, the statement that sync_binlog syncs every 10 transactions is > false. > > If they wait, the wait would be as large as the wait for the disk write > > and the result is that all transactions will be waiting for disk writes. > > > > If somebody can shed more light on this, I would like to hear it. > > > > Tx > > Karen. > > > > > > On Mar 17, 2011, at 12:14 AM, Johan De Meersman wrote: > > > > > ----- Original Message ----- > > >> From: "Chao Zhu" <zhuc...@gmail.com> > > >> > > >> One Q: Can mysql binlog use raw device on Linux? > > > > > > Mmm, good question. Don't really know; but I'm not convinced you'll get > > huge benefits from it, either. Modern filesystems tend to perform pretty > > close to raw throughput. > > > > > > From a just-thinking-it-through point of view, I'd guess no - mysqld > > never seems to open binlogs for append, it always opens a new one. This > may > > have something to do with the way replication works; not to mention the > > question of what'll happen if the log is full - it's not a circular > buffer. > > > > > >> Can we use asynch IO for binlog writing? sequential non-qio fsync is > > slowing our throughput... > > > > > > Mmm... Theoretically, yes, you could use an async device (even nfs over > > UDP if you're so inclined) but async means that you're going to be losing > > some transactions if the server crashes. > > > > > > You can also tweak > > > http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#sysvar_sync_binlog-basically, > this controls how often the binlog fsyncs. Same caveat applies, > > obviously: set this to ten, and you'll have ten times less fsyncs, but > you > > risk losing ten transactions in a crash. > > > > > > If your binlogs are async, then you also risk having slaves out of sync > > if your master crashes. > > > > > > > > > Personally, if your binlogs are slowing you down, I would recommend > > putting them on faster storage. Multiple small, fast disks in RAID10 are > > going to be very fast, or you could invest in solid state disks - not all > > that expensive anymore, really. Maybe even just a RAM disk - you'll lose > > data when the machine crashes (and need an initscript for save/load of > the > > data on that disk), but not if just the mysqld crashes. > > > > > > > > > Weigh the benefits of each option very, very carefully against the risk > > of losing data before you go through with this. > > > > > > > > > -- > > > Bier met grenadyn > > > Is als mosterd by den wyn > > > Sy die't drinkt, is eene kwezel > > > Hy die't drinkt, is ras een ezel > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/mysql?unsub=a...@apple.com > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=zhuc...@gmail.com > > > > > > > -- > Regards > Zhu Chao > -- Claudio
