Jeremy Zawodny posted yet another great article over at Linux Magazine on some improved defaults that can save you a lot of grief when your network fails intermittently. In summary:
Faster replication heartbeat (old default is 3600 seconds = 1 hour):
slave_net_timeout 10
Disable DNS hostname lookups:
skip-name-resolve
Sane connection timeout (may need to be raised if your network is [...]
INSERT INTO…SELECT locks the table being read by the SELECT statement due to MySQL 5.0’s statement-based replication. Here’s a great post from the MySQL Performance Blog explaining the problem in detail and what to do about it.
Note: this problem is supposed to be eliminated when using MySQL 5.1 row-based replication.
I thought it would be interesting [...]
I’m a big believer in readability, and I love to write code that is easy to understand. Check out my newly-posted MySQL Style Guide which covers:
Simple queries
Complex queries
Subqueries
UNIONS
Advanced queries
Useful tips:
Use table aliases sparingly and only when absolutely necessary (a few extra keystrokes now will save you hours of gazing at cryptic queries later)
CAPITALIZE keywords religiously
Whitespace [...]
Thanks to the MySQL Performance Blog for this tip:
RESET QUERY CACHE;
FLUSH TABLES;
SET GLOBAL key_buffer_size=0;
SET GLOBAL key_buffer_size=14691328;
In my quest to understand MySQL’s EXPLAIN statement and to learn more strategies for optimizing queries, I came across this excellent blog post from 20bits by Jesse Farmer:
http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
In summary:
Benchmark, benchmark, benchmark!
Profile, profile, profile!
Tighten Up Your Schema
Partition Your Tables
Don’t Overuse Artificial Primary Keys
Learn Your Indices
SQL is Not C
Understand your engines
MySQL specific shortcuts
Read Peter Zaitsev’s MySQL Performance [...]
If a MySQL slave encounters an error while replicating commands from the master, the slave will abort.
One way this can happen is if you are using triggers on a table that calls a stored procedure, but the stored procedures are missing on the slave because you forgot to include the –routines option when generating a [...]
Sometimes MySQL doesn’t use the index on a column when performing a query.
Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:
SELECT * FROM [...]
To get the best locking performance out of InnoDB in 5.1, you’ll want to set the following options (“My Favorite New Feature of MySQL 5.1: Less InnoDB Locking” explains why):
[mysqld]
innodb_autoinc_lock_mode=2
binlog_format=row
tx_isolation=READ-COMMITTED
This is beautiful! Check out this post on Bret Taylor’s blog:
We use MySQL for storing all of the data in FriendFeed. Our database has grown a lot as our user base has grown. We now store over 250 million entries and a bunch of other data, from comments and “likes” to friend lists.
As our database [...]
I just wanted to quickly mention something about how MySQL handles out-of-disk-space situations when importing an SQL file. Here's my situation:
I have an 80GB hard disk,
7GB free space,
and am loading a 2GB SQL database dump .
No problem, right? Well not always. I started it importing and went to bed. In the morning it hadn't finished and I was getting out-of-space notifications on my taskbar.