MySQL Webinar Report - plus test of newer version on big workload
Results 1 to 3 of 3

Thread: MySQL Webinar Report - plus test of newer version on big workload

  1. #1
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,623

    MySQL Webinar Report - plus test of newer version on big workload

    (I'm just posting this in case anyone is interested. I don't work for Oracle or MySQL, and I don't particularly care which RDBMS you use. At my work, we are using MySQL, and were before I got here because someone understood it was "industry standard".)

    The other day I sat in on a Webinar, "Getting the Best MySQL Performance in Your Products, Part III, Query Tuning".

    I picked up a few pointers, learned some stuff (I hope) that I was a tad fuzzy on before. Please keep in mind that I'm not a DBA,
    and there's some question if I'm a Real Programmer(tm) at all

    One thing that piqued my curiosity was the fact they were harping on MySQL 5.6, which I believe is in BETA. They claim some pretty
    significant performance improvements since then. Of interest to some might be a new Performance_schema that helps with analysis of
    performance issues, "semi-joins" and materialization (supporting subqueries in FROM, maybe? -if I'm understanding), auto-indexing of derived
    tables, and faster filesorts/sort+limit.

    Our projects have been, so far, run on MySQL 5.1. For a test, I ran a script that does a lot of DB munging, table creation, etc. It's supposed
    to be an AI thing and was never tuned to run quickly; the hope was that it would, however, produce accurate guesses about what
    a widget's function is, based on our system's previous knowledge of similar widgets.

    A test run of this script generally takes just over an hour on my development box. For "fun", I deinstalled MySQL 5.1 and installed
    5.5 --- and then ran the script again. It was done in 27 minutes.

    Needless to say, I'm a little bit interested in getting some of our stuff into a newer MySQL Engine Real Soon Now(tm) ;-)
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,354
    Quote Originally Posted by dalecosp View Post
    A test run of this script generally takes just over an hour on my development box. For "fun", I deinstalled MySQL 5.1 and installed
    5.5 --- and then ran the script again. It was done in 27 minutes.
    Did you account for other variables such as:
    1. System load
    2. SQL server load
    3. SQL server configuration (e.g. did you use the same my.ini/my.cnf config?)
    4. Fragmentation of SQL data within storage file on disk
    5. Fragmentation of storage file throughout disk
    ?

  3. #3
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,623
    Not sure about 4-5. On 'Nixen FFS, fragmentation is generally a non-issue. I'm not sure if running inside VirtualBox on Windows would make a difference ... it well might.

    Load issues are non-existent. The development box sits inside my desktop machine and no one else uses it. As far as my.cnf goes, the only changes made were commenting out the following, which 5.5 complains about having in there:

    Code:
    #set-variable = max_connections=$n
    #skip-federated
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •