linuxperformancetuningandstabilizationtips-mysqlconf2010.pdf

(299 KB) Pobierz
Linux Performance Tuning and
Stabilization Tips
Yoshinori Matsunobu
Lead of MySQL Professional Services APAC
Sun Microsystems
Yoshinori.Matsunobu@sun.com
Copyright 2010 Sun Microsystems inc
The World’s Most Popular Open Source Database
1
Table of contents
• Memory and Swap space management
• Synchronous I/O, Filesystem, and I/O scheduler
• Useful commands and tools
– iostat, mpstat, oprofile, SystemTap, gdb
Copyright 2010 Sun Microsystems inc
The World’s Most Popular Open Source Database
2
Random Access Memory
• The most important H/W component for RDBMS
• RAM access speed is much faster than HDD/SSD
– RAM: -60ns
• 100,000 queries per second is not impossible
– HDD: -5ms
– SSD: 100-500us
• 16-64GB RAM is now pretty common
• *hot application data* should be cached in memory
• Minimizing hot application data size is important
– Use compact data types (SMALLINT instead of
VARCHAR/BIGINT, TIMESTAMP instead of DATETIME, etc)
– Do not create unnecessary indexes
– Delete records or move to archived tables, to keep hot tables
smaller
Copyright 2010 Sun Microsystems inc
The World’s Most Popular Open Source Database
3
Cache hot application data in memory
DBT-2 (W200)
Buffer pool 1G
Buffer pool 2G
Buffer pool 5G
Buffer pool 30G
(All data in cache)
•
•
•
•
Transactions per Minute %user
1125.44
1863.19
4385.18
36784.76
2%
3%
5.5%
36%
%iowait
30%
28%
33%
8%
DBT-2 benchmark (write intensive)
20-25GB hot data (200 warehouses, running 1 hour)
Nehalem 2.93GHz x 8 cores, MySQL 5.5.2, 4 RAID1+0 HDDs
RAM size affects everything. Not only for SELECT, but also for
INSERT/UPDATE/DELETE
– INSERT: Random reads/writes happen when inserting into
indexes in random order
– UPDATE/DELETE: Random reads/writes happen when modifying
records
Copyright 2010 Sun Microsystems inc
The World’s Most Popular Open Source Database
4
Buffered I/O
Use Direct I/O
Direct I/O
InnoDB Buffer Pool
InnoDB Buffer Pool
Filesystem Cache
RAM
RAM
InnoDB Data File
•
•
•
InnoDB Data File
Direct I/O is important to fully utilize Memory
innodb_flush_method=O_DIRECT
Alignment: File i/o unit must be a factor of 512 bytes
– Can’t use O_DIRECT for InnoDB Log File, Binary Log File, MyISAM,
PostgreSQL data files, etc
The World’s Most Popular Open Source Database
5
Copyright 2010 Sun Microsystems inc

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin