I dashed off a hasty post about speeding up replication slaves, and gave no references or explanation. That's what happens when I write quickly! This post explains what the heck I was talking about.

You should be warned. Amazingly, in 10 years of PHP/MySQL development work I never hit this issue in the manner I did this week. There are several reasons that PHP could not be able to connect to MySQL. The MySQL daemon could be down. It could be an authentication problem. Or, perhas the entire server is offline. The last one there is the one I want to talk about.

In your php.ini you will find a value called mysql.connect_timeout. In the PHP ext/mysql it defaults to 60 seconds. Likewise, the php.ini has the same value. As far as I can tell, this timeout only comes in to play when the server is completely offline. If the server is up, but mysqld is not, the server refuses the connection immediately. I suppose if the server was under high load it could be used as well.

Well, IMO, 60 seconds is way to long to wait on a connection to the database for a web application. We had a server offline and expected the …

I’ve finally decided to work on a number of improvements in Instrumentation within the MySQL Server I’ve wanted for the first year, See What is the optimal thread specific buffer size?. It’s been a while since I’ve compiled from source, and from these issues, the first under Ubuntu 7.10 (a fresh install). Here are some of the problems, and solutions overcome, just for some others that may experience them.

I should have simply read my own notes from years ago in Compiling MySQL, specifically the pre-requisites list, but it sometimes helps to remember why things are so.

In summary, I needed the following:

apt-get install automake libtool g++ ncurses-dev

Thanks Miademora, I meant to say that, forgot

First error, “aclocal: not found”

$ …

So you want to know what join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size values for your application should be? These MySQL thread specific buffers are variables I can never get right because there are insufficient metrics, instrumentation or even abstract details with the present MySQL Versions. These are important because Memory is a resource that you want to maximum towards your database data (the System Global Area), and not towards the Process Global Area in which there is no limitations.

I’ve wanted to know this answer for some time, I’ve asked many people including internal MySQL resources, I’d hoped that when joining MySQL more details would be available, but I’ve never been able to get an answer. I’ve always been meaning to work this out, it’s now 2008 and well the time has now come to do something about it.

The …

Giuseppe Maxia had a stop-over yesterday on his way to the MySQL Staff Meeting. We met for community discussions, and rounded off with Weißbier and dinner in Munich’s famous Hofbräuhaus.

What a fantastic feeling! The Bavarian spirit of joy and happiness has been decoupled from tobacco stench! I’ve always liked the first and hated the second.

So the German non-smoker protection legislation of 1 January 2008 is good news for anyone considering arranging meetings in Germany. You will no longer be “welcomed” at the airport by an offensive smell. Headaches in German …

Paul Tuckfield of YouTube has spoken about how he sped up his slaves by pre-fetching the slave's relay logs. I wrote an implementation of this, tried it on my workload, and it didn't speed them up. (I didn't expect it to; I don't have the right workload). I had a few email exchanges with Paul and some other experts on the topic and we agreed my workload isn't going to benefit from the pre-fetching.

In the meantime, I've got a pretty sophisticated implementation of Paul's idea just sitting around, unused. I haven't released it for the same reasons Paul didn't release his: I'm afraid it might do more harm than good.

However, if you'd like the code, send me an email at [baron at this domain] and I'll share the code with you. In return, I would like you to tell me about your hardware and your workload, and to do at least some rudimentary benchmarks to show whether it works or not on your workload. If I find that this is beneficial for …

In Issue 3 of MySQL Magazine Peter Zaitsev writes, ” I would set [innodb_log_file_size] to 256 megabytes for small sized boxes up to 5% of the total memory on the big boxes.” I tend to set my log file size to around 128M. Depending on your system setting the log file size around a few hundred megs should be the right balance between preventing checkpointing and still having fast recovery times.

The manual states, “Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.” The sample my.cnf files that come with MySQL state to set the innodb_log_file_size to 25% of the innodb buffer pool size. I think this mistakenly advises people to set their log files much larger than they need to be causing unnecessarily long recovery times.

Crash recovery time is an important feature of InnoDB. In the event of a server crash large log files could mean waiting hours for …

Sometimes I don't like MySQL for the way it documents its software. And I don't like people who would try to define something and get the definition from the MySQL documentation. Let me give an example, MySQL defines Handler_read_key server status variable as the number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

If you search around the web, people will also use this definition. But what does this actually? No one would try to go deeper because they actually don't know. Don't get me wrong, I also don't know what this actually means.

Let me dissect the way this definition is written, what does number of requests means? Is it the number of queries? What does a "fixed position" mean? And how is this …

Paul Tuckfield of YouTube has spoken about how he sped up his replicas by pre-fetching the replica’s relay logs. I wrote an implementation of this, tried it on my workload, and it didn’t speed them up. (I didn’t expect it to; I don’t have the right workload). I had a few email exchanges with Paul and some other experts on the topic and we agreed my workload isn’t going to benefit from the pre-fetching.

I dashed off a hasty post about speeding up replication replicas, and gave no references or explanation. That’s what happens when I write quickly! This post explains what the heck I was talking about. I first heard Paul Tuckfield talk at the first MySQL Camp, in November 2006. He mentioned that he speeds up MySQL replication by “pre-fetching relay logs” on the replica. Actually, I think he used the term “pipelining” at that point.