I can’t stress enough that people look at SQL statements that are being executed against your production MySQL database, and you optimize queries when you can.

Often it’s the improvement to the large number of similar queries executed that can optimize resources. In this example, we take a very simple query, and by removing an unnecessary order by, we eliminate MySQL internally creating a temporary (in memory) table.

So what’s the big deal.

• The query is simpler to read and understand
• Memory required for the connection is not assigned
• A number of internal steps are no longer required (4 of 21 logging messages, not an ideal measurement, but an indication). In this case, it was easily a 10% performance improvement for each query.

This query is executed 10-100 times per second, so the improvement in performance is significant.

mysql> explain select max(mdate) …

Are you having problems getting up to speed on MySQL? Are you asking yourself “Is there a hands-on training course we can send a developer/system admin to learn MySQL?”. In response, at 42SQL we have put together two new training courses, MySQL Essentials and MySQL Operations.

MySQL Essentials Training Details

With MySQL Essentials we tackle the core essentials that a developer/system admin/junior DBA would require in order to support an initial development environment that uses MySQL. Essentials training teaches the following skills:

• Which version of MySQL to use (including the various different variants and patches available)
• Backup, retention, and recovery strategies
• Configuration and Monitoring of MySQL
• Optimal schema and data objects configuration management
• …

It’s a shame that MySQL does not provide better granularity when you want to look at all SQL statements being executed in a MySQL server. I canvas that you can with the general log, but the inherit starting/stopping problems in 5.0, improved in 5.1, but I would still like to see the option on a per connection basis, or even a time period. MySQL Proxy can provide a solution here but also with some caveats.

You should however in a NON production environment, take the time to enable the general log and look the SQL Statements. Prior to looking at the SQL, monitoring of the GLOBAL STATUS variables combined with Statpack revealed the following in a 1 minute interval.

====================================================================================================
                                         Statement Activity …

This neat one line command can be of interest when you are rebuilding a MySQL slave and replication is currently catching up.

$ watch --interval=1 --differences 'mysql -uuser -ppassword -e "show slave status\G"'

You will see the standard SHOW SLAVE STATUS output, but the watch command presents an updated view every second, and highlights differences. This can be useful in a background window to keep an eye on those ‘Seconds Behind Master’.

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.10.10.10
                Master_User: slave
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000626
        Read_Master_Log_Pos: 88159239
             Relay_Log_File: slave-relay.000005
              Relay_Log_Pos: 426677632
      Relay_Master_Log_File: mysql-bin.000621 …

I had the opportunity to review a client’s production Drupal installation recently. This is a new site and traffic is just starting to pick up. Drupal is a popular LAMP stack open source CMS system using the MySQL Database.

Unfortunately I don’t always have the chance to focus on one product when consulting, sometimes the time can be minutes to a few hours. Some observations from looking at Drupal.

Disk footprint

Presently, volume and content is of a low volume, but expecting to ramp up. I do however find 90% of disk volume in one table called ‘watchdog’;

+--------------+--------------+--------------+-------------+--------+
| table_schema | total_mb     | data_mb      | index_mb    | tables |
+--------------+--------------+--------------+-------------+--------+
| xxxxx        | 812.95555878 | …

This week I was the invited speaker to give a 4 hr presentation to the Federal Government Sector in Washington DC on “Best Practices in Migrating to MySQL“. This was a followup to my day long “MySQL for the Oracle DBA Bootcamp” which I presented in Washington DC last year. It was good to see a number of attendees from my first DC presentation.

There was good attendance across various government departments and companies providing services to the government sector, as well a variety of job descriptions.

Thanks to Carahsoft and Sun/MySQL for organizing and sponsoring the event. Thanks also to Phil Hildebrand who provided fantastic support during my preparation answering all my SQL Server questions.

Thanks also …

I had a customer recently who had very hierarchical data, with some complicated measures that didn’t aggregate up according to regular ole aggregation rules (sum, min, max, avg, count, distinct count). Now, one can do weighted averages using sql expressions in a Measure Expression these rules were complex and they also were dependent on the other dimension attributes. UGGGGH.

Come to that: their analysts had the pristine, blessed data sets calculated at different rollups (already aggregated to Company Regions). Mondrian though, is often too smart for it’s own good. If it has data in cache, and things it can roll up a measure to a higher level (Company Companies can be rolled up to Regions if it’s a SUM for instance) Mondrian will do that. This is desirable in like 99.9% of cases. Unless, you want to “solve” your cube and just tell Mondrian to read the data from your tables.

I started thinking - since …

Living in Portland, OR, I was sad to see O’Reilly move OSCON to San Jose this year (don’t they have enough with MySQL, Velocity, …?). So what did the Portland locals do? Start their own:

Open Source Bridge (we have a lot of bridges here in Portland).

It looks like they just opened up their call for papers recently, quoting from their page: “Open Source Bridge is accepting proposals now through March 31st for our 2009 conference, which will take place June 17–19 at the Oregon Convention Center in Portland, OR.”

February 2009 has been a very busy time for presentations on Open Source Software in the Dallas/Fort Worth Area. Yahoo's Tommy Falgout presented at the recent DallasPHP.Org meeting on Scaling MySQL. You can find his presentation at http://www.dallasphp.org and I highly recommend their meetings as an excellent.

Next up is the Dallas OpenSolaris/Dallas Sun Users Group(DSUG)and they are offering the ultimate geek-bait! Free Pizza!

Meetings Held the Third Thursday of Every Month
Next Session: Thurs Feb 19th (6:30PM Pizza, 7:00PM Program)
Topic: Open Source Roundtable - share your Open Source experiences with the group
Speaker: Jim McGuinness will kick off the discussion with updates on MySQL, Glassfish and Openoffice
Location: Dallas Sun Office, Mansion Conference Room
16000 North Dallas Parkway, Suite 700

Register here: …

Last week, my good colleague Gerv gently took me to task about requiring that videos submitted to the Mozilla Net Effects video program be licensed under the Creative Common NonCommercial-ShareAlike license (instead of an actual Free Culture licensed like Creative Common ShareAlike license or Creative Common Attribution license) . I thought about this for a while and got to wondering why I’d ever let fear of misuse overcome my experience and common sense.

Licensing and contract choices are often driven by fear and greed. We work, play, love and give in an …