Diamond Notes

Slides available @ http://www.pythian.com/blogs/917/best-practices-for-database-administrators-slides-and-links

Best Practices

Make it easy for yourself

• automate
• document (everything)
• use your brain for cpu not for storage (request tracking system & google!!!!)

Monitoring Basics

• graph (cacti, nagios)
• alerts (nagios)
• check your checks

Tradeoffs always exists

Backups & Restore

Backup of course but make sure you Restore!!

In case of failure…

• restore from backup
• master/slave
• master/master
• cluster

Test HA (High Availability) also!!

what scenarios do they cover?

what scenarios do they not cover?

Ounces of Prevention

• configuration vs. reality
• possibly memory usage
• disk space/tablespace size (limiting tablespace for innodb?)

Pounds of Cure

• error logs
• slow query logs
• query review (like code review)

mysqldumpslow (what server versions available?)

mysqlsla http://hackmysql.com

mk-query-profiler

show status before and after

mysqlreport http://hackmysql.com

procedure analyse()

what does each column, table do?

what’s in a name? good information!! can you read the data columns/tables and decipher what is going on?

question “best practices”

Schema Profile

• start normal
• denormalize if necessary
• stored procedures for developers (maybe they are afraid of normalization)

Replication

Be careful of TRIGGERs and any DML for SP or UDF

Sync often with mk-table-sync

Handle duplicates carefully
Maintenance

partition

archive

purge

static data

Manage User Expectations

aggregate data once every hour or fifteen minutes

split off processing:

• customer/non-customer
• internal/external

Foreign Keys

application still has to handle problem!!