increasing mysql productivity

Increasing MySQL Productivity

From design to implementation

Improving MySQL Productivity from Design to Implementation - 2010.06

Title

Ronald Bradfordhttp://ronaldbradford.com

2010.06

http://ronaldbradford.com

http://ronaldbradford.com


Objectives

MySQL development tips and tricks

Identify poor development practices

Recommended best practices

Improve knowledge and MySQL skills

Optimal deployment & support

Productivity ROI• Increase your productivity• Improve development quality


Session Scope

Design

Development

Security

Testing

Instrumentation

Implementation

Support

R & D


ODTUG Presentations Review

Data Integrity, Storage Engines, Diagnostics, SQL Syntax, Replication, Optimizing SQL, Meta Data, Query Analyzer, Workbench, Data Import/Export, Caching with Memcached, MySQL joins, Database Administration

http://www.odtugkaleidoscope.com/MySQL.html

http://ronaldbradford.com/blog/mysql-idiosyncrasies-that-bite-2010-06-28/




MySQL Idiosyncrasies that BITE

What are MySQL Defaults

The impact of using defaults

Ensuring better compatibility






Storage Engines in ReviewWhat is the pluggable storage architectureBuilt In EnginesPlug in EnginesEngine CharacteristicsHow to create a storage engine






InnoDB Usage and Diagnostics

MySQL transactional storage engine

Clustered and Secondary Indexes

Understanding innoDB threads

Performance metrics






What do you mean "SQL Syntax Error"

The ANSI standard

Similarities that produce differences

What's missing






Everything you didn't know about MySQL Replication

Replication Basics

Asynchronous

Topology options

Replication usages






Importing and Exporting Data with MySQL

Loading Data (csv, tsv, fixed, report)

Performance benefits

Dumping Data






Capturing, Analyzing and Optimizing your SQL

Various capture methods

Bulk and per query analysis

Optimization techniques and examples






Query Analysis with MySQL Enterprise Monitor

GUI SQL Analysis

Monitoring

Alerts and Advisors






Using MySQL Meta Data Effectively

the INFORMATION_SCHEMA






Navigating MySQL Stored Procedures & Funtions, Views and Triggers

The MySQL Syntax

Limitations






Data Caching with Memcached and MySQL

Leveraging Caching

Write thru/Write back

UDF support






Design and Development using MySQL Workbench

GUI Schema Development

Re-engineering schemas

Query browser integration






JoinFu - The Art of SQL

Join Basics

And/Or conditions

Hierarchal data

Aggregation and ranking






The Five Minute DBA

Quick DBA tips

Configuration

Monitoring





Design


Design Best Practices

Data Types

Schema Management

Sharding 101

Higher Availability


About MySQL Data Types

Numeric Data Types

Oracle has 1

MySQL has 9

TINYINT,SMALLINT,MEDIUMINT,INT, BIGINT,FLOAT,DOUBLE,DECIMAL,BIT


Optimal Auto Increment Primary Key

Don't use BIGINT AUTO_INCREMENT

Use INT UNSIGNED AUTO_INCREMENT

BIGINT is 8 bytes

INT is 4 Bytes

INT UNSIGNED stores 4.3 billion values

Productivity ROI• Can reduce index space by 50+% • Better memory usage, less I/O


INT(1)

This is not 1 byte, it's 4 bytes

(1) is only for client display only

Client with 10+ flags using INT(1)

40 bytes reduced to 10 bytes

2 bytes using bit operators

INT(1) is not what it looks like

Horror Stories


Dates

MySQL supports

DATE, DATETIME, TIMESTAMP, YEAR

TIMESTAMP for Epoch values

TIMESTAMP is 4 bytes

DATETIME is 8 bytes

Supports DEFAULT CURRENT_TIMESTAMP

Neither store milliseconds


The beauty of ENUM

Values Check Constraint

Ideal for static codes

Compact - i.e. 1 byte for 'True', 'False'

Human readable values

5.1 Non blocking ALTER


TRUE/FALSE Datatype Examples

CREATE TABLE enums ( flag1 CHAR(1) NOT NULL COMMENT 'T or F, Y or N', flag2 TINYINT NOT NULL COMMENT '0 or 1', flag3 BIT NOT NULL COMMENT 'True or False', flag4 ENUM ('True','False') NOT NULL);

INSERT INTO enums(flag4) VALUES ('True', 'False');SELECT flag4 FROM enums;


InnoDB Primary Key

Primary key is clustered index15/16 fill factor for sequential50% for random

Secondary Indexes hold primary key value

Best Practice - Keep primary keys shortexcept for high Disk I/O


Schema Best Practices

Have pristine schema

Use different schemas

Backup copies of tables

Temporary objects


Schema Management

Always have current schema.sql

Use Patch/Revert SQL for upgrades

See http://schemasync.org

Productivity ROI• Reproducibility • Upgrade/Downgrade path

http://schemasync.org

http://schemasync.org


Best Practices

Separate Schema changes and application code

Productivity ROI• Developers develop • Administrators administer


Sharding 101

Sharding 101

Design


Sharding 101

A database table consists of

Columns

Rows


Sharding 101

col1 col2 col3 col4 col5 col6 col7

Columns


Sharding 101

col1 col2 col3 col4 col5 col6 col7row1row2row3row4row5row6row7row8row9row10row11row12row13row14

Rows


Sharding 101

A database consists of

Tables

Table Rows


Sharding 101

table1 table2 table3 table4 table5 table6 table7

Tables


Sharding 101

table1 table2 table3 table4 table5 table6 table7row1row2row3row4row5row6row7row8row9row10row11row12row13row14

Table Rows


Sharding 101

Partitioning is?

Grouping like rows in a table together

e.g.

By Date

By Local

By parent grouping


Sharding 101

col1 col2 col3 col4 col5 col6 col7row1row2row3row4row5row6row7row8row9row10row11row12row13row14

Partition


Sharding 101

Sharding is?

Grouping like tables and/or like rows together


Sharding 101


Tables


Sharding 101


Tables + Rows

1 1


Sharding 101


Tables + Rows

2 2


Sharding 101


Tables + Rows

3 3


Sharding 101


Shards

1

2

3

1

2

3


Sharding 101


Shards

1

2

3

1

2

3

4 4 4


Sharding 101


Shards

1

2

3

1

2

3

4 456

89

7


Sharding 101

How do you group tables?

That’s the secret


Sharding 101 - First Steps

First Steps

Consider schema separation

Define a good partition key

Balanced solution

Support rebalancingProductivity ROI

• Write Scalability• Done before application grows in complexity and usage


Sharding 101 - Next Steps

Next Steps

Managing Joins

Searching Data

Unique Constraints

Aggregated ReportingProductivity ROI

• Write Scalability• Done before application grows in complexity and usage


Manual Partitioning

Ideal for Write Once Data

MySQL supports Atomic table rename

RENAME TABLE current TO old, new TO current;


Data Partitioning

Feature of MySQL 5.1

RANGE

LIST

HASH

KEY

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html




Sharding 101

Supporting Growth

Design


Supporting Growth

Availability

Scalability

Resilience

Seek ProfessionalAdvice


Availability

How do you support maintenance?

Software upgrades

Application releases

24x7 operations

Eliminate single points of failure

Productivity ROI• Less Downtime • Greater Sales / Better Reputation


Scalability

Read ScalabilityReplication

Write ScalabilitySharding

CachingImplement from day 1

Use best product for purpose

Productivity ROI• Support Demand


Scalability Implementation

Implementation

H/W acquisition time

Zero new human resource need

Zero application changes

Productivity ROI• Seamless growth support


Resilience

What is your backup strategy?

What is your recovery strategy?

How long does it take?

Have you actually tested it end to end?

Take the surveyhttp://ronaldbradford.com/blog/checked-your-mysql-recovery-process-recently-2010-02-15/

Productivity ROI• Company viability

http://ronaldbradford.com/blog/checked-your-mysql-recovery-process-recently-2010-02-15/

http://ronaldbradford.com/blog/checked-your-mysql-recovery-process-recently-2010-02-15/


MySQL Configuration

Design


Configuration Best Practices

Always use transactions

SQL_MODE

Data Integrity

MySQL Idiosyncrasies that BITE Talk





Security

Design


MySQL User Security

Default is woeful

Minimum

$ mysql_secure_installation

Recommended

Operating System

Permissions & Privileges


Operating System Security

Defaults are not secure

Never run as 'root' user

Separate Data/Binary Logs/Logs/Configuration/Backups

Individual directory permissions

Productivity ROI• Minimize security risk• Better auditability


MySQL Installation Best Practice

Best Practice

/mysql /etc /data /binlog /log /mysql-version

/etc/my.cnf/etc/profile.d/mysql.sh/etc/init.d/mysqld


MySQL Installation Security

Software installed by root$ chown -R root:root /mysql$ chown -R mysql:mysql /mysql/{data,log,binlog/etc}$ chmod 700 /mysql/{data,binlog}$ chmod 750 /mysql/{etc,log}


Application User Security

CREATE USER appuser@localhost IDENTIFIED BY 'sakila';GRANT SELECT,INSERT,UPDATE,DELETE ON schema.* TO appuser@localhost;

CREATE USER superman@'%';GRANT ALL ON *.* TO superman@'%';

Best Practice

Normal Practice

See MySQL Idiosyncrasies that BITE session for more information

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_default-storage-engine



Application User Security Best Practices

Application User (Read/Write Access)

INSERT, UPDATE, DELETE, SELECT

Application Viewer (Read Only Access)

SELECT

Application DBA (Schema Access Only)

CREATE, DROP, CREATE ROUTINE, SELECT, INSERT, UPDATE, DELETE

Productivity ROI• Track Data Security• Separation of responsibilities


Why not use GRANT ALL

GRANT ALL ON *.* TO user@’%’

*.* gives you access to all tables in all schemas

@’%’ give you access from any external location

ALL gives youALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, USAGE

See MySQL Idiosyncrasies that BITE session for more information




Why SUPER is bad?

SUPER

Bypasses read_only

Bypasses init_connect

Can Disable binary logging

Change configuration dynamically

No reserved connection


Security

DesignDevelopment


Development Best Practices

Version control

General query log

SQL Commenting

SQL Formatting

Future proofing

Embrace CLI

Caching


Version Control

Use for all development

Especially single user development

Use for schema management

Building block of automated build/release

Productivity ROI• Central repository of code• Track code changes by developer• Create reproducable releases


General Query Log

Turn on for all development environments

Logs all SQL Statements

Aggregate and Email results to developer

See Capturing, Analyzing and Optimizing SQL Presentation

Productivity ROI• Developers are seeing SQL in operation• Enables access to SQL to analyze


Leveraging General Query Log

For single user development environment

In SQL Session

In Application

Run Function/Process

In SQL Session

mysql> SELECT 'Function X Start';

mysql> SELECT 'Function X End';


Slow Query Log

Turn on for all development environments

Logs slow SQL Statements ( > 1 second)

Aggregate and Email results to developer

See Capturing, Analyzing and Optimizing SQL Presentation


SQL Commenting

Identify queries by code function

Identify OLTP / Batch / Cache queries

SELECT /* XXX123 */ col1, ...

UPDATE /* YYY999 */ ...

SELECT /* Batch */...Productivity ROI

• DBA/SA can identify code function and purpose quickly


SQL Commenting (2)

26 Query SELECT /* 5m cache */ ..... 26 Query SELECT /* ViewPost */ t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (2849, 2842, 2836, 2824, 2812, 2680, 2813, 2800, 2770, 2784) ORDER BY t.name ASC 26 Query SELECT /* batch */ meta_key, meta_value FROM wp_usermeta WHERE user_id = 2


SQL Formatting

Create single line queries

Don't embed newlines

Enables per line analysis by CLI tools

Productivity ROI• DBA/SA can use simple CLI tools including grep,awk,cut etc for SQL analysis


SQL Formatting (2)

26 Query SELECT FOUND_ROWS() 26 Query SELECT post_id,start,end,allday,rpt,IF(end>='2010-06-04 00:00:00',1,0) AS active FROM wp_ec3_schedule WHERE post_id IN (2849,2842,2836,2824,2812,2680,2770,2784) ORDER BY start

26 Query SELECT * FROM wp_users WHERE user_login = 'ronald' 26 Query SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (2849, 2842, 2836, 2824, 2812, 2680, 2813, 2800, 2770, 2784) ORDER BY t.name ASC 26 Query SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 2


SQL Analysis

Bulk trending analysis

Volume of SQL statements

Query Execution Plan (QEP)

Online v Batch/Cache SQL via commenting

Productivity ROI• Identify bottlenecks ASAP without load• Iterative Design feedback


EXPLAIN Basics

EXPLAIN SELECT ...

No key used

key length

Extra - Using Filesort, Using Temporary


Future proofing your SQL

Specify INSERT columns

INSERT INTO table(a,b,c) VALUES(...)

SELECT * is generally bad

What columns are actually used in codeTEXT/BLOB can cause extra disk I/ONew columns can change performance

Productivity ROI• Reduce likelihood of runtime errors when structural changes to objects


Avoid Fancy Constructs

DELAYED

IGNORE

LOW PRIORITY

REPLACE

Productivity ROI• Changes ACID and statement precedence• May have additional performance overhead


Using Deterministic Functions

Use '2010-06-21' instead of CURDATE()

Same for NOW()

Leverage Query Cache (if enabled)

Productivity ROI• Leverages database caching when enabled• Allows testing via parameterization


Common Coding Errors

Remove Duplicate Code

Productivity ROI• Less code to maintain• Remove chance of human errors


Common SQL Errors

Remove redundant SQL

Use general query log

You may be surprised!


Common Coding Errors - Repeating Queries

5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist` WHERE (ArtistID = 196 )5 Query SELECT * FROM àrtist` WHERE (ArtistID = 2188 )5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`5 Query SELECT * FROM àrtist`

The WRONG way


Common Coding Errors - Row at a time (RAT) Processing

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'SELECT option_value FROM wp_options WHERE option_name = 'aiosp_title_format' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_show_only_even' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_num_months' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_day_length' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_hide_event_box' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_advanced' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_navigation' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'ec3_disable_popups' LIMIT 1SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 1


RAT v CAT Processing

SELECT * FROM activities_theme WHERE theme_parent_id=0SELECT * FROM activities_theme WHERE theme_parent_id=1SELECT * FROM activities_theme WHERE theme_parent_id=2SELECT * FROM activities_theme WHERE theme_parent_id=11SELECT * FROM activities_theme WHERE theme_parent_id=16

SELECT * FROM activities_theme WHERE theme_parent_id in (0,1,2,11,16)

Row (RAT) Processing

Chunk (CAT) Processing


Common Coding Errors - Boundary Conditions

The following SQL executed 6,000 times in 5 minute analysis period

0 is an invalid pages_id

SELECT pages_id, pages_livestats_code, pages_title, pages_parent, pages_exhibid, pages_theme, pages_accession_num FROM pages WHERE pages_id = 0

The WRONG way


In a highly tuned system the greatest time in a

query is network overhead


About Database Connections

Development


Defining your database connection

$ cd public_html$ grep "mysql*_connect" * */* */*/* db-disp.php:$cid = mysql_connect("localhost", "museum", "******") or die ('I cannot connect to the database because: ' . mysql_error());test.php:$cid = mysql_connect("localhost", "museum", "******");PMCollection/connection.php: $dbcnx = mysql_connect("$sqlhost", "$sqluser", "$sqlpass");PMCollection/connection_live.php: $dbcnx = mysql_connect("$sqlhost", "$sqluser", "$sqlpass");PMCollection/connection_local.php: $dbcnx = mysql_connect("$sqlhost", "$sqluser", "$sqlpass");PMEcards/connection.php: $dbcnx = mysql_connect("$sqlhost", "$sqluser", "$sqlpass");core/connection.php: $dbcnx = mysql_connect("$sqlhost", "$sqluser", "$sqlpass");discussion_admin/db_fns.php: $cid = mysql_connect("localhost", "museum", "******");discussion_admin/header.php:// $cid = mysql_connect("localhost", "museum", "******");discussion_admin/inc_title.php: //$cid = mysql_connect("localhost", "museum", "******");discussion_admin/stats.php: //$cid = mysql_connect("localhost", "museum",

The WRONG way


Database connection example

class database {

function getConnection($type, $message) { $cp = $this->getConnectionParameter($type); if (empty($cp)) { $message = new message ("fatal", "Unable to determine '$type' ... return; } try { $con = mysqli_connect($cp->host,$cp->user,$cp->passwd,$cp->database); if (!$con) { $message = new message ("fatal", "Unable to obtain a '$type' ... return; } mysqli_query($con, "SET NAMES 'utf8'"); } catch (Exception $e) { $message = new message ("fatal", "Unable to obtain a '$type' ... debug($e->getMessage()); } return $con; }

The RIGHT way


Database connection parameters example

$ cat config/database.inc.sample

<? require_once "classes/core/connectionparameter.inc";

$connections = array();

// New connections "description", "host", "user", "passwd", "database", "settings"

$connections["write"] = new connectionparameter( "Write DB Connection", "localhost", "writeuser", "******", "db"); $connections["read"] = new connectionparameter( "Read DB Connection", "localhost", "readuser", "******", "db");?>


Database parameterization and version control

Parameters in separate file

config/database.inc.sample

NOTE: .sample in Version Control

Does not override deployment

Productivity ROI• Automated code deployment to multiple environments


Horror Stories - Connection Parameters

Changing Connector/J Settings (1)

Have to reboot server

i.e. no way to flush connection pool

How not to code connection parameters


Horror Stories - Connection Parameters

Changing Connector/J Settings (2)

Rebuild and redeploy jar

How not to code connection parameters


Database Connections

Open and close database connections only when necessary

Productivity ROI• Reduce unnecessary database load• Increases page serve volume• Increases true DB throughput


Database Connections Initialization

$ cat header.php ... $con = getConnection(); ...

if($this_user->user_row["status"]!='ws' && in_array($this_page->getValue(),$page)){ header("Location: /wholesale/permission.php"); exit(); } ... if () { header("Location: abc.php"); exit(); } ... if () { header("Location: xyz.php"); exit(); } ...

The WRONG way


Database Connections Deconstruction

$ cat footer.php

... $con->close();

The WRONG way


Database Connections - Optimal Usage

// do all possible pre-processing first

...

// Then get DB connection $con = database->getConnection("read");

// Close your connection ASAP

// Always future proof objects method calls if ($con != NULL) { $con->close(); }

The RIGHT way


Instrumentation

Development


Application Instrumentation

Creating one primary abstract DB call

Enable logging of ALL SQL statements

Enable embedded HTML output

Total Execution Time/Count

Individual SQL Execution Time/SQL

Productivity ROI• Enable runtime analysis via browser• No additional tools needed to gather

Copyright 2007 MySQL Inc MySQL - The Best Online Database for modern applicationsPerformance Analysis – Step by Step

117

Application Toolset

Joomla CMS

Copyright 2007 MySQL Inc MySQL - The Best Online Database for modern applicationsPerformance Analysis – Step by Step

118

Cold Fusion


Application Instrumentation Benefits

End to End Timing

Component Timing

(i.e. a series of SQL)

Observe as desired

Intelligent Activation

e.g. Page Load exceeds x ms


Quick Tips

Development


Using Text Compression

Via Database

COMPRESS() UNCOMPRESS()

Via Application

Spread CPU Load to many servers

Database Structure Change

TEXT --> BLOB Productivity ROI• Shorter Backup/Recovery times• Greater volume of data for disk usage


Using Text Compression - Example

CREATE TABLE `Mkt` ( `MktID` varchar(10) NOT NULL, `Description` text, PRIMARY KEY (`MktID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1

select min(length(Description)) as min_len, avg(length(Description)) as avg_len, max(length(Description)) as max_lenfrom Mkt;

+---------+---------+---------+| min_len | avg_len | max_len |+---------+---------+---------+| 20 |1122.920 | 7999 | +---------+---------+---------+


Using Compression Example (2)

SELECT length(Description) as len_before, length(compress(Description)) as len_afterFROM Mkt WHERE MktID = '42';+------------+-----------+| len_before | len_after |+------------+-----------+| 7810 | 2025 |+------------+-----------+

...WHERE MktID LIKE '6_';+------------+-----------+| len_before | len_after |+------------+-----------+| 11 | 20 || 53 | 53 || 113 | 100 |

Best 74% saving

No benefit for short data


Using Text Compression (2)

Prime Candidates

Large Text Fields

Repeating data (e.g. xml elements)

Not Indexed

Not Searched


Caching 101

Use caching whenever possible

Caching options

Query results

Objects

HTML component content


Security

DesignDevelopment

Te

stin

g


Testing is not about what works; testing is about breaking your

software


Writing testable code

Small code functions

Use Refactoring


Query Analysis

Database Size / data volumeVersion

QueryQEPResult set sizeRows affected/retrievedTable structure


Function Testability

MVC

API


Security

DesignDevelopment

Te

stin

g

Instrumentation


If you don't have monitoring in place, make it

your top priority


Instrumentation Objectives

System Dashboard

Monitoring

Alerting

Public System Status


System Dashboard

What is the state of NOW!

sampling 1,5,10 seconds

One page listing most important numbers

Red/Yellow/Green

Private/Public View

Productivity ROI• One starting point for all staff. No manual "let me see" needed.


Title

Screen print

DashboardExample


Monitoring

Records history over time

Sampling (e.g. 5 minutes / 1 minute)

Graphical Analysis


Alerting

Identify key limits

Notification rules

24x7


Public Status

Keep your users informed

Planned outage

Unplanned outage

Host elsewhere

Use a Blog

Allows for user feedback

http://status.twitter.com/http://status.blogs.smugmug.com/






The Rule of Everything

Everything

Monitor

Measure

Graph

Automate


Security

DesignDevelopment

Te

stin

g

ImplementationInstrumentation


Implementation Objectives

Instrumentation Implementation

No Downtime!

Pre-emptive Load Management

Higher Availability

Automated Deployment


Development Team:

We need a maintenance window for software

upgrades and new releases.


Management Team:

No Downtime


Development Team:

But we need this to fix problems and improve

performance.


Management Team:

No Downtime


What is your definition of no

downtime?


What is your requirements for no downtime?

For example:Serve PagesServe Ads

NOTE:No need to add/change contentSearch dataCharge for ad impressionsSell Merchandise


MySQL Higher Availability - First Step

MySQL pair

Master / Fail Over Master

Only as good as the weakest link

Slave SQL_THREAD

Slave Disk I/OProductivity ROI

• Actively tests primary DR strategy


Pre-emptive Load Management


Pre-emptive Load Management (2)

What is the Twitter "Failed Whale"

Based on page Load time/Volume

Reject early

http://en.oreilly.com/velocity2009/public/schedule/detail/7479http://www.slideshare.net/netik/billions-of-hits-scaling-twitter



http://www.slideshare.net/netik/billions-of-hits-scaling-twitter

http://www.slideshare.net/netik/billions-of-hits-scaling-twitter



Combined with "Failed Whale"

Darkmode

Disable intensive tasks (e.g. Name Search)



Data Messaging Queue

Per table/chunk

Write Access

Read Access

No Access

Proactively disable/limit application access


Data Messaging Queue Example

Disable Write access to user data

Application changes via notification

Disable option/Notification message

New user registration

Edit profile

Read Access to login/lookup etc


Security

DesignDevelopment

Te

stin

g

Implementation

Support

Instrumentation


Don't change a setting without

evidence to prove/disprove the

Number 1 problem!


Where is the bottleneck?

Is the database the problem?

Front End Performance


Perception

The website will always be too slow

Identify the true components

End to End time

Database may be only a small portion


Front End Tips

• Know your total website load time http://getfirebug.com/

• How much time is actually database related?

• Reduce HTML page size - 15% improvement

• Remove full URL’s, inline css styles

• Reduce/combine css & js files

http://getfirebug.com

http://getfirebug.com


Front End Tips

• Split static content to different ServerName

• Spread static content over multiple ServerNames (e.g. 3)

• Sprites - Combining lightweight images - http://spriteme.org/

• Cookie-less domain name for static content

http://spriteme.org

http://spriteme.org

http://spriteme.org

http://spriteme.org


Continual Improvement

Lower MTTR (mean time to recovery)

Lower MTTD (Mean time to detect)


Conclusion


Security

DesignDevelopment

Te

stin

g

ImplementationInstrumentation

Database Connections

Datatypes

Schema ManagementSh

arding

AvailabilityScalabilityResilience

Version control

SQL Standards Caching

Dashboard - Monitoring - Alerting

NO DOWNTIME

AutomationUser Experience

Break your

system

Support

User Management


RonaldBradford.com

MySQL4OracleDBA.com

increasing mysql productivity

Technology

mysql joins

mysqlproductivityfrom

mysql defaultsthe impact

values productivity

advisors http

ranking http

examples http

storage engine http