Tag: mysql

Posted on by

MySQL SSD experiments and a request

Open Query too is exploring utilising SSDs in a MySQL infrastructure, but we wouldn’t be us if we didn’t also try some alternative perspective on it. Right now we’re running some comparative tests against various spinning HD setups in the same box, using the same controller, so we’re looking for differences rather than absolute speed.

The results so far are interesting, but the selection of SSDs we have available is limited (never enough toys!) So, a request: do you have an SSD, it’d be great if we could run our test tool on it for a bit. It won’t take long, but naturally the box shouldn’t be used for something else while the test is running. We can either log in remotely, or exchange code and results over email. Simply contact us through our site’s contact form, and we’ll sort things out! Thanks.

If you work for a vendor and would like to have your gear put through a bit of real world stress, please let us know also. Our reference architecture will definitely contain brand/model information as the performance and other aspects of SSDs varies widely.

Posted on by
Posted on by — 1 Comment

MySQL Replication Heartbeat

Well isn’t that interesting, hidden all the way at the end of the MySQL 5.4 information are two words that really peaked my interest: Replication Heartbeat. And it wasn’t even using caps or other highlighting in the original text. Reading through the feature list of 5.4, I’m very impressed. All necessary/useful stuff for the real world, no marketing or enterprise blah.

Of course we’ll have to explore it in detail to have more opinion. Proof is in production, not paper. As this is the first most of us have heard/seen of it, it’ll take time to explore. Someone who tried to install the tarball this morning got an assertion during the system table installation. That’s not the best first impression, but that might be a build issue. I’m really pretty excited about the lineup of actual useful features.

Update… ok so at the end of the announcement it refers to a Preview Release (Edwin, can you please murder this ghastly oxymoronic abomination in OSS Marketing?) and then a note that apart from some InnoDB performance patches the described enhancements are not actually in the version that was just made available. There will be future announcements/releases. Was I just giving compliments for actual delivery, and it turns out it’s vaporware after all. Aaargh!!!

So, we’ll be perusing https://launchpad.net/mysql-server/5.4 instead. The old “show us the code” works best. Let’s hope the code is actually there.

Posted on by — 1 Comment
Posted on by

Move from LiveJournal to Open Query blog

I’m shifting away from LiveJournal. It lacks ability to search and otherwise peruse archived blog posts. And of course it’s only me, while Open Query has more people.

From now on the posts will be at https://openquery.com.au/blog/ and this is aggregated to Planet MySQL as a group blog. You may have already seen Walter posting from his seat at the MySQL Conf. All posts and comments from my LJ blog have been migrated to WordPress, thanks to magic performed by young Akash Mehta. Unfortunately the comment threading can’t be exported.

The full export means that my personal posts are now also present at Open Query, although I may move those elsewhere later. The existing blog entries on LJ will stay for a while at least, although I do have to pay for the LiveJournal subdomain to keep the URLs alive.

A little sidenote on the export… it actually to considerable effort and script hacking and possibly even some screen scraping. Consider this is my own data… Remember, LJ used to have a perfect export, which actually enticed one to stay put. Funny, isn’t it. But that was in the Danga days, and we’re two owners down the line from that (SixApart and then the spinoff-sale to the Russians). Lock-in makes people want to leave more.

Posted on by
Posted on by

MySQL conference ’09 Keynote: “State of the Dolphin” by Karen Padir

Here is a semi-liveblog from the opening keynote of the 2009 MySQL Conference & Expo on April 21st in Santa Clara, California. Karen Padir presents “State of the Dolphin”.

The room is big, with 2 screens on each side of the central stage. It seems to be completely filling up, but I got a good spot on the second row in the front. Directly around me I see Dups, Sheeri, Giuseppe, Kai, Mark Callaghan and many other faces I know, even they don’t necessarily know me 🙂

Opening music, fancy animations. Quotes from satisfied customers, combined with tribal-style music and statistics.

Colin Charles kicks off. Good morning, welcome. learn a lot, provide feedback. Thanking sponsors in the first minute..

Introduces Karen Padir, she promised to bring more women to the conference next year.

Welcome, she introduces herself, tells where she is from and what she did in her education. Jokes: “Odds are good but the goods are odd” about sun’s crowd.
Went to Redhat for a year in between working for Sun, because of opensource interest. She came back to Sun because of Sun’s OSS commitment. Shows a slide with a bunch of different Open Source-ish projects at Sun.
She worked with Glassfish for a long time, encourages people to go by them at the expo hall.

Now, on Oracle. Talks about Oracles commitment to opensource. names two examples of sideline involvement of Oracle in Sun projects. Doesn’t really come across, but might be me.

Everyone uses MySQL everyday, whether on Facebook, Google or whatever.
MySQL had a great year! some stats fly by. 5.1 has gained momentum: 3+M downloads in 100 days. 54% of users have 5.1 in production.

Sheet: What I’ve heard. Waht she has learned in the past 2 months since she has been on the job.

MySQL 5.4 is released today. What happened to 5.2 and 5.3 http://www.mysql.com/5.4
– innodb scalability: up to 16 way x86
– subquery optimizations
– new query algorithms

Some stats fly by for InnoDb that look promising, but i wonder what will hapopen in real life.

Introduces Ken Jacobs. Ken starts talkng about where the improvements of 59% come from: opensource ecosystem.
5.4 will have more features in teh near future. Fast online index creation, compressed table storage. Will be intorduced in the next weeks.
Ken announces new product: embedded InnoDb. low level non-sql api.
karen gives him acquirer of teh year award, with a bottle of vodka. Whoever comes with 9 billion next year will win then 🙂

Karen takes over again, introduces MySQL cluster 7.0. It disappears before I can look at the slide, apparently not that important 🙂
Next: MySQL query analyser. She asks who uses it. very few hands. Pulls mark Matthews and Gary Whizin up on the stage with a joke. They work for MySQL Monitor engineering team.
They tell a story about what happens when you have a slow app. The oldway: dig in and have not so much fun. New way: enterprise monitor. Mark showcases the tool by analyzing an off-the -helf CRM, not very interesting.

Karen comes back, asks all former and current MySQL employees to stand up. She thanks them all, quite genuinely. Nice thought 🙂

Community contributions. Says 5.4 is a good example of community contributions. Sounds strange since noone knew it was coming.

commits to monthly updates on all shipping community releases.

Invites Kaj Arno to the stage. Kaj says he wants to have documentation GPL’ed.

Next: MySQL drizzle project: she calls it shepherded by Sun/MySQL.

Next: MySQL awards.
Partner awards: Intel, infobright, lifeboat.
Applications of the year: Zappos.com, Alcatel-lucent and Symantec.
Community: Marc Delisle, Ronald Bradford and Shlomi Noach

Announces the rest of the conference.

Colin Charles announces Mark Callaghan (turns out he was sitting right in front of me 🙂 ) from google. introduces himself.
Talks about adapting MySQL to their needs, easier than making apps adapt to MySQL. Talks about using MySQL at google, cannot name numbers unfortunately.
many primary shards, with many replica’s per shard. They are happy with MySQL.
Tells that he is amazed at how many slaves can be coupled with a master. They found out by trial-and-error.

Thanks, Monty, MySQL people, Heikki Tuuri and company. inspiration provided by Yasufumi and Percona.

History of MySQL at google: first MyISAM and another RDBMS. after that 4.0 and Inno. After mark arrived they upgraded to 5.0.

Talks abotu features of an enterprise DBMS, the problems they overcame, how they test new MySQL builds: stress test, sample workloads, killing off servers, check for compiler warnings and check for data drift (test same workload with old and new build and see if they are the same).

How to deploy: automatically searching aggregate error logs. Keep track of number of crashes on daily basis. Automated removal of machines from service.

How to monitor: user_stats and table_stats patches. Monitor top-down, they use more bash and awk then mysql enterprise monitor 🙂

Talks abotu features they added (and sometimes removed) from/to replication. Crash safe-slaves, Semi-sync replication, global transaction ID’s
Performance features they added. InnoDb improvements, backport connection pool feature from MySQL 6. Stresses they are not the only ones doing it (Percona, Sun, Innodb)

Manageability features they added and other features they added: row-change logging, Flapulator (bash and awk scripts in cron jobs checking for crashes etc.), Online data drift checking.

Tells about the crises they have had and the open problems they have, the runs out of time.

Next it is on to Kickfire, who are a big sponsor. I end my liveblog here 🙂

I will post pictures asapI’ve posted photos, although the camera was pretty crappy, so it might not be very good quality. Donations for a new camera are welcome 😉

Zak’s uploaded the photos for me to his flickr account – view the set here.

Posted on by
Posted on by

Oracle agrees to buy Sun

See Oracle Agrees to Acquire Sun Microsystems (NY Times, 20 April 2009). Wellwell, that’s quite interesting, isn’t it… as to what it means for MySQL, ZFS, OpenSolaris, OpenOffice.org, VirtualBox, Java, and numerous other tidbits, that remains to be seen.

It must be a buzzy kind of day at the MySQL Users Conference 2009, with this news coming in just before the opening… and it’s only been a year since Sun bought MySQL and Jonathan Schwartz did a key note at the conference. I’m sure we’ll hear a lot more from a lot of people, not necessarily content but definitely conjecture and pre-emptive opinions 😉

Posted on by
Posted on by

Open Query at MySQL Users Conference 2009

I’m not personally there this year, but Walter Heck will be. In case you haven’t met Walter yet, photo enclosed 😉

Photo of WalterHe’s a techie, like you, and he’d love to meet you and hear how you’re using MySQL and surrounding technologies and what things might make your life easier in terms of application architecture, development, deployment, maintenance, and so on.

This may or may not fit with the services that Open Query provides, but the key point is to listen, not sell. If there’s a good match, of course that’s fine too!

As a sidenote, just to pre-empt the inevitable question of “Arjen why are you not here?”: flying over to the US has always been fine, but coming back is a pile of grief every time taking multiple weeks to recover. I’ve never had that issue with for instance trips to Europe. That’s not just grief in terms of personal discomfort or lost work time, but it means my daughter really loses out on her papa for about 3 weeks.

Before I made the decision to note make the trip this year, I did enter various items into the session submission process months back – they didn’t get accepted, but I’m not unique in that respect. Many well known speakers from previous years did not get a slot this year. No worries. There’s plenty of things going on in the surroundings, including Sheeri’s game day and community conference and Percona’s performance sessions, as well as all the excellent encounters in the corridors and in the evenings.

Having been absent in 2007 as well, I would like to add a little bit of outside perspective on the event… while for this week everything appears to be focused on the conference (at least when you look at Planet MySQL), most MySQL users around the world will of course not be at the conf, and have the same needs like the rest of the year. I believe it’s really important to tell about insights and developments all year round, as well as being active in the various interaction media (such as forums, user groups and events).

Anyway, have a great time at the conference, and do have a chat with Walter!

Posted on by
Posted on by

InnoDB lock timeout before query execution

I found this yesterday while tracking down a locking issue for a client. They had a connection time out on a lock, but before it times out, SHOW PROCESSLIST had the status ‘statistics’ so it wasn’t actually executing the query yet. So, what was it doing and why did it time out there?

The answer actually was remarkably simple, but I did have to take a peek in the MySQL server source code (horay for Open Source!) The server sets the thd_proc_info to ‘statistics’ when calling the join optimiser, that’s the part of the optimiser that works out the best join order.

A lesser known feature of the join optimiser is that if it works out that only one row can match (lookup on primary or unique key field), it’ll try to retrieve the row. If there’s no match, there’s an “impossible WHERE clause” and essentially the entire query is optimised away and 0 rows returned.

If there is a match, all references to columns in that table can be replaced with the values that were just retrieved, turning them into constants. After all, there’s only one row matching! This can optimise away tables, removing the need to execute some or all joins. You don’t actually see this directly if you do EXPLAIN EXTENDED and then SHOW WARNINGS, because that output is from the parse tree which does not know about the join structure. But what you will see there is that the columns were replaced with constants, so from that you can deduce what’s going on.

For a query
EXPLAIN EXTENDED SELECT name from Country where code='AUS'
you would see access type const in the explain (which is the indicator for this optimisation), and SHOW WARNINGS brings up
select 'Australia' AS `name` from `world`.`country` where 1
so you see the WHERE clause is gone completely and the name column in the SELECT has been fed the value Australia so it’s all constants.

Back to the original issue… if another connection holds an exclusive (write) lock on that particular row, the join optimiser will be waiting for the lock to be released and that’s how you can actually get a lock timeout at this stage. It’s a perfectly normal thing to happen.

Posted on by
Posted on by

The problem with April Fools in the MySQL/web space…

…is that truth is stranger than fiction. Reality does not appear any more plausible than plain nonsense.

We were discussing this yesterday on #ourdelta (Freenode IRC) in the context of How MySQL really executes a query by Baron. Antony Curtis noted that if he’d write a truthful post on that topic, people would think it was made-up regardless of the day of the year!

Another proof of the premise: Baron has now put a giant banner above/below his post, explaining that it was a joke. Apparently that’s necessary?

I tend to come up with neat ideas for April Fools throughout the year, neglect to write them down, and come the day I have a blank. But, given the above, there’s another option: you just write a truthful story, still leaving people wondering whether it’s for real. I reckon the main issue is probably with the rest of the year, where lots of people still write nonsense, and the truth remains weird as usual. At least on April 1st you know to question. I hope.

Posted on by
Posted on by

Predictive caching in a MySQL-backed infrastructure

Sounds a bit far fetched (pun intended ;-), but we’re doing it. This is not inside of the MySQL server, but rather the overall application design. Let me run you through the logic…

Some key aspects to scaling are: not doing unnecessary queries, and caching what you can. Just a quick baseline. The fastest query is the one you don’t do, or the one you’ve already done before – the latter being caching.

A simple yet brilliant example of this is the Youtube trick where a script reads the relay log, converting updates into appropriate selects and running them so that the InnoDB cache will have the blocks in memory when the slave SQL thread executes the actual update. Maatkit now has a tool for this, so it’s publically available. It’s not quite predictive, but it’s a neat trick anyway that sometimes comes in handy. Search engines use similar tricks.

Extending on this, with certain applications you actually tell what is likely to happen next, sometimes for a particular user and often for many users. Individual user behaviour may sometimes appear random, but as a group it can be highly predictable. The analysis needs to be done properly though, otherwise averaging will make certain interesting behavioural patterns disappear.

Anyway, if you can identify these patterns you can take appropriate measures, such as do some queries so they get cached, and/or schedule other relevant actions (so it’s more than just caching, but it’s a reasonably suitable name anyway). This allows the app to deal with higher peak load, as well as improving response time for individual user.

I might do a talk or article on the predictive caching concept some time, as I appreciate that the short description may appear a bit abstract or obscure. But I assure you it’s entirely practical and real.

It’s one example of how Open Query helps its clients scale well, by design. We focus on preventing emergencies, which includes not just scenarios where stuff fails (and does a safe failover), but also the “oh dear we suddenly have so many more users than a minute ago” type of happening, which should actually be an occasion to enjoy, not stress about.

Posted on by