Limit on General Query Log Size?

I ran into a rather interesting situation today with a client. It seems that the mysqld daemon stopped with no errors in the error log. I ran through the obvious problems … not enough disk space, memory utilization etc and came up empty.

The server was running MySQL 4.1 on Fedora Core 5. We can save the discussion about running your database on reasonable up to date hardware and operating system for another post. Core 5 runs the GNU/Linux kernel 2.4 along with the ext3 filesystem and so the thought was in the back of my mind that it might be an issue with file size. Well, as Sun’s own documentation shows this shouldn’t be the case.

During the investigation it was uncovered that the general query log was not only enabled but 16 gigabytes in size. Aside from being so large it was absolutely useless for anything, it was the obvious culprit for too large a file. After zapping the log file, it was possible to start the MySQL server  successfully. It was then that I looked up the previously referenced information about file size limits in the kernel and MySQL itself and found out that 16 GB shouldn’t have been a problem.

Checking through bugs.mysql.com didn’t uncover anything. I have already thought of how I can reproduce the problem pretty painlessly if it’s a general problem and not something specific to Fedora Core 5. But before I take several hours to run tests I wanted to see if anyone else had heard of this issue.

The rather obvious lessons that the client should take away from this:

1) There was no reason to have the general query log on in the first place and it should have been turned off.

2) If you need to use either slow or general query logging taking the time to set up log rotation.

Ever seen this before? If so please take 30 seconds and let me know. I would really appreciate it. And who knows, it could be one of your servers down the road that could be saved if it really is a bug. Of course you would never let your log files get that large, right? Right?

MySQL Administator’s Bible Book Giveway Winner!!

Last week I offered a chance for a reader to win a copy of the MySQL Administrator’s Bible. The reason why is that I was recording an interview with Brian Aker about the Drizzle project and wanted to hear what you thought I should ask Brian. There were some excellent responses. I worked as many of them as possible into the interview although there were some that for legal reasons we could not cover.  Even if your question was not included your name was included in the drawing. Thanks everyone for the input. The interview with Brian will be part of the  Open Source Database podcast scheduled to be released on July the 24th.

And now for the winner!

Congratulations Chadwick. I hope you enjoy the book.

Free Book Giveaway

Now that I have your attention, this is actually an update to my last post.  In about four hours I have an interview with Brian Aker for the new podcast I am putting together in conjunction with Open Source Database Magazine.  While I have enough questions for a good interview, I am always looking for more possibilities. Yesterday I said I would randomly choose a name from people who emailed me or commented on the post with a question for Brian about Drizzle and the winner would receive a free copy of MySQL Administrator’s Bible. While I have received some response, it has not been overwhelming. So here is your last chance!! Send me an email (bmurphy AT paragon-cs.com) or comment on the post. Throw your hat in  ring.  As I said previously, because of shipping cost, I have to limit this to people in the United States.

Good luck!!

Ask Brian Aker!

Thursday night I will be recording a conversation with Brian Aker about his work with the Drizzle database.  We will cover everything from why Drizzle was created to what he is currently doing day-to-day.

This will be turned into the initial podcast for the  Open Source Database Magazine podcast.  My goal is release a podcast around the same time as each issue of the magazine. While the magazine will certainly be technical in nature, I don’t see the podcast as going that direction. The podcast will focus more on the people behind the open source products.  The “human side” of open source databases. I find printed interviews of people to be minimally satisfying at best. Interviews are a natural for podcasts. Can’t say that technical content won’t ever sneak in there — but it will definitely focus on the “softer” side of open source databases.

Here is your chance to participate. Either send me an email (bmurphy AT paragon-cs.com) or comment on this post with ideas for questions. I already have a number of questions that I have developed, but there is room for more.

Plus, I will randomly select (random.org!) a winner from those who submit questions and send them a copy of the MySQL Administrator’s Bible that Sheeri and I wrote.  I will have to limit the prize to people who live in the United States as otherwise it shipping will be cost-prohibitive.  This is purely random and isn’t going to be based on what I think is the best question.

I don’t think I will have time to cover every question, but here is your chance to ask Brian what’s on your mind.

PostgreSQL 8.4 Released

Josh Berkus just announced the release of PostgreSQL 8.4. The summer issue of Open Source Database Magazine (out July 15th) will include a story on the new features of PostgreSQL 8.4.  Go forth and download!! Below is the text of Josh’s email giving an outline of the new features:

————————————

The PostgreSQL Global Development Group has released version 8.4, continuing the rapid development of the world’s most advanced open source database. This release contains an abundance of enhancements to make administering, querying, and programming of PostgreSQL databases easier than ever before. Our development team has spent 16 months adding over two hundred improvements to all aspects of database functionality, helping every PostgreSQL user in small or large ways.

Many of the changes in PostgreSQL 8.4 are new or improved administration and monitoring tools and commands. Each user has their own favorite features which will make day-to-day work with PostgreSQL easier and more productive for them. Among the most popular enhancements are:

* Parallel Database Restore
speeding up recovery from backup up to 8 times
* Per-Column Permissions
allowing more granular control of sensitive data
* Per-database Collation Support
making PostgreSQL more useful in multi-lingual environments
* In-place Upgrades through pg_migrator (beta)
enabling upgrades from 8.3 to 8.4 without extensive downtime
* New Query Monitoring Tools
giving administrators more insight into query activity
* Greatly Reduced VACUUM Overhead
through the Visibility Map
* New Monitoring Tools
for current queries, query load and deadlocks

Version 8.4 also makes data analysis easier through the advanced ANSI SQL2003 features of windowing functions, common table expressions and recursive queries. Enhancements to stored procedures, such as default parameters and variadic parameters, make database server programming simpler and more compact. Of course, there are also performance improvements included in this version.

MySQL Magazine is dead.. long live Open Source Database Magazine!

Hello everyone,

I began MySQL Magazine in the early summer of 2007. It was began, not as a lark, but without any idea that it would ever become so popular. It was just a way I thought I could contribute to the MySQL community. Over time it has grown very well with recent issues being downloaded around 10,000 times directly from the website. I have no way of counting other downloads although I know issues are posted in multiple other locations.

I have been debating for about six months on changing things up a bit. As I said in my last post, change for the sake of change is almost never good. This isn’t change just for change’s sake.   I am convinced that enlarging the old MySQL Magazine to include any open source db will be beneficial to everyone. More content and an exchange of knowledge across “cultures” so to speak.  The last several years have seen the rise of various databases that fit the needs of cloud and large-scale computing such as Hadoop. Even more recently, the Drizzle project has gained quite a bit of steam. PostgreSQL has been around since roughly the time period of the cooling of the earth’s crust and has a devoted fan base (and a great product in the appropriate place I might add!!). The list could continue but I would like to keep this blog post a reasonable length.

I plan on writing about both Drizzle and PostgreSQL in addition to my normal MySQL fare. While my knowledge of  either of those systems is minimal at best, they are areas of interest to me and there is always a need for articles targeted at noobs like me who need introductory material. I can at least supply that!

I would point out that exchanging knowledge, as I termed it earlier,  is not mudslinging. I invite comparisons between systems (where appropriate) but if a comparison is not backed up by performance data or supportable by facts it will not be published. Not looking for flame wars, I am looking for a place where developers and administrators can go to learn more about all open source databases. Hope that makes sense!

Articles for the new magazine can be about any of these previously mentioned and any other database that operates under an open source license. MySQL Magazine had been running roughly 20 – 25 pages in size over the last three or four issues of the magazine. I would like to increase the size to around 30-35 pages if possible. I always encourage articles about the administration side of the server as well as the coding side of the server.

I will be using Scribus to build the magazine. It allows decent control over layout, reasonable pdf output and is a fine open source project in its own right. Submissions can be pdf, Open Office or MS Office. As with MySQL Magazine, as long as I have advertisers, if article authors have access to a paypal account I pay $50 an article (plus all the fame you can handle ).

So, if you administer or develop for an open source database, now is the time to step up. I will be publishing the first issue on July the 15th which means all finalized versions of articles must submitted by July the 7th.

The new magazine will reside @ osdbzine.net.  I just registered the domain so it will take some time for it to propagate through the ‘net. Right now there is only a placeholder for the site.  I will build up some content between now and July the 15th.

Have an idea for an article? Email me at bmurphy AT paragon-cs.net. No need to send a full-length article. Just a brief 50 – 100 word outline of what you are proposing. And don’t be scared. If I recall correctly, I have never turned anyone down. Looking forward to hearing from everyone.

Thoughts on the new release model

After hearing rumblings that a new release model was coming and then announcements that a new model was coming, I took the time  to watch the MySQL University session on it Thursday. I have thought for a long time that previous method of release management was less than perfect for MySQL Server resulting in long time periods between “GA” releases.

After listening to the session I have a great deal of hope that this will be a very positive change. I would recommend that you go listen to the session when it is posted (the pdf of slides are available but not the dim dim recording yet). It is very thorough. The basics are that server development is being moved to what is called a “Milestone Release Model”. These milestone releases will occur every three to six months and it is planned that they be of RC quality. Not every milestone will turn into a GA release. It seems to me (although I am not completely clear on this) that a GA release will occur every two or three milestones — which means a new GA occurs every year to eighteen months which is much more manageable than some of the previous release cycles.

How do you increase the GA release rate while not increasing the number of bugs? A significant way would be release fewer “major features” with each GA. If you are only incorporating two or three major features with a GA release it becomes much more manageable. In addition features won’t enter the “trunk” — the code that is at least RC quality — until the major bugs have been worked out. There are branches off this trunk for the development code.

Another important point is that while the milestones have specific features planned for them (say online backup), they are not set in stone. Rather than requiring that a certain feature is “shipped” with a milestone, a feature can be pulled from a release cycle in order to insure that the milestone is reached within the determined time. To me, this is a fantastic feature of this model. Why should any one feature hold up the entire process just so it can stabilize and be included? Stabilization/bug fixing of those features can (and will) take place in a separate branch if I understand correctly. Once a feature becomes more stable it could be included in the next release cycle.

Where does this leave MySQL server 6.0? I am afraid that 6.0 is going to become the orphan of the MySQL world. The May the 22nd announcement of the 6.0.11 release contained this:
“6.0.11 will be the last release of 6.0. After this we will be
transitioning into a New Release Model for the MySQL Server”
The good news is that it is planned for the features of 6.0 (online backup et al.) will rolled into future server versions. Just as an interesting aside, when looking up the above quote I discovered the change notes for 6.0.12 which is labeled as “not yet released”.

The initial milestones have already been named:

summit
azalea
betony
celosia

Summit is the current milestone where work is occurring – it evidently began in April. I assume this was in conjunction with the announcement/release of MySQL Server 5.4 at the user conferenece. Azalea, the next milestone, either begin in late May or in June (it wasn’t totally clear to me). All the details are available in the university session. I would highly recommend that you take the time to view the session so you have a good understanding of the new development model.

All in all, I think this is going to be a good step for MySQL Server. According to information I have received, Oracle/Sun is trying to work more with the community to include them in the process. The process should make it easier for community contributions to be included. Not only that, with this model, it shouldn’t take two plus years for any community contributions to be included.

This is a win for everyone as far as I can tell, if things work out as planned. The end-user/customer gets new features when they are ready without waiting for other features to stabilize, the community gets a model that should be more open, Oracle/Sun gets more contributions from the community and the incredible 37 month release cycle for MySQL 5.1 is a distant memory. The internal developers will potentially get to see their work “out in the wild” much sooner.

Change for the sake of change is almost never good. But this a case of change where it is needed. My thanks to those involved in making this happen!

Dear Larry

Dear Larry,

I know that you have no idea who I am. That’s OK. Even so, I would like to request that you please place the ZFS file system under the GPL license. In case you don’t know the ZFS file system is a nice, shiny file system that is part of the Solaris operating system that you acquired along with the rest of Sun’s assets last month.

ZFS has some really nice built in features for a file system. This includes my favorite feature – the ability to take a snapshot of a file system partition that is in use. While this can be done under Linux through the use of  LVM, it’s a bit of a hack (although a hack I appreciate LVM developers!!). Having it all incorporated into the file system would be really nifty.

Currently ZFS is licensed under CDDL.   The problem is that this license isn’t compatible with the more typical GPL license. Oh, and there is 56 patents relating to ZFS. I don’t want to turn this into a discussion of patents, but unless the code is released under GPL there is no way it can be incorporated into Linux. Can I be honest? All this license talk gives me a headache. Let’s break this down so I can understand it. Do you know how many systems out there run Linux, Larry? If ZFS became the de facto file system for Solaris, OS X and Linux you would rule the world! Just think about all those machines out there running your code. You would be bigger than Bill. Just a thought

I am sure there are some really smart people at Oracle who could figure out how accomplish this in no time. Just send out the proper email and it could be done in a week I bet. One little act and you would build a huge pile of credibility in a snap. And yes, “huge pile” is a technical term for “lots”.

While I certainly can’t speak for Oracle database administrators, I am willing to bet that those who run Oracle on Linux would love this also. It’s not just us MySQL people who could use ZFS.

All of us who use Linux on a daily basis would really appreciate it. Just one less thing to worry about.

Looking forward to hearing from you.

Sincerely,

Keith

“Just a MySQL DBA out here in the trenches”

Sad news

The following was in the just released monthly bug report for the Falcon storage engine:

“With the news that Sun has aggreed to be purchaced by Oracle, Some inevitable changes will occur. Once the acquisition is made, the need for Falcon as a MySQL storage engine will be re-evaluated. Until then, Falcon will continue to improve stability and performance. The team will also evaluate other technical niches that may be unique to Falcon.”

I for one would be very disappointed to see Falcon not supported by Oracle. I know they have worked very hard to create a next-generation storage engine.  While it could be argued that InnoDB can fill all use cases, I believe that choices are a good thing and having one less choice is not a good thing.

Good luck all on the team. You have been nothing but kind and generous when answering my dumb questions via email and in person. You can count my vote for “keep it!!”.

Scale out when it makes sense

Not to rehash an old argument, but I am watching a recent  video on performance work being done by Sun hosted by Allan Packer (a recorded MySQL University session) and one of his bullet points was “Scale out when it makes sense, rather than just because there is no alternative”. That is what they are working on in his group at Sun.

You mean I will be able to buy 8 and 16 cores boxes and be able to use all the cores?

Hallelujah and Amen brother

Allan, thanks to you and all other who are working on this both inside and outside Sun.