I wanted to drop a quick note and let everyone know I am going to be speaking at an IOUG event on 9/28/2010 in Downtown Chicago. I will be targeting DBA’s, Developers, and users who want to know more about MySQL but do not have the time to devote a ton of time to learning everything little thing. I will be covering DBA 101 tasks in my 5 minute DBA talk, Developer & DBA common mistakes, common high availability architectures, and talking about the various versions, forks, and patches of MySQL that are floating around in the community.
You can register here:
I hope to see you there.
It was awesome to see everyone at the 2010 mysql UC. Sorry if I did not get a chance to chat with everyone, time just flew by! I had great turn out for my two sessions and had a lot of great conversations with people. If people are looking for my slides they are posted on the User Conference Website here: http://en.oreilly.com/mysql2010/public/schedule/speaker/75377 .. Thanks Everyone!
I have been asked this numerous times ever since I posted my InnoDB sample configuration files, Do I have sample configuration parameters for a MyISAM setup? We are seeing less and less people use MyISAM, but it is still popular ( especially in prepackaged form i.e. wordpress). So i figured why not adjust my sample InnoDB configs and make them suitable for MyISAM.
The biggest limitation to MySIAM is used to be the default key buffer only can could only be sized up to 4GB ( This was fixed in 5.0.52) . While you can create separate key buffers and assign indexes to them, it’s not very common ( Common as in present in low-end shops who need 5 minute dba help) in part because I think people do not fully understand it and you do have to plan for it. Setting up secondary caches is a database/application specific setup, it can not really be generalized, …[Read more]
As some people have mentioned here and here Increasing the innodb log file size can lead to nice increases in performance. This is a trick we often deploy with clients so their is not anything really new here. However their is a caveat, please be aware their is a potentially huge downside to having large log file sizes and that’s crash recovery time. You trade real-world performance for crash recovery time. When your expecting your shiny Heartbeat-DRBD setup to fail-over in under a minute this can be disastrous! In fact I have been some places were recovery time is in the hours. Just keep this in mind before you change your settings.
So you need to purchase a new database server, and you really don’t know where to start..
Because their maybe different recommendations for different OS’s I am going to stick with Linux with these recommendations. I want to say right off the bat here… choosing the right hardware should probably not be a five minute task. I think you really need to spend time reviewing your application, it’s access patterns, the io capacity, etc. You just can’t do this in five minutes generally. But if your under the gun and asked me to spec something out today… here are some general guidelines.
As of March 10th 2009 here is what I would recommend ( This are going to change every few months potentially with new versions of MySQL & new hardware:
Currently the scalability of Innodb beyond 8 cores is limited, in fact with the current ( unpatched ) releases you may see a performance regression …
A very special 5 minute DBA post here, we are crossing over… sys admin & dba oh my! I tend to always look first at the OS, and then move over to looking at what is going on inside the database. So if you have five minutes to look at the OS, what do you look for? What tools do you use? What gotchas are their?
First Everyone should be familiar with top. This is a great tool and place to start.
top - 20:42:56 up 2 days, 6:36, 4 users, load average: 1.02, 1.08, 1.01
Tasks: 201 total, 1 running, 198 sleeping, 1 stopped, 1 zombie
Cpu(s): 8.8%us, 0.6%sy, 0.0%ni, 76.3%id, 14.2%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 8173772k total, 8123568k used, 50204k free, 130972k buffers
Swap: 6032368k total, 45172k used, 5987196k free, 6533228k cached
PID USER PR …
I was soooo tempted to post a 1 word post on this, just posting “Innodb”. It would be very easy, and its the advice that I am going to give in the end anyways. After all we need to get back to the real reason behind these 5 minute DBA topics. These are supposed to be a short tidbit of advice to those who are not going to spend more then 5 minutes making a decision. What’s easier then a single word. Right? But that would be lazy… so. Here goes.
Currently there are only 2 main storage engines that you can consider for your general purpose DB’s. Innodb or MyISAM. While these two storage engines are included with your MySQL installation, there are several storage engines that are in some “pre”-release state that could shake things up in the future. Because of their relative youth and official release status of these engines, I would probably hold off deploying them unless you are you sure you need them for something. …[Read more]
Because I was asked….What should I set my my.cnf parameters to? What are good default values? How much memory should I allocate to the db if I have X amount of ram? What is a good starting point for the mysql config files?
You’re not really Googling for a my.cnf to use are you? You probably are, Shame on you! The best thing to do is to test before you make changes, and find the best configuration for your application. But your not going to are you? oh well I might as well accommodate you. I make no claims these will work for everyone. In fact if you hire me later on I may look at you funny after I analyze your system, and may call you funny names behind your back for using the wrong settings. Because there are a lot of people out their who are only database folks 5 minutes at a time (that’s what these posts are about), they are probably going to stick the my-huge.cnf and go with that anyways. So why not throw …[Read more]
I thought I would write a few blog posts on what I am calling the 5 minute DBA. The content of these is interesting or frequently asked questions that I get when out on a gig, you know those ones were a developer or a dba runs up and says: “Hey how do I do this?” I figure, If nothing else maybe it will save me some time in future.
There are two audiences here. The first is the true 5 minute dba. Their seems to be a lot of folks out their who end up responsible for fixing or maintaining a MySQL database who are not really DBA’s. They maybe developers, sysadmins, or even network guys who know just a little about databases. These guys and gals become DBA’s five minutes at time during the day generally when something goes wrong. They tend to be looking for the quick fix, something that can be done in five minutes or less so they can get back to their other important tasks.
The second group are comprised …[Read more]