mysql tech tour 2015 - manage & tune

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

How To Manage & Tune MySQL

Mark SwarbrickPrinciple Presales Consultant


Safe Harbor StatementThe 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

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 4

Manage & Tune MySQL

Tuning Considerations + Go-live factors to consider


Tuning + Go-LiveConsiderations


Performance Testing – How to measure success

Monitoring – Make sure you are monitoring the correct metrics

Backup Strategy – Will your backups be suitable for purpose?

High Availability – Does the uptime requirement match the design?

Failover – Have you got the policies and procedures in place to failover to a backup data center or server?

Growth – Have you planned for growth of data and throughput?

Tuning – How to tune and monitor for performance


MySQL Performance Schema• Identify performance bottlenecks• Identify problematic queries • Get real time insight into locks• See exactly what is happening

within MySQL • Get real time insight into MySQL

internals • Get real time insight into query

executions

7

mysql> select * from host_summary_by_stages;+------+--------------------------------+-------+-----------+-----------+| host | event_name | total | wait_sum | wait_avg | +------+--------------------------------+-------+-----------+-----------+| hal | stage/sql/Opening tables | 889 | 1.97 ms | 2.22 us | | hal | stage/sql/Creating sort index | 4 | 1.79 ms | 446.30 us | | hal | stage/sql/init | 10 | 312.27 us | 31.23 us | | hal | stage/sql/checking permissions | 10 | 300.62 us | 30.06 us | | hal | stage/sql/freeing items | 5 | 85.89 us | 17.18 us | | hal | stage/sql/statistics | 5 | 79.15 us | 15.83 us | | hal | stage/sql/preparing | 5 | 69.12 us | 13.82 us | | hal | stage/sql/optimizing | 5 | 53.11 us | 10.62 us | | hal | stage/sql/Sending data | 5 | 44.66 us | 8.93 us | | hal | stage/sql/closing tables | 5 | 37.54 us | 7.51 us | | hal | stage/sql/System lock | 5 | 34.28 us | 6.86 us | | hal | stage/sql/query end | 5 | 24.37 us | 4.87 us | | hal | stage/sql/end | 5 | 8.60 us | 1.72 us | | hal | stage/sql/Sorting result | 5 | 8.33 us | 1.67 us | | hal | stage/sql/executing | 5 | 5.37 us | 1.07 us | | hal | stage/sql/cleaning up | 5 | 4.60 us | 919.00 ns | +------+--------------------------------+-------+-----------+-----------+


MySQL Workbench Overview

• MySQL Database IDE• Millions of Downloads• Windows, OS X, Linux


Workbench 6.0

New Look, Redesigned GUI, Simplified Workflow• Home Screen, Managing Connections, Edit & Admin, Color Scheme, Icons

For Developers• Schema Inspector, Maintenance, Cascading Delete, Vertical Results, Table Data Search, Context Sensitive Help, Table Templates

For Migration• New Sybase Anywhere, SQLite

For Administrators• Server Status / Server Data, Backup GUI, Audit Log Inspector

For Design• Improved Synchronization, Improved model printing to PDF files

What’s New


Performance TasksIn Workbench

Monitor

Assess

OptimizeTune

Configure


Start by looking at Server Status

Management Tab – Server Status


Basics

• Enabled/Disabled Features

Directory Setup On best disks? Adequate space? Slow Query Log On?


Performance Testing – How to measure success

Monitoring – Make sure you are monitoring the correct metrics

Availability

Use a decent query

Logs

Logs filling up disk space

Slow query log filling space

Disk Space

Disks getting full is the most common problem

Ideally alarm on how soon disk space changes rather than absolute disk space


Performance Testing – How to measure successMonitoring – Make sure you are monitoring the correct metrics

Stalls / Spikes

Table Locks

CPU Spikes

Memory Paging

Connections

Are you reaching max_connections limit?

Can the application connect?

Processes

Long running Processes



TransactionsLong running or long idle transactionsQueued transactions (size of trans queue) (show innodb status)

ReplicationIs Replication runningWhats the replication lag



Query PerformanceTop Queries to optimise – (interactive monitor not generate alerts on)Response time outliers Queries not using indexQueries using full/ partial scans / table scansQueries that return errors / warning

Operational AspectsServer restartsServer config change eventsMessages in the error Log


Backup Strategy – Will your backups be suitable for purpose

MySQLdump - It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldumpcan also be used to generate files in CSV, other delimited text, or XML format.

MySQL Enterprise Backup - "Hot" Online Backups - Backups take place entirely online, without interrupting MySQL transactions

High Performance - Save time with faster backup and recovery

Incremental Backup - Backup only data that has changed since the last backup

Partial Backup - Target particular tables or tablespaces

Full Instance Backup - Backs up data, as well as configuration and other information to easily create a complete "replica"

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

MySQL Backup Types: Comparison

mysqldump LVM Snapshots MySQL Replication MySQL Enterprise Backup

Full Backup ✔ ✔ ✔ ✔

Incremental Backups ✖ ✔ ✖ ✔

Partial Backups ✔ ✖ ✖ ✔

Compression Support ✖ ✖ ✖ ✔

Allows updates ✖ ✖ ✔ ✔

Point in Time - Consistent ✖ ✔ ✔ ✔

Backup Speed Poor Good Very Good Very Good

Recovery Speed Very Poor Good Very Good Very Good

Partial Restore ✔ ✖ ✖ ✔

LZ4 Compression ✖ ✔

AES 256 Encryption ✖ ✔

Corruption Detection ✔ ✖ ✖ ✔

Meets Regulatory Archive Req.

✔ ✖ ✖ ✔

Supports DDL ✔ ✖ ✖ ✔


High Availability – Does the uptime requirements match the design?

See Planning for outages section ;-) But always good to refer back to the original design spec!


Failover – Have you got the policies and procedures in place to failover to a backup data center or server?

Most HA strategies fail not due to the hardware or software, but to the lack of awareness and testing of policies and procedures in place!


Growth – Have you planned for growth of data and throughput?


Tuning – How to tune and monitor for performance

Hardware

Config Options

Schema

InnoDB

Performance Monitoring


Hardware

Schema ChangesData Growth

Indexes

SQL

90% of Performance Problems

Source of Database Performance Problems

23

Hardware: The Perfect MySQL Server

• The more cores the better (especially for 5.5 and later)• x86_64 - 64 bit for more memory is important– The more the better• Fast HD (10-15k RPM SATA) or NAS/SAN……– RAID 10 for most, RAID 5 OK if very read intensive– Hardware RAID battery backed up cache critical!– More disks are always better! - 4+ recommended, 8-16 can increase IO• …Or SSD (for higher throughput)– Intel, Fusion-IO good choices; good option for Slaves• At least 2 x NICs for redundancy• Slaves should be as powerful as the Master

Config OptionsThe MySQL server is controlled by “System Variables”

• Set Via:– my.cnf / my.ini– SET [GLOBAL] <variable>=<value>– client, i.e mysql• Can be local (session) or global

The World’s Most Popular Open Source DatabaseCopyright 2010 Oracle


Schemas

• Size = performance, smaller is better– Size right! Do not automatically use 255 for VARCHAR• Temp tables, most caches, expand to full size

• Use “procedure analyse” to determine the optimal types given the values in your table

– http://dev.mysql.com/doc/refman/5.1/en/procedure-analyse.html– mysql> select * from tab procedure analyse (64,2000) \G

• Consider the types:– enum : http://dev.mysql.com/doc/refman/5.1/en/enum.html – set : http://dev.mysql.com/doc/refman/5.1/en/set.html

• Compress large strings– Use the MySQL COMPRESS and UNCOMPRESS functions


InnoDB Tuning

• Unlike MyISAM - InnoDB uses a single cache for both index and data– innodb_buffer_pool_size - should be 70-80% of available memory.– It is not uncommon for this to be very large, i.e. 34GB on a system with 40GB of memory– Make sure its not set so large as to cause swapping!– mysql>show status like 'Innodb_buffer%' ;• InnoDB can use direct IO on systems that support it - Linux, FreeBSD, and Solaris– innodb_flush_method = O_DIRECT• For more InnoDB tuning see– http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning-troubleshooting.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning-troubleshooting.html

A Virtual MySQL DBA Assistant!

Web-based, global view of MySQL/Cluster applications (on-premise and Cloud deployments)

Automated, rules-based monitoring and alerts (SMTP, SNMP enabled)

Query capture, monitoring, analysis and tuning, correlated with Monitor graphs

Real-time Replication Monitor with auto-discovery of master-slave topologies

Integrated with Oracle Support

MySQL Enterprise Monitor


Thank You!

mysql tech tour 2015 - manage & tune

Technology