using mysql in automated testing
TRANSCRIPT
![Page 1: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/1.jpg)
![Page 2: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/2.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Using MySQL in Automated Testing
Morgan Tocker MySQL Community Manager February 2015
![Page 3: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/3.jpg)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
3
![Page 4: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/4.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |4
Today’s Agenda
Introduction
Tools
Bootstrapping
Pushing Database Changes
Metrics to Capture
1
2
3
4
5
![Page 5: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/5.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Introduction
• Database are not an outlier • Can be automated / scripted / managed as part of a deployment • Many good tools exist
5
![Page 6: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/6.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
History of MySQL specific versions
• MySQL 5.1 and below: • No Online DDL • Adding indexes to InnoDB tables causes rebuild • Supports triggers from 5.0 (!)
6
![Page 7: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/7.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
History of MySQL specific versions (cont.)
• MySQL 5.5 (2010): • InnoDB as default • Fast ALTER TABLE
7
![Page 8: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/8.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
History of MySQL specific versions (cont.)
• MySQL 5.6 (2013): • Online DDL • Performance Schema by default
8
![Page 9: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/9.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
History of MySQL specific versions (cont.)
• MySQL 5.7 (In Development): • Multiple triggers per table
9
![Page 10: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/10.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Online DDL (MySQL 5.6+)
• Can add indexes, many other changes without blocking READS or WRITES: • http://dev.mysql.com/doc/refman/5.6/en/innodb-‐create-‐index-‐overview.html
• Still requires application to handle two versions of schema. • Does not facilitate READ/WRITE split with slaves.
10
![Page 11: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/11.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | 11
In-Place? Copies Table? Allows Concurrent DML?
Allows Concurrent Query?
CREATE INDEX,ADD INDEX Yes* No* Yes YesADD FULLTEXT INDEX Yes No* No YesDROP INDEX Yes No Yes YesOPTIMIZE TABLE Yes Yes Yes YesSet default value for a column Yes No Yes YesChange auto-increment value for a column Yes No Yes YesAdd a foreign key constraint Yes* No* Yes YesDrop a foreign key constraint Yes No Yes YesRename a column Yes* No* Yes* YesAdd a column Yes Yes Yes* YesDrop a column Yes Yes Yes YesReorder columns Yes Yes Yes YesChange ROW_FORMAT property Yes Yes Yes YesChange KEY_BLOCK_SIZE property Yes Yes Yes YesMake column NULL Yes Yes Yes YesMake column NOT NULL Yes* Yes Yes YesChange data type of column No Yes No YesAdd primary key Yes* Yes Yes YesDrop primary key and add another Yes Yes Yes YesDrop primary key No Yes No YesConvert character set No Yes No YesSpecify character set No Yes No YesRebuild with FORCE option No Yes No Yes
![Page 12: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/12.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |12
Today’s Agenda
Introduction
Tools
Bootstrapping
Pushing Database Changes
Metrics to Capture
2
3
4
2
1
5
![Page 13: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/13.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Worth Considering
• pt-‐online-‐schema-‐change • MySQL Sandbox • SYS • Outbrain Propagator • Liquibase • ORM specific migrations • libeatmydata
13
![Page 14: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/14.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
pt-‐online-‐schema-‐change
• Part of “Percona Toolkit”. • Simulates Online DDL by creating a shadow table and adding triggers to the original table. • Later performs a hot switchover.
• Useful for statements that do not support Online DDL. • Required in substitution of Online DDL in the case slaves are required for active use.
14
![Page 15: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/15.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
MySQL Sandbox
• Allows you to run multiple copies at once, each self contained. • Can bootstrap a fresh install from a .tar.gz of MySQL. • Can bootstrap common topologies. • i.e. Master with a slave
15
![Page 16: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/16.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
SYS
• Common set of views on MySQL’s Performance Schema. • Entirely SQL view of all of MySQL’s Performance. • Could potentially problematic queries in QA during test suite execution, etc…
16
![Page 17: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/17.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Outbrain Propagator
• Based on SQL Scripts • Concept of Environments
17
![Page 18: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/18.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. | 18
![Page 19: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/19.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Liquibase
• Specify change in XML/Yaml/JSON/SQL
19
![Page 20: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/20.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
ORM Specific
• Rails Migrations • Django Migrations (1.7+)
20
![Page 21: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/21.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
libeatmydata
• Disables fsyncs and related functions • Packaged in Ubuntu • apt-‐get install eatmydata
21
root@mysqldb:/usr/local/mysql# eatmydata ./bin/mysqld —user=mysql
![Page 22: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/22.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |22
Today’s Agenda
Introduction
Tools
Bootstrapping
Pushing Database Changes
Metrics to Capture
2
3
4
1
5
3
![Page 23: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/23.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Building a new QA Environment
• Recommend starting with either MySQL::Sandbox or official repos. • Can restore data from mysqldump, or copying data data directory in place.
23
![Page 24: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/24.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Official Repos
24
sudo yum localinstall http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm yum install mysql-community-server
• More Information: • http://dev.mysql.com/downloads/repo/yum/ • http://dev.mysql.com/downloads/repo/apt/ • http://dev.mysql.com/downloads/repo/suse/
![Page 25: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/25.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
MySQL::Sandbox
25
$ make_sandbox mysql-5.6.23-osx10.9-x86_64.tar.gz unpacking /Users/morgo/Downloads/mysql-5.6.23-osx10.9-x86_64.tar.gz Executing low_level_make_sandbox --basedir=/Users/morgo/Downloads/5.6.23 \ --sandbox_directory=msb_5_6_23 \ --install_version=5.6 \ --sandbox_port=5623 \ --no_ver_after_name \ --my_clause=log-error=msandbox.err The MySQL Sandbox, version 3.0.47 (C) 2006-2013 Giuseppe Maxia installing with the following parameters: upper_directory = /Users/morgo/sandboxes sandbox_directory = msb_5_6_23 sandbox_port = 5623 .. do you agree? ([Y],n) Y loading grants .. sandbox server started Your sandbox server was installed in $HOME/sandboxes/msb_5_6_23
![Page 26: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/26.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
My Local Sandboxes
26
morgo@Rbook:~/sandboxes$ ls clear_all msb_5_5_40 msb_5_6_15 msb_5_6_22 msb_5_7_3 msb_5_7_5.bak sandbox_action status_all msb_5_5_32 msb_5_6_12 msb_5_6_16 msb_5_6_23 msb_5_7_4 plugin.conf send_kill_all stop_all msb_5_5_36 msb_5_6_13 msb_5_6_17 msb_5_7_1 msb_5_7_5 restart_all start_all use_all
morgo@Rbook:~/sandboxes$ cd msb_5_6_23 morgo@Rbook:~/sandboxes/msb_5_6_23$ ./use Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.23 MySQL Community Server (GPL) .. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost] {msandbox} ((none)) >
Version 5.6.23 = Port 5623
![Page 27: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/27.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Restoring Data
• Some testing environments (i.e. staging) may have a snapshot of actual data. • This is actually a very good practice if your data security policy allows for it. • Ensures highest chance of same performance characteristics as production.
27
![Page 28: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/28.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Restoring Data (cont.)
• mysqldump can be very slow to load data into your newly setup environment. • Consider copying data directory in raw format instead.
28
![Page 29: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/29.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
How to Configure MySQL in QA?
• It’s up to you! • For CI rule of thumb says that the test suite should execute in less than 5 minutes.
29
![Page 30: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/30.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Alternative #1 -‐ mysql-‐reckless.cnf
• Disable MySQL’s durability:
• Warning: Not 100% a best practice.
30
sync_frm=0 sync_binlog=0 innodb-flush-log-at-trx-commit=0 innodb-doublewrite=0 innodb_support_xa=0 innodb_checksum_algorithm=none innodb_adaptive_flushing=OFF
Source: https://github.com/morgo/mysql-‐compatibility-‐config/blob/master/mysql-‐56/mysql-‐56-‐reckless.cnf
![Page 31: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/31.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Alternative #2 -‐ Disable fsync and friends
• At a lower level, kill durability. • Keeps MySQL functionality consistent between QA and Production. • Possibly requires Linux.
31
root@mysqldb:/usr/local/mysql# eatmydata ./bin/mysqld —user=mysql
![Page 32: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/32.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |32
Today’s Agenda
Introduction
Tools
Bootstrapping
Pushing Database Changes
Metrics to Capture
2
3
4
1
5
4
![Page 33: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/33.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Two Categories of Change
• Data Definition Language • aka DDL • Adding/Dropping Indexes • Adding/Dropping Columns
• ..
• Data manipulation language
• aka DML
• One off tasks to modify data
• Repairing incorrect values • ..
33
![Page 34: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/34.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DDL Considerations
• DDL does not support transactions • Is still atomic on a per-‐table basis • Grouping multiple tables may complicate deployment:
34
ALTER TABLE t1 ADD INDEX (a); ALTER TABLE t2 ADD INDEX (a); // t2 fails ALTER TABLE t3 ADD INDEX (a);
![Page 35: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/35.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DDL Considerations (cont.)
• Good practice to group all changes for one table to single chained ALTER command. • Saves on table rebuilds where required:
35
ALTER TABLE my_table ADD my_column1 VARCHAR(255), ADD my_column2 VARCHAR(255);
![Page 36: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/36.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DDL Considerations (cont.)
• Don’t play a guessing game with Online DDL. • ALTER TABLE supports LOCK=NONE extension:
36
CREATE TABLE acdc (a int);
ALTER TABLE acdc ADD PRIMARY KEY (a), LOCK=NONE; Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE acdc DROP PRIMARY KEY, LOCK=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.
![Page 37: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/37.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DDL Considerations (cont.)
• Any time you change indexes: • Existing query plans can change • Diligent DBAs watch for regressions :)
37
![Page 38: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/38.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DML Example
• Added a new column called is_paying_user on users table. • New code correctly sets is_paying_user to 0 or 1. • All 5M existing users will need the is_paying_user property set.
38
![Page 39: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/39.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DML Considerations
• SQL is not guaranteed idempotent • Best to make sure that any script fails or does nothing if executed twice.
39
UPDATE t SET a = a+1; // potentially bad. UPDATE t SET a = 11; // better UPDATE t SET a = 11 WHERE a = 10; // safest
![Page 40: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/40.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DML Considerations (cont.)
• Modifications via temporary tables are unsafe for Statement-‐based replication:
40
CREATE TEMPORARY TABLE tmp_user_payments AS SELECT DISTINCT(user_id) as user_id FROM payments WHERE payment_total > 0;
/* Slave can disconnect here and lose tmp table */
UPDATE users, tmp_user_payments SET is_paying_user=1 WHERE user tmp_user_payments.user_id=users.user_id;
![Page 41: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/41.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Suggested Fixes:
• Use Row-‐Based replication (proposed default in 5.7) • Use base table instead of temporary table. • Perform change in single step, such as via sub query (really requires 5.6+)
41
![Page 42: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/42.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
DML Considerations (cont.)
• Locks are held for the duration of a transaction • Scripts that appear safe in QA may still create production problems • Common practice to chunk-‐split batch operations to yield locks (i.e. 10K row modifications per transaction.) • Not strictly ACID :)
42
![Page 43: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/43.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Distributed Systems
• i.e. Sharded of Functional Partitioned Deployments • Presents special challenges • Application typically must talk to two versions of a schema at once. • Requirement still true for non distributed systems…
43
![Page 44: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/44.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Why two versions of a Schema?
• Even when DDL is online, can’t control when it finishes and new schema is immediately visible to application. • Guaranteed safe way: • Stop old version of Application • Apply Database Changes • Start new version of Application
44
Painful
![Page 45: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/45.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Examples of Incompatibilities
• Renaming a column • $row[4]; // Using nth column. Can’t guarantee new column preserves order. • INSERT into my_table VALUES (1,2,3); // Non-‐full inserts if column count changes • SELECT * FROM a UNION SELECT * FROM b; // Column count mismatch if a adds a column • INSERT INTO a SELECT * FROM b // Column count mismatch if a adds a column
45
![Page 46: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/46.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
How to make an application speak two schemas?
• Introduce Social Change • Developers will not always consider deployment issues • Easy way to force them is to make database changes separate from other changes
46
![Page 47: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/47.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
When to add that column?
• Make the change happen in the release before. • If it’s easier to explain, have two types of software releases: • Database Changes • Code Changes
47
![Page 48: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/48.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
When to add that column? (cont.)
• Can also be handled as push prerequisites or post-‐requisites. • Adds to complexity. • Release steps should not boil the ocean -‐ hard to keep simple and automated.
48
![Page 49: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/49.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Out of Scope Today
• Almost starts discussion on how to refactor databases • This is a good book • Not MySQL specific
49
![Page 50: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/50.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |50
Today’s Agenda
Introduction
Tools
Bootstrapping
Pushing Database Changes
Metrics to Capture
2
3
4
1
55
![Page 51: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/51.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
“Bad” Things
• Not always likely to catch slow queries in QA while tests execute. • Data set can not always reflect production.
• May be able to catch symptoms.
51
![Page 52: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/52.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Symptoms
• SHOW GLOBAL STATUS LIKE 'Select_full_join'; • Slow queries • Unhandled errors or warnings • In non-‐strict versions of MySQL “warnings” are probably “bugs”. • Not recommended to use deprecated features from Day #1.
52
![Page 53: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/53.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Test suite execution stats
• SYS schema with MySQL is very useful. • SQL Interface to fetch runtime data directly.
53
![Page 54: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/54.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Slow Queries
54
mysql> select * from statement_analysis\G *************************** 1. row *************************** query: SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` ( db: sys full_scan: * exec_count: 4 err_count: 0 warn_count: 0 total_latency: 100.45 ms max_latency: 30.83 ms avg_latency: 25.11 ms lock_latency: 1.59 ms rows_sent: 683 rows_sent_avg: 171 rows_examined: 1367 rows_examined_avg: 342 rows_affected: 0 rows_affected_avg: 0 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 683 sort_merge_passes: 0 digest: 0efbdc5d2727eea1b943011cde39e375 first_seen: 2015-02-20 07:53:52 last_seen: 2015-02-20 07:54:10
![Page 55: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/55.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |
Runtime Stats
mysql> select * from wait_classes_global_by_latency; +-------------------+--------------+---------------+-------------+-------------+-------------+ | event_class | total_events | total_latency | min_latency | avg_latency | max_latency | +-------------------+--------------+---------------+-------------+-------------+-------------+ | wait/io/file | 550470 | 46.01 s | 19.44 ns | 83.58 µs | 4.21 s | | wait/io/socket | 228833 | 2.71 s | 0 ps | 11.86 µs | 29.93 ms | | wait/io/table | 64063 | 1.89 s | 99.79 ns | 29.43 µs | 68.07 ms | | wait/lock/table | 76029 | 47.19 ms | 65.45 ns | 620.74 ns | 969.88 µs | | wait/synch/mutex | 635925 | 34.93 ms | 19.44 ns | 54.93 ns | 107.70 µs | | wait/synch/rwlock | 61287 | 7.62 ms | 21.38 ns | 124.37 ns | 34.65 µs | +-------------------+--------------+---------------+-------------+-------------+-------------+
55
![Page 56: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/56.jpg)
Copyright © 2015 Oracle and/or its affiliates. All rights reserved. |56
![Page 57: Using MySQL in Automated Testing](https://reader030.vdocuments.us/reader030/viewer/2022032421/55a7081d1a28ab02708b4630/html5/thumbnails/57.jpg)