mysql & nosql conference: mysql for sysadmins

32
MySQL for Sysadmins Binlogic Conference 2012

Upload: javier-tomas-zon

Post on 16-Jul-2015

76 views

Category:

Technology


1 download

TRANSCRIPT

Page 1: MySQL & noSQL Conference: MySQL for Sysadmins

MySQL for Sysadmins

Binlogic Conference 2012

Page 2: MySQL & noSQL Conference: MySQL for Sysadmins

BEGIN

• Scope

• Things you need to know

• Before install

• After install

• Online processes

• Application kit

Page 3: MySQL & noSQL Conference: MySQL for Sysadmins

Scope

• Sysadmins

• Database Administrators

• Dev Ops

• Operation Team

• Everyone that use MySQL

Page 4: MySQL & noSQL Conference: MySQL for Sysadmins

Things you need to know

• Use 64bits!

• Use kernel and software updated

• MySQL loves RAM

• MyISAM, InnoDB and many others

• Storage Engines are for tables not for DB

• Use partitions when is possible

• Optimize your SQL code

Page 5: MySQL & noSQL Conference: MySQL for Sysadmins

Before install

• Operating System

Page 6: MySQL & noSQL Conference: MySQL for Sysadmins

Before install

• HDD / SSD / RAM storage

Page 7: MySQL & noSQL Conference: MySQL for Sysadmins

Before install

• Filesystems

EXT3

EXT4

XFS

• RAID

RAID 0

RAID 1

RAID 5

RAID 6

RAID 10

Page 8: MySQL & noSQL Conference: MySQL for Sysadmins

Benchmark EXT4 vs XFS

Page 9: MySQL & noSQL Conference: MySQL for Sysadmins

Benchmark EXT4 vs XFS

Page 10: MySQL & noSQL Conference: MySQL for Sysadmins

Benchmark EXT4 vs XFS

Page 11: MySQL & noSQL Conference: MySQL for Sysadmins

Benchmark EXT4 vs XFS

Page 12: MySQL & noSQL Conference: MySQL for Sysadmins

Benchmark EXT4 vs XFS

Page 13: MySQL & noSQL Conference: MySQL for Sysadmins

Before install

• Disk Partitions• /

• /tmp

• /home

• innodb data / logs

• binary logs / relay logs

• datadir

• tmpdir

Page 14: MySQL & noSQL Conference: MySQL for Sysadmins

Before install

• MySQL version / branch• MySQL

• MariaDB

• Percona

• Custom branches

» Twitter

» Facebook

» Drizzle

Page 15: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• Change MySQL defaults [ my.cnf ]

» bind-address

» skip-name-resolve

» Run: mysql_secure_install

» Check users privileges

Page 16: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• MyISAM performance

– Read» key_buffer

» sort_buffer

» read_buffer_size

Page 17: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• InnoDB performance

– Read» innodb_buffer_pool_size

» query_cache_size

» query_cache_type

– Write » innodb_flush_method

» innodb_flush_log_at_trx_commit

Reference: http://goo.gl/W6svD

Page 18: MySQL & noSQL Conference: MySQL for Sysadmins

InnoBD performance

Page 19: MySQL & noSQL Conference: MySQL for Sysadmins

InnoBD performance

Page 20: MySQL & noSQL Conference: MySQL for Sysadmins

InnoBD performance

Page 21: MySQL & noSQL Conference: MySQL for Sysadmins

InnoBD performance

Page 22: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• Checks your logs» general_log_file

» general_log

» log_slow_queries

» long_query_time

» log-queries-not-using-indexes

Page 23: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• Binary logs / Relay logs» server-id

» log_bin

» expire_logs_days

» max_binlog_size

» replicate_do_db

» relay_log

» relay_log_index

» relay-log-space-limit

» max-relay-log-size

Page 24: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• Separated partitions for logs and data» datadir

» innodb_file_per_table

» innodb_data_home_dir

» innodb_log_group_home_dir

» innodb_log_file_size

» innodb_log_files_in_group

Page 25: MySQL & noSQL Conference: MySQL for Sysadmins

After install

• Double check your DDL scripts

• Keep monitoring your DML scripts

• Secure your server with DCL scripts

Page 26: MySQL & noSQL Conference: MySQL for Sysadmins

Online processes

• ALTER TABLE without Downtime

• How long? Could we test it before?

• Backup» INTO OUTFILE / mysqldump –T

» Copy Binary logs

• Restore» LOAD DATA INFILE / mysqlimport

» mysqlbinlog | mysql

Page 27: MySQL & noSQL Conference: MySQL for Sysadmins

Dumping faster

time mysqlimport sakila /tmp/sakila-dump/*.txt

real 0m0.188s

user 0m0.004s

sys 0m0.004s

Page 28: MySQL & noSQL Conference: MySQL for Sysadmins

Dumping faster

time mysql < sakila-data.sql

real 0m1.196s

user 0m0.056s

sys 0m0.008s

Page 29: MySQL & noSQL Conference: MySQL for Sysadmins

Application Kit

• Percona Toolkit» pt-heartbeat

» pt-online-schema-change

» pt-slave-delay

• Scripts en GitHub?

• Benchmarks » sysbench

» scripts

Page 30: MySQL & noSQL Conference: MySQL for Sysadmins

COMMIT

• Pay attention before and after install your server

• Check your SQL code before you start hate MySQL

• Run backups and test it! Take time for that..

• Make your own scripts

• Keep using MySQL =)

Page 31: MySQL & noSQL Conference: MySQL for Sysadmins

COMMIT

• Read » Blog de percona performance blog (EN)

» http://www.ilsistemista.net/ (EN)

» Binlogic Blog

» Kibibyte.net Blog ( 09/2012 )

Page 32: MySQL & noSQL Conference: MySQL for Sysadmins

@jtomaszon

[email protected]

www.kibibyte.net

Questions?

Contact me