Re: mysql using aio/raw device on linux
Hi... If we are to compare MySQL/binlog with Oracle's redo log, there are some known differences. First of all, the (somewhat) equivalent structure in Oracle for the MySQL's binary log is not the redo log the log writer is writing to. It is the archived log. The function of those is also different. The redo log is needed for crash recovery. The archived log is needed for media recovery. Second, In Oracle, transactions always have to wait for commits. Taking that as the starting point, the optimizations for writing the redo are put in place. The approach is therefore "we have to write, so let us optimize it". As can be seen, it does not make sense to optimize redo writing unless we are targeting zero transaction loss. There is a large difference between zero and "some" in this context. So, if we ever wanted to optimize the mysql's writing to binary logs, it is unclear what we would do that for. It cannot be protection against server crashes, because that could be resolved by using InnoDB or any other transactional storage engine. It is not protection against media failures, because a loss of the binary log because of media failures is equivalent to losing the archived log in Oracle for the same reasons. The only thing that is somewhat impacted is replication on host crashes. However, if we wanted to be precise on this, we would notice that similarly to losing the transactions in the OS cache, we could also lose updates to the MySQL tables at the same time. Which means that on server crashes the primary servers should be abandoned and the service be switched to replica. If this is done, it does not matter that some transactions are lost (as it does not when the same thing is done in Oracle, during primary media failures and provided it is not configured to not lose data between primary and standby). Tx Karen. On Mar 17, 2011, at 7:15 PM, Zhu,Chao wrote: > 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 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,
Re: Increase for 1 using REPLACE function
2011/03/18 17:24 -0500, Afan Pasalic I have to increase 'no_of_visits' for 1. Using UPDATE function is easy: update visits set no_of_visits=no_of_visits+1 where visitor_id=123 but, how it should be (if possible at all) if I want to use REPLACE function? I tried something similar replace into visitors values ($visitor_id, (no_of_visits+1)) but it doesn't work?!? Of course; the MySQL "REPLACE" command is not meant for that. It is simply the same as "INSERT" unless the table has a key, also given in the transaction. "UPDATE" is the right command for this. -- 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
2011/03/18 08:49 -0500, LAMP Is here anybody from mysql development team, to suggest to build IN ALL function? There is a problem here: the basic operation is on the record, each record by each record, all by itself. The solution to your problem entails acting on more distinct records until enough have been encountered. If you imagine the table input to a program that checks for hits, you will see the problem. The program reads its input, for every number of the four that you want matched it holds on to its mate until that mate is matched with all four of the chosen. It is a global condition, and SQL works one record at a time. Global conditions are detected only through the summary functions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Increase for 1 using REPLACE function
I have to increase 'no_of_visits' for 1. Using UPDATE function is easy: update visits set no_of_visits=no_of_visits+1 where visitor_id=123 but, how it should be (if possible at all) if I want to use REPLACE function? I tried something similar replace into visitors values ($visitor_id, (no_of_visits+1)) but it doesn't work?!? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Surge 2011 Conference CFP
We are excited to announce Surge 2011, the Scalability and Performance Conference, to be held in Baltimore on Sept 28-30, 2011. The event focuses on case studies that demonstrate successes (and failures) in Web applications and Internet architectures. This year, we're adding Hack Day on September 28th. The inaugural, 2010 conference (http://omniti.com/surge/2010) was a smashing success and we are currently accepting submissions for papers through April 3rd. You can find more information about topics online: http://omniti.com/surge/2011 2010 attendees compared Surge to the early days of Velocity, and our speakers received 3.5-4 out of 4 stars for quality of presentation and quality of content! Nearly 90% of first-year attendees are planning to come again in 2011. For more information about the CFP or sponsorship of the event, please contact us at surge (AT) omniti (DOT) com. -- Katherine Jeschke Marketing Director OmniTI Computer Consulting, Inc. 7070 Samuel Morse Drive, Ste.150 Columbia, MD 21046 O: 410/872-4910, 222 C: 443/643-6140 omniti.com circonus.com
Re: Need help with query
On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote: Indeed, I don't thing there is. Just be sure that each record has an unique combination of org_id and item_id, otherwise you might end up with an org_id that, for example, references 4 times item_id 34 in 4 different records, but no other item_ids. This is obvisouly not what you want. Geert-Jan Correct. That's why I use "select distinct org_id, item_id" in sub- query. Is here anybody from mysql development team, to suggest to build IN ALL function? :-) 2011/3/17 LAMP 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 or
