MySQL University keeps rolling along. We’ve had some fantastic sessions just recently (not including my own, of course!), such as Lenz’s presentation of backing up MySQL with filesystem snapshots, Allan Packer’s presentation on the optimization of MySQL and, going back a little further, David Van Couvering and Petr Pisl’s talk on using PHP and MySQL within Netbeans.

Remember that all of these presentations can be viewed again online if you missed them first time round!

We’ve got some good topics coming up, but we ned more!!

Got some hot topic that you want to tell the world about?

Using MySQL in an interesting way?

Got a good MySQL scalability story that you want to tell?

Developed a great storage engine?

Any and all of these topics are welcome for discussion and presentation at MySQL University.

Did anybody notice that http://planetmysql.org now redirects to http://planet.mysql.com?

Curious to know the reason why, perhaps an official MySQL person can give us some details.
Also it’s a 302 redirect, not a 301 redirect, interesting?

 wget http://planetmysql.org
--2009-02-26 14:40:09--  http://planetmysql.org/
Resolving planetmysql.org... 213.136.52.29
Connecting to planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://www.planetmysql.org/ [following]
--2009-02-26 14:40:10--  http://www.planetmysql.org/
Resolving www.planetmysql.org... 213.136.52.29
Connecting to www.planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://planet.mysql.com/ [following]
--2009-02-26 14:40:10-- …

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) …

In 1809, a hero in America's Revolutionary War, General John Stark, was forced to decline an invitation to a military reunion due to ill health. He sent a toast to be read in his absence that began, "Live Free or Die." That phrase is now the official motto of the American state of New Hampshire (where Stark lived), known for a fierce sense of independence (and no income taxes).

Hold that thought for a moment.

Today, we're announcing the single biggest and most important OEM/distribution agreement Sun's ever signed for the open source Solaris operating system - through which we'll be joining forces with the world's largest supplier of high volume servers, Hewlett Packard. As a result of the deal, Solaris gains tier 1 status, …

Per day:

Per Month:

Or more interestingly… What day are commits being made? Are we working over the weekend?

Do we work all night?

Drizzle hackers are just as likely to commit something at 3am as they are at 10am.

In this first Drizzle Podcast, Sheeri K. Cabral and Jay Pipes talk about what Drizzle is and how Drizzle is different from MySQL both technically and from a community standpoint.

• The main web page for Drizzle is http://drizzle.org/.
  
• The main project page for Drizzle is at Launchpad: https://launchpad.net/drizzle.
  
• The Drizzle mailing list is at https://launchpad.net/~drizzle-discuss.
  
• The Drizzle wiki, including documentation, is at http://drizzle.org/wiki/

  read more

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 | …