"On an instance with 10 of thousands of tables, we are often flushing tables. Occasionally flush tables with read lock to take a cold snapshot for backup purposes etc, this process fails every so often:
pager grep -i flush; is invaluable in those…"
"My pleasure, this was a fun one to diagnose.
Incidentally, I made a minor tweak, calling DISTINCT against ResortID in both the outer select and sub-query as there may be two resorts with the same name and meeting the attribute…"
"Incidentally, I should have written the HAVING clause like this:
, count(r1.ResortID) AS count1
, ResortAttribute ra1
Thanks, that makes sense. If then, the initial query is being run hundreds of times per minute the impact on IO would be remarkable in many cases (multiply that by thousands of tables and 'remarkable' become problematic)."
"Hold on there, you've changed the return set criteria with that join. The last four AttributeID (I HATE camel-hump notation!) in the original query are "OR"s. Making it all one IN with a LEFT JOIN turns all of the "AND"s…"
"I'll make it to the UC next year for sure, if for no other reason than to heckle :P
The Oracle move is for one of 5 product lines I take care of, so I'll definitely still have MySQL around. We've been moving a design that wasn't…"
"Glad to hear you are doing well! The UC is in April next year but the call for papers is going to come out any day now. I see you are have a new role as a developer... why the switch? And, are you converting from MySQL to ORACLE for everything?…"
"I've been great really. Busy as hell with VCLK. We're transitioning a big chunk of data to Oracle. As for the UC, isn't it next April or something? I might be in the bay area for Oracle's Openworld in Oct, but I'm not sure.…"
I imagine you and I could go back and forth all night and day with bad SQL examples...I think we have in fact.
Let's digest this one:
Someone is searching for the resort that includes all of $x amenities and may include $z amenities.…"
Glad to hear you are doing well! The UC is in April next year but the call for papers is going to come out any day now. I see you are have a new role as a developer... why the switch? And, are you converting from MySQL to ORACLE for everything? Sounds like a cool project to be on!
Let me know when you're up in the Bay, we'll do lunch!