I was working with a mail archive MySQL database today and was twiddling my thumbs waiting for simple queries to complete.  The database has about 12 million rows and is on a 2x2GHz 2GB Linux Server x64.  I wanted to try to optimize the database and found this little gem from Lee at SoftLayer blog post.

SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA,
CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE
from
information_schema.TABLES
where
TABLE_SCHEMA NOT IN ('information_schema','mysql')
and
Data_free > 0

After you run the SQL on your database you can use optimize table table_name to optimize it.

My performance increased enough to be noticeable. =)

Here is a list of Windows’ performance counters to use in monitoring performance of an SQL server.
Create Performance Collection rules targeted to a SQL Server computer group for the following performance counters:

• LogicalDisk(*)Avg Disk sec/Read
  Should be under 20ms. Beyond 50ms is very bad
• LogicalDisk(*)Avg Disk sec/Write
  Should be under 20ms. Beyond 50ms is very bad
• LogicalDisk(*)Disk Read Bytes/sec
• LogicalDisk(*)Disk Reads/sec
  The Reads and Read Bytes/sec counters can be used on conjunction with the Writes and Write Bytes/sec counters to see the ratio of Reads/Writes your database is doing. This will help determine the optimal RAID configuration to optimize for reads, writes, or a balance of both.
• LogicalDisk(*)Disk Write Bytes/sec
• LogicalDisk(*)Disk Writes/sec
• MSSQL:Buffer MangerBuffer cache hit ratio
  This should be as close to 100% as possible. Below 97-98% indicates SQL server needs more physical memory. If you are much below that, the SQL Server needs more memory.
• MSSQL:Buffer MangerPage Lookups/sec
• MSSQL:Buffer MangerPage reads/sec
• MSSQL:Buffer MangerPage writes/sec
  You can use this to see how many writes you are performing to your disk. Each Page is 8KB.