OpenBSD Journal

MySQL benchmarks on OpenBSD

Contributed by dhartmei on from the all-bets-are-on dept.

On NewsForge, Tony Bourke writes about Using MySQL to benchmark OS performance. In this first article, he explains the test setup used to compare performance of MySQL on different operating systems, including OpenBSD 3.6 SMP and UP.

The twist is that the results will be published later in a second article. So, instead of agreeing or disagreeing with the validity of the test setup based on how favorable the results turn out towards any OS, you'll have to decide whether the comparison is fair (and relevant) in advance.

According to Bourke [...] there were quite a few surprises that I think will challenge a few tightly-held assumptions in regards to which operating systems are fast and which aren't

(Comments are closed)

Comments

  1. By Anonymous Coward (130.238.5.7) on

    http://software.newsforge.com/software/04/12/27/1243207.shtml?tid=72&tid=29 results

    Comments

    1. By SH (82.182.103.172) on

      For some reason the author did not include OpenBSD in the conclusion. If he add the conclusion, I hope he has some thoughts on ProPolice that surely affects the benchmarks somewhat.

      Comments

      1. By Puffy (128.113.201.229) on

        This isn't like the network performance testing of 2003 which was used to bash OpenBSD repeatedly. OpenBSD isn't riced up to perform database work.
        So what?
        There's nothing here to really brush off as "Oh, you're not taking into account X" or "Dumbass, OpenBSD does Y so therefore it has to be slower". Chalking it up to propolice is a complete copout and pretty ignorant.
        There's no fight to pick, and this is a non-story. OpenBSD is middle-of-the-pack for performance. Wow.

        Comments

        1. By SH (82.182.103.172) on

          There's nothing here to really brush off as "Oh, you're not taking into account X" or "Dumbass, OpenBSD does Y so therefore it has to be slower". Chalking it up to propolice is a complete copout and pretty ignorant.

          Perhaps you should work a bit more on your reading skills? I quite simply wondered what thoughts, if any, the author of the benchmarks has concerning ProPolice. Thre is no implied "brush off". Got it?

      2. By Anonymous Coward (208.27.203.127) on

        > For some reason the author did not include OpenBSD in the conclusion.

        Actually I don't see a need for him to include OpenBSD in the final conclusion. The
        commentary before pretty well summed it up.

        > If he add the conclusion, I hope he has some thoughts on ProPolice that surely affects
        > the benchmarks somewhat.

        I doubt ProPolice had anything to do with this. If Theo is right in his belief that all the
        security enhancements don't impact more than 2 - 3% of system preformance. Then I
        would have expected to see at 47 - 48% improvement over the single CPU benchmark
        (we all won't be delusional to think that two CPUs means the machine is twice as fast
        *smile*).

        Which tends to lead me in one of two directions.

        1. There is some barrier to the thread support that is limited it.
        2. The kernel locks are not fine enough.

        If it was #2, I would expect (based on past experience with Linux MP systems back when
        it started with the big lock) again to see some gain. Which leads me to be #1 is the issue.

        BTW, #1 could be threading library or could be mysql use of threading under OpenBSD I
        don't honestly know which so I won't even guess.

        Don't know how much preformance work has been done on OpenBSD's threading library?
        I see it as a good area for someone to jump in a play. That is IMSNHO the best thing to do
        with articles like these.

        - Ben

        Comments

        1. By Brad (65.110.162.61) brad at comstyle dot com on

          Its very obvious if you do any research that libpthread being used in OpenBSD is derived from FreeBSD' libc_r. A userland implementation of a threads library and it does not take advantage of any kernel backing and thus multiple CPUs. Some work is on-going to develop a replacement libpthread that will allow for kernel backing. At the moment we're not making any performance guarentees with the SMP work. It clearly is brand new and can use any and all testing for stability/compatibility issues (making sure all CPUs are setup properly, think P4 HT systems that need icky ACPI for MPBIOS setup and that interrupt routing issues are dealt with on the more complex MP systems) first before worrying about performance.

        2. By henning (213.128.133.133) henning on

          it is #1. our threading library works entirely in userland, thus one process stays on one CPU, no matter how many threads it runs.
          That's why OpenBSD loses a bit with the 2nd CPU installed; the MP locks cost a bit performance but we gain (nearly) nothing back.

        3. By Anthony (68.145.111.152) on

          First, SMP on OpenBSD is currently biglock... so if by "not fine enough" you mean "not fine at all", then yes. However that's not that bad in and of itself, particularly on two processors. Look at FreeBSD 4 and Linux 2.4.

          Second, threading is currently userspace, and it's not possible for two threads in the same process to run concurrently on two different processors. Much of the advantage of threads is the ability to use non-blocking I/O without your brain melting, so that's usually enough. It hurts for some GUI stuff and multi-threaded servers like Apache 2 and apparently databases... but if you really need these things go with another OS or fund an OpenBSD developer to port the fancy implementation from FreeBSD.

          Third, there's a number of optimizations that help even on UP systems that OpenBSD doesn't have. See #2 for the solution.

          Chances are it doesn't matter (you have a lightly loaded server, or you have a server that does things OpenBSD is fast at) or you already use another OS.

          I realize people like to reduce the number of OSes to keep the mental overhead down, and to reduce the workload of testing and applying patches, but you pay a price for that.

          Comments

          1. By Ben (24.118.184.77) mouring@nospam.eviladmin.org on

            My point in general was that Propolice, W^X, random malloc(), etc isn't why OpenBSD benched out the way it did. =) Which is what the parent thread was suggesting.

            We all know that OpenBSD is missing key features in the area of MP. It took Linux, FreeBSD, BSDi (God rest its' soul), etc years of work to make MP beyond a toy. Rome wasn't built in a day, nor would I expect OpenBSD's SMP/Thread support. =) Tho, that would product some interesting chatter if it was.

            I am glad to see my little BP6 dual 533mhz box being put to good use and not crashing under 3.6. If only other OSes liked that poor box as well.

            - Ben

      3. By Anonymous Coward (81.64.227.144) on

        Anyway, OpenBSD's team is used to sell propolice as a "no more than 1%" performance hit. So this shouldn't impact.

        And letting it activated for mysql compilation reflects the current usage (why to choose OpenBSD and, later, kill one of his great security feature on a sensible networked daemon ?), so this shows the cost of our security needs, that's all.

        And btw, the benchmarked gentoo is also propoliced.

  2. By Bruno Rohée (62.4.19.202) bruno@rohee.com on

    First thing this is quite a well done benchmark.

    The author just ignore some details about OpenBSD. Its thread implementation is entirely in userland and all threads are inside the same process so benchmarking a single application on a two way machine was just losing time, there can't be any win in performance for a single process under OpenBSD for now, threaded application or not. The slight performance lowering on the MP machine was to be expected given the overhead introduced by the MP kernel.

    However the sentence "The results would seem to indicate that it might be wise to run a uniprocessor kernel even if two processors are available." that he applies to NetBSD and given the quite comparable results would also apply to OpenBSD is misguided. The mysql process sure don't benefit of it but all other processes on the machine would use the CPU that isn't hogged by the DB. So if it is not a purely dedicated DB server that conclusion is wrong. You could even benefit from MP under OpenBSD running mysql, just run different DB under different processes (and store them on different disks, to avoid trashing...).

    Comments

    1. By Anonymous Coward (143.166.255.18) on

      Are you out of your mind? This is, as usual, a completely retarded benchmark. I especially like the perl tool for meassuring performance; I could not have come up with something dumber than that even if I tried. Wste of time and internet space; I wish this shit would dissapear forever.

      Comments

      1. By Anonymous Coward (69.197.92.181) on

        What's more useless, a perfectly valid and well done benchmark, or you bitching and moaning like an asshat? Maybe you need to reconsider what really needs to be removed from the internet.

        Comments

        1. By Anonymous Coward (143.166.255.18) on

          You are a moron.

    2. By spinaltoad (24.0.202.65) todd@qdsdirect.com on

      Last May I ran into a similar scaling issue with MySQL. We sold a Quad Xenon 2.8 gig, 4 gig ram, ten x 15k rpm drives, 512meg Intel raid card. Everything was original Intel equipment, including the massive pedestal case. They were hitting a performance wall with using windows and MySQL. Except for the fact they used MySQL, they were a Microsoft shop. I was brought in to help them test on other systems. I don't have the original bench marks, but here are my findings. Under windows, MySQL would not utilize more 50% of the processor. It was returning queries slower than a Dual Xenon. FreeBSD 4.8 was used with Linux threads. It was able to return results about 2~3 percent faster than windows. The speed increase was not good enough to justify them learning a new system. But, we still couldn't get the cpu to come close to 100% utilization. Then Linux, I installed Red Hat Enterprise because they had heard of it. Bingo, we hit 100% cpu utilization. It returned queries about 5~10% faster than windows. One problem, 50% of cpu utilization went toward thread maintenance. This server was built for data-mining, so the tests were heavily biased toward select statements. The findings led me to conclude that MySQL does not scale well with SMP. This is primarily a result of the multi-threaded nature of MySQL. I have seen Multi-process application scale very nicely with SMP, such as the case with medical imaging, math lab, etc. It would also explain why Firebird has support to run as a multi-threaded or multi-process database. And why Firebird suggests that multi-threads be used for single cpu’s and multi-process be use for SMP. There needs to a fundamental change occurs to the way multi-threaded applications are handled in an SMP in order to see good scalability. (hope fully FreeBSD 5.x, OpenBSD , or Dragon Fly can tackle this problem. ) As for Linux, I don’t know if someone is planning to get rid of those big locks that cause all that high over head for thread maintenance.

  3. By Anonymous Coward (213.118.35.44) on

    I run a few mysql servers with OpenBSD, and I'm perfectly well aware that it doesn't provide bleeding edge performance. I'd rather have a slightly slower box (or just two boxes) that's secure, than having to remove root kits from a bleeding fast box every other day.
    In the end, two slighte slower and secure boxes are a lot cheaper than one 1337 über speed-tweaked box which gets rooted. I like my data.

    Comments

    1. By Anonymous Coward (69.197.92.181) on

      You know, a machine just running mysql isn't likely to be rooted regardless of what OS its on. Assuming you are anything remotely resembling competant, the only thing listening will be sshd, no matter what OS you use. So what benefit does using openbsd really provide in a case like this? Aren't you better off using something faster? Maybe its time to stop using security as an excuse to have a slow OS?

      Comments

      1. By Luiz Gustavo (200.165.130.203) on http://hades.uint8t.org

        Of course linux should perform better, otherwise those billions of dollars that got invested in it would be void.

        Comments

        1. By Anonymous Coward (69.197.92.181) on

          And since it does perform better, why not use it? What are you gaining from running openbsd on a machine that will only ever have sshd listening? Lower performance is acceptable as a trade off sometimes, but in this case what are you trading for?

          Comments

          1. By Anonymous Coward (64.119.174.202) on

            Please take your common sense and critical thinking elsewhere. This is a site of groupthink, it's not welcome here.

            Comments

            1. By Anonymous Coward (68.148.237.181) on

              Who modded this one up? Do you not understand what he's saying?

              Comments

              1. By Nate (70.24.120.242) on

                I think it was modded up because of the humour involved in a person noting you make sense and telling you to take it else where.

          2. By Anonymous Coward (67.34.129.203) on

            I switched from Linux to OpenBSD not just for security reasons, but also ease of maintenance (I'm full-time in design/coding, and admin stuff is just extra "chore" that's gotta get done). I was simply amazed how easy it is to install and maintain OBSD servers. Then when I saw that the quality and attention to detail went all the way down to the man pages, I was sold. No more Linux for me, unless somebody's willing to pay me handsomely to use the penguin. And believe it or not, there's lots of servers out there running Apache+MySQL, so it's not like the BKL is that big of a deal, for those cases. I tend to prefer PostgreSQL, which is also multithreaded, and only once have I needed to setup a dedicated machine for it (huge project, lots of transactions). I personally believe that most places don't really need 100% performance, and ease of maintenance and security are more important in general.

            Comments

            1. By Anonymous Coward (69.197.92.181) on

              Postgresql is not completely multithreaded like mysql is. It uses fork to spawn a process per connection. And in what way is openbsd easier to maintain? Having to update your entire OS to a new version to get a new version of your database is easy? I guess you probably wouldn't be using the ports anyways since they are always outdated. So maybe you think having to compile everything yourself is easy maintenance?

              Comments

              1. By Anonymous Coward (67.34.129.203) on

                You're right, it starts up new postmaster process for every connection. That itself is a good reason to prefer psql on OpenBSD. Besides the fact it's a better database. :)
                And indeed, I do compile it myself. Even when I was using Debian I would compile it myself, because Debian's versions were alway lagging behind a lot. For some softwares this isn't a big deal, but I never liked the idea of missing out on important bug fixes and new features that offer more power or make maintenance easier (eg, auto-vacuum, replication, etc.)
                As to why OBSD is easier to maintain? Well that's simple. It installs itself with sane defaults, chroot'ed daemons and priviledge separation active. When I was using Linux, I'd have to go patch the kernel with GrSecurity/lm_sensors/etc., configure the kernel (which ain't that fun IMO, especially w/ grsec patch), build a kernel package for every server, and do that again every time a new kernel bug came out, which happens comparatively often in penguin-land. I'd also have to track GrSecurity's website to stay abreast of changes there. Also, I had to put a lot of time and work into securing the box the way I wanted it. I'd had to edit init scripts and create chroot directory structures for named, etc. because Debian refuses to give sane default settings like OBSD. I also ended up writting a Perl script that went through the whole filesystem and removed the s-bit on any binaries that didn't need it, because there were so many of those. And I had to keep track of all this in the dpkg DB, so it didn't
                overwrite my changes...
                I also like how fast OBSD gets installed. It's quick and to the point, and there's really very little one has to do to get a fully-functional server. Even though the OS is pretty small, the fact it already includes Apache and BIND (with sane defaults) is a big time-saver for me. Same thing with disk quotas (no quota package to install, no kernel to config... just edit the fstab and rc.conf, and presto!)
                And I also like the siteXX.tgz custom install option. I haven't used it yet, but probably will for one of my projects. To have the OS automatically take care of your custom stuff at install time for you is pure genius!
                As for keeping stuff up-to-date, I don't have a problem with CVS and running "make && make install" in the right directory. I don't see that as any worse than "apt-get update && apt-get upgrade". Of course, the OBSD patches are comparatively fewer (I was constantly getting bombareded every other day with messages from debian-security-announced telling me to update such and such package), so in the end I also gained here too.

              2. By Daniel (213.6.120.100) on

                Having to update your entire OS to a new version to get a new version of your database is easy? I guess you probably wouldn't be using the ports anyways since they are always outdated. So maybe you think having to compile everything yourself is easy maintenance?

                In most cases, it's a matter of minutes to bring a port in -stable up to date wrt the most recent version of the software (Perhaps not mysql, but done it for PostgreSQL). The ports/packages system is clean and lightweight, well documented and the fake install and systraced builds protect you from severe mistakes.

                I don't want to say Debian's or Redhat's package systems are bad, but if .rpms/.debs are faulty or not available, my personal impression is, that it's much harder to fix/create them yourself than it is to create a OpenBSD pkg yourself.

    2. By Anonymous Coward (208.252.48.163) on

      Nice FUD. I run MySQL on a number of Linux and FreeBSD machines and have had zero root kits because I keep up with security updates, same as OpenBSD admins do. Zero.

      Comments

      1. By Luiz Gustavo (200.165.130.203) on http://hades.uint8t.org

        Sorry to steal your thunder, but keeping up-to-date patch wise is not enough. Have you ever heard of 0day?

        From my point-of-view that's what security is all about.

  4. Comments

    1. By gwyllion (134.58.253.113) on

      Did you check the first comment? Identical URL.

      Comments

      1. By Anonymous Coward (213.208.100.201) on

        But it is (at least) a hyperlink.

        And I personally prefer useful stuff over initial stuff anyway... which is how a wiki works (by "standing on the shoulders of giants").

        </offtopic>

Latest Articles

Credits

Copyright © - Daniel Hartmeier. All rights reserved. Articles and comments are copyright their respective authors, submission implies license to publish on this web site. Contents of the archive prior to as well as images and HTML templates were copied from the fabulous original deadly.org with Jose's and Jim's kind permission. This journal runs as CGI with httpd(8) on OpenBSD, the source code is BSD licensed. undeadly \Un*dead"ly\, a. Not subject to death; immortal. [Obs.]