Executive Summary

Advice is seldom welcome.
And those who want it most
always like it the least.

   Philip Dormer Stanhope, Earl of Chesterfield  (1694-1773)

Guiding philosophy of this document:  The hero isn’t the developer that battles problems through the weekend, but the one that avoided the problem.

Best practices (BP in this document) can be a wide avenue leading to a solid, reliable infrastructure, or alternately the last refuge of a lazy scoundrel.  The pivot on which this turns is justification. If a set of guidelines and rules leads to a cleaner business and technical process, then it is useful.  Otherwise, the practices are not acceptable.  BP must apply to all cases or show clearly where they are applicable.

In short, No Justification == Not Acceptable.

In this set of practices, the software development Capability Maturity Model will be used as a framework.  These, in order of maturity, are:

1. INITIAL – “Individual Heroics”
2. REPEATABLE – Basic Processes, e.g. Project Management, Quality Assurance
3. DEFINED – Documentation, Standardization, Integration, Communication
4. MANAGED – Monitoring, Measurement, Reporting
5. OPTIMIZED – Continuous Improvement

General Guidelines

The goal of this document is to create a solution set will allow any member of the development team who examines the tables and schema of Vacasa databases—whether production or development—to get a fair idea of the purpose and methodology of the database. This can be achieved in a number of ways:

• Comment all functions, procedures, triggers, CREATE scripts, macros and code:
  Good commenting does not have to be a novella, nor necessarily dry and purely technical, but all relevant components should have at least an informative, brief explanation of the type of data requested, for what it will be used, and its final appearance. This information is entered in the design view of the table or within the script/code.
• Please execute database work via JIRA tickets, and only via JIRA tickets
  The sole purpose of a ticketing system is to provide automatic documentation and an audit trail.  Do not work from email or Slack; this is simply asking for trouble.  Rather, encourage project sponsors and/or your colleagues to file a ticket for actual work requests.  Never execute work from a verbal order; this only leads to confusion, and IMs aren’t much better.  If the issue at hand is your own project, file the ticket yourself so there is a work log.  NO CHANGES to the database DDL should be made without a JIRA ticket in the database queue.
• Provide written documentation
  As long as the item above is being followed, this is no problem.  Within the ticket(s), include the schema and how to use the database, relevant create scripts, and when necessary, file attachments and screen shots.  This method also instantly allows feedback from other members of the technical group.
• Naming conventions
  With respect to data structure and schema creation, the Vacasa standard is all lowercase. Neither Leszynski and other Hungarian-style systems nor Microsoft/C/Java/Javascript systems will be used within Vacasa databases.  The following conventions will apply:
  • Production databases will all be prefixed with prod_ (prod_domain, prod_stat, etc.)
  • Database names will be singular.
  • Table names will be singular. (e.g., account is valid, prod_domain.accounts is not)
  • Column names are singular.
  • Use the underscore as a word delimiter (e.g., flag_type is correct, flagtype is not)
  • Never use any capital letters in a database, table, or column name.  Within DBMS PERL scripts, follow the same conventions.  For PHP and other development department coding, follow the development department’s best practices.  The key here is to integrate into whatever environment you may find yourself.

• Remember that business users are neither technologists nor engineers. Do not expect them to be.  Information technology exists to support what they do, not the reverse.

Detailed Standards

Architecture

Vacasa’s database servers can be divided into the following broad groups:

• Production
• Staging
• Development
• Utility

More servers may be added to the development or utility areas, but these are the primary classifications.

All revenue bearing processes and websites will access databases on production servers only.  Under no circumstances will production websites or processes access any databases other than those housed on production-grade servers.  Periodic auditing by senior developers and DBAs will ensure this is so.

Security

Development servers

All database instances defined as “development” class are relatively open access to all members of the tech team.  Full privileges will be granted to all necessary personnel, including Update, Create, Drop, Alter, Create Routine, Alter Routine, and Lock Tables permissions.  When requested, a DBA will properly notify all users of the server, refresh the database from production, and then reinitialize it.  Note that prior notice is required with data refresh operations (see above under General Guidelines)

Utility servers

TBD.

Staging servers

TBD.

Production servers

Production servers are defined as restricted access boxes.  There are two areas of security to consider—direct access to shell accounts on these servers and access/ permissions to the database instance(s) running on the production servers.

Users and passwords

• The shell-level access on production servers will be limited to system administrators and DBAs. Developers shall not have access to these servers.  Likewise, only DBAs and automated processes will have any access at all to production databases.  Each human user will have their own account, and each automated process will have its own account, which will be granted appropriate access only to the databases/tables to which it requires access.  (see below)
• The “root” MySQL user on all production databases will be removed immediately after the clean installation of the DBMS is completed. The root user in MySQL has both superuser powers and is a known user.  No users will be created on production databases without ticket-based approval from the senior DBA.  Unapproved accounts will be immediately deleted.  No passwords may ever be blank, and production passwords for software and automated operations will be appropriately encrypted.  Whenever feasible, do not use the “all hosts” hostname (%) when creating users; rather, grant access only for hosts from which the user will be accessing the database instance.
• No production database server will be exposed directly to the internet for any reason.
• Automated tasks—shell/PERL/PHP scripts, stored procedures, etc.—will have individual database users created for their needs. Access will be granted to these automated users based on precisely what they need to accomplish, with table level, not global access.  The nomenclature for these users will take the form of xxxxxxx_user, where xxxxxxx indicates the function of the process in question, e.g. sproc_user, tableau_user, appname_user, etc.  Passwords for these users will remain confidential in the same manner as other production passwords.

 Queries

• The first line of offense when tuning queries is EXPLAIN SELECT. Use it.    Always.
• Always try to isolate index fields on one side of condition in a query
• Avoid using CURRENT_DATE() or NOW() as it invalidates the cache. Instead, grab the date/time value and assign it to a constant and then use the constant variable repeatedly within the query/stored procedure.
• Avoid correlated subqueries. Think in sets not loops.
• Avoid more than one self-join; if feasible, use temporary/memory tables to shrink the dataset with which the larger queries are working.

Programming standards

The following guidelines are presented in a bullet-pointed format as a set of practices which will do much towards optimizing our underlying database systems.

• Do not use the database for BLOB or TEXT storage unless a strong case can be made for the utility of such an endeavor.  They are slow, involve external storage files, and are inefficient and indexing them is very expensive.
• Likewise, do not store images within the database. It’s a database, not a filesystem.
• Stick to ANSI SQL whenever possible. Using MySQL’s extra toolsets is certainly encouraged where warranted, but code becomes un-portable when extensions are utilized.  Be aware that rewriting will be required if the ANSI standard is abandoned.
• Do not mix display code and database code.
• Do not store display code or HTML in the database, save parking templates.
• Use connection pooling whenever possible. This saves time and RAM.
• Design applications from the ground up to have separate read and write connection strings/filehandles. This makes load balancing possible when load becomes an issue.
• All shell/PERL/PHP scripts residing on production servers must be checked into source code control.
• All utility code written by DBA personnel on utility servers must be checked into source code control.

Table schemas

The following guidelines are presented in a bullet-pointed format as a set of practices which will do much towards optimizing our underlying database systems.

• Every table within approved tables will have one primary key. This column will be named “id” which is an integer type and defined as BIGINT UNSIGNED AUTO_INCREMENT.
• All tables will have one column named “mod_date” which is a DATETIME column and has the qualifier ON UPDATE CURRENT_TIMESTAMP enabled.
• Whenever appropriate, include one column named “create_date” of type DATETIME which inserts the creation date of the row via its DEFAULT VALUE.
• Normalize wherever possible, but remember that every step towards a true Type-5 normalization requires a separate JOIN statement to retrieve data. It is suggested that normalization only occur to the extent of removing redundant columns.
• Denormalization should only occur when the benefits are immediate, provable, and readily apparent, and do not conversely lead to multiple copies of the same data in multiple tables.
• InnoDB is the Vacasa standard table storage engine.
• Order your columns in such a way that the first column is always the “id” column, the penultimate column is “create_date,” and the last column is always “mod_date.”

Replication

TBD.  Still getting my head around this vis a vis the wonky AWS setup.

Server Configuration, Tuning, and Maintenance

• When we move to standalone database instances, a standard skeleton my.cnf will be available via Confluence. Until such time, a set of standard SET GLOBAL commands will be available to be executed on RDS instance startup.
• Do not make changes in production without peer review.
• NEVER benchmark without a goal. Have a stated objective such as “improve overall performance by 20%”. Otherwise you’ll waste a lot of time tuning milliseconds out and miss other areas—perhaps even within the codebase—that are the actual bottleneck.
• Change just one thing at a time and re-run the benchmarks
• Disable the query cache by setting the cache size to 0 when running MySQL benchmarks.
• Log slow queries and use mysqldumpslow to parse the log. [NB: A shell script has already been developed to extract logs from table-based logs in a standard mysql logging formate for further analysis)  The option (–log-queries-not-using-indexes) of logging any query that does not use an index on a table will also be judiciously utilized.  However, always bear in mind that production logging is a two edged sword; every millisecond spent writing/flushing logs takes away from overall instance performance.
• Use the mytop utility for monitoring the threads and overall performance of MySQL servers.
• Repeated queries on an unindexed field will kill your application faster than anything else. Ensure that all queries are on indexed fields.  I say again:  EXPLAIN EXTENDED.
• Don’t de-normalize just because you think it will be easier to initially code. Start with normalized database schemes. Remember that someone will most likely have to maintain your code later, and it may not be you.
• Server parameter tweaking is not a catch-all. Tuning server parameters can help but it’s very specific to certain situations.
• On multi-column indexes, pay attention to order of fields within the index definition. Match the composite indexes to the queries, and assist developers in always querying in the same order.
• Use the smallest data types possible. Don’t use bigint, when int will do. Or, don’t use char(200), when a varchar or smaller char() would do.
• Consider horizontally spitting many-columned tables if they contain a lot of NULLs or rarely used columns. As a very generalized rule of thumb, if a table has more than 10 columns, it may need to be split into multiple tables.
• InnoDB can’t optimize SELECT COUNT(*) Use counter tables or gather this information from information_schema.

Conclusion

Conclusion placeholder