OpenBSD Journal

Cooking with MySQL

Contributed by jose on from the database-kitchen dept.

Paul DuBois, author of the MySQL Cookbook from O'Reilly, has written a few articles on MySQL which may be of interest to some of the people here. The first article is about Summaries and NULL Values you get when digging through MySQL tables. The second article is about Managing Multiple Simultaneous AUTO_INCREMENT Values between various tables. The third and final article is entitled Computing Team Standings and deals with comparing and computing values between rows in a table.

All in all some useful stuff. MySQL is available in the OpenBSD ports tree.

(Comments are closed)


Comments
  1. By Chris Hedemark () chris@yonderway.com on http://yonderway.com

    MySQL is *awesome* as a back end for web site applications or anywhere that performance is paramount over other features.

    But if you are looking for an alternative to Oracle, please do also check out PostgreSQL. We're actually migrating some huge databases out of Oracle onto PostgreSQL now and while it isn't quite as fast as Oracle, the feature set is there and the price difference makes the performance drop quite acceptable (in fact, it would be very easy to apply the savings to sexier hardware to make up for the difference).

    MySQL has had a lot of problems compiling on OpenBSD especially if you aren't running x86 architecture. I have a lot of sparc64 servers to get the 64 bit pointers, larger caches, etc. and MySQL doesn't seem to play well on this platform yet. PostgreSQL seems much happier here.

    Comments
    1. By Me () on

      Anything else would be a lot better.

      Comments
      1. By Fairy () on

        Forget *BSD.

        MySQL is very unstable on FreeBSD as well.

        Comments
        1. By ViPER () viper@dmrt.net on http://www.securitydatabase.net

          Stupid trolls...
          http://www.securitydatabase.net is just one of my obsd servers that runs flawless with mysql.
          Hell, i never ever had any problems what so ever.

          Comments
          1. By Heo () on

            Has it ever been under heavy load?

            Comments
        2. By Anonymous Coward () on

          The .se domain has been running on FreeBSD + MySQL for years.

      2. By nils () on

        Mysql really rocks on openbsd, been running it for a couple of years on production machines, never had any problems.

        Comments
        1. By Bingo () on

          You gotta be kiddin' d00de

      3. By djm () on

        MySQL has worked flawlessly for me on i386 for a number of years now.

      4. By OpenBSD Troll () on

        Hrmph .

        I'll respond this obviously well-thought out and considered posting with a dissenting opinion.

        I've personally used MySQL for years on OpenBSD (through several upgrades) as a back-end for a variety of web services (dynamic content, public forum, some web toys). It has never given me a lick of trouble. This server has been under low-to-medium load all it;s long life.

        Fine.

        At work, we run dual OpenBSD servers to handle all mail for a largish corporation. One does all the spam filtering, and the other does the delivery. Both are powered by PostFix using MySQL as a backend for lookups and mail stashes. These are busy servers. Very busy.

        The app I work on does not support MySQL via JDBC because of the lack of "real" prepared statements, but as a server-room workhourse, I cannot recommend MySQL more. It works well under load, under a variety of situations and platforms.

        Of course, you are expected to tune it for optimal performance.

        Yes, there was a problem with a potential crash with some versions of OBSD and some releases of MySQL. Though I never saw this problem myself (when running the "dangerous" combination) my understanding was that the OBSD team worked hard to solve the problem.

        BTW, flat-out saying "Such and such Sucks!" without any attempt at providing evidence (however anecdotal) is lame.

        I guarantee that I can make a solid MySQL system work on a busy server, and make it run better than a novice DBA could with PostGreSQL. Database applications are all about the DBA and the tuning.

    2. By Ray Reese () russ at zerotech dot net on mailto:russ at zerotech dot net

      Hmm, by chance can you say which company you work for that's moving from Oracle to PostgreSQL? I'm an Engineer at Digex, and my boss is a big Oracle guy. When presented with a project to build an IP Management system to manage our many /8 networks, and /16 networks, we were simply told we needed to use Oracle. However I think PostgreSQL would be suited better since it supports Network data types such as host, cidr, and macaddr, with math that can be applied to such as <<= to determine if it belongs to a subnet or not. It would make development a lot easier.

      However, when we presented this to my boss he merely laughed and assumed because it was Open Source, it was of "cheap quality." I did however have to remind him that more than 75% of Digex's core infrastructure runs off Open Source software, and that the very development tools we use the most like Perl and gcc, are Open Source. Still however, he declined. So I've been spamming him with 'switch' stories about Oracle. So far my big one is the whole .ORG domain registrar being moved to a PostgreSQL database from Oracle.

      His rebuttle? "Maybe they couldn't afford Oracle."

      So more switch stories will help :)

      Comments
      1. By Anonymous Coward () on

        Heh. Can you say "kickback"? Knew you could...

        Odds are the only thing you're going to
        get from this butthead for trying to save
        the company kilobux is fired.

      2. By Anonymous Coward () on

        Make him write this oracle-word down so he is responsible for extra costs of reimplementing CIDR and MAC stuff in expensive oracle...

    3. By Yank () on

      Before making any comment, pls do some serious benchmarks against mysql on OpenBSD and you'll know why the OpenBSD+mysql combo stinks.

  2. By Anonymous Coward () on

    PostgreSQL is good too (not as fast, but a more complete database) and it has a better license :)

  3. By Jedi/Sector One () j@pureftpd.org on http://www.pureftpd.org/

    There's a little statement in the MySQL documentation that always kept me away from that piece of software.

    It's about auto-incremented columns, something widely used as primary indexes. Snippet from the documentation :

    "The behavior of the auto-increment mechanism is not defined if a user gives a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type."

    A fact is that auto-incremental columns always increment. Previous IDs don't seem to be reused even after deleting some rows.

    That fact, plus the previous citation means to me that your databases will definitely stop working at a random date (when an auto-incremental column will overflow) .

    And I've not seen any tool in MySQL to reorganize these columns in order to pack the IDs. And I don't see any reliable way to do it. Foreign keys allow to sync updates and deletes, and not to change keys.

    How do people manage this?

    Comments
    1. By Anonymous Coward () on

      data dump & reload

      Comments
      1. By Anonymous Coward () on

        I thought the reason to use Unix was to get away from MS solutions to problems?

      2. By Anonymous Coward () on

        OMFG, you're kidding!

        For the record, the only MySQL database I've ever administered was not and is not anywhere near this particularly bit of idiocy, being only ~4000 records. Now I must hope that it stays that way...

    2. By Anonymous Coward () on

      And you think PostgreSQL has some magic never-wrapping index just because they don't say that they don't? or some other database?

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.]