MySQL

Secure your accounts and devices

Xaprb, home of innotop - Wed, 2013-12-18 20:17

This is a public service announcement. Many people I know are not taking important steps necessary to secure their online accounts and devices (computers, cellphones) against malicious people and software. It’s a matter of time before something seriously harmful happens to them.

This blog post will urge you to use higher security than popular advice you’ll hear. It really, really, really is necessary to use strong measures to secure your digital life. The technology being used to attack you is very advanced, operates at a large scale, and you probably stand to lose much more than you realize.

You’re also likely not as good at being secure as you think you are. If you’re like most people, you don’t take some important precautions, and you overestimate the strength and effectiveness of security measures you do use. Password Security

The simplest and most effective way to dramatically boost your online security is use a password storage program, or password safe. You need to stop making passwords you can remember and make long, random passwords on websites. The only practical way to do this is to use a password safe.

Why? Because if you can remember the password, it’s trivially hackable. For example, passwords like 10qp29wo38ei47ru can be broken instantly. Anything you can feasibly remember is just too weak.

And, any rule you set for yourself that requires self-discipline will be violated, because you’re lazy. You need to make security easier so that you automatically do things more securely. A password safe is the best way to do that, by far. A good rule of thumb for most people is that you should not try to know your own passwords, except the password to your password safe. (People with the need to be hyper-secure will take extraordinary measures, but those aren’t practical or necessary for most of us.)

I use 1Password. Others I know of are LastPass and KeePass Password Safe. I personally wouldn’t use any others, because lesser-known ones are more likely to be malware.

It’s easy to share a password safe’s data across devices, and make a backup of it, by using a service such as Dropbox. The password safe’s files are encrypted, so the contents will not be at risk even if the file syncing service is compromised for some reason. (Use a strong password to encrypt your password safe!)

It’s important to note that online passwords are different from the password you use to log into your personal computer. Online passwords are much more exposed to brute-force, large-scale hacking attacks. By contrast, your laptop probably isn’t going to be subjected to a brute-force password cracking attack, because attackers usually need physical access to the computer to do that. This is not a reason to use a weak password for your computer; I’m just trying to illustrate how important it is to use really long, random passwords for websites and other online services, because they are frequent targets of brute-force attacks.

Here are some other important rules for password security.

  • Never use the same password in more than one service or login. If you do, someone who compromises it will be able to compromise other services you use.
  • Set your password generation program (likely part of your password safe) to make long, random passwords with numbers, special characters, and mixed case. I leave mine set to 20 characters by default. If a website won’t accept such a long password I’ll shorten it. For popular websites such as LinkedIn, Facebook, etc I use much longer passwords, 50 characters or more. They are such valuable attack targets that I’m paranoid.
  • Don’t use your web browser’s features for storing passwords and credit cards. Browsers themselves, and their password storage, are the target of many attacks.
  • Never write passwords down on paper, except once. The only paper copy of my passwords is the master password to my computer, password safe, and GPG key. These are in my bank’s safe deposit box, because if something happens to me I don’t want my family to be completely screwed. (I could write another blog post on the need for a will, power of attorney, advance medical directive, etc.)
  • Never treat any account online, no matter how trivial, as “not important enough for a secure password.”

That last item deserves a little story. Ten years ago I didn’t use a password safe, and I treated most websites casually. “Oh, this is just a discussion forum, I don’t care about it.” I used an easy-to-type password for such sites. I used the same one everywhere, and it was a common five-letter English word (not my name, if you’re guessing). Suddenly one day I realized that someone could guess this password easily, log in, change the password and in many cases the email address, and lock me out of my own account. They could then proceed to impersonate me, do illegal and harmful things in my name, etc. Worse, they could go find other places that I had accounts (easy to find — just search Google for my name or username!) and do the same things in many places. I scrambled to find and fix this problem. At the end of it, I realized I had created more than 300 accounts that could have been compromised. Needless to say, I was very, very lucky. My reputation, employment, credit rating, and even my status as a free citizen could have been taken away from me. Don’t let this happen to you! Use Two-Factor Auth

Two-factor authentication (aka 2-step login) is a much stronger mechanism for account security than a password alone. It uses a “second factor” (something you physically possess) in addition to the common “first factor” (something you know — a password) to verify that you are the person authorized to access the account.

Typically, the login process with two-factor authentication looks like this:

  • You enter your username and password.
  • The service sends a text message to your phone. The message contains a 6-digit number.
  • You must enter the number to finish logging in.

With two-factor auth in place, it is very difficult for malicious hackers to access your account, even if they know your password. Two-factor auth is way more secure than other tactics such as long passwords, but it doesn’t mean you shouldn’t also use a password safe and unique, random, non-memorized passwords.

Two-factor auth has a bunch of special ways to handle other common scenarios, such as devices that can’t display the dialog to ask for the 6-digit code, or what if you lose your cellphone, or what if you’re away from your own computer and don’t have your cellphone. Nonetheless, these edge cases are easy to handle. For example, you can get recovery codes for when you lose or don’t have your cellphone. You should store these — where else? — in your password safe.

There seems to be a perception that lots of people think two-factor auth is not convenient. I disagree. I’ve never found it inconvenient, and I use two-factor auth a lot. And I’ve never met these people, whoever they are, who think two-factor auth is such a high burden. The worst thing that happens to me is that I sometimes have to get out of my chair and get my phone from another room to log in.

Unfortunately, most websites don’t support two-factor authentication. Fortunately, many of the most popular and valuable services do, including Facebook, Google, Paypal, Dropbox, LinkedIn, Twitter, and most of the other services that you probably use which are most likely to get compromised. Here is a list of services with two-factor auth, with instructions on how to set it up for each one.

Please enable two-factor authentication if it is supported! I can’t tell you how many of my friends and family have had their Gmail, Facebook, Twitter, and other services compromised. Please don’t let this happen to you! It could do serious harm to you — worse than a stolen credit card. Secure Your Devices

Sooner or later someone is going to get access to one of your devices — tablet, phone, laptop, thumb drive. I’ve never had a phone or laptop lost or stolen myself, but it’s a matter of time. I’ve known a lot of people in this situation. One of my old bosses, for example, forgot a laptop in the seat pocket of an airplane, and someone took it and didn’t return it.

And how many times have you heard about some government worker leaving a laptop at the coffee shop and suddenly millions of people’s Social Security numbers are stolen?

Think about your phone. If someone stole my phone and it weren’t protected, they’d have access to a bunch of my accounts, contact lists, email, and a lot of other stuff I really, really do not want them messing with. If you’re in the majority of people who leave your phone completely unsecured, think about the consequences for a few minutes. Someone getting access to all the data and accounts on your phone could probably ruin your life for a long time if they wanted to.

All of this is easily preventable. Given that one or more of your devices will someday certainly end up in the hands of someone who may have bad intentions, I think it’s only prudent to take some basic measures:

  • Set the device to require a password, lock code, or pattern to be used to unlock it after it goes to sleep, when it’s idle for a bit, or when you first power it on. If someone steals your device, and can access it without entering your password, you’re well and truly screwed.
  • Use full-device encryption. If someone steals your device, for heaven’s sake don’t let them have access to your data. For Mac users, use File Vault under Preferences / Security and Privacy. Encrypt the whole drive, not just the home directory. On Windows, use TrueCrypt, and on Linux, you probably already know what you’re doing.
  • On Android tablets and phones, you can encrypt the entire device. You have to set up a screen lock code first.
  • If you use a thumb drive or external hard drive to transfer files between devices, encrypt it.
  • Encrypt your backup hard drives. Backups are one of the most common ways that data is stolen. (You have backups, right? I could write another entire blog post on backups. Three things are inevitable: death, taxes, and loss of data that you really care about.)
  • Use a service such as Prey Project to let you have at least some basic control over your device if it’s lost or stolen. If you’re using an Android device, set up Android Device Manager so you can track and control your device remotely. I don’t know if there’s anything similar for Apple devices.
  • Keep records of your devices’ make, model, serial number, and so on. Prey Project makes this easy.
  • On your phone or tablet, customize the lockscreen with a message such as “user@email.com – reward if found” and on your laptops, stick a small label inside the lid with your name and phone number. You never know if a nice person will return something to you. I know I would do it for you.
Things that don’t help

Finally, here are some techniques that aren’t as useful as you might have been told.

  • Changing passwords doesn’t significantly enhance security unless you change from an insecure password to a strong one. Changing passwords is most useful, in my opinion, when a service has already been compromised or potentially compromised. It’s possible on any given day that an attacker has gotten a list of encrypted passwords for a service, hasn’t yet been discovered, and hasn’t yet decrypted them, and that you’ll foil the attack by changing your password in the meanwhile, but this is such a vanishingly small chance that it’s not meaningful.
  • (OK, this ended up being a list of 1 thing. Tell me what else should go here.)
Summary

Here is a summary of the most valuable steps you can take to protect yourself:

  • Get a password safe, and use it for all of your accounts. Protect it with a long password. Make this the one password you memorize.
  • Use long (as long as possible), randomly generated passwords for all online accounts and services, and never reuse a password.
  • Use two-factor authentication for all services that support it.
  • Encrypt your hard drives, phones and tablets, and backups, and use a password or code to lock all computers, phones, tablets, etc when you turn them off, leave them idle, or put them to sleep.
  • Install something like Prey Project on your portable devices, and label them so nice people can return them to you.
  • Write down the location and access instructions (including passwords) for your password safe, computer, backup hard drives, etc and put it in a safe deposit box.

Friends try not to let friends get hacked and ruined. Don’t stop at upgrading your own security. Please tell your friends and family to do it, too!

Do you have any other suggestions? Please use the comments below to add your thoughts.

Categories: MySQL

Secure your accounts and devices

Xaprb, home of innotop - Wed, 2013-12-18 00:00

This is a public service announcement. Many people I know are not taking important steps necessary to secure their online accounts and devices (computers, cellphones) against malicious people and software. It’s a matter of time before something seriously harmful happens to them.

This blog post will urge you to use higher security than popular advice you’ll hear. It really, really, really is necessary to use strong measures to secure your digital life. The technology being used to attack you is very advanced, operates at a large scale, and you probably stand to lose much more than you realize.

You’re also likely not as good at being secure as you think you are. If you’re like most people, you don’t take some important precautions, and you overestimate the strength and effectiveness of security measures you do use.

Password Security

The simplest and most effective way to dramatically boost your online security is use a password storage program, or password safe. You need to stop making passwords you can remember and make long, random passwords on websites. The only practical way to do this is to use a password safe.

Why? Because if you can remember the password, it’s trivially hackable. For example, passwords like 10qp29wo38ei47ru can be broken instantly. Anything you can feasibly remember is just too weak.

And, any rule you set for yourself that requires self-discipline will be violated, because you’re lazy. You need to make security easier so that you automatically do things more securely. A password safe is the best way to do that, by far. A good rule of thumb for most people is that you should not try to know your own passwords, except the password to your password safe. (People with the need to be hyper-secure will take extraordinary measures, but those aren’t practical or necessary for most of us.)

I use 1Password. Others I know of are LastPass and KeePass Password Safe. I personally wouldn’t use any others, because lesser-known ones are more likely to be malware.

It’s easy to share a password safe’s data across devices, and make a backup of it, by using a service such as Dropbox. The password safe’s files are encrypted, so the contents will not be at risk even if the file syncing service is compromised for some reason. (Use a strong password to encrypt your password safe!)

It’s important to note that online passwords are different from the password you use to log into your personal computer. Online passwords are much more exposed to brute-force, large-scale hacking attacks. By contrast, your laptop probably isn’t going to be subjected to a brute-force password cracking attack, because attackers usually need physical access to the computer to do that. This is not a reason to use a weak password for your computer; I’m just trying to illustrate how important it is to use really long, random passwords for websites and other online services, because they are frequent targets of brute-force attacks.

Here are some other important rules for password security.

  • Never use the same password in more than one service or login. If you do, someone who compromises it will be able to compromise other services you use.
  • Set your password generation program (likely part of your password safe) to make long, random passwords with numbers, special characters, and mixed case. I leave mine set to 20 characters by default. If a website won’t accept such a long password I’ll shorten it. For popular websites such as LinkedIn, Facebook, etc I use much longer passwords, 50 characters or more. They are such valuable attack targets that I’m paranoid.
  • Don’t use your web browser’s features for storing passwords and credit cards. Browsers themselves, and their password storage, are the target of many attacks.
  • Never write passwords down on paper, except once. The only paper copy of my passwords is the master password to my computer, password safe, and GPG key. These are in my bank’s safe deposit box, because if something happens to me I don’t want my family to be completely screwed. (I could write another blog post on the need for a will, power of attorney, advance medical directive, etc.)
  • Never treat any account online, no matter how trivial, as “not important enough for a secure password.” That last item deserves a little story. Ten years ago I didn’t use a password safe, and I treated most websites casually. “Oh, this is just a discussion forum, I don’t care about it.” I used an easy-to-type password for such sites. I used the same one everywhere, and it was a common five-letter English word (not my name, if you’re guessing). Suddenly one day I realized that someone could guess this password easily, log in, change the password and in many cases the email address, and lock me out of my own account. They could then proceed to impersonate me, do illegal and harmful things in my name, etc. Worse, they could go find other places that I had accounts (easy to find – just search Google for my name or username!) and do the same things in many places. I scrambled to find and fix this problem. At the end of it, I realized I had created more than 300 accounts that could have been compromised. Needless to say, I was very, very lucky. My reputation, employment, credit rating, and even my status as a free citizen could have been taken away from me. Don’t let this happen to you!
Use Two-Factor Auth

Two-factor authentication (aka 2-step login) is a much stronger mechanism for account security than a password alone. It uses a “second factor” (something you physically possess) in addition to the common “first factor” (something you know – a password) to verify that you are the person authorized to access the account.

Typically, the login process with two-factor authentication looks like this:

  • You enter your username and password.
  • The service sends a text message to your phone. The message contains a 6-digit number.
  • You must enter the number to finish logging in. With two-factor auth in place, it is very difficult for malicious hackers to access your account, even if they know your password. Two-factor auth is way more secure than other tactics such as long passwords, but it doesn’t mean you shouldn’t also use a password safe and unique, random, non-memorized passwords.

Two-factor auth has a bunch of special ways to handle other common scenarios, such as devices that can’t display the dialog to ask for the 6-digit code, or what if you lose your cellphone, or what if you’re away from your own computer and don’t have your cellphone. Nonetheless, these edge cases are easy to handle. For example, you can get recovery codes for when you lose or don’t have your cellphone. You should store these – where else? – in your password safe.

There seems to be a perception that lots of people think two-factor auth is not convenient. I disagree. I’ve never found it inconvenient, and I use two-factor auth a lot. And I’ve never met these people, whoever they are, who think two-factor auth is such a high burden. The worst thing that happens to me is that I sometimes have to get out of my chair and get my phone from another room to log in.

Unfortunately, most websites don’t support two-factor authentication. Fortunately, many of the most popular and valuable services do, including Facebook, Google, Paypal, Dropbox, LinkedIn, Twitter, and most of the other services that you probably use which are most likely to get compromised. Here is a list of services with two-factor auth, with instructions on how to set it up for each one.

Please enable two-factor authentication if it is supported! I can’t tell you how many of my friends and family have had their Gmail, Facebook, Twitter, and other services compromised. Please don’t let this happen to you! It could do serious harm to you – worse than a stolen credit card.

Secure Your Devices

Sooner or later someone is going to get access to one of your devices – tablet, phone, laptop, thumb drive. I’ve never had a phone or laptop lost or stolen myself, but it’s a matter of time. I’ve known a lot of people in this situation. One of my old bosses, for example, forgot a laptop in the seat pocket of an airplane, and someone took it and didn’t return it.

And how many times have you heard about some government worker leaving a laptop at the coffee shop and suddenly millions of people’s Social Security numbers are stolen?

Think about your phone. If someone stole my phone and it weren’t protected, they’d have access to a bunch of my accounts, contact lists, email, and a lot of other stuff I really, really do not want them messing with. If you’re in the majority of people who leave your phone completely unsecured, think about the consequences for a few minutes. Someone getting access to all the data and accounts on your phone could probably ruin your life for a long time if they wanted to.

All of this is easily preventable. Given that one or more of your devices will someday certainly end up in the hands of someone who may have bad intentions, I think it’s only prudent to take some basic measures:

  • Set the device to require a password, lock code, or pattern to be used to unlock it after it goes to sleep, when it’s idle for a bit, or when you first power it on. If someone steals your device, and can access it without entering your password, you’re well and truly screwed.
  • Use full-device encryption. If someone steals your device, for heaven’s sake don’t let them have access to your data. For Mac users, use File Vault under Preferences / Security and Privacy. Encrypt the whole drive, not just the home directory. On Windows, use TrueCrypt, and on Linux, you probably already know what you’re doing.
  • On Android tablets and phones, you can encrypt the entire device. You have to set up a screen lock code first.
  • If you use a thumb drive or external hard drive to transfer files between devices, encrypt it.
  • Encrypt your backup hard drives. Backups are one of the most common ways that data is stolen. (You have backups, right? I could write another entire blog post on backups. Three things are inevitable: death, taxes, and loss of data that you really care about.)
  • Use a service such as Prey Project to let you have at least some basic control over your device if it’s lost or stolen. Android phones now have the Android Device Manager and Google Location History, but you have to enable these.
  • Keep records of your devices’ make, model, serial number, and so on. Prey Project makes this easy.
  • On your phone or tablet, customize the lockscreen with a message such as “user@email.com – reward if found” and on your laptops, stick a small label inside the lid with your name and phone number. You never know if a nice person will return something to you. I know I would do it for you.
External Links and Resources Things that don’t help

Finally, here are some techniques that aren’t as useful as you might have been told.

  • Changing passwords doesn’t significantly enhance security unless you change from an insecure password to a strong one. Changing passwords is most useful, in my opinion, when a service has already been compromised or potentially compromised. It’s possible on any given day that an attacker has gotten a list of encrypted passwords for a service, hasn’t yet been discovered, and hasn’t yet decrypted them, and that you’ll foil the attack by changing your password in the meanwhile, but this is such a vanishingly small chance that it’s not meaningful.
  • (OK, this ended up being a list of 1 thing. Tell me what else should go here.)
Summary

Here is a summary of the most valuable steps you can take to protect yourself:

  • Get a password safe, and use it for all of your accounts. Protect it with a long password. Make this the one password you memorize.
  • Use long (as long as possible), randomly generated passwords for all online accounts and services, and never reuse a password.
  • Use two-factor authentication for all services that support it.
  • Encrypt your hard drives, phones and tablets, and backups, and use a password or code to lock all computers, phones, tablets, etc when you turn them off, leave them idle, or put them to sleep.
  • Install something like Prey Project on your portable devices, and label them so nice people can return them to you.
  • Write down the location and access instructions (including passwords) for your password safe, computer, backup hard drives, etc and put it in a safe deposit box. Friends try not to let friends get hacked and ruined. Don’t stop at upgrading your own security. Please tell your friends and family to do it, too!

Do you have any other suggestions? Please use the comments below to add your thoughts.

Categories: MySQL

How is the MariaDB Knowledge Base licensed?

Xaprb, home of innotop - Mon, 2013-12-16 22:37

I clicked around for a few moments but didn’t immediately see a license mentioned for the MariaDB knowledgebase. As far as I know, the MySQL documentation is not licensed in a way that would allow copying or derivative works, but at least some of the MariaDB Knowledge Base seems to be pretty similar to the corresponding MySQL documentation. See for example LOAD DATA LOCAL INFILE: MariaDB, MySQL.

Oracle’s MySQL documentation has a licensing notice that states:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.

Can someone clarify the situation?

Categories: MySQL

How is the MariaDB Knowledge Base licensed?

Xaprb, home of innotop - Mon, 2013-12-16 00:00

I clicked around for a few moments but didn’t immediately see a license mentioned for the MariaDB knowledgebase. As far as I know, the MySQL documentation is not licensed in a way that would allow copying or derivative works, but at least some of the MariaDB Knowledge Base seems to be pretty similar to the corresponding MySQL documentation. See for example LOAD DATA LOCAL INFILE: MariaDB, MySQL.

Oracle’s MySQL documentation has a licensing notice that states:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.

Can someone clarify the situation?

Categories: MySQL

Props to the MySQL Community Team

Xaprb, home of innotop - Sat, 2013-12-07 21:02

Enough negativity sometimes gets slung around that it’s easy to forget how much good is going on. I want to give a public thumbs-up to the great job the MySQL community team, especially Morgan Tocker, is doing. I don’t remember ever having so much good interaction with this team, not even in the “good old days”:

  • Advance notice of things they’re thinking about doing (deprecating, changing, adding, etc)
  • Heads-up via private emails about news and upcoming things of interest (new features, upcoming announcements that aren’t public yet, etc)
  • Solicitation of opinion on proposals that are being floated internally (do you use this feature, would it hurt you if we removed this option, do you care about this legacy behavior we’re thinking about sanitizing)

I don’t know who or what has made this change happen, but it’s really welcome. I know Oracle is a giant company with all sorts of legal and regulatory hoops to jump through, for things that seem like they ought to be obviously the right thing to do in an open-source community. I had thought we were not going to get this kind of interaction from them, but happily I was wrong.

(At the same time, I still wish for more public bug reports and test cases; I believe those things are really in everyone’s best interests, both short- and long-term.)

Categories: MySQL

S**t sales engineers say

Xaprb, home of innotop - Sat, 2013-12-07 20:51

Here’s a trip down memory lane. I was just cleaning out some stuff and I found some notes I took from a hilarious MySQL seminar a few years back. I won’t say when or where, to protect the guilty.[1]

I found it so absurd that I had to write down what I was witnessing. Enough time has passed that we can probably all laugh about this now. Times and people have changed.

The seminar was a sales pitch in disguise, of course. The speakers were singing Powerpoint Karaoke to slides real tech people had written. Every now and then, when they advanced a slide, they must have had a panicked moment. “I don’t remember this slide at all!” they must have been thinking. So they’d mumble something really funny and trying-too-hard-to-be-casual about “oh, yeah, [insert topic here] but you all already know this, I won’t bore you with the details [advance slide hastily].” It’s strange how transparent that is to the audience.

Here are some of the things the sales “engineers” said during this seminar, in response to audience questions:

  • Q. How does auto-increment work in replication? A: On slaves, you have to ALTER TABLE to remove auto-increment because only one table in a cluster can be auto-increment. When you switch replication to a different master you have to ALTER TABLE on all servers in the whole cluster to add/remove auto-increment. (This lie was told early in the day. Each successive person who took a turn presenting built upon it instead of correcting it. I’m not sure whether this was admirable teamwork or cowardly face-saving.)
  • Q. Does InnoDB’s log grow forever? A: Yes. You have to back up, delete, and restore your database if you want to shrink it.
  • Q. What size sort buffer should I have? A: 128MB is the suggested starting point. You want this sucker to be BIG.

There was more, but that’s enough for a chuckle. Note to sales engineers everywhere: beware the guy in the front row scribbling notes and grinning.

What are your best memories of worst sales engineer moments?

1. For the avoidance of doubt, it was NOT any of the trainers, support staff, consultants, or otherwise anyone prominently visible to the community. Nor was it anyone else whose name I’ve mentioned before. I doubt any readers of this blog, except for former MySQL AB employees (pre-Sun), would have ever heard of these people. I had to think hard to remember who those names belonged to.

Categories: MySQL

Props to the MySQL Community Team

Xaprb, home of innotop - Sat, 2013-12-07 00:00

Enough negativity sometimes gets slung around that it’s easy to forget how much good is going on. I want to give a public thumbs-up to the great job the MySQL community team, especially Morgan Tocker, is doing. I don’t remember ever having so much good interaction with this team, not even in the “good old days”:

  • Advance notice of things they’re thinking about doing (deprecating, changing, adding, etc)
  • Heads-up via private emails about news and upcoming things of interest (new features, upcoming announcements that aren’t public yet, etc)
  • Solicitation of opinion on proposals that are being floated internally (do you use this feature, would it hurt you if we removed this option, do you care about this legacy behavior we’re thinking about sanitizing) I don’t know who or what has made this change happen, but it’s really welcome. I know Oracle is a giant company with all sorts of legal and regulatory hoops to jump through, for things that seem like they ought to be obviously the right thing to do in an open-source community. I had thought we were not going to get this kind of interaction from them, but happily I was wrong.

(At the same time, I still wish for more public bug reports and test cases; I believe those things are really in everyone’s best interests, both short- and long-term.)

Categories: MySQL

S**t sales engineers say

Xaprb, home of innotop - Sat, 2013-12-07 00:00

Here’s a trip down memory lane. I was just cleaning out some stuff and I found some notes I took from a hilarious MySQL seminar a few years back. I won’t say when or where, to protect the guilty.[1]

I found it so absurd that I had to write down what I was witnessing. Enough time has passed that we can probably all laugh about this now. Times and people have changed.

The seminar was a sales pitch in disguise, of course. The speakers were singing Powerpoint Karaoke to slides real tech people had written. Every now and then, when they advanced a slide, they must have had a panicked moment. “I don’t remember this slide at all!” they must have been thinking. So they’d mumble something really funny and trying-too-hard-to-be-casual about “oh, yeah, [insert topic here] but you all already know this, I won’t bore you with the details [advance slide hastily].” It’s strange how transparent that is to the audience.

Here are some of the things the sales “engineers” said during this seminar, in response to audience questions:

  • Q. How does auto-increment work in replication? A: On slaves, you have to ALTER TABLE to remove auto-increment because only one table in a cluster can be auto-increment. When you switch replication to a different master you have to ALTER TABLE on all servers in the whole cluster to add/remove auto-increment. (This lie was told early in the day. Each successive person who took a turn presenting built upon it instead of correcting it. I’m not sure whether this was admirable teamwork or cowardly face-saving.)
  • Q. Does InnoDB’s log grow forever? A: Yes. You have to back up, delete, and restore your database if you want to shrink it.
  • Q. What size sort buffer should I have? A: 128MB is the suggested starting point. You want this sucker to be BIG.

There was more, but that’s enough for a chuckle. Note to sales engineers everywhere: beware the guy in the front row scribbling notes and grinning.

What are your best memories of worst sales engineer moments?

1. For the avoidance of doubt, it was NOT any of the trainers, support staff, consultants, or otherwise anyone prominently visible to the community. Nor was it anyone else whose name I’ve mentioned before. I doubt any readers of this blog, except for former MySQL AB employees (pre-Sun), would have ever heard of these people. I had to think hard to remember who those names belonged to.

Categories: MySQL

EXPLAIN UPDATE in MySQL 5.6

Xaprb, home of innotop - Tue, 2013-11-26 21:35

I just tried out EXPLAIN UPDATE in MySQL 5.6 and found unexpected results. This query has no usable index: EXPLAIN UPDATE ... WHERE col1 = 9 AND col2 = 'something'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: index possible_keys: NULL key: PRIMARY key_len: 55 ref: NULL rows: 51 Extra: Using where

The EXPLAIN output makes it seem like a perfectly fine query, but it’s a full table scan. If I do the old trick of rewriting it to a SELECT I see that: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 51 Extra: Using where

Should I file this as a bug? It seems like one to me.

Categories: MySQL

EXPLAIN UPDATE in MySQL 5.6

Xaprb, home of innotop - Tue, 2013-11-26 00:00

I just tried out EXPLAIN UPDATE in MySQL 5.6 and found unexpected results. This query has no usable index:

EXPLAIN UPDATE ... WHERE col1 = 9 AND col2 = 'something'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: index possible_keys: NULL key: PRIMARY key_len: 55 ref: NULL rows: 51 Extra: Using where

The EXPLAIN output makes it seem like a perfectly fine query, but it’s a full table scan. If I do the old trick of rewriting it to a SELECT I see that:

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 51 Extra: Using where

Should I file this as a bug? It seems like one to me.

Categories: MySQL

Pager script for shrinking EXPLAIN output

Sergey Petrunia's blog - Fri, 2013-11-22 22:14

Everyone who works with MySQL (or MariaDB) query optimizer has to spend a lot of time looking at EXPLAIN outputs. You typically first look at the tabular form, because it is easier to read. You can immediately see what the join order is, what numbers of records will be read, etc:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey; +------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+ | 1 | SIMPLE | customer | ALL | PRIMARY | NULL | NULL | NULL | 150303 | | | 1 | SIMPLE | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3sf1.customer.c_custkey | 7 | | +------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+

The only problem is that it quickly gets too wide and doesn’t fit even on wide screens. To relieve the pain, I wrote the script that shrinks EXPLAIN output by removing spaces and less useful information. You set the script as mysql client pager command:

MariaDB [dbt3sf1]> \P shrink_explain.pl PAGER set to ’shrink_explain.pl’

and then the above EXPLAIN becomes shorter:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey; +--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+ |id|select_type|table |type|possible_keys|key |key_len|ref |rows |Extra| +--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+ |1 |SIMPLE |customer|ALL |PRIMARY |NULL |NULL |NULL |150303| | |1 |SIMPLE |orders |ref |i_o_custkey |i_o_custkey|5 |customer.c_custkey|7 | | +--+-----------+--------+----+-------------+-----------+-------+------------------+------+-----+

The worst horizontal space hog is the possible_keys column:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and -> o_orderkey in (select l_orderkey from lineitem where l_shipdate='1995-01-01'); +------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+ | 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 150303 | | | 1 | PRIMARY | orders | ref | i_o_custkey | i_o_custkey | 5 | dbt3sf1.customer.c_custkey | 7 | Using where | | 2 | DEPENDENT SUBQUERY | lineitem | index_subquery | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,l_shipdate_partkey,l_shipdate_suppkey | PRIMARY | 4 | func | 2 | Using where | +------+--------------------+----------+----------------+-----------------------------------------------------------------------------------------------+-------------+---------+----------------------------+--------+-------------+

I have added an option to aggressively shrink the possible_keys column:

MariaDB [dbt3sf1]> \P shrink_explain.pl –shrink-possible-keys PAGER set to ’shrink_explain.pl –shrink-possible-keys’ MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and -> o_orderkey in (select l_orderkey from lineitem where l_shipdate=’1995-01-01′); +--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+ |id|select_type |table |type |possible_keys|key |key_len|ref |rows |Extra | +--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+ |1 |PRIMARY |customer|ALL |PRIMARY |NULL |NULL |NULL |150303| | |1 |PRIMARY |orders |ref |i_o_custkey |i_o_custkey|5 |customer.c_custkey|7 |Using where| |2 |DEPENDENT SUBQ.|lineitem|index_subquery|PRIMARY,i_...|PRIMARY |4 |func |2 |Using where| +--+---------------+--------+--------------+-------------+-----------+-------+------------------+------+-----------+

and there is an option to remove possible_keys altogether:

MariaDB [dbt3sf1]> \P shrink_explain.pl –remove-possible-keys PAGER set to ’shrink_explain.pl –remove-possible-keys’ MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey and -> o_orderkey in (select l_orderkey from lineitem where l_shipdate=’1995-01-01′); +--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+ |id|select_type |table |type |...|key |key_len|ref |rows |Extra | +--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+ |1 |PRIMARY |customer|ALL |...|NULL |NULL |NULL |150303| | |1 |PRIMARY |orders |ref |...|i_o_custkey|5 |customer.c_custkey|7 |Using where| |2 |DEPENDENT SUBQ.|lineitem|index_subquery|...|PRIMARY |4 |func |2 |Using where| +--+---------------+--------+--------------+---+-----------+-------+------------------+------+-----------+

The script can be downloaded here: shrink_explain.pl.

Categories: MySQL

SHOW EXPLAIN in MariaDB 10.0 vs EXPLAIN FOR CONNECTION in MySQL 5.7

Sergey Petrunia's blog - Wed, 2013-11-13 21:31

MariaDB 10.0 has SHOW EXPLAIN feature. It was coded by yours truly and first introduced about a year ago. Last release of MySQL 5.7 introduced EXPLAIN FOR CONNECTION, which looks rather similar to MariaDB’s SHOW EXPLAIN. I was wondering how these two compare.

The basics

The usage scenarios are similar. In both cases, you first need a connection id of a running query. It is typically obtained by running SHOW PROCESSLIST:

MariaDB [(none)]> show processlist; +----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+ | 2 | root | localhost | dbt3sf10 | Query | 2 | Sending data | select count(*) from orders | 0.000 | | 3 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +----+------+-----------+----------+---------+------+--------------+-----------------------------+----------+

Then, in MariaDB, you run:

MariaDB [(none)]> show explain for 2; +------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+ | 1 | SIMPLE | orders | index | NULL | i_o_orderdate | 4 | NULL | 14856637 | Using index | +------+-------------+--------+-------+---------------+---------------+---------+------+----------+-------------+

and you get the EXPLAIN for the query that connection #2 is running. SHOW EXPLAIN always generates a warning, the warning has the text of the query that the EXPLAIN is for:

MariaDB [(none)]> show warnings; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Note | 1003 | select count(*) from orders | +-------+------+-----------------------------+

The idea behind this was that without the warning, it will be difficult to tell which query this EXPLAIN belongs to. It could be that the query you saw in SHOW PROCESSLIST has finished and another one has started.

SHOW EXPLAIN works for any query that EXPLAIN works for (starting from MariaDB 10.0.5 EXPLAIN UPDATE/DELETE/INSERT are supported). If you attempt to run SHOW EXPLAIN on a connection that is not running a statement that has EXPLAIN, you will get:

MariaDB [(none)]> show explain for 2; ERROR 1933 (HY000): Target is not running an EXPLAINable command

Now, let’s take a look at MySQL’s EXPLAIN FOR CONNECTION:

MySQL [(none)]> explain for connection 1; +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 922880 | Using where | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

Looks very similar to SHOW EXPLAIN, but there is no warning with query text. The only way out I can see is to run SHOW PROCESSLIST again, find your query there, and look at the “Time” column. If the value is big enough, this means that the query you see in SHOW PROCESSLIST output was already running when you ran SHOW EXPLAIN.

If the target connection is not running a query, you will get nothing

MySQL [(none)]> explain for connection 1; Query OK, 0 rows affected (0.00 sec) More details

Unlike SHOW EXPLAIN, EXPLAIN FOR CONNECTION supports flags. That is, you can run EXPLAIN [EXTENDED|PARTITIONS|FORMAT=JSON] FOR CONNECTION . However, EXPLAIN EXTENDED will not print the warning with query after rewrites, and EXPLAIN FORMAT=JSON will not print attached_condition entries. I think, these are caused by limitations of EXPLAIN FOR CONNECTION code.

There are cases where EXPLAIN FOR CONNECTION will produce outputs like:

MySQL [(none)]> explain for connection 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Plan isn’t ready yet | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------+

In some cases, a part of the plan is not ready:

MySQL [(none)]> explain for connection 1; +----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Plan isn’t ready yet | | 2 | SUBQUERY | lineitem | ALL | NULL | NULL | NULL | NULL | 974084 | NULL | +----+-------------+----------+------+---------------+------+---------+------+--------+----------------------+

SHOW EXPLAIN in MariaDB could produce similar partial query plans, but after release 10.0.5, there is always a full query plan. It would be interesting to discuss the reasons for this, but the discussion won’t fit into this blog post.

Another interesting question is whether MySQL 5.7’s EXPLAIN FOR CONNECTION allows for printing of query plan into slow query log. Before MariaDB 10.0.5, SHOW EXPLAIN code didn’t allow this. Changes in MariaDB 10.0.5 made printing EXPLAIN at the query end easy, and now MariaDB can save EXPLAINs in the Slow Query Log. Will MySQL 5.7 follow and also add such feature?

Categories: MySQL

Freeing some Velocity videos

Xaprb, home of innotop - Sat, 2013-11-09 17:51

Following my previous post on Velocity videos, I had some private email conversations with good folks at O’Reilly, and a really nice in-person exchange with a top-level person as well. I was surprised to hear them encourage me to publish my videos online freely!

I still believe that nothing substitutes for the experience of attending an O’Reilly conference in-person, but I’ll also be the first to admit that my talks are usually more conceptual and academic than practical, and designed to start a conversation rather than to tell you the Truth According To Baron. Thus, I think they’re worth sharing more widely.

O’Reilly alleviated my concerns about “killing the golden goose,” but I like one person’s take on the cost of O’Reilly’s conferences. “You think education is expensive? Try ignorance.”

I’ll post some of my past talks soon for your enjoyment.

Categories: MySQL

Freeing some Velocity videos

Xaprb, home of innotop - Sat, 2013-11-09 00:00

Following my previous post on Velocity videos, I had some private email conversations with good folks at O’Reilly, and a really nice in-person exchange with a top-level person as well. I was surprised to hear them encourage me to publish my videos online freely!

I still believe that nothing substitutes for the experience of attending an O’Reilly conference in-person, but I’ll also be the first to admit that my talks are usually more conceptual and academic than practical, and designed to start a conversation rather than to tell you the Truth According To Baron. Thus, I think they’re worth sharing more widely.

O’Reilly alleviated my concerns about “killing the golden goose,” but I like one person’s take on the cost of O’Reilly’s conferences. “You think education is expensive? Try ignorance.”

I’ll post some of my past talks soon for your enjoyment.

Categories: MySQL

A chance for Cassandra Storage Engine

Sergey Petrunia's blog - Thu, 2013-11-07 08:17

Short version: It looks like DataStax has released an official C++ driver for their new CQL native protocol. This makes it possible to update MariaDB’s Cassandra Storage Engine to work with all column families in Cassandra 1.2 and up.

Long version: In case the above didn’t make any sense to you, here’s some background:

  • Cassandra Storage Engine was developed against Cassandra 1.1
  • Cassandra 1.1 had a client-server protocol based on Thrift API. It had a number of limitations, the most important was lack of support for streaming. These translated into limitations in Cassandra storage engine, for example, I could not support secondary indexes.
  • Cassandra 1.2 was released in February, and it had a new, native CQL protocol. The protocol supported streaming, but there were no C/C++ drivers.
  • Besides that, Cassandra has changed its data model in version 1.2. After this change, Thrift API clients were screwed could only access column families defined with WITH COMPACT STORAGE attribute. This applied to Cassandra Storage Engine as well.

Now, with official C/C++ driver for CQL protocol, it should be possible to update Cassandra Storage Engine to work with new versions of Cassandra. It will take some effort to move from using Thrift API to using CQL, but the required amount of effort just went down a lot.

Categories: MySQL

MySQL 5.6: no testcases for non-crashing bugs, either (and an incomplete bugfix)

Sergey Petrunia's blog - Thu, 2013-10-24 13:22

I normally don’t write such posts, but this time I’ll do a little whining. I was looking at MySQL’s Bug#69581, which was also filed against MariaDB as MDEV-5102. With some effort (little for this particular bug), one can find out that Bug#69581’s counterpart in Oracle’s internal bug database is Bug#16862316, and the fix is in revision 5366 in MySQL 5.6.14.

There are two problems, though:

  • The fix doesn’t include a testcase. We know, Oracle doesn’t publish testcases for crashing bugs, with a justification that it’s for security reasons. However, this bug is not a crashing one. I’m wondering what was the justification for not including testcase for it?
  • Luckily, the bug entry at bugs.mysql.com has the testcase, so I was able to play with it. As a result, I have found that MySQL’s fix is incomplete, and filed another bug, Bug#70703.

I think, the poor bugfix made by Oracle is not a big deal. Although, this is not the first time we at MariaDB see a poor bugfix by Oracle (Can the forks keep up? Well, they would, if they could trust upstream’s patches and just copy them, without having to check whether the fixes are real fixes or band-aids).

The lack of testcases is a much bigger concern. Has Percona Server merged fix for MySQL Bug#69581 correctly? Well, they don’t have any features related to partitioned tables, so most likely, the answer is Yes. But won’t it be nicer if mysql-test test suite had a testcase for this, and they could check it by running tests? It’s not only about forks/branches. Everybody who changes MySQL/MariaDB/Percona source code benefits from mysql-test testsuite having a good test coverage.

Now, are there any volunteers who could run through MySQL 5.6 changelog and find out whether missing testcase for non-crashing bug was an isolated occurrence, or it is a new trend?

Categories: MySQL

MariaDB 10.0: EXPLAIN in the slow query log

Sergey Petrunia's blog - Thu, 2013-10-17 23:07

MariaDB can now print query’s EXPLAIN into the slow query log! The feature is based on SHOW EXPLAIN technology and its output looks like this:

# Time: 131017 21:26:02 # User@Host: root[root] @ localhost [] # Thread_id: 2 Schema: dbt3sf1 QC_hit: No # Query_time: 5.535819 Lock_time: 0.000350 Rows_sent: 1 Rows_examined: 65633 ## <explain> ## id select_type table type possible_keys key key_len ref rows Extra ## 1 SIMPLE nation ref PRIMARY,n_name n_name 26 const 1 Using where; Using index ## 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3sf1.nation.n_nationkey 25124 Using index ## 1 SIMPLE orders ref i_o_custkey i_o_custkey 5 dbt3sf1.customer.c_custkey 7 Using index ## </explain> SET timestamp=1382030762; select count(*) from customer, orders, nation where c_custkey=o_custkey and c_nationkey=n_nationkey and n_name='GERMANY';

By default, EXPLAIN is not printed to the slow query log, one needs to enable it manually like this (either from SQL or from my.cnf):

set log_slow_verbosity='explain‘;

Now, I’d like to find time to hack pt-query-digest to make use of EXPLAINs. For start, let it show the number of different query plans. Then, show query plans on the response time distribution… so that one can tell which ones were fast or slow… The stopping factor here is that pt-query-digest is 500KB of unfamiliar Perl code.

Categories: MySQL

EXPLAIN UPDATE/DELETE/INSERT in MySQL and MariaDB

Sergey Petrunia's blog - Thu, 2013-10-17 20:18

MySQL 5.6 added support for EXPLAIN INSERT/UPDATE/DELETE. MySQL 5.7 made some improvements to EXPLAIN code. Also, 5.7 added support for EXPLAIN FOR CONNECTION, which looks very similar to MariaDB’s SHOW EXPLAIN. I was working on putting EXPLAIN INSERT/UPDATE/DELETE into MariaDB 10.0, so I’ve dug up some details and thought I’d share them:

EXPLAIN UPDATE/DELETE

EXPLAIN UPDATE looks like regular EXPLAIN SELECT:

MySQL [dbt3sf1]> explain update orders set o_orderpriority='1-URGENT' where o_orderDATE='1998-07-21'; +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | orders | range | i_o_orderdate | i_o_orderdate | 4 | const | 628 | Using where | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

EXPLAIN PARTITIONS shows partitions. EXPLAIN EXTENDED shows the “filtered” column, but doesn’t produce a warning with query after rewrites.

In MySQL 5.7, select_type changes from SIMPLE to UPDATE.

MySQL [dbt3sf1]> explain update orders set o_orderpriority='3-MEDIUM' where o_orderDATE='1998-07-21'; +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+ | 1 | UPDATE | orders | range | i_o_orderdate | i_o_orderdate | 4 | const | 628 | Using where | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+

for multi-table UPDATEs (or DELETEs), select_type=UPDATE (or DELETE) only for tables that are being updated.

Inside, there are some issues. Best-release-ever still suffers from an old MySQL design where execution path taken by EXPLAIN is different from execution path taken by the actual query. If you have something in two places, it goes out of sync in no time, and I have alreay found one example EXPLAIN will show “Using join buffer” where the execution doesn’t use it (filed as BUG#70553). A shameless plug: in MariaDB 10.0, we use one execution path, so bugs like this one are not possible.

Another peculiarity is Using temporary. You can run a query like this:

MySQL [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21'; +----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+ | 1 | UPDATE | orders | range | i_o_orderdate | i_o_orderdate | 4 | const | 628 | Using where; Using temporary | +----+-------------+--------+-------+---------------+---------------+---------+-------+------+------------------------------+

and see Using temporary. This looks weird, why would this query need a temporary table? Running the UPDATE and checking Created_tmp_tables shows that indeed no temporary tables were created. The reason for “Using temporary” is that the UPDATE modifies columns in the index that is used to find rows. When that happens, the optimizer needs to collect record ids in a buffer before doing any updates. But, records are collected in a buffer, not in a temporary table. We in MariaDB decided to denote this with Using buffer instead:

MariaDB [dbt3sf1]> explain update orders set o_orderDATE='1998-07-22' where o_orderDATE='1998-07-21'; +------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+ | 1 | SIMPLE | orders | range | i_o_orderdate | i_o_orderdate | 4 | NULL | 628 | Using where; Using buffer | +------+-------------+--------+-------+---------------+---------------+---------+------+------+---------------------------+ EXPLAIN INSERT

This one is weird - INSERT doesn’t have a query plan, why would one need EXPLAIN for it? One could argue that EXPLAIN INSERT ... SELECT makes sense, as the SELECT part has a query plan. However, using that logic, EXPLAIN CREATE TABLE … SELECT should have been supported also, but it isn’t.

In MySQL 5.6, EXPLAIN INSERT seems to be totally useless:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423'); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+

In MySQL 5.7, it shows the table that we insert into:

MySQL [dbt3sf1]> explain insert into orders (o_custkey) values ('32423'); +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | INSERT | orders | ALL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+

“type=ALL” is misleading, because no full table scan will take place. The only scenario when EXPLAIN INSERT can be useful is when one is doing an INSERT into partitioned table. In that case, EXPLAIN PARTITIONS will show the result of partition pruning. Note that partition pruning for INSERTs works in a rather peculiar way - depending on the number of rows inserted, it may choose not to perform partition pruning and will show that all partitions will be used.

I think this post is getting long, so I will talk about SHOW EXPLAIN and EXPLAIN FOR CONNECTION in a separate post.

Categories: MySQL

Webinar recording: MariaDB Query Optimizer - Improving query performance

Sergey Petrunia's blog - Wed, 2013-10-09 12:33

Last week, we at SkySQL had a webinar titled MariaDB Query Optimizer - Improving Query Performance with yours truly as the author and presenter. The recording is now available online at the above link.

The webinar is an overview of all important parts of the MySQL/MariaDB query optimizer. Special attention is given to the new optimizer features and differences between MySQL and MariaDB.

Categories: MySQL
Syndicate content