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

[dalecosp]

(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) ;-)

[bradgrafelman]

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

?

[dalecosp]

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:

#set-variable = max_connections=$n
#skip-federated