I had the chance to review the new Query Analyzer program from Sun over the last few days. I am very interested in how it performs as I have previously not had a chance to see the program in action (or the Enterprise Monitor program either for that matter). So, before getting into what the Query Analyzer can (and cannot) do let’s look at what Sun says it does.

From the November 19th, 2008 press release:

The MySQL Query Analyzer saves time and effort in finding and fixing problem queries by providing:

• Aggregated view into query execution counts, run time, result sets across all MySQL servers with no dependence on MySQL logs or SHOW PROCESSLIST
• Sortable views by all monitored statistics
• Searchable and sortable queries by query type, content, server, database, date/time, interval range, and “when first seen”
• Historical and real-time analysis of all queries across all servers
• Drill downs into sampled query execution statistics, fully qualified with variable substitutions, and EXPLAIN results

Can you do all this with other tools or by hand?

Absolutely. Some of the those very tools I have discussed before. Most I have used at least once or twice. The question is really can Query Analyzer do this better? Read the rest of this entry »

Hello and welcome to my Log Buffer, the weekly review of database blogs. My name is Keith Murphy and I am the editor of MySQL Magazine, a free digital magazine with information for MySQL administrators and developers, released quarterly.  I am also a MySQL database administrator at Pythian.

I am losing count of how many of these Log Buffers I have done. If I recall correctly, this is number three. But then again, it could be number four. You know what they say though—the more the merrier!

This week’s Buffer features a great allegory about a database, some tips, feature requests, and breaking news. It seems that this week, people shared information that was relevant across database servers, so I recommend that no matter the database server you work on, you should read through each section today.

MySQL

Since the writer of the Buffer gets to choose the order of the stories, I will begin with my favorite db—MySQL. This week the MySQL world saw  some benchmarking of testing of recent improvements to the XtraDB storage engine. the discussion generated around what was done to improve the performance on a 16-core box was really interesting. I’m looking forward to working with the XtraDB engine.

Read the rest of this entry »

Had an interesting situation come up today with a client. We had a situation where a server crashed because it ran out of memory. The calculation we used to monitor memory usage did not take into account all factors. When looking at this, I noticed a couple of things:

1. There are numerous calculations available online.
2. It seems that none of them take everything in account.

Wouldn’t it be nice if there was a total possible memory consumption status value? It can be a valuable piece of information. (And while I am requesting things, what about peak memory usage by the mysqld server since it has been online?) Realistically, there is almost no case where you would actually use the maximum amount of memory. But knowing how much memory that MySQL server could possibly use would be a very useful bit of information to know.

Second, why isn’t there an “official” formula that actually shows everything? It’s easy to forget things. Just as the simple case, what about the query cache? Did you remember it?

Read the rest of this entry »

It’s that time again. Time for fame and fortune! Sorry, as usual there is no fortune, but the good news is that our readership continues to grow, so author fame is imminent. We are planning the winter issue of MySQL Magazine. With the new GA release of MySQL server 5.1 last month, there is certainly plenty to talk about!

You can reach me with your ideas at bmurphy@paragon-cs.com . I need to see all article proposals by the 15^th of this month. Draft articles must be in by the 10^th of January, so if I accept your proposal, you have a month to get things ready. I should also mention that in the past we have used blog postings (with author permission and full credit of course). If you have blogged something recently that you feel is worthy of publishing in the magazine, send me the link and I will take a look.

Never heard of MySQL Magazine? It is a digital magazine designed for both database administrators and developers of the MySQL database server. All six back-issues are available for free download from the website.

I was doing some research over the weekend on how transactions work “under the hood” in MySQL. I thought it might be enlightening if I wrote about what I found.

The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes?

Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

Read the rest of this entry »

For those of you who have been under a rock for the last several years, there is a buzz-phrase floating around—cloud computing. If you haven’t been paying attention, it is time to wake up.

While I could spend an entire blog post—if not several—on a definition of cloud computing, I will be talking only about cloud computing in the sense of companies moving servers from their building or network operations center to running virtual servers in this computing cloud.

While there are a number of companies providing virtual servers, the most visible is Amazon, with their Amazon Web Services (AWS). I will be talking about AWS in this post as it is the service with which I am most familiar. It seems like every month, AWS rolls out new options and services. Just recently Amazon announced that you can now run on AWS the Windows operating system along with SQL Server.

Amazon also announced a service level agreement (SLA) of 99.5%. The SLA is important. It is a guarantee of service uptime. If Amazon don’t meet the SLA, then you get money back. As any of you will know, you have to be able to count on your data center. 99.5% is a pretty good level of coverage.

Beyond that, one of the new features Amazon will be implementing during the next year is the use of regions and availability zones. Regions are distinctly different areas of a country (or completely separate countries); availability zones are designed to be insulated from failures in other availability zones and provide inexpensive, low-latency network connectivity to other availability zones in the same region. What does this mean? It will soon be very easy to deploy a set of servers in different areas and/or regions so that your data and servers are spread out and not vulnerable to a single point of failure.

I am not going to go into any detail about how virtual servers work. That’s not the point of this post. I am going to concentrate on what you can do with virtual servers.

Read the rest of this entry »

I have put up a poll on my personal blog, Diamond Notes asking whether you prefer Ubuntu or Debian as an operating system for MySQL server. I am curious because I have seen a shift in the last year of Debian users to the Ubuntu distribution, and I would like to put some actual numbers to what I am seeing. (Please don’t vote if you use another OS or another Linux distribution, as I am strictly interested in the usage patterns of these two operating systems.)

Thanks,
Keith.

This week gives me a chance to get back into something I love to do—write. For those who don’t know, my name is Keith Murphy and I am a MySQL DBA at the Pythian Group. In addition, I have the privilege of being the editor of the MySQL Magazine, a quarterly  magazine for those who use MySQL on a daily basis, either as a DBA or a developer. The sixth issue was just released last week and is available for download now. But enough about me! Let’s see what you all had to say this week.

Beginning with the world of MySQL.

Monty Taylor kicks things off, bringing us news of the ability to use the innodb plugin with Drizzle. I just think its great how the Drizzle development is moving along so rapidly. My thanks to everyone involved.  Monty also puts out a call to help defray the cost of the purchase of the drizzle.org domain name.

There have been several posts this week on virtualization of MySQL Server. I point you to a colleague at Pythian, Sheeri Cabral, who wrote about some of the benefits our customers have already experienced while using virtualized servers. While virtualized servers are not a panacea, they definitely have a place in the DBA’s environment. If you don’t work with any currently, you will be doing so down the road.

The OpenSQL “unconference” is coming! The date is Nov 14 -16 in Charlottesville, Virginia, USA. Details and registration information are available here. Speaking of conferences, the MySQL Users Conference, which is in April in Santa Clara, CA, USA has extended its deadline for topic proposals.

Baron has a great post on naming conventions for your schema.

In the how-to department, Falko Timme has a good tutorial on how to set up GreenSQL to protect your databases from SQL Injection attacks. Finally, Johan Andersson has a good introductory blog post on optimizing queries for a NDB cluster, and a post on how you can get the Cluster Sandbox tool for testing the MySQL Cluster.

The Oracle world had sad news this week. Carl Backstrom, who was an Oracle employee and APEX developer, was killed early Sunday morning in a car accident in Nevada. Read the rest of this entry »

Or, There is a Use for GUI Tools!

Recently I was working on a problem for a customer. They are converting a server with two InnoDB data files to innodb_file_per_table:

$ ls -l ibdata*
-rwxr-xr-x   1 mysql    mysql       2.0G Oct 14 13:10 ibdata1
-rwxr-xr-x   1 mysql    mysql       350G Oct 14 13:10 ibdata2

I honestly don’t recall ever seeing (or hearing about) so large a data file.

The method chosen for conversion boils down to this:

1. stop and start the server to enable innodb_file_per_table
2. alter all tables to myisam
3. stop server and delete ibdata file
4. restart server
5. convert tables back to InnoDB
6. add foreign keys

This post isn’t about how we did it, or about whether or not it was the best way, or anything like that. This post is really about the the last two steps.

There were over 500 InnoDB tables and something like 375 foreign keys. So I needed to somehow create the ALTER TABLE statements for these 500 servers. To drop the Foreign Keys, I used the following SELECT statement:

SELECT CONCAT( "mysql -u root -pPASS ", table_schema," -e 'ALTER TABLE ",table_name, ' DROP FOREIGN KEY ', constraint_name,';'' &') AS list_of_fks FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME is not null ORDER BY TABLE_NAME, COLUMN_NAME;

This created a list of statements that I could then put in a batch file, edit a little, and have them run in parallel.

I used the following SELECT statement to find all InnoDB tables and prepare a command to convert them to MyISAM:

SELECT CONCAT("mysql -u root -pPASS ", TABLE_SCHEMA, " -e 'ALTER TABLE ", TABLE_NAME, " ENGINE=MYISAM;' & ") FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='INNODB';

Again, the output can be put in a batch file, modified a little, and run in parallel.

Once everything was converted to MyISAM, I shut down the database and removed the InnoDB log and data files and modified the my.cnf to so I had file_per_table and a much smaller InnoDB data file. That was the fairly straightforward part. Now the fun began. Read the rest of this entry »

In many parts of the world times are uncertain. I live in the United States and we are in the middle of a financial meltdown that many fear may be as bad as the Great Depression. Because the world’s economies are so linked it is causing severe distress in many other countries as well. I just read that two trillion dollars have been lost from nest eggs in the last 15 months here in the States.

I am not going to turn this into a rant about who is right, who is wrong, or  what should be done about it to resolve the problem. This isn’t the place. I probably don’t even have the right answer. I have a different angle.

If you are involved with MySQL as a database administrator, or if you work directly with MySQL in some other aspect, you can probably breathe a little easier. Why is this? MySQL Server has grown in market penetration for a long time. It is now a significant section of the RDBMS pie.  I predict that this market penetration will only continue to grow. As this economic downturn/recession/whatever continues, companies will look harder for ways to save money. What better way to do so than replace your proprietary RDBMS that can cost you significant amounts of money, with MySQL Server? For all intents, the same functionality is there, the speed and flexibility is certainly there, and there is a giant company behind MySQL now, providing “enterprise-ready” support.

The market is crying right now for MySQL database administrators. We don’t cost any more than Oracle or Microsoft DBAs, you know. Just a couple of years ago, very few companies hired MySQL DBAs. They hired developers who also did database administration, or a system administrators who also managed the MySQL server. Now, as the number of database servers increases and the amount of data grows they want real, honest-to-goodness database administrators. If you have production experience with MySQL server in any significant amount you will not have any problems finding a job. I don’t think this is going to change anytime soon. So, even if your company succumbs to the times, there are others out there who need your experience. Don’t be dismayed! Read the rest of this entry »