Peter Zaitsev, CEO
Percona University Minsk, Belarus
Oct 21, 2013
Running MySQL on Linux
www.percona.com 2
About the Presentation
• Cover what you need to run MySQL on
Linux Successfully
• Distribution
• Hardware
• OS Configuration
• MySQL Installation
• MySQL Configuration
www.percona.com 3
Why Linux ?
• Almost 90% of MySQL Users use Linux for
Production
• And almost 80% for Development
• http://bit.ly/QSdooC
• Most high profile MySQL installations are
done on Linux
• Great Default Choice
• Unless you have some very good reason
not to – chose Linux for MySQL
www.percona.com 4
What Distribution are you Using ?
www.percona.com 5
Choices
• “Server Grade Distribution”
• Long Support; Stability
• Recent enough
• Especially with Newer Hardware • Good TRIM support for SSDs only in
recent Kernels
• Most Popular Choices
• RHEL/CentOS/Oracle
• Debian, Ubuntu
www.percona.com 6
Hardware
• Hardware can go a long way !
• Over 200K simple queries/sec on modern
hardware.
• With 100 queries/page = 120K page
views/minute
• Some 80M page views/day
(considering daily spike)
www.percona.com 7
Hardware
• CPU
• Go for fast cores. All cores are rarely
used these days
• Cache and fast memory bus is important
• Memory
• Often most important for performance
• Your working set must fit in memory well.
• Less memory = more pressure on IO
www.percona.com 8
Storage
• Directly attached Flash is best • PCI-E cards (FusionIO, Virident) are
fastest • SATA SSD are very cost effective • Mind vendors and Monitor for uniform
performance • Wear not Performance Might be a Limit
• http://bit.ly/1f2u9I9 • Ensure you have RAID w BBU on
conventional drives • RAID10 best for heavy load
www.percona.com 9
Know your Goals
• Are you bound by Reads ? Writes ?
Random ? Sequential ?
• Do you need sustained writes or handle
spikes ?
• What Concurrency Do you Operate ?
• Many devices reach peak IOPs at
Unreachable Concurrency
• Think about Latency/Response Time
• SAN/NAS frequent cause of surprise
www.percona.com 10
Performance vs Memory
www.percona.com 11
Compression of Performance Gains
www.percona.com 12
Alignment
• Partition Alignment
• File System Alignment options
• Can cause significant performance
difference
• http://bit.ly/ilfNkT
• http://bit.ly/ks9trM
1
2
www.percona.com 13
Alignment Benchmarks
1
3
www.percona.com 14
Network
• Latency is king
• Minimize number of hops between
Database and Web Server
• Ensure running 1Gbit link speed at least
• 10Gb is gaining popularity
• Monitor for packet loss and latency
• Network problems are often blamed on
database
www.percona.com 15
Network Tuning
• Might be needed sometimes • echo 8192 > /proc/sys/net/ipv4/tcp_max_syn_backlog
• Mysql: back_log=1000
• net.ipv4.ip_local_port_range="1024 64000“
• net.core.somaxconn = 1024
• net.core.rmem_max = 16777216
• net.core.wmem_max = 16777216
• net.ipv4.tcp_rmem = 4096 87380 16777216
• net.ipv4.tcp_wmem = 4096 65536 16777216
• net.ipv4.max_tw_buckets=360000
• net.core.netdev_max_backlog = 2500
• ifconfig eth0 txqueuelen 1000
• Persistent connections with MySQL
• Thread_pool; Proxy
www.percona.com 16
Virtualization and Cloud
• Virtualization has cost
• Cloud rarely provides highest
performance hardware
• Getting better
• There is a lot of MySQL ran in the cloud
and Virtualized Environments
www.percona.com 17
Linux Configuration
www.percona.com 18
In General…
• For Most workloads Linux Runs MySQL
Surprisingly Well with no additional tuning
www.percona.com 19
General Configuration
• Disable SELinux
• Unless you really need it on your
system
• In /etc/selinux/config • SELINUX=disabled
• Same with AppArmor
• Reduce Tendency to swap
• vm.swappiness=0
• vm.dirty_ratio=5
www.percona.com 20
General Configuration
• Reduce the File Cache
• vm.dirty_ratio=5
• NUMA can cause problems
• http://bit.ly/I0GSt3
• Interleaving with numactl • Out there in Percona Server startup
scripts
www.percona.com 21
Storage Configuration
• Separate OS and MySQL Partition • Configure IO Scheduler
• CFQ can cause problems • Deadline or Noop are often better choice
• elevator=deadline on kernel boot
• Queue length (especially MyISAM) • # echo 100000 > /sys/block/sdX/queue/nr_requests
• Consider using LVM • Virtually no overhead when not in
snapshot mode • Reserve some space for snapshots
www.percona.com 22
FILESYSTEMS
Which one do you use ?
www.percona.com 23
Filesystems
• XFS – “Old” High Performance Favorite
• EXT4 – Works very good for some
workloads
• Avoid:
• Ext2
• Ext3
• ReiserFS
• ZFS (may be good for development)
• Btrfs
www.percona.com 24
FS Tuning
• relatime (noatime)
• ext3: tune2fs –O dir_index -c –l –i 0 –e
remount-ro
• xfs: nobarrier
• Assuming RAID w BBU or Flash
www.percona.com 25
MYSQL VARIANTS
What are you Running ?
www.percona.com 26
Main Choices
• MySQL
• Percona Server
• MariaDB
www.percona.com 27
MySQL Versions
• Newer versions scale better
• MySQL 5.6 is most scalable
• Percona Server brings even more
improvements • Now GA!
• Use 5.6 for New Development
• Upgrade to MySQL 5.6 if you need it now
www.percona.com 28
How to Install
• Use Repositories: Best
• http://www.percona.com/doc/percona-
server/5.5/installation.html
• Download Packages (RPM, DEB etc)
• Use Tar.gz package
• Good for testing with MySQL Sandbox
• Build our own
• Do you really have good reason to do it ?
• Many problems are caused by bad builds
www.percona.com 29
MySQL Configuration
• You need to tune MySQL configuration
• Even newer MySQL 5.6 defaults are
unlikely to be optimal
• Check out presentation which goes in
depth into configuration tuning
• http://bit.ly/1fuP0SZ
• Typically getting 5 variables right
responsible for 90% performance gain
www.percona.com 30
Top Variables
• Innodb_buffer_pool_size
• Set 80% of memory sometimes more
• Innodb_flush_method=O_DIRECT
• Innodb_log_file_size
• Set 256MB or more
• Larger logs = longer recovery time
• Innodb_flush_log_at_trx_commit=?
www.percona.com 31
Advanced ideas
• Running multiple MySQL instances ? • taskset -pc 0,12,2,14 `cat /var/lib/mysql/mysqld1.pid`
• Memory Allocation Hostspot ?
• Jemalloc
• Tcmalloc –old favorite
• LVM Snapshot before maintenance or
upgrade
• Roll back easily if things go wrong
www.percona.com 32
Beware of the “Scripts”
• Start/Stop Scripts can be nasty
• Timeouts might be not enough for safe
database shutdown
• The automated upgrade/check
• DPKG post Install Scripts
• Can surprisingly restart your MySQL
Server
www.percona.com 33
Automation
• Manual approach does not scale
• And is Error Prone
• Automate installation, upgrades,
configuration
• Puppet, Chef, Ansible and others
• Keep Configuration under version control
• At very least leave comments on what
you chance and why
www.percona.com 34
Monitoring and Trending
• Many tools to choose from!
• Just make sure you’re doing it
• Nagios and CACTI
• Check out PMP • http://www.percona.com/software/percon
a-monitoring-plugins
• Graphite is a great tool for advanced
analyses
• http://graphite.wikidot.com/
www.percona.com 35
Linux OOM Killer
• What are your priorities ? • SSH • MySQL Server • Various jobs
• Backup • Percona Toolkit • Background Batch jobs
• Configuring • echo -17 > /proc/2592/oom_adj
• do not kill ever • echo 10 > /proc/2592/oom_adj
• More like to be killed
www.percona.com 36
Tools you need to Know
• vmstat/iostat/top
• Strace
• Oprofile
• GDB
• Percona Toolkit