slave_net_timeout

Do not set MySQLs slave_net_timeout too small... If your master is bursty, and your replication delay backs up... Disaster ensues! slave_net_timeout was set to 20s on a server farm of ours. Slaves ended up having a new relay log file created every 20s of 300bytes. Hours later the filesystem was jacked.

Case sensitivity in MySQL/Linux/Perl

A fun interesting-ism of MySQL is case insensitivity for just about everything. Bewares though! The Perl DBI interface is not entirely case insensitive! In attempts to make things clean and look great our friendly local DBA converted all tables to lowers. Thus breaking many Perl scripts :(

Backups

Creating a consistent backup in MySQL is very simple.

FLUSH TABLES with READ LOCK;
[MyISAM - copy all DB files from the filesystem]

Tables must be flushed to disk and write locked. For the love of god, do not just copy files while the database is live. Not only is resultant copy corrupt requiring a table repair/check - you have no idea at what point the "snapshot" was taken. Additionally, running sync() is redundant, but some folks suggest it.

Alternatively, my favorite method is a simple SELECT [data you want saved] INTO while the database is running.

Linux Elevator Algorithm

There has been much debate over elevator algorithms concerning the 2.6 kernel.  For a database system, deadline and CFQ seem to be the most debated.  For a small database with relatively few drives in a RAID array, and relatively small memory, deadline works best.  One of my systems has a 48 x 15k drive SAS RAID array.  When you combine over 2GB/s of throughput, 128GB of memory, command queuing, etc - latency isn't the issue.  Leveraging caches to get a sequential R/W load is dramatically more important.  CFQ does this via implementation of R/B trees ordered by sector.  If you're periodically reshuffling the deck based on time expiration (deadline), you end up seeking all over the disk. 

Converting high performance servers to CFQ over deadline resulted in a 10-30% performance bump depending on load.

OLTP servers might have different experiences, but CFQ in an OLAP environment appears to be the way to go.  (for us! haha)

MySQL Collation and Character sets

Have a table where lower/upper case ASCII characters mean different things. See example:

mysql> create table collation_test(value char(1));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into collation_test(value) values ('T');
Query OK, 1 row affected (0.00 sec)

[A bunch more stuff]

mysql> select * from collation_test;
+-------+
| value |
+-------+
| T |
| T |
| T |
| t |
| t |
| F |
| F |
| f |
| f |
+-------+
9 rows in set (0.00 sec)

mysql> select value,count(*) from collation_test group by value;
+-------+----------+
| value | count(*) |
+-------+----------+
| F | 4 |
| T | 5 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> alter table collation_test CONVERT TO CHARACTER SET latin1
mysql> COLLATE latin1_general_cs;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql> select value,count(*) from collation_test group by value;
+-------+----------+
| value | count(*) |
+-------+----------+
| F | 2 |
| f | 2 |
| T | 3 |
| t | 2 |
+-------+----------+
4 rows in set (0.00 sec)

MySQL Default values (string / int)

Had a great one today:

Create table mine (id integer);

insert into mine(id) values (0),(1),(2),(3),(0),(1);

select * from mine;
[Nets 0,1,2,3,0,1]


select * from mine where id = "Test";
[0,0]
select * from mine where id != "Test";
[1,2,3,1]

String / int columns

Found a table of ours:
hashid bigint
value_a varchar(16)
value_b varchar(16)

In MyISAM, yes, this is a dynamically sized table. Turns out, data was only integers. Altered the table to be

hashid bigint
value_a integer
value_b integer

Goes to fixed size rows which is great. Also dropped size from 411M to 350M.

Remember the golden rule:
Optimize your dataset before your hardware...