Good morning and welcome today to the administering MySQL database. My name is Chris Spalazzo and I'm in developer relations and I work very closely with our good friends at MySQL and Live World. Today we're going to have two speakers talk to you. One is going to be JD Duncan who's a consulting engineer from MySQL who will be showing you a number of different tools on how to do administration with your MySQL database. The other is going to be Bruce Dembecki who will be showing you a real-world deployment of how Live World uses MySQL running on XSERVs to make all their products available. So without any further ado, let me introduce to you JD Duncan from MySQL. JD, come on up.

Hi, I'm John David Duncan. People know me as JD. I'm a consulting engineer for MySQL. I'm based here in San Francisco. I'm really only going to talk to you for a few minutes. I'm going to talk about in administering MySQL land about tools that are available really in two groups of tools.

The first is administration in the sense of looking at your data structure, creating users and grants, kind of basic essential DBA tasks. The second set of tools are monitoring tools. Things you use after a database server is in production to keep an eye on it. After that, after just a few minutes of that, I will turn it over to Bruce from Live World, who can talk about real-world experiences doing this.

So the administration world in MySQL begins with the command line tools. These are shipped as part of the database server. Everybody who has MySQL running has them. And if you've ever done anything with the database, they're really the first thing you use. There are a good two dozen of these that you download with the package.

The most important, MySQL, is the MySQL command line tool. You simply use it for interactive queries of any sort. And then the rest of them are more specialized. A couple of the more significant ones, MySQL Admin provides a kind of scriptable interface for command line tasks. Things like stopping the server. giving grants.

MySQL dump you use to export your database and can be used to make backups. And then there's one called MySQL binlog that's an important utility for managing your replication slaves and can also be used to take a backup and do point in time recovery for it. And then there are a bunch more after that.

MySQL internally has a small team of developers who create graphical tools. And these are also available for free there at MySQL.com. The more advanced one is called MySQL Administrator. It runs on Windows and on Linux and on Macs. It's a very, very nice looking kind of baseline graphical administration tool for databases.

I'll show it to you in just a minute here. And I'll also show you MySQL Query Browser, which is more a tool along the lines of 4D or Access for for actually generating ad hoc queries and looking at your data and manipulating your data. The team that creates these has a few more things going on. They've got a nice MySQL migration toolkit. It doesn't run on Mac. I can't show it to you right now. And they've got a whole schedule of future projects.

So from there, let's go to the demo. We're here in my laptop. This terminal window right here should probably be familiar to just about everyone who's ever used MySQL. Here on my laptop, I'm kind of cutting edge. I've got a MySQL 5 server running. One of the newer things in this tool that came along with MySQL 4.1, which a lot of people don't know about and probably will be happy to see, is interactive help.

So I Now you've always been able to type help and get client-side help just in the tool. But now you can type help contents, and you actually get server-side help. And if we're interested in administration, you can type help administration and get a list of topics. And we're actually going to go help create user. And we get the syntax for create user command. And I'm actually going to create one.

So there's the MySQL command line tool with its new help feature. Now let's actually get into the graphical tools, go into MySQL Administrator, and look at this user we've just created. This is the main screen of MySQL Administrator. tells me that I'm connected to a MySQL 503 alpha server via local host. That tells me the version of my client library. My operating systems on the client end says that the server is running. Just a sort of basic overview screen. And I'm going to go over here to accounts.

And I see a number of these users that I've been creating. Let's delete one or two of them. I'll just click on the minus down here to delete a user. Here's the one I had just created, I think, the WWDC1. I'll actually create another user in here. Now a user in MySQL always has a host, so I have to go to these little tabs down here. Say that I'm creating a user at any host.

And here we are. I think that user administration is probably the most important feature of these graphical tools. And that even as a person with a Unix background and a really command line oriented way of doing things, I know that grant syntax is awkward. And that important production database systems, you really want a more powerful tool along the lines of this for your administration.

And this even in our intent is a baseline sort of administration tool. We rely on MySQL as a company relies on third party vendors to create more powerful tools for you. I'll go into another capability of MySQL administrator here. I've gone to backup and I'll show a little bit about database backups.

Here I have a backup project. The project is called System Tables, and my System Tables backup project is a way of backing up the MySQL database, the system database, with all the tables in it. So I'm just going to go down here to start backup. It generates a file name for me with a date and time stamp in it. Builds me a backup. This runs MySQL dump, if you're familiar with it. I go into the next pane to restore.

I'm going to choose a file to restore from. I think here's the backup I just made, system tables 2005, '06, '09. I'm going to open it. And I've got a dump of the whole database there, but say all I really want This is the big MySQL 5 system tables with stored procedures in them. All I really want are my user tables and my host tables, so I select them.

I've restored the backup. One more thing I'll show you in administrator. is the catalog's view. Just gives me a A view of all the databases I have, the tables in the databases. So here, this one's called US Geo. It's got some United States geographical data. In this tool in Administrator, I can see my data structure, but I can't do anything with it. To actually use that data, I'll go over here into Query Browser, the lightning bolt.

Here's my US Geo table. Again, I'm running MySQL 5, so I have a table called County that's got all the counties in the country, and then I have a view built on that table called SC County that only contains counties in South Carolina. I double-click on this. And up here in the query box at the top of the screen, the tool has generated a query for me.

I click on execute and I get my data. Now, MySQL Query Browser, the Mac version of it was just released this week. It's in alpha. It's actually missing the most powerful tool bar on here that would allow you to create more complex queries and joins. So as the future versions are released over the next weeks and months, we'll get the full functionality in this tool. This is our early release of MySQL Query Browser. So those are the three tools I wanted to show you, and let's go back to the presentation, please.

Those are our tools provided here at MySQL. We really rely on other companies to create more powerful administration tools, either as commercial products. And this one I wanted to point out, Navicat. It's from Navicat.com. It's a really nice looking tool on OS X and pretty powerful. And then as an open source project and a web-based administration tool, phpMyAdmin is very commonly used and certainly has a lot of functionality.

So that's the look at administration type tools, managing users, looking at your data structure. And from there we go on to the tools you use when a database environment is running in production, really for two functions. One is monitoring, monitoring performance and health of the system. And most of the best general purpose network monitoring software in the open source world is based on RRD. It's all from rrdtool.org. Here's a sample strip chart of some monitoring you can get out of a MySQL database server.

Other open source projects, if you want to be alerted, say if your database server goes down or if your slave has stopped replicating, Nagios from Nagios.org is a multi-function general purpose network monitoring system that MySQL plugs into very well. Also, a very young tool created by the internal staff at Google is the Google MySQL Monitoring and Investigation Module. It's what they use internally for their MySQL servers and it's available from code.google.com.

I'll talk a little bit about where to go for more information. The MySQL Developer Zone, dev.mysql.com. There are a large number of forums on real specific topics. I recommend them highly because you can usually find just the right audience for something particular you're interested in. These are pretty new.

Mailing lists, we've had for a much longer time. Really, the general MySQL mailing list goes back 10 years and it's on everything. But the fact is the volume of it is so high it's kind of unreadable. I find it unreadable. But there's still a lot of traffic going on there for questions and answers. There's a bugs database at bugs.mysql.com. And there's the online manual, which is always the most up-to-date MySQL documentation. So MySQL is a company.

has a professional services staff and has a subscription services model that you can buy per server per year. We call it MySQL Network. It gives you access to our support team to support levels up to 24 by 7 and to a set of tools that we hope are valuable before you have a support kind of incident, like a knowledge base, things that you can use before you can get in trouble.

We also have a consulting staff. I'm sometimes part of it. We do things like architecture and design, performance tuning engagements. With that, I want to turn it over to Bruce Dembecki from Live World, who does this stuff every day, runs a whole lot of websites, and can talk about a real mission-critical, complex, multi-server, master-slave MySQL situation. Thanks, JD.

Well, good morning. My name's Bruce. I'm from LiveWorld. There's a lot of stuff that we'll be talking about, and if you want any of the information that I've got or some help with some of the tools that I show you later, feel free to email me afterwards, bruce at liveworld.com.

Well, good morning. My name's Bruce. I'm from LiveWorld. There's a lot of stuff that we'll be talking about, and if you want any of the information that I've got or some help with some of the tools that I show you later, feel free to email me afterwards, bruce at liveworld.com.

The first thing we're going to cover very briefly is, you know, why Live World? What is it that I'm doing on this stage? Why am I here and not the folks at MySQL or Apple? Then we're going to take a little look at our database servers. Different versions of MySQL, and I'm not talking now about whether it should be 4.0 or 4.1 or 5.0, but rather, whose binary should you use? Should it be Apple's binary, MySQL's binary? Should you build your own? and then we'll spend quite a bit of time on deploying MySQL and the various issues that get involved in that.

So, for starters, why LiveWorld? What are we doing here? Who are we? I'm going to read the slide for a minute. This is about the only time I'm going to do that today. We are a leading provider of collaborative community solutions since 1996. That pretty much means we do discussion boards and chat rooms and a variety of other community tools for our clients.

Before 1996, everybody on our executive team and most of our senior engineers worked at Apple actually, in the online group at Apple, and they were responsible for bringing you things like Apple Link, eWorld, and before that the Apple Link Personal Edition, which went on to become a different product called America Online.

So we know a lot about online community and online stuff. We are a public and profitable company. We work closely with MySQL regularly. When we discover problems and issues, we share them with MySQL. And to date, we've had half a dozen major bugs, and every single one has had a fix in the code within 24 hours of us reporting it. So we're very impressed with MySQL. We love working with them. They fix problems quickly and very well.

We actually have a very high volume series of sites and we have a high redundancy configuration. We're a little bit old fashioned in that we deploy everything in at least pairs so that if something breaks there's another one sitting there right away ready to go. And just by the pure nature of who we do our discussion boards for, we have a very high profile and high volume client base.

To give you some ideas, these are some of the companies that we've worked with over the last few years. If you were to go to eBay.com and go to visit any of the community tools such as the discussion boards or the clubs, that would be actually running on our servers out of our data center. Same with HBO, BEA, and so on.

Some examples: BEA's newsgroups, the developer-to-developer support area. In a previous life, it was just a newsgroup. BEA wanted to do something more, so we actually turned it into a discussion board and linked it back into an NTP server. It's still available as a newsfeed if you want it, but now it's also an interactive web discussion board and it has an RSS feed if we want to step another generation ahead. eBay has a clubs tool that they deploy in several different countries. This one is eBay Germany's clubs tool. eBay discussion boards, in this case in China. eBay US, QVC, A&E.

and a variety of other things. Dove, the soap company, they have an interesting program that they launched quite a few months back and they're rolling it out into different countries. So far we're at 27 different countries and I believe there's another 14 to go live in the next few months. And the basic concept is that they're talking about beauty products. You know what? When it comes to beauty products, it's all Greek to me.

and Expedia. I guess some of you have used Expedia and you might have looked at a hotel. You've noticed over the last few months when you're looking for a hotel on Expedia, they're now giving you user feedback on that particular hotel. So that's actually coming out of our servers and it's built on database discussion about technology.

While we have other products that work with chats and online events, the focus of our discussion this morning is going to be on our discussion boards because that's where we're looking at our MySQL statistics and where we're using the database most heavily. Some facts from last month's numbers. We have 18 active discussion board clients and 91 live discussion boards. They service 30 countries and 20 different languages. So we've had a lot of fun and games with character sets.

Last month we did 84 million dynamic page views. It's very important to consider this. When you're doing a discussion board and you're creating each page on the fly, customize to each user saying, "Welcome, Bruce. You are logged in." Every time you do one of those pages, it's quite a lot of work to put together the page. Not just 84 million pages, 84 million dynamic pages.

In Database Land we did that with four and a bit MySQL servers. That's 1400 queries per second in May, which is, I don't know how many billion queries, but it was a lot. With our 91 discussion boards we have 91 databases and a whole bunch of backups every day and server snapshots that we do in our database. We'll talk some more about that.

There's a growing trend in the development community to develop on Macintosh and deploy on another platform. It's quite common when we were at the MySQL conference in April, just about 60% of the laptops were Macs. I'm sure that number is a little different at this conference. Despite the high number of Mac laptops at the MySQL conference, there are very few people deploying on Macintosh servers. We actually do, and we believe that we have got the largest discussion boards deployed on Mac servers in the world.

So our servers are by and by 8 gigabyte G5 XSERVs. I want to talk for a minute about error correcting memory that comes in the XSERVs. It makes an incredible difference. When we first switched to Mac servers for our databases, we went with G5 towers because it was a couple of months before the XSERVs were going to be available and we couldn't wait.

So after about a year, they started to become a little bit unstable and they would crash

Our hard drive setup is a little bit different than what you would expect. We bought these machines with RAID cards and three drives with the expectation that we would deploy a RAID 5 redundant setup on our drives.

When it comes down to it, RAID 5 is very good. It's typically fast and it's got some overhead associated with the fact that you're providing the redundant features. On three drives that you would have in an XSERV, it becomes a little slow. It's twice as slow as the regular hard drive because of the RAID 5 overhead.

That was slower than we could live with. It's not the same problem if you have a larger RAID with six or seven drives, say in an XSERV RAID, but on three drives you don't have enough spindles to make up for that overhead. So we chopped and changed with different variations to see what would work best. We eventually settled on this. We took the middle drive and we connected it back into the system bus and it runs the operating system. It's where all our logs live and MySQL isn't really using that as heavily as it's using the data drives.

The other two drives are mirrored on the hardware RAID card, which gives us the redundancy that we want. And because it's the hardware RAID card that's doing the mirroring, we have no overhead on the operating system in making sure that mirroring is happening. So this works very well.

It's the equivalent in terms of speed of a single drive. and it certainly is performing well enough to handle our loads at the moment. Our loads right now are very low on CPU. Over time, as our sites grow, that tends to increase. We know that in the future, I/O will become a problem again.

Also, in the future, we're going to be taking advantage of 16 gigabytes of RAM that's available now for the XServe. Apple, in April, some of you might have heard they released a new operating system. It's got 64-bit support, which we think is wonderful in database land. And at the same time, most people didn't hear that they actually certified 2 gigabyte RAMs for the XServe.

So we actually ordered our first...

The other thing in the future, towards the end of the year, we'll start deploying XServe RAIDs here. We know that as our load increases, the I.O. will become a problem. At that point, we already have some XServe RAIDs in the data center doing other stuff.

We know that the RAID 5 solution there will be three to four times faster than what we're doing now. So we're not worried about I.O. The message here that I was trying to deliver is...

So if I talk about our database architecture for a few minutes, we deploy everything in pairs.

Two things that are important here. One is the two servers are replicating backwards and forwards between each other. Any change to the data on one is replicated on the other. and vice versa. So any change that happens here is replicated on the other machine very quickly using the MySQL replication system. And we're very pleased with that. It works well. It's reliable and it's a little fast, which can be a problem sometimes.

We also, right now on most of our systems, have multiple instances using MySQL D multi that comes from MySQL. We use two or three instances on our 8 gigabyte machines because we're still waiting for 64-bit binaries from MySQL and Apple. And we have some tests that we've done, but we'll go into that a little later.

So for now, we might have two or three instances on our 8 gigabyte machines to take advantage of the fact that machines can handle the load and they have the memory. In front of our database servers, between our application servers and our database servers, we actually use hardware load balancers.

That's a very unusual statement, and it comes as a result of the troubles we were having last year with our G5 towers. Out of frustration with nothing else that we could think of to fix the problem, we had just upgraded our load balancers, so we took our old ones, we threw them in front of the database servers.

The upshot was when one of our towers crashed, the load balancer would decide that the traffic should go somewhere else and failover automatically. We stopped having stability problems and issues as far as our application servers were concerned. We were very excited by that. We still wanted to fix the crashing servers, but at least we'd mitigated the problem.

MySQL Database Now that we have mitigated the problem completely by using XSERVs and the error correcting memory, we've decided to leave the hardware load balancers in place. They offer a good service to us and it's a lot easier for me when I'm working on these things if I can take a server offline in the middle of the day than having to do it in the middle of the night. Don't get to do it very often, but in theory I could.

Behind all of this, we also have MySQL Admin. MySQL Admin does one-way replication from our active server pair. And we use that for doing things like reports, backups, and our email watches. People often subscribe to threads on the discussion board saying, "Send me an email every day with a summary of all the things that happened on this thread." Those things can happen off the production systems.

So, there's some questions about whose binary should I use. Apple has a binary that's included with OS X Server. MySQL makes a binary which is very easy to use also. And, of course, being an open source product, you can download the source from MySQL and build your own binary.

So I just want to talk briefly about the three binaries that are out there, or the three options that are easily available to people. First of all, the Apple build. It's right there. It's out of the box with OS X Server. And go with the theme of the week, it just works. It's there. You turn it on. It's great. There has, during the years with 10.3, been a tendency for the Apple version to be a couple of builds behind where MySQL's release version is.

Depending on your opinion, that can be a good thing or a bad thing. In Tata-based land, you want to have a rock-solid server and you don't want to be upgrading it every time there's a new version from the manufacturer. If it's working and working well, don't mess with it.

There's also the occasion where there might be a new bug introduced that you just have to avoid. And the only way to get around that is to upgrade it, which means you might have to move away from Apple's binary until Apple catches up with a newer version. So depending on that, could be good, could be bad.

MySQL also makes a binary. It's easy to download from mysql.com. You can always get the latest release version from MySQL. It's easy to install. It's just a package installer. Double click on it, you get a typical install window. You can also install an admin switch to turn it on or off from the system preferences.

The problem that commonly arises when you're using a MySQL binary is that Apple's binary is still in OSX Server. It's in a different place. Apple installs their binaries in User Bin. MySQL installs their binaries in User Local. All of your systems, if you're using a terminal, will have user bin very early in the path statement. So when you type MySQL at the command line, even if you've added MySQL to your path, chances are user bin is in the path before then, and you'll be using Apple's binary.

It's not so much a problem when you're launching a server that's done with a script which usually knows exactly where it's looking, but when you're using the command line tools, you can find that you're using a different version than your server, which is rarely a problem, but it is something to consider. We avoid this problem by deleting the MySQL binaries from user bin and setting up symlinks from there to the MySQL binaries.

Finally, there's the option of do it yourself. You can download the source code from MySQL and compile your own binaries. It requires a knowledge of compiling and building, and it's not something that under normal circumstances we would consider doing. It would be difficult for me to explain to eBay that their sites went offline because Bruce built this copy of MySQL with his own compiler. JD Duncan, Bruce Dembecki MySQL gave us a fix in the source code overnight. The only way we could deploy it was to actually download the source and build it ourselves because it was still a few weeks away from MySQL releasing a new binary.

So that's an example of a special need. Another example might be if you had a server with 8 or 16 gigabytes of memory and you wanted to take advantage of Tiger 64-bit capabilities, you might want to download and build a 64-bit binary. I know that we have. We had some help from the guys at Apple that build Apple's binary. We had some help from the guys at MySQL that build MySQL's binary. And we sort of put the information together and we built our own 64-bit binary.

And it's great. It works. We have it deployed in a very small scale on our smallest data set on one of the servers in the pair. So we have some first-hand experience running 64-bit MySQL on Tiger. We're not going to deploy it on a large scale until MySQL's binary or Apple's binary becomes available for all the reasons that we talked about.

So we're here talking about databases, and I feel obliged, like any good person presenting a database speech, just to mention, don't forget to tune your database when you set it up. Typical things that you will do is look at memory settings, make sure you're allocating the right amount of memory for your needs based on what you need and what your server is capable of and what else your server is doing. In our case, we have dedicated MySQL servers, so we're allocating lots and lots of memory.

But some people have a server that will be running Apache, PHP, MySQL, Mail Server, DNS, a few other things. So you can't always allocate all the memory in the machine to MySQL. What I'm suggesting is that you think about what does MySQL need, what does the rest of the machine need, what's available, and make some decisions based on that. Once you have the total memory set, set the individual caches. In MySQL, there's a query cache, there's the MyISM key buffer. There's a whole bunch of different caches that will affect what's happening and how much memory you allocate to each will be important.

Also, choose your database engine. MySQL has a model where you can have different database engines that do different things. The default engine in MySQL is called MyISM, and most people just forge right ahead with that. There's also an engine included with any download of MySQL called InnoDB, which is a transaction-capable database engine.

It also has some other performance improvements, and generally speaking, unless there are special needs which dictate otherwise, InnoDB usually outperforms MyISM and the other tables for most needs. So unless you have a special need for one of the other table types, one of the other engine types, InnoDB is the best choice in most cases.

Finally, don't forget to index your database. MySQL has something called the slow query log, which is very useful. You should enable it and you should review the output of that. It will essentially tell you the exact query of any query that took longer than n seconds. By default, n is 10.

And if you've got queries that are running 10 seconds, you really need to look at why that is and what they're doing and perhaps find ways to improve your indexes, find ways to improve your query. The other thing that can help when you're looking at indexes is MySQL's built-in explain command.

You can take any query, throw explain at the front of it, and the MySQL optimizer will tell you what it would do if it was really doing the query. It will tell you what indexes were available, what indexes it would choose, how many rows of data it would look at to answer your query. So that can be helpful in managing your indexes and coming up with the best solution.

So now I want to talk a little bit more. We mentioned our MySQL admin machine. I'd like to talk about production and administration tasks. In our case, production machines have to be fast. It's very important. We have a lot of load on those machines, and they need to be fast, they need to be quick. That's important to us. Administration tasks have a tendency to be slow. They usually involve a lot of number crunching and going through things.

The mix of the two can be quite unpleasant. It will often, in our case, cause problems for our users, which in turn cause problems for our clients, which in turn cause problems for our sysadmins. MySQL Admin is our solution because we separated the production and the admin tasks.

MySQL Admin runs many instances of MySQL. It copies from each database pair that is running every instance of MySQL. It does this delayed replication from that server. MySQL Admin is the machine that we use to run reports, to run backups, to figure out how many users posted message yesterday so that when our clients ask this information, they expect to see it and they can get it in a timely manner but without impacting the users on the production systems. There's another difference here in that MySQL Admin has the full logs.

The production servers have less logs. The production servers don't actually need the logs, they're just entering the information as they happen. But they don't process it, so the information isn't there. We keep it elsewhere. I'm going to give you an example of a script that we use. There's an interesting challenge here.

Logs and stats are space consuming. Their performance is inhibiting because of their size. Over months and years, with the sort of access and hits that we're getting, they add up to a lot of space. And just adding a new record to a big table like that means it has to make some changes to the index and can be quite slow.

So our challenge is to reduce the size of the logs on the production servers and at the same time keep the full set of logs on the admin server. With our replication model that MySQL has, every time you make a change to the data on the production server, it ultimately gets replicated through to the admin server. So we have to be careful that we don't lose that data.

I've written a quick script here. I'm not a programmer, so I tend to write things in shell script. And for the more serious developers out there, I apologize that we're not doing something smarter. But the reality is, for administering the databases, this is all you need. The MySQL client has all the smarts to do with MySQL, and you can activate that from a shell script and say, "Give me this piece of information that I need." And then you can manipulate it. So it doesn't need heavy-hitting programming skills to create these sort of tools.

In this case, our script says, "Tell me the time now in seconds." We then munze that a little bit and we sort of take off seven days worth of seconds. We multiply it by a thousand because our timestamps are in milliseconds. So now I have a number which is the time a week ago, as it would be represented in a timestamp on our database.

We then ask the server to give me a list of database names that are discussion boards. And we go through that list one at a time, deleting all of the logs. The most important thing here in this lower query that's in yellow is the start of the query which says set SQL log bin equals zero.

That command will tell the MySQL server, for anything associated with this connection and this connection only, don't write my changes to the binary log. The binary log is where changes in data are replicated to the slave. So if this connection doesn't write its changes to the binary log, this following command will only happen on the production machine. It won't happen on the admin machine.

It also won't happen on the other production machine that is a partner here. So you have to remember to run the script on both servers. By doing this, we can keep the log files down to a reasonable size in our production servers and still give us all the data that we need in the admin servers.

So let's talk about replication. MySQL has a wonderful replication model. It's fast, it's reliable, and it synchronizes the data. It works by not sending data between the machines, but sharing any query that changes the data. So if the two machines start off with the same data and any query changes the data here will be executed here too, data should be the same.

The problem we have occasionally is that it's a little bit too fast. It's rare that this replication process gets more than a second behind in our servers, and that's plenty good enough for us.

The thing with replication is before I can get to the other production machine and stop that from happening, they're already gone.

: MySQL database is a very complex and complex database. The answer is to go to backup, but that can be time consuming. In our case, on our smallest data set, it takes four hours to go from last night's backup to being live again. That was a big problem for us and we weren't happy with four hours of downtime and all the data lost between last night and this afternoon.

We came up with a system which we call delayed replication. It's a modification of MySQL's replication based on the fact that MySQL uses two threads to run replication. The first change is made, it downloads it and it writes it to the slave machine. The second thread executes those queries. Both of them are always listening to the log files coming from the master and coming from the I/O thread so that they happen very, very quickly.

But because that nature of how replication is built, we have an ability to manage those two threads and as a result, we can do something that looks kind of like this. Over a period of time, if we start at 4 o'clock, we're going to tell the slave to stop executing SQL queries coming from the master.

And then at 4.01, we're going to tell the slave to start copying changes from the master. That'll go on for a few minutes. This is a small amount of data because you're not moving the data itself, you're moving the queries to change the data. So over a high-speed network, I can copy all of the data that might have changed data in two or three days in a matter of seconds.

So only running this for four minutes is no problem at all. And then at 4.05, we stop the input-output thread. We stop reading changes that happen on the master. We then do nothing for five minutes, just so I've got a buffer of time that I can really get to the other machine and stop it. And finally, at 10 past 4, we will turn on the SQL thread and start processing those changes again.

The impact here is that between 4.01 and 4.05, we're reading all the changes that were made on the server between 2--on the master server between 2.05 and 4.05. Then we do nothing for five minutes. Nothing changes anywhere on our slave with the delayed replication. And at 10 past 4, we start executing those queries.

This cycle repeats every two hours, and the upshot is I have a series of two-hour snapshots because I can go back in our binary logs to the specific points where these start and stop things occurred. And at any time I can say, stop, don't do anything. I just made a really stupid mistake over here on the production servers. Or some other thing happens. Databases corrupt sometimes without user error. It doesn't happen often, but it can happen.

When these things happen, it's nice to be able to go to a recent backup without having lost lots of data since all of last night, without having to go through the whole process of recovering from a backup, packing, unpacking, etc. So we found when this happens, instead of a four-hour switch from last night's backup and losing data for the last 13 hours, we have a half-hour switch to go to the last snapshot on MySQL admin, and we've only lost no more than two hours' worth of data. Okay.

So we found when this happens, instead of a four-hour switch from last night's backup and losing data for the last 13 hours, we have a half-hour switch to go to the last snapshot on MySQL admin, and we've only lost no more than two hours' worth of data. The method we use is unusual and is not common in terms of what people put between their application servers and their database servers.

We had an extra set of load balances. We solved a problem with our G5 towers last year, and we liked it so much we kept it. It's very effective. It works great. It's probably not something most people are going to consider, but we'd highly recommend it if you happen to have the hardware available.

The method we use is unusual and is not common in terms of what people put between their application servers and their database servers. We had an extra set of load balances. We solved a problem with our G5 towers last year, and we liked it so much we kept it.

Another way of using failover is now with drivers. The JDBC driver from MySQL has a number of features that allow you to failover in the event of something happening to the database server you think you should be talking to. That includes the ability to set an auto-reconnect flag so that if your database connection goes away, you should reconnect.

The other way of using failover is to set a load balancer. That's actually important for us with our hardware load balancer because when MySQL 1 crashes, the connection is lost, and even though the load balancer sends the traffic request to MySQL 2, you need to reconnect in order to do it.

, and I'm going to talk about the MySQL 2.0. That's actually important for us with our hardware load balancer because when MySQL 1 crashes, the connection is lost, and even though the load balancer sends the traffic request to MySQL 2, you need to reconnect in order to do it.

They also have an option where you can have the primary server plus a secondary server, which is accessed in a read-only mode. If, for example, you have our situation with MySQL Admin, which is copying data from the production servers, but it's never actually sending any changes here back to the production servers, we would never want one of our application servers to change the data on MySQL Admin because it wouldn't get reflected in production land.

So by setting the JDBC driver to access via read-only mode means that you can still keep your discussion board up to some extent because you can read what's in there, you just can't write the changes. Fault tolerance is an interesting question. A lot of people have different views of what's important in fault tolerance.

And I'll try and... I'll try and give you an example. A bank has a certain opinion of fault tolerance that would be different than we do doing a discussion board. With a bank, if you're dealing with people's money, you have a certain responsibility, and your main focus is going to be data integrity. We obviously are concerned about data integrity, but the reality is if the guys on HBO are talking about what's on Sex and the City tomorrow and we lost the post, it's not the end of the world.

I'm not suggesting we don't take it seriously. I'm just thinking that from our point of view, fault tolerance is more important that we have consistent uptime than it is that we have consistent data. Turns out that all the methods of dealing with fault tolerance apply to both camps. Wherever you fall in this line, whether it be data consistency or uptime, fault tolerance, all of these areas are going to be important to you in either way.

So in SQL land, everyone who's familiar with SQL will know that transactional queries are a critical part of fault tolerance, more so for the banks than for us, but still for us as well. MySQL includes a transactional-based engine, which is in ODB, which we mentioned earlier, and is a very robust solution to this area.

We also have replication as part of any fault tolerance program, keeping more than one set of data critically important. Not just last night's backup, but actually having two live copies of the data. Failover, if you have more than one copy of data, will be an important part of the fault tolerance program.

As will backups. And if you do a backup and if your interest is in data integrity, if I have to roll to last night's backup, I want to make sure that we're logging everything so that I can recover all the changes since last night. As will backups. And if you do a backup and if your interest is in data integrity, if I have to roll to last night's backup, I want to make sure that we're logging everything so that I can recover all the changes since last night.

A very important part of managing your MySQL servers, administering your MySQL servers, or any servers in your database, in your data center, involves monitoring. You need to know everything that's going on in your servers all the time. You need to have a monitoring and alerting system. You need to know what's happening. You need to be told if there's a problem.

There's a number of different tests that you will need to do on a database server. We have ultimately built all our own tests. We recommend this. It's a good practice because you can customize those tests to what's important to your deployment and your setup. Again, as with the hardware, I showed you what our hardware was like. I wasn't suggesting you all need to use that hardware. I was trying to help illustrate the thought process you need to go through when assessing your own needs.

By building your own tests to monitor the database, you can come up with tests that tell you if the server is up or not. In the case of MySQL, it runs typically on port 3306, so a ping of port 3306 might be a good test to tell you if MySQL is running. It's not really a great test, but for some people it's enough.

Active thread count is a great test for us. I'll talk more about that in just a minute. It tells us how many threads are actually processing requests at the moment. Replication status. Is replication running or not running? Another test you might want to do is the database actually retrieving data. It's one thing to do a ping on port 3306. That tells you something's listening to the TCP stack on that port.

It's another thing to actually say, "Yo, MySQL, are you there, dude?" We do that by actually pulling a piece of data out of an InnoDB table that we have on the server specifically for monitoring. It's on all of our servers. We put it in the MySQL database, which JD will probably cringe at. The MySQL database should be reserved for MySQL administration tasks, but we think of this as an administration task, so that's where we put it.

It has a single table called status, which has one row in it. That row says, "Okay, I'm And we asked the database, give us all the information in the status table. If it tells us okay, we know that it's there. We can text, we can match the string okay in our monitoring system, and as long as we see that, we're happy.

and it tells us the database is both up and it's able to retrieve information from InnoDB. That can be important. We've had cases in the past where InnoDB has crashed but MySQL is still running. So if you're doing a test of whether the server is up, it's still there. It can tell you about its stats but it can't actually pull any data out of InnoDB. So it's not really running well enough to be happy. So those are the sort of tests that you might want to look at.

I mentioned that we look at thread counts. That's important to us. And it's a good measure of what's happening in your system. We have typically 200 to 300 connections to our database server at any given moment. And as you've probably worked out by our numbers before, we're doing about 300 queries per second on any given instance.

We run a test to see how many threads are active or how many threads are actually running. And that typically will come back to be two or three threads. That's all that's active in any given snapshot. We're still doing 300 queries per second. They're just not running for very long.

And that's the way you want it to be. You might find that if the server is not performing well, Those 300 threads that are connected to the database are going to start asking queries that aren't getting rapid answers. The active thread count is going to start to grow.

It might grow to 7 or 8, which would be okay in our case. It might grow to 60 or 70, which would be a sure indication of a problem. It might even grow to 300 or 400, which is absolutely certain there's a problem. We developed a test. Once we had a test, we tied it to TCP.

We used XINETD, and we associated this script with this port number. So anyone who hits port number... 10,000 on the server will cause that script to run and get the output of the script. You can do this with any number of scripts, with any number of things. XINETD is a wonderful tool for executing scripts on your servers.

So now that we're doing that, let's just look quickly at the script again. It's a shell script because I don't know any of the other languages. Be generous to call this a language. Our script uses MySQL to show the status where the number of threads that are active. Status like threads running.

We then munged that through a little bit and we take all the output from MySQL and we just collect the number. So, an important piece of information in that output from that script, from that command to MySQL, is the number of threads that are running. We turn that into an integer.

We create a string variable, which will consist of the phrase, good thread usage, then the number, and... Then we actually look at that and say, okay, if the number is greater than 20, we don't want to say good thread usage. We'll change our string to say high thread usage, then the number, and we'll output that. So the output of our script is either good thread usage, seven threads, or high thread usage, 28 threads, or 303 threads.

Any basic monitoring package we use in the mapper is going to have a problem determining from the number alone whether it should be having a problem. But we can easily match good as something to say we're okay and not to worry. And if we can match the text string high, say if we get that word high in our response, the monitoring system should go off and react accordingly. Okay. So, monitoring. It's important that you use the information that you collect.

Once you get an alert, there's another important step. You have to take some action. The action has to be appropriate. It might be as simple as restarting a server, and it might be running a script that might fix the problem all on its own. It may even be you don't have to do anything, depending on the nature of the alert. It's just a disk percentage full warning. It may be at this point where you don't have to do anything yet. It's just good information to know.

It's important to set up a monitoring system that will look at all the aspects of what's going on in your data center, and depending on each of those different aspects, react accordingly with different types of alerts that should generate from you and your operations people different types of actions.

So to summarize where we are, just to talk about things that make a difference to us when running MySQL in our data center, make hardware choices that make sense for your need. I showed you the process we went through when we looked at our disk needs for our XSERVs and whether we should have it all on RAID or do different things.

That's good for us. What's good for us may not be good for you. We recognize that. Hopefully you recognize that too. Our needs are what our needs are and your needs are going to be different. So think about your needs. Don't just buy this if that's all you need.

Tune the database. I'm not going to spend any time on it. It's just basic. Every time you hear someone speak about databases, they should be telling you to set the memory and the indexes. Separate the admin and the production tasks. If you're a high volume site, you'll need to think seriously about doing this. Not everybody needs to. Made a huge difference to us. Depending on your size, you might want to do this.

And then go through that process of monitoring. Test, alert, respond. You need to know what's happening and if something that you don't want to be happening is happening, you need to know about it and you need to do something about it. And the last bullet point here, we didn't talk about a lot directly, but we did implicitly. And that is to automate things.

We showed you how we automated the script that deletes the logs from the production servers. That's a huge time saver. It runs every day. We never have to worry about it. And it just happens. I don't have to worry about the disk running out of space on our production servers because there's lots and lots of space. MySQL Admin has all the logs, but it also has a great big XServe rate attached to it. And we have to watch that because the logs are very, very big.

We can also automate our responses to some of the monitoring issues. In fact, we do. We have a monitoring test that looks to see if replication is on or off. If it's off, the first thing it does is activate a script which tells that particular MySQL server, turn on your replication.

and it repeats that three or four times over a two-minute period. If after two minutes replication is still off, then they'll trigger an alert which will tell one of us because clearly it's something that's beyond just replication stopped and it started again by the automatic system. So find different ways to automate what you can.

For more information, you can contact me, Bruce. Bruce is my name. Liveworld is my company. My email address falls from that, bruce at liveworld.com. Chris is going to come up and talk a little bit more. Thank you very much. Thank you. Thank you very much. There's a lot of other resources that you can find information on for all of this, including the WWDC 2005.