Diamond Notes

We are having the great opportunity of hosting Jay Pipes tomorrow at our Raleigh MUG. If you want to go and haven’t signed up yet you can go here to sign up:

http://mysql.meetup.com/cities/us/nc/raleigh/

Hope to see you there!! Meeting starts at 7:00.

Hey everyone. I just finished uploaded the latest versions of the three QPP programs. They are all working well and I consider them “version 1.0″. I will be updating the qpp pages as soon as possible with a tutorial on how all these programs work together.

The basic idea is that you use the querysniffer program to gather production data from your servers as needed without stopping/starting the MySQL server. Queryparse is actually used to control querysniffer and push the gathered data into a database from which you can use simple php/html pages to view the statistics.

Querybench is used to benchmark/stress-test a test server with production data (gathered, once again, with querysniffer). You can even set it up so that the database can be “rolled back” to its initial state after a run.

All in all, I am very pleased. I have fulfilled all the goals I set out to achieve when I began this little journey a few months ago. It will be very useful in my work and I hope others find it useful as well.

I would like to publicly thank Ian Kilgore who helped far above and beyond anything required. He is one of the system administrators where I work and is the genius programmer behind this stuff. I just had the idea and kind of directed things along. The only “programming” I have done is working on the documentation a bit and developing the web pages for the display of the statistics.

Feedback can be sent to “bmurphy” at “paragon-cs.com”.

Now what do I with all this free time I have left over *cough*?

I am starting on putting together the Winter issue of the MySQL Magazine. This will be our third issue and it is getting better each time!
Here is your opportunity to contribute to the community. I am accepting ideas/outlines/proposals for articles for this issue through November the 9th.   That gives you almost two weeks to get back to me about that great idea you have in your head.  Articles will be due to be completed by December the 1st. If you aren’t sure about an idea send me an email  - bmurphy (at) paragon-cs.com

Previous issues are available at: http://www.mysqlzine.net

Hope to hear from you soon!

Thanks MySQL AB!!

For those who don’t read the general mysql mailing list:

Dear MySQL users,

we are proud to present to you the MySQL Server 5.1.22-rc release,
the first 5.1 “release candidate” version of the popular open source
database.

Bear in mind that this is still a “candidate” release, and as with any
other pre-production release, caution should be taken when installing on
production level systems or systems with critical data. For production
level systems using 5.0, we would like to direct your attention to the
product description of MySQL Enterprise at:

http://mysql.com/products/enterprise/

The MySQL 5.1.22-rc release is now available in source and binary form
for a number of platforms from our download pages at

http://dev.mysql.com/downloads/

and mirror sites. Note that not all mirror sites may be up to date at
this point in time, so if you can’t find this version on some mirror,
please try again later or choose another download site.

Please also note that some of our mirrors are currently experiencing
problems that may result in serving corrupted files. We are working with
the mirror maintainers to resolve this.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches etc.:

http://forge.mysql.com/wiki/Contributing

The following section lists the changes from version to version in the
MySQL source code since the latest released version of MySQL 5.1, the
MySQL 5.1.21-beta release. It can also be viewed online at

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-22.html

Functionality added or changed:
* There is a new innodb_autoinc_lock_mode system variable to
configure the locking behavior that InnoDB uses for generating
auto-increment values. The default behavior now is slightly
different from before, which involves a minor incompatibility
for multiple-row inserts that specify an explicit value for
the auto-increment column in some but not all rows.
This can be used to improve scalability and performance, see
Section 13.5.6.3, “How AUTO_INCREMENT Handling Works in InnoDB.”:
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Bugs fixed:
* NDB Cluster: Backups of TIMESTAMP columns made with
ndb_restore on a MySQL Cluster using data nodes hosts of one
endian could not be used to restore the cluster’s data to data
node hosts of the other endian.
(Bug#30134: http://bugs.mysql.com/30134)
* NDB Cluster (Replication): Multi-master replication setups did
not handle –log-slave-updates correctly.
(Bug#30017: http://bugs.mysql.com/30017)
* When sorting rows in an INNODB table using a primary key,
where the sort was on the the primary key column and the DESC
operator was applied, the rows would be incorrectly sorted if
you included a simple WHERE field = value clause in the query.
(Bug#31001: http://bugs.mysql.com/31001)
* Replication of InnoDB partitioned tables could lose updates
with row-based or mixed replication format.
(Bug#28430: http://bugs.mysql.com/28430)
* mysql_install_db could fail to find its message file.
(Bug#30678: http://bugs.mysql.com/30678)
* Non-range queries of the form SELECT … FROM … WHERE
keypart_1=const, …, keypart_n=const ORDER BY … FOR UPDATE
sometimes were unnecessarily blocked waiting for a lock if
another transaction was using SELECT … FOR UPDATE on the
same table. (Bug#28570: http://bugs.mysql.com/28570)
* Under some circumstances, a UDF initialization function could
be passed incorrect argument lengths.
(Bug#29804: http://bugs.mysql.com/29804)
* CONNECTION_ID() always returned 0 for the embedded server
(libmysqld). (Bug#30389: http://bugs.mysql.com/30389)
* The mysql_list_fields() C API function incorrectly set
MYSQL_FIELD::decimals for some view columns.
(Bug#29306: http://bugs.mysql.com/29306)
* Read lock requests that were blocked by a pending write lock
request were not allowed to proceed if the statement
requesting the write lock was killed.
(Bug#21281: http://bugs.mysql.com/21281)
* Memory corruption occurred for some queries with a top-level
OR operation in the WHERE condition if they contained equality
predicates and other sargable predicates in disjunctive parts
of the condition. (Bug#30396: http://bugs.mysql.com/30396)
* The server created temporary tables for filesort operations in
the working directory, not in the directory specified by the
tmpdir system variable.
(Bug#30287: http://bugs.mysql.com/30287)
* Using KILL QUERY or KILL CONNECTION to kill a SELECT statement
caused a server crash if the query cache was enabled.
(Bug#30201: http://bugs.mysql.com/30201)
* Operations that used the time zone replicated the time zone
only for successful operations, but did not replicate the time
zone for errors that need to know it.
(Bug#29536: http://bugs.mysql.com/29536)
* mysqldump from the MySQL 5.1.21 distribution could not be used
to create a dump from a MySQL 5.1.20 or older server.
(Bug#30123: http://bugs.mysql.com/30123)
* When using a combination of HANDLER… READ and DELETE on a
table, MySQL continued to open new copies of the table every
time, leading to an exhaustion of file descriptors. This was
caused in MySQL 5.1.15 by a fix for
Bug#21587: http://bugs.mysql.com/21587; the current fix
consists of reverting the earlier fix.
(Bug#29474: http://bugs.mysql.com/29474)
* Tables using the InnoDB storage engine incremented
AUTO_INCREMENT values incorrectly with ON DUPLICATE KEY
UPDATE. (Bug#28781: http://bugs.mysql.com/28781)

Enjoy !
Joerg

–
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

–

Welcome to Log Buffer #64. For those who haven’t come across my little patch of the Internet - I’m Keith Murphy. I am the MySQL DBA at iContact, Inc and the editor of MySQL Magazine. I do my best to contribute to the MySQL community any way I can. Special thanks to the Pythian Group for giving me the honor of writing this week’s buffer. Thanks for stopping by!!

MySQL

The guys over at MySQL Performance Blog - the high performance MySQL gurus - reported that a important bug for was fixed (currently only for 5.1.22 -which isn’t released yet). The bug was an issue with the auto-increment field for an InnoDB table. This causes serious scalability issues with Innodb so is a welcome fix. No known information on if this will be back-ported.

One experienced Oracle DBA who is transitioning to MySQL blogs about his nine tips for new MySQL DBAs. They are quite enlightening. Along with that, the new DBA should take a look at George J. Trujillo Jr.’s blog entry for “Top Ten Things to do Before Installing MySQL”. For the more experienced DBA who is planning for the scaling of their systems — stop by and take a look at thoughts on the capacity planning of a MySQL DBA.

This week was the MySQL Developers conference in Heidelburg, Germany. A number of the developers blogged about it including Colin Charles, Paul McCullagh and Lukas Smith. They discuss upcoming features including my favorite - the coming online backup in MySQL 5.2 (or whatever the version after 5.1.x ends up being called).

Postgres

This week for the world of Postgres brings the introduction of a new tool called Bucardo. It brings asynchronous multi-master replication system for Postgres. Greg is the developer of the tool and blogs about it here. Magnus Hagander’s blog has a good introduction to the integrated security features of Postgres and how it can integrate with both Windows and Unix clients. Take a look here. And finally, for the Postgres fans, is a cool tip from </depesz> about a nice way to access Posgres documentation from Firefox quickly and easily.

Oracle

In Oracle news, Kris Buytaert points out that Oracle still takes a solid hour to install. It is ironic that I also remember when Oracle first started shipping a Linux product and how they distributed CDs (with Linux Magazine?) like AOL “back in the day”. It was either in 1998 or 1999 and I had just started using MySQL for database functions at the dial-up ISP I started.

Want to participate in some Oracle surveys? Doug Burns points to a couple that Oracle DBAs can participate in.

Microsoft SQL Server

The PASS conference in Denver, CO generated quite the buzz in the MS SQL Server blog-o-sphere. And even though the release of SQL Server 2008 is some time off (Q2 2008) the next version of SQL Server is building buzz. The PASS conference was covered in detail at http://www.mssqltips.com and pointed out by Andy Warren’s blog. More “views and opinions PASS” and maybe a prescription for the ailing DBA are available from the SQL Doctor here. One of the speakers at the conference — the Crazy DBA — even posted about their experience speaking. Worth reading by anyone who is looking to be a “first timer” speaking at a conference. More thoughts on the conference from Ken Henderson.

Who says that DBAs don’t have a sense of humor? With a name like “Patron Saint of Lost Yaks” there is at least one DBA with a sense of humor. In addition to the humor, they have a good introduction to horizontal partitioning in SQL Server on his/her blog here. Also, the PSoLY brings us a cool tip about inserting binary data . Wrapping things up for the company from Redmond is a blog entry by SimonS about how the SSIS date package isn’t as accurate as you might think (and a solution to the problem).

DB2

DB2 Magazine has a nice blog entry about effectively indexing DB2 databases. Susan Visser has an interesting blog entry about a new book Understanding DB2 9 Security. In addition she has an interesting statistic I will leave to the gentle reader to find. She also announces the chance for a DB9 DBA to win a free trip to a conference.

According to Daniel Krook there is a fairly substantiated rumor that IBM is porting DB2 to the Intel Mac.

And always remember - the views and opinions expressed here are my views and opinions and not necessarily the views or opinions of my employer.

Haven’t posted since late last month. Been busy. We have decided to expand the project around the query sniffer tool to make something a little more all-inclusive. One of my major goals is to have a toolset that I can use to grab queries on the fly from a production server (as querysniffer will) and then feed that it another tool that uses that data to fire them off at a test server to see how the server handles a production load. Kind of like Jeremy Zawodny’s Mybench program on steroids.

Another part to this toolkit is a query analyzer. The idea is this. Grab data with querysniffer and use this tool to analyze the data and report statistics for your production query data. The obvious stat is as follows:

• ranking the queries by number of times in data (showing that query XY executed 1,000 times in 100 MB of captured data, query XZ executed 900 times and query AA execute 765 times and so forth.)

That one is being implemented. I am curious as to what others think would be good stats to gather with the stats part of the package. For now at least, stay away from stats requiring the tracking of time. We are working on how to implement time stamping, but it may not even be possible. Feel free to comment on the posting with statistics features. If you want, you can email me at “kmurphy” at “icontact.com” with these request.
We are making significant progress on the tool bundle. I hope to release some type of beta next week. I actually tested the querybench program yesterday with data from a production server but it didn’t work. Releasing a beta next week isn’t a promise but we are working hard on it and I think there is a reasonable chance it will happen. I will post as soon as there is something for everyone to work with.

So, iContact has secured funding for our continued expansion. One online copy of the press release is here:

http://www.vcaonline.com/news/news.asp?ID=2007062901

So, its been a while.   About two months ago I accepted a new job with a company in Durham, NC. The company is called iContact and it has been a blast. I started on May the 29th so it has been just over a month. We have a fairly complex application and quite a bit of data. In addition, we are growing at a nice rate (and the growth continues to accelerate). I am looking forward to the challenge of not only managing what we have but also preparing for the scaling out of the system as it grows. I will be blogging about some of the things going on around here with the hope that I can be of some help to others in the community.

I have decided to start doing a MySQL podcast. I have been working with MySQL longer than the average bear (MySQL was first released to public in 1995 - I started using it around 1998 or 1999). I have seen it mature into a stable product around the time of the the 3.23 release. During this time frame I worked at Internet company in the San Francisco area. We had a Sun box (an E4500 if memory serves) that ran our database. We were receiving 180 million queries a day on the database. Day after day after day…. This was in 2000. Since then, of course, even more features have been added. With the clustering features of 5.x you have a database system that is as good as anything available at any cost. For the price of the hardware you can build a system that is as large as needed. Server blows ups on you..no problem..True “5 9’s” stuff. In fact, the clustering was originally developed for Telcos. This type of uptime is mission-critical to them.

Sorry for the long ramble. For a product with so many installations (10 million estimated) - there really is very little information available. I only find one podcast that is exclusively about MySQL. So, I decided to throw my hat into the ring. I will try and cover as much as is feasible when it comes to MySQL. I will concentrate on DBA information but I am sure we will swing over into developer land from time to time. I will try and bring in people from the front line to talk about their work and what is their perspective of MySQL. I encourage feedback from the listeners. Just send email to bmurphy at paragon-cs.com. I am going to begin planning the first ‘cast and getting it recorded. I am planning on two ‘cast a month. Look for the first one to be available around the end of February.

So, here it is. My first official blog. I have been actively involved with computers and the Internet since around 1992. I also happen to write - I am finishing the final copy of my first book (shameless plug..it’s on computer and network security..will be available second quarter of 2007..stay tuned). Even so, I have never been drawn to the whole blogging thing. I don’t know..just didn’t. Anyways, I am going to be using this blog to explain some of the new services we are making available at Paragon Consulting Services. Paragon is the company I started a few years ago doing consulting work. So this is where we start.

Note about the name of the blog. Diamond Notes may seem a weird name. Maybe so, but there is a reason behind the name. The reason why I chose Paragon as the name for my consulting services is that paragon means a model of excellence. It also means a large diamond - hence Diamond Notes.