It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

I've been trying to circle back and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I'll write more about the process of writing this book, and what we've done well and what we've learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization. The last two chapters I've written about (Query Performance Optimization and Advanced MySQL Features) have generated lots of feed back along the lines …

Your comments on the Advanced MySQL Features chapter were great. A lot of the questions I got (in the comments and via email) about chapter 6 are really addressed in chapter 5, "Query Performance Optimization," so I'm posting its outline too. I have the same questions: are there things you'd like to see us cover? Do you have any favorite techniques you'd like to see us include? Any other comments or questions?

Work continues apace on High Performance MySQL, Second Edition (the link leads to the chapter outline). I'm working now on Chapter 6, Advanced SQL Functionality, and thought I'd solicit input on it. Are there things you'd like to see us cover? Do you have any favorite techniques you'd like to see us include? Feel free to leave feedback in the comments. The chapter is already significantly done, with 26 pages written, but the ink's not on paper yet, so there's still time to correct omissions!

I wanted to point out something that might not be obvious from the name: MySQL Parallel Dump can be used as a generic wrapper to discover tables and databases, and fork off worker processes to do something to them in parallel. That "something" can easily be invoking mysqlcheck -- or any other program. This makes it really easy for you to do multi-threaded whatever-you-need-to-do on MySQL tables. Here's how.

I have run in to the issue where I wanted to change some configuration on our production MySQL server but did not want to restart MySQL since its in production.  One of the parameters we like to change time to time is the “log_slow_queries” so we can see what queries are slow in production environment.  [...] …

I have been asked numerous times what does “load average” means in top. If you don’t know what top is and you have access to linux machine, go type top now and see what it shows.

load average: 2.05, 2.17, 1.93

Quick answer is: first number (2.05) is 1 minute avg, second number (2.17) is 5 minute avg, third number (1.93) is 15 min avg. Generally system admins look at these #’s to see how is their server is doing. But now you wonder, if this is the #’s you look at, why is there cpu %? Isn’t that computer load also? Ofcourse it is. BUT, meaning of cpu % shown in [ Cpu(s): 14.2% us, 1.7% sy, 0.0% ni, 80.7% id, 3.1% wa, 0.0% hi, 0.3% si, 0.0% st ] actually just means how much % of time was spent doing stuff on cpu. On the other hand, load average takes other things such as how much cpu’s were being used and how many process had to wait for their turn to use cpu, etc. Thats why sometimes you will see high % for Cpu …

Problem:

One of the sites I maintain has been getting more and more traffic everyday. A very good thing for the site, not so good for the solo server which is serving those pages. The site is VERY dynamic with LAMP setup. We only have one server serving our web pages to our users. Since its a dynamic site with PHP and MySQL, it has a lot of load during peak times. Average load time of a page is between 1-2 secs during normal usage, 2-5 secs under average to heavy load.

During heavy load, we started to see our mysql stop responding to requests which is a big concern for us since we don’t show content if there is no db connection. We had to come with a solution, fast, to prevent this issue from appearing.

Solution:

So here are couple things I ended up doing on the server side to reduce load on this server without having to do much code change.

• We have couple other …

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …