According to Wikipedia, “Best practices can … be defined as the most efficient and effective way of accomplishing a task, based on repeatable procedures that have proven themselves over time for large numbers of people.” In this session, we will go over the following areas of best practices, discuss how they may or may not work in your environment, and let the audience share their own best practices (in note format, sorry for the stream-of-consciousness!):
0 THINK – what policies make sense? ie, innodb autoextend? Table per file? QUESTION EVERY SINGLE THING I OR ANYONE ELSE SAYS AND SEE IF IT MAKES SENSE FOR YOU!
1 TEST (part of job) (unit tests for stored procedure example)
2 DOCUMENT (part of job)—including document your best practices!
3 BACKUP…and restore!!! test restores! how often, what to backup, snapshot plus others? if you backup you have to restore it…..? ALSO, when to purge logs? What can you afford to lose? In what situation? ie, if east coast has huge power failure, it’s OK for your web 2.0 site to be down, ppl understand.
4 DEFRAG/ANALYZE/OPTIMIZE/EXPLAIN – compress/arcihive old data.
5 Reassess server setup, ie re-tune, every 6? month. Get query shapes. Reassess schema, indexes, etc.
6 Assess Risk—Can you live with the worst case scenario? Life doesn’t have a Ctrl-Z button!
7 MAKE PLANS
8 Monitor, graph, metrics, log analyzing – history?
9 Work with developers – give them queries, do query reviews (pop quiz w/slow query logs?), give them best coding/SQL practices.
10 2nd set of eyes/sanity check – damn semicolons! Agile development, agile DBA/querying too!
11 Keep up to date with information about patches, upgrades, new tech to make your job easier. If you’re a GUI person, learn the commandline; if you’re a commandline person, learn a GUI.
12 Change control – easy for code, harder for my.cnf, startup scripts, schemas, maintenance scripts, HARDWARE, what’s a slave vs. master when it can change w/failover. – incl, how do you sync app and db changes? downtime windows? history?
13 QA db, including a good sample of data (or all of it, sanitized)
14 Auth and privileges – who? How often? What types of passwords? how secure? (my password algorithm) Physical access? firewall + configs? only essential programs – do you really need gcc on a production db box?
15 Asset DB
16 Issue Tracking
17 Integrate Asset DB, Issue Tracking, Change Control
18 Check Data Integrity (esp. slaves!)
19 Try it yourself, after & risk assessment.
20 BIG tip – improve 1% or 5% at a time. better. just like not rewriting the code! Takes a long time to start from scratch, but start writing a plan for each maintenance, then add some automation, etc.
Sheeri K. Cabral has a master’s degree in computer science specializing in databases from Brandeis University. She has background as a systems administrator; has worked with Oracle, Sybase, DB2, Solaris, RedHat/Fedora, AIX, and HP-UX. Unstoppable as a volunteer and activist since age 14, Cabral founded and organizes the Boston, Massachusetts, USA, MySQL User group, and wrote the MySQL Administrator’s Bible (www.tinyurl.com/mysqlbible). She currently works for PalominoDB, a remote database management company
View a complete list of MySQL contacts.