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.

In my travels I do a lot of analysis of the slow query and the general query logs. In many Java shops I end up seeing the most frequently called command is

set autocommit=1;

Sometimes this statement is called 300-400x more then any other statement. What’s surprising to most of these shops, is that they are leaving autocommit on… so logically they are baffled why the statement is appearing over and over again. You see by default the autocommit statement wraps the calls to the database. So if you trace the calls you will see:

set autocommit=1;
select * from sometable
set autocommit=1;

Yep, even select statements see this behavior. So what, Its a small set statement right? . But that small set statement has to traverse the network, be parsed, be executed, then return something. Lets assume that adds 2ms to your transaction, over the course of a day that’s going to really add up. Over a million queries that …

A quick one here, we often talk about effectively utilizing memory to get the most of your MySQL Server.  I wanted to remind folks to not forget about allocating memory to a tmpfs for the tmpdir.  Certainly this is not going to help everyone, but those who create lots of temporary table will find the performance boost most welcome. In fact in some cases you maybe better off allocating a little extra memory to a tmpfs then you would be the innodb buffer pool, but like everything it depends on your workload and environment.

What is tmpfs?  In a nutshell a filesystem ontop of a ramdisk  used for temporay file access.  Read the wiki page for more.   Sounds like a great thing for /tmp right? By the way It really urks me that most people leave /tmp as part of the root filesystem ….  shameful, read my common mistakes post for more complaining …

I harped on this earlier this month. The network is an often over looked, but vital component of every application. I have been in many shops content with running 100Mb/s between the application and database simply because they are no where near maxing out the available network bandwidth between the two servers. What they are forgetting is there is a big latency difference between 10Mb,100Mb, & 1000mb. Speaking from my testing on Waffle Grid we see that under load 100mb connection routinely has network latency in the 3000-4000 microsecond range, while running under load in 1gbe tests we routinely run at around 1100 microseconds. By the way the same test using a Dolphin interconnect card finishes with an average latency of less then 300 microseconds. These tests average less then 5Mb/s being pushed over the network, which from a network perspective would not even hit half the available …

An often overlooked and way underrated component to any site is the collection and reporting of solid historical performance metrics.   Don’t get me wrong, everyone collects some sort of web page stats, has access to mysql status variables, and uses top to check cpu and process stats ( what else does anyone need right?).    But the metrics I am talking about are not your simple # of pages,# of users, apache log metrics that everyone has in their web server, nor are they point in time cpu stats, or cumulative row accessed counters.  No, we  are talking about true performance stats, gathered over a long period of time.  These stats can be used for troubleshooting, trending, and make great art and conversation pieces ( have seen my one of a kind CPU graph).

Think of it this way,  someone complains your site is slow.  They say when they hit the first page it just seems sluggish.  What does …

A quick note…

As mentioned several times here, hardware can not be treated as a black box.  Every mysql professional who is charged with performance tuning has to understand where often overlooked bottlenecks can occur. This can occur anywhere in the system : disk, cpu, memory, networking.   Everyone who reads my blog knows that I have beaten the disk horse until its bloody corpse, although I still believe too many people ignore disk performance… Everyone looks at CPU, in fact every monitoring tool known to man seems to include cpu stats.  But what about network performance?  The performance of the network is even more taken for granted then disk is.  I mean to most people they don’t give a second thought to what’s happening between servers, after all isn’t that the “network teams”  job.   Unfortunately I run into network problems more often then I would like.  What could these  …

One of the services we (Sun/MySQL) offer is a Performance Tuning and Optimization consulting package. These consulting engagements generally take several different forms as each client has a specific need that must be resolved. But there is a commonality between all of these assignments, they all require some amount of bottleneck detection as well as benchmarking. The topic of benchmarking and bottleneck detection is huge, and has a very broad audience. I really can not do it justice in a single post or two. In fact we are actually planning to give a Webinar in March ( more details will be forth coming ) talking about how we go about finding bottlenecks while out at client sites, and even in an hour I can barely scratch the surface.

What I thought I would share are a few of the easier cases of finding and fixing a bottleneck I have encountered. I am still amazed that more people have not performed similar steps to eliminate …

Over the past couple of months I have seen a few different clients with innodb setup to have multiple datafiles opposed to using file per table or a single datafile. While I generally do not think twice about a handful of datafiles, I started seeing clients with 50-100 or more 2-4GB datafiles on a single RAID set ( or in some cases a single disk ), and this started to raise the little alarms in my head. Asking these various clients the key question, why? The answers were surprisingly similar. Things like: “we always did it this way” , “their used to be a 2GB file limit”, “we did this in oracle”, etc. Their was not a ton of compelling answers ( like manageability and performance ).

Looking at these systems it seemed like they are really causing “self-induced” fragmentation. They all have large tables, and are doing scans over large amounts of the data… for instance a 40GB table in a database with 2GB datafiles. In the best …

Ahh seems like a few people do take the time to read my blog:) Peter Z Commented here on my common disk performance mistakes post. He makes some great arguments, and you may want to give it a read. While he does not agree with everything I say it is interesting to see his views. Remember different folks have different experiences and a lot of times there are multiple roads on the path the performance nirvana.

Let me start off saying I wholly admit that saying “everything” is a disk issue is a dramatic exaggeration. And i did not specifically say disk, I said “The problem is always an IO problem”, more on that later. I have run into my far share of issues outside of this sphere ( network, context switching, cpu ), but I still find disk performance to be by far the most common issue effecting systems I deal with. In my …

Over the past several years whether I am working with a small company or a fortune 500 client I have seen lots of issues and mistakes made around the configuration, setup, and ongoing maintenance of disk on Unix and Linux Servers. This is not only an issue with MySQL shops, rather it can be an issue with all database setups whether it is Oracle, DB2, or Sybase. Neglecting the disk is setting yourself up for long term issues. These mistakes often force companies to throw more and more hardware at the problem… Lets look at some common mistakes around disk:

#1 The problem is always an IO problem, and remember spindles not capacity

In performance disk is everything. It makes you happy, it makes you sad. Learn to love it. Learn to hate it. Learn to understand it. Everything eventually comes back to disk. Take this example: Lets say you have a bad query that you fix with an index. Why …