optimize mysql performance for developers
DESCRIPTION
Optimize MySQL performance for developersTRANSCRIPT
![Page 1: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/1.jpg)
Optimize MySQL For Developers
QCon Beijing 2011
YangHaichao
Senior MySQL DBA@SINA
http://weibo.com/jackbillow
![Page 2: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/2.jpg)
Agenda
• Architecture of Database-related
• Scaling your Database
• Schema Design
• Optimize Access
![Page 3: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/3.jpg)
Performance vs Architecture
![Page 4: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/4.jpg)
Datastore
• Relational Databases• MySQL
• Non Relational Databases• Memcached
• Redis
• MongoDB
• RD and NRD is Friends or Foes?
• MySQL + Memcached
• MySQL + Redis
![Page 5: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/5.jpg)
Caching
• Put a cache in front of your database• Distribute
• Write-through for scaling reads
• Write-back for scaling reads and writes
• Cache tier
![Page 6: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/6.jpg)
Principles
• Nothing’s perfect but some solutions are
good enough for a while
• Scalability involve partitioning, indexing and
replication
• All data for real-time queries MUST be in
memory. Disk is for writes only
![Page 7: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/7.jpg)
Scaling your database
![Page 8: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/8.jpg)
Replication
• Master - Slave• Only scaling reads
• Master - Master• Scaling reads and writes but many limits
![Page 9: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/9.jpg)
Functional Segmentation
Segment databases into functional areas
• User
• Feed
• Comment
• Attention
• Fans
• …
![Page 10: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/10.jpg)
Horizontal Split
• Hash
• Range
• Lookup table
• Middle layer
![Page 11: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/11.jpg)
Minimize Database
• No business logic
• No distributed transactions
• No joins and sorting
![Page 12: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/12.jpg)
Schema Design
![Page 13: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/13.jpg)
CAP & BASE
Consistency:ACID
Transactions
Availability
(Total
Redundancy)
Partition
Tolerance:
Infinite scaleout
Oracle
RAC
NO
GO
NoSQL
DB
![Page 14: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/14.jpg)
The Schema
• Best stage for optimize performance
• Improve performance is bigest
• Divide and conquer
• Normalize & de-normalize
![Page 15: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/15.jpg)
Data type
• Small is usually better
• Use INT UNSIGNED for IPv4 addresses
• Use TEXT or BLOB sparingly• Consider separate tables
![Page 16: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/16.jpg)
Index
• Over indexing can be an overhead
• On multiple column indexes the order fields
within the index definition is important
• Poor indexes are same as not having any
indexes
• Good selectivity on index fields
![Page 17: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/17.jpg)
Storage Engine
• Understanding benefits and drawbacks of
each storage engine
• Different storage engine has different index
capability
![Page 18: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/18.jpg)
Optimization Access
![Page 19: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/19.jpg)
Thinking in Access
• Any interaction with the database are the
high cost
• Decrease data access is better than SQL
tuning
![Page 20: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/20.jpg)
SQL is not C or C++
![Page 21: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/21.jpg)
Reduce Access to data
• Must specity column in select
• Only use index in query
• Assumsing success
![Page 22: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/22.jpg)
Reduce the Number of Interactions
• Pushing control structures into SQL
• Combining statements
• Fetching all you need at once
![Page 23: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/23.jpg)
Reduce the Number of Interactions
• INSERT ... ON DUPLICATE KEY UPDATE
• REPLACE
• INSERT IGNORE
![Page 24: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/24.jpg)
Reduce CPU computing
• Extensive use of prepared statements and
bind variables
• Column not calculate as far as possible
• Move cpu-intensive work to application
![Page 25: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/25.jpg)
Parallelism
• Reorganizing processing
• Isolating hot spots
• Shortening critical sections
• Dealing with multiple queues
![Page 26: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/26.jpg)
Last, but not least…
• Architecture and design is in the best stages
of improving performance
• Develop huge application you mush keep
scaling data in mind at first
• Perform SQL in very few data accesses is
increasingly important
• Performance tuning is an trade-off and
iterative process
![Page 27: Optimize MySQL performance for developers](https://reader034.vdocuments.us/reader034/viewer/2022042714/54b7965a4a795920598b4596/html5/thumbnails/27.jpg)
Thank you for coming
Q & A
@jackbillow