September 3, 2014

The #1 mistake hosting providers make for MySQL servers

This article is not meant to malign hosting providers, but I want to point out something you should be aware of if you’re getting someone else to build and host your servers for you.

Most hosting providers — even the big names — continue to install 32-bit GNU/Linux operating systems on 64-bit hardware. This is a serious mistake.

You have to tell them to install a 64-bit operating system. If you don’t then you will come to a point where your needs grow and you want to use more memory — and they will gladly install 8 or 16GB of memory for you, but MySQL can’t use it because it runs in a single process, which is limited to about 2.5GB of memory. And then you have to rebuild the whole operating system from scratch. But you don’t want any downtime, so you have to buy another server, set it up as a slave, switch your site to use it, and then rebuild the old server. That 32-bit OS turned into a pretty expensive mistake.

I do not know why the hosting providers keep doing this. Just yesterday I got a quote from a hosting provider for a medium-high-end system with 8GB of RAM, and forgot to tell them 64-bit OS, and they actually listed 32-bit explicitly on the quote — useless! I would estimate about half of all the hosted systems I’ve seen so far have this mismatch. I don’t know why they do this — maybe there is a reason, but I don’t know it and it looks pretty silly to me. 64-bit hardware and operating systems aren’t new anymore. In fact, 32-bit is hard to find in server-class hardware these days. So it certainly looks like the hosting companies need to change what they’re doing, but maybe there’s a different reason.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. “Oh you want x64, not x32? That’ll be a $500 upgrade fee.” Seems like a reason some hosting companies would have…

  2. That would be evil. I haven’t detected any evilness, just defaults that need to be changed.

  3. vicaya says:

    Sure, installing 32-bit OS for machines (including VM) with more than 4GB RAM doesn’t make sense. OTOH, there are significant memory usage savings (pointers being 4-bytes instead of 8-bytes) installing 32-bit OS for cheap hosting plans, especially VPS (with less than 2GB allocated) running in VMs on 64-bit hardware. It’s all about cost.

  4. But I see this with dedicated machines all the time. Nobody else is using all that memory. It’s not saving the hosting provider anything. So it still just looks like “that’s the default” to me.

  5. Yea I see this all the time, and not only in hosting. It also happens with third parties delivering new servers into a company’s datacenter.
    They often don’t tell what they intend to put on (RHEL5 without additional info), or even if it was specified it turns out that the installed system is acually 32bit. You just have to check, every time. It’s quite annoying and several of my clients have had to have servers reinstalled (delaying timelines) because of this. On the good side, I now tell clients so they can keep an eye on it and make sure it doesn’t happen to them. This has already saved hassles in a few places…

    Hosting providers invent arbitrary feature segmentation to productise their possible offerings. Just like MySQL does. Rob Young was just writing about the enterprise-only query analyser; but the two bits of user feedback he bases the “demand” on are actually about the server delivering inadequate auditing/logging facilities. It would stand to reason to just fix that, rather than tweak around on the outside. However, that doesn’t make for a product you can sell, and thus they go for the seemingly non-obvious approach.
    From the user perspective, weirdness results ;-)

  6. Have you ever tried to build PHP 4 on RHEL 4 64bit? You will hit errors. This is true when building many items from source or following how-tos. Also, you can often find many 3rd party RPMS for 32 but not 64 bit. I suspect this is a key decision for many of the self-managed server providers. They want to head off as many support queries as possible. Also many how-tos are geared at 32 bit systems. For some of the larger dedicated providers (especially in the self-managed space), they want to head off as much support inquiries as possible. When you look over their clients, few actually benefit from 64-bit, so why install it? Presumably if you know it is an issue, you will request it at the install.

    This however is changing. Some providers now have automatic deployment tools so you just select your OS at the time of the order and it is auto-deployed. One of our partners has a system where you pick your processor, RAM, disk setup, partitioning scheme and OS. The server is auto-deployed with your specifications.

  7. Sergi says:

    And when you finally have a 64 bit hardware, with a 64 bit Debian and a 64 bit MySQL (last version) you find that master-slave replication fails (corrupted indexes) because of that same hardware (Quad Core SuperMicro).

  8. Is there a way to programmatically detect this mistake? Then we could build this as a “monitor & advisor” inside MONyog.

  9. Rohit Nadhani,

    In MySQL server variables exists “version_compile_machine”.

    You can check that against the result of: “uname -p”

  10. As I already posted, I aggree:

    http://marksverbiage.blogspot.com/2008/01/mysql-address-space.html

    Running out of address space on a 32-bit system is really bad, can’t really be avoided, and prevents you from allocating memory sensibly.

    Mark

  11. I think the installation process at ISP/hosting providers is often highly automated (using disk images etc.). They are not *building* the OS. Most users will compare the price, and those ISP’s that need 4 hours to configure a system can’t compete on price with those who can set up a server in 30 minutes!

    For the same reason they will not experiment with systems they do no know well (that is why RHEL4 32 bit is still the most widely deplyoed system I believe, and some issues that may take quite some time to solve are listed above).

    What would you be willing to pay additionally as ‘startup fee’ and in ‘monthly fee’ as well for 64 bit services? I think that reality is that only a very small percentage of users are willing to pay *anything* because only the same very small percentage will encounter the problem with the limit of 32 bit systems.

  12. Karl Ravn says:

    One simple mistake that one of my coworkers did, was that he thought that AMD64 was for AMD-processors only, so he downloaded Intel x86 instead of the SuSE installation. We noticed this when the system was already in use. we noticed it when we always had 12-13gb of disk cache, and it took almost a year before we had to reinstall.

    It costed about 500usd for the external service technician, and somewhere between 2-3000usd for employees preparing the server with fast backups, test installations on another server and such. Everything was done out-of-office hours, so it costed a whole bunch of money, and we were pretty tired during that time as well.

    So, AMD64 = EMT64, which is the normal 64-bit installation on “regular” servers. Not for AMD only.

  13. DBA Newbie says:

    “So, AMD64 = EMT64, which is the normal 64-bit installation on “regular” servers. Not for AMD only.” -> Wow, I don’t know either. Is it true for every software?

  14. I work for a big hosting provider and we provision x64 Linux servers by default. (Only after a lot of kicking and screaming about database problems under x32…)

  15. peter says:

    Sergi,

    MySQL Replication works just fine on 64bit… If you have issues find them and report the bug – it could be something hardware/OS specific as well.

  16. peter says:

    AMD64 and EMT64 is pretty much the same stuff this is why the distributions often called x86_64 these days which is vendor agnostic.

  17. peter says:

    Rohit,

    You can check for “lm” flag in /proc/cpuinfo
    this means CPU is 64bit capable and OS and MySQL binaries better to be 64 bit.

  18. Edwin says:

    “… MySQL can’t use it because it runs in a single process, which is limited to about 2.5GB of memory.”

    Does this also apply to 32-bit Windows environments running on 64-bit hardware?

  19. Sergi says:

    Peter,

    We’re pretty sure it’s a hardware bug, as replication works ok in our dual core machines but fails miserably in all our quad core machines. From time to time we get the “ERROR 126 (HY000): Incorrect key file for table” error message, we repair the table, but it only lasts for a few minutes before crashing again. We installled a newer version as we found that these problem were already solved but we still have them. Thanks anyway.

  20. Have you tried EC2, you get to choose your os and platform at the same time, and OS’s are specifically setup for 32 or 64 bit architecture.

  21. Edwin, the limit may be a different number on 32-bit windows, but it’s not over 4GB, because that’s all a 32-bit pointer can address.

    Sergi, it sounds like you are mixing 64-bit vs 32-bit with dual-core vs. quad-core. They are different things.

    Richard, this isn’t about our systems — this is a warning to all people who might read this blog. They are the ones who get stuck with 32-bit systems. Then they call us and we help them go through the rather unnecessary process of buying another server so they can juggle data back and forth to upgrade without downtime.

  22. Sergi says:

    Baron, I’m not mixing. 32 bits OS/MySQL works fine both in dual and quad core machines. 64 bit OS/MySQL replicates correctly in my dual core machines, but fails in the quad core machines. That’s why we think is a hardware bug. That and also because our hosting provider uses cheap SuperMicro machines.

  23. Sergi, I see now — sorry I didn’t read back through the comments again. I agree with Peter. Sorry you’re having this trouble — it has not happened to me, though.

  24. Sergi says:

    No problem Baron. I hope one day we’ll find the solution or at least change our cheap machines for better ones without that problem ;)

  25. Stefan A says:

    I’m running Mysql 64 from SUN coolstack on my Sun Solaris 10 x86 2*DualCore…
    Having some 400-700 reads and writes per second concurrently on different tables (no joins) using perl, the CPU is fixed to 25% and the rest is ideling…. OK perl is sometimes going th 25% too….

    Where the hell do I have to tell the Software to use the hardware?

  26. Hmmm, let me guess. You have 4 cores, and the CPU is at 25%. That means 1 core is at 100% utilization.

    I’ve got it! You must be using MySQL!

    Joking aside: MySQL doesn’t parallelize a single query across multiple cores. If you want help getting better performance out of this hardware, we’re happy to help: submit the contact form on http://www.percona.com. Or you can just buy our book. There’s a link at the top right of the page.

  27. Stefan A says:

    Your quess is very close: I’m using MySQL (no joke!)

    Thanks for the offer, I new I will meet experts here.
    I’ll come back on this, in case I’m in real trouble. Currently, I can go with the 25% as my tests are topping the demmand by 50… I’m just interested, why it is like this, so I don’t want to invest Money, now.

    I’m running several Perl scripts.
    - one is inserting lots of sets in table1 with a name and a date field
    - one is adding into sets of table1 (oldest set first at 30/s) (this will not show more MySQL usage, but opens a second perl by about 25% – I have to check the waitings in this script)
    - one is creating a set in table2 if not already available for that name (Currently about 350/s having 17% Mysql and 8% Perl)
    - the same is looking up table1 and if a math over three fields comes to a result higher than x, than it adds the result into an other table2 (this will do the job up to 150/s, while the Update job is doing 200/s
    - and an other job is closing sets in table2, if an maximum amount is reached (some…)

    As i mentioned: no joins! every perl is reading from one table, updating the current set in this table and is bossibly updating/inserting into the other table…

    These does not look like single queries….
    There must be something else.

  28. If you’re not using InnoDB, your concurrency on that single table is very limited, so you will effectively run only one query at a time.

  29. >> Then they call us and we help them go through the rather unnecessary process of buying another server so they can juggle data back and forth to upgrade without downtime.

    @Baron:
    Can you explain how one can get the advantages of a 64bit server without “the rather unnecessary process of buying another server”. We seem have outgrown our 32bit setup as well. Any advise is greatly appreciated

  30. I mean it was unnecessary if the OS had been 64-bit to start with. If you have to upgrade now, you have to either shut down your server for the upgrade (downtime) or you need another server to avoid downtime.

Speak Your Mind

*