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.

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...

SAN / Direct Attached storage

[update]

Been monkeying in the lab with some Sun/IBM/HP hardware - an EMC CX-80 and a few commodity MSAs.  Overall testing: Direct attached is better.  Shelves of 16 drives, RAID5, EXT3, 3TB MySQL volume, nominal DB load (50-50 R/W balance)  Turns out that the direct attached RAID controller gets to dedicate all memory/throughput to itself. (gee whiz!)  The SAN splits its cache among everyone using it (configurable, but besides the point).  With 10+ servers slamming the SAN, cache thrashing gets to the point where all the benefits of a SAN are moot.

[Original post]

Open Question:

What gives you better performance, SAN infrastructure, or Direct Attached? A SAN can leverage a fibre channel HBA, pretty snazzy. Direct Attached usually uses the same technology.

SAN allows you to use hot backup tools.

DA means you eliminate a single point of failure.

SAN allows you to resize partitions if you failed to realized the actual growth rate.

DA might be a little more battle-tested in small-medium size business world.

I hope to update this in the future.

MySQL: How to use lots of memory

So obviously in a database, you want to leverage memory to reduce disk accesses.

InnoDB: Set innodb_buffer_pool_size as large as possible.

MyISAM: The plot thickens. Pre 5.0.52, you could only set key buffers to 4G. Now you can go larger. To leverage memory, I do the following, changed to >4G now that it's been fixed, concept is the same. Note: preloading the caches takes some time (it has to read the whole MYI from disk) Rebooting with this configuration is *expensive*, but server is back to 100% at reboot instead of warming up. Remove LOAD INDEX commands if that's too annoying for your purpose.

file [my.cnf]
# Initialization file
init-file = init.sql
# Global key cache
key_buffer_size = 4G
# Table based key caches:
[tablename1]_cache.key_buffer_size=4G;
[tablename2]_cache.key_buffer_size=4G;
[tablename3]_cache.key_buffer_size=4G;
[tablename4]_cache.key_buffer_size=4G;

file [init.sql]
CACHE INDEX [database1].[tablename1] IN [tablename1]_cache
CACHE INDEX [database1].[tablename2] IN [tablename2]_cache
CACHE INDEX [database1].[tablename3] IN [tablename3]_cache
CACHE INDEX [database1].[tablename4] IN [tablename4]_cache
LOAD INDEX INTO CACHE [database1].[tablename1]
LOAD INDEX INTO CACHE [database1].[tablename2]
LOAD INDEX INTO CACHE [database1].[tablename3]
LOAD INDEX INTO CACHE [database1].[tablename4]

MySQL Cluster

Serves a very good purpose in an OLTP environment. It's entirely useless for OLAP. Disk backed tables help, but you're still stuck with in-memory indexes. Pull the plug on the rack, cluster takes a looong time to come back.

Data retention, try purging a few thousand rows, you're limited by MaxNoOfConcurrentOperations. Allocating an object for each row to be inserted/deleted/returned is an impossibility for an OLAP system. The widely accepted solution is to chunk deletes (delete from [table] where date < [target] limit [N])

Quite unfortunate due to possiblity of parallelization of large GROUP BYs and other misc data aggregation.

Maybe in 6.0?

MySQL and I/O Schedulers

Been doing some experimenting in the lab. Appears that deadline works great for a OLTP database load, but CFQ performs better in an OLAP environment.

Will post some empirical results eventually, but here's my $.02:

Let the OS reorder / batch anything headed to disk. Re-order as much as possible to reduce head movement.