As many of us know, the 5th annual MySQL Conference and Expo is happening April 20-23rd, 2009 in Santa Clara, California. The theme is Innovation Everywhere, and this year the conference organizers have taken an innovative page from OSCon and decided to host a free “camp” during the conference.

As far as I know, MySQL Camp is the only free, non-commercial programming occurring. We already have a fantastic lineup of speakers and last week I was surprised with another bounty — MySQL Camp has been extended to Thursday!

While you are making your travel and lodging arrangements, remember that on Sunday April 19th on the mezzanine of the conference hotel there will be a Games Day from 12 noon - 12 midnight. Stop by, play …

MySQL 5.1 has a lot of nice new features, partitioning, event scheduler, dynamic logging, etc... Most of these require changing some application code or design logic to make use of them. However, there is a few new features that almost every user of InnoDB can take advantage of right away and that is the ability to set MySQL so that less locking is required for many statements in InnoDB. There are a few changes which make this possible.

The first one is the new auto_increment locking for InnoDB. There is a good article which talks about this here and a large section the in the manual.

In MySQL 5.0 and previous, InnoDB used a special query-duration table level lock to acquire the AUTO-INC …

Thanks to those of you who came to listen in on the webinar. I’ve posted the slides here if you want them. If you have any follow-up questions, check out #gearman on irc.freenode.net.

Slaves can be used for:

1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.
2. Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).
3. Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
   

Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For …

It looks like this bug finally got some attention, and a patch is queued. http://bugs.mysql.com/bug.php?id=38187
In brief, the bug happens when you alternate and reuse names of savepoints. Quoting from the bug page:

InnoDB treats the savepoints like a stack, e.g.,    SAVEPOINT a;   SAVEPOINT b;   SAVEPOINT c;   SAVEPOINT b; 

• 
  
• Consistent backup:
  A consistent backup is one that represents a snapshot of all data at a point in time. Consistent backups are used for disaster recovery. An inconsistent backup can be useful for retrieving partial data — for example, if a long-time customer accidentally deletes information from his profile, you can go back to an inconsistent backup and retrieve that information. It is not important that all the data be consistent with each other when retrieving a partial amount of point-in-time data.
• Logical backups:
  Logical backups are backups that contain SQL statements to create and populate tables. In MySQL, logical backups can only be done while mysqld is running, and are usually done with the mysqldump tool. mysqldump can logically export (”dump”) multiple databases, tables, views, stored routines, triggers and events. It can …

The move from PHP 5 to PHP 6 will be a painful one. But once it’s done, I hope that it will be easier to handle safe web development for a global, multi-language internet. After all these years, we still … Continue reading →

Changes (as compared to 8.01) include:

Features:
* Both SQLyog and SJA will now detect if Base64 encoding is required for HTTP-tunnel.

Funtionality changed:
*  CHUNK setting  (for exports)  now also has effect for non-HTTP-tunneled connections.  Using this option is required if it takes more time to retrieve data from a table than server ‘net_write_timeout’ setting.
*  SQLyog will now not reconnect if connection is lost during imports.  Instead an error message will prompt (the reason for this is that session variables defined on top of the script  would be reset to server defaults with reconnection.  As a result (and most important) special characters could garble and other errors could occur as well)..
* When trying to INSERT or UPDATE spatial data types from the DATA/RESULT tab SQLyog will now throw an error. …

As MySQL continues to expand in the market place, I am seeing a common mistake new DBAs make. They are waiting too long to develop their performance tuning skills. Whenever I work with new DBAs I always tell them it is important to focus on three areas:Understand the architecture and how things work. It is not good to guess.Get very good at back up and recovery if you want to keep your job.Get

Well now, there has been some commenting and even a follow up blogpost to me previous blog on MySQL Stored Procedure Performance.

One of the issues, and I knew about that one, was that when I compared 100000 client INSERTs with 100000 INSERTs done from a Stored Procedure, the overhead of the former would largely be the Client / Server communication latency. This was noted by Anthony T Curtis in his blog Yet more on Stored Procedure performance.

Me not being one who will put a stop to a debate, or whatever it is, we are mostly fact-fining I think, I have done YET some more tests. What Anthony was doing was to limit the overhead of the network latency by using multi-statement SQL for the INSERT, hence …