Re: mysql using aio/raw device on linux
- 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=arch...@jab.org
Re: Need help with query
Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP
Re: Need help with query
What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php. PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query
First I was thinking there is function IN ALL or something like that, since there are functions IN and EXISTS. And I would be able to make a query something like this select distinct org_id, item_id from orders where item_id in all (34, 36, 58, 63) order by org_id asc But, there isn't any. :-( The correct query is select r.org_id from ( select distinct a.org_id, a.item_id from orders a where a.item_id in (34, 36, 58, 63) order by a.org_id asc ) r group by r.org_id having count(*) = 4 On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote: What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php . PB --- On 3/17/2011 12:00 PM, LAMP wrote: Yes, that was my question. Though, since English is not my first language, let me try to post it again: There is a list of all orgs and items org bough, from table called orders item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 select org_id from orders where item_id in (34. 36. 58. 63) will give me a result 5 13 15 28 1520 1649 2607 7295 8871 8934 This is the list of ALL orgs they bought ANY of items (34. 36. 58. 63). Agree? What I need is a list of orgs they bought all of items 34, 36, 58, 63. every of them. Result should be only orgs 2607 and 1520. I hope it's more clear now. On Mar 15, 2011, at 10:47 PM, Rhino wrote: Your original question said: Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. That's the question I answered with my suggested query. It sounds like that is not what you meant after all but I'm not sure what you DO want with your query. Why are 2607 and 1520 the only right answers? Because they are. I look at the database and manually found the result I have to get. What's wrong with my statement? Based on your own query, it looks like you only want an org_id for item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of that org_id amongst the desired item_ids actually, there is mistake in my query, it should say having count(org_id) = 4 and, yes, that's what I want. I can get the correct list using the query I posted but I was hoping there is BETTER way. but that wasn't in your statement of the problem. So please clarify EXACTLY what you want. Giving an incomplete or contradictory description of you want only wastes both your time and mine. As I stated earlier, English is not my first language and I was trying to do my best. Sorry for confusing you. -- Rhino On 2011-03-15 20:35, LAMP wrote: On Mar 15, 2011, at 6:18 PM, Rhino wrote: All you should need is this: select distinct org_id from orders where item_id in (34, 36, 58, 63) I'm assuming that the DISTINCT operator is available in the version of MySQL that you are using. I don't currently have any version of MySQL installed so I can't try this myself to be sure it works in your version of MySQL. -- Rhino your query will give me every org_id that has ANY of item_id., I need org_id that has ALL of item_id. right? result would be 2607 1520 8934 7295 1649 8871 On 2011-03-15 18:51, LAMP wrote: Hi, I need a help to build a query. Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( `item_id` int, `org_id` int, ) ENGINE=MyISAM item_idorg_id 342607 342607 341520 362607 361520 368934 3828 3815 385 3813 582607 582607 587295 581649 587295 581520 632607 632607 638871 637295 631520 6515 6520 951520 951520 957295 981520 987295 Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 but, I'm sure there is better solution? Thanks for any help. LAMP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql using aio/raw device on linux
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=arch...@jab.org
Re: mysql using aio/raw device on linux
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, 0x7FFFC914, 1, 0x7FFFC900) = 0 /1: kaio(AIOWRITE, 261, 0x390D3CE00, 8704, 0x0F5FB0007BB2B218) = 0 /1: kaio(AIOWRITE, 261, 0x390C8, 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
Re: mysql using aio/raw device on linux
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, 0x7FFFC914, 1, 0x7FFFC900) = 0 /1: kaio(AIOWRITE, 261, 0x390D3CE00, 8704, 0x0F5FB0007BB2B218) = 0 /1: kaio(AIOWRITE, 261, 0x390C8, 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
Re: mysql using aio/raw device on linux
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, 0x7FFFC914, 1, 0x7FFFC900) = 0 /1: kaio(AIOWRITE, 261, 0x390D3CE00, 8704, 0x0F5FB0007BB2B218) = 0 /1: kaio(AIOWRITE, 261, 0x390C8, 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
