Introducing
Your new presentation assistant.
Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.
Trending searches
Practical approach to database applications optimization
Application caching
Before you begin
Cache pitfalls
Incremental caches
- Cache invalidation
- Cache collision
- Cache stampede
- Protect against burst of visits
- Short TTLs
- Cache only IDs for complicated queries.
- Cache HTML for a short time.
- PHP 5.5+
- PHP-FPM + Nginx / Apache workers
- Opcache
- MySQL 5.7
- Front-end analysis
- Configure Doctrine DBAL/Cache
http://blog.lavoie.sl/2014/12/preventing-cache-stampede-using-doctrine-cache.html
Doctrine DBAL/Cache
A note on ORMs
Caching tools
Network waterfall
Be sure the problem is the backend
Even if you use legacy mysql API, you can benefit from Doctrine.
DBAL
Cache
Doctrine Cache Detector
Automatically select the best available cache
https://github.com/lavoiesl/doctrine-cache-detector
- Easy to use
- Manages schema/migrations
- Clean code
- Listeners for automatic caches and logging
- Slow for complex queries
- Run complex queries outside the ORM.
- QueryBuilder
- Logger
Consistent API for a variety of cache backends
- Redis
- APC
- Filesystem
- Memcache
- etc.
Doctrine Cache Provider
Allows to use the database as a key/value store
https://github.com/lavoiesl/doctrine-cache-provider
Redis
Very fast shared key/store
http://redis.io/
Practical approach to database applications optimization
Sébastien Lavoie
WeMakeCustom
@lavoiesl
Comments: http://joind.in/12997
Slides: http://prezi.com/zoeyctbskei5/
Gather some data
Optimize your queries
Don’t just go in blindly with micro optimizations
QueryBuilder for varying joins
Split your queries
Using XHProf and XHProf.io
Joins are very costly, do not join if you don’t need.
Add your join tables and the WHEREs at the same time.
Very often, some information is not directly related to your query and should already be known to your application.
Log slow MySQL queries
Gather precise data about which function uses the most time/cpu/memory
Using Doctrine DBAL logger
Using MySQL slow query log
Wrap all your manual calls to mysqli
(or equivalent) in a function and dump the slow ones.
Use around 100ms so get the most data
Using pt-query-digest from Percona Toolkit
Using NewRelic
MySQL EXPLAIN
Indices are expensive to manage,
do not throw an index on everything, but:
- SELECTs using only columns from an index bypass table lookups.
- JOINs and ORDER BY using indices are extremely faster.
- Index for multi-column ORDER BY / WHERE.
See this PHPQuebec talk from Jehad Keriaki:
https://joind.in/11328