- An introduction to Benchmarking
- data structures
- query optimisation and query cache
The Good Scientists Guide to Benchmarking
- The scientific method suggests changing only one variable at a time
- The scientific method suggests repetition, more than once to verify results. If results vary greatly, think about taking averages
- Repeat, rinse , repeat , rinse!
The Good Scientists Guide to Benchmarking II
- Isolate your environment
- Use a different MySQL instance
- Savle all configurations!
Benchmarking Tools II
Slow Query Log
- Then, use mysqldumpslow
- In 5.1, you can log these details ...
- Provides the execution plan chosen by the MySQL optimiser for a...
Scans and seeks
- A seek, jumps into a random place to fetch needed data.
- A scan will jump to the start of the data...
When do you get a full table scan?
- No WHERE conditions
- No index on any field in WHERE condition
- When your range returns a large number of rows, i.e. too many records in WHERE condition
- Pre-MySQL 5, using OR in a WHERE clause
- SELECT * FROM
- Don't use them; replace with a JOIN
- unique_subquery: results are known to be distinct
- index_subquery: otherwise
- Co-related subqueries are worse
Good Schema Practice
- Use small data types
- Is a BIGINT really required?
- Small data types allow more index and data recoreds to fit into a single block of memory
- Normalise first, de-normalise later
- Generally, 3NF works pretty well
Storing IP addresses
- IP addresses always become an INT UNSIGNED
- Each subnet corresponds to an 8-byte division of the underlying INT UNSIGNED
- From string to int? Use INET_ATON()
- From into to ...
- Understand your applications read/write ratio for most effective use
- Compromise between CPU usage and read performance
- Remember that the bigger your query cache, you may not see better performance, even if your application is read heavy
Query Cache Invalidation
- Coarse invalidation designed to prevent CPU overuse
- Thusm any modification to any table referenced in the SELECT will invali...
Choosing a Storage Engine
- MySQLs strong point: many engines MySQLはたくさんエンジンがあるよ
- Use InnoDB for most operations (esp. OLTP), except:
- big, read only tables
- high volume streaming tables(logging)
- specialised needs (have special engines)
- Tune InnoDB wisely
Quick InnoDB Tuning Tips
Real World MySQL Use (RWMU)
- Run many servers
- Your serious application cannot run on "the server"
- "Shared nothing" architecture
- make no single point of contention in the system
- Scales well, just by adding cheap nodes
- If it works for Google, it will work for you!
RWMU: State and Session Information
- Dont keep state within the application server
- Key to being stateless: session data
- Cookies are best validated by checksums and timestamps(encrypting is a waste of CPU cycles)
RWMU : Data Partitioning
- replication is great for read heavy applications
- Write intensive applications should look at partitioning
- Partition with a global master server in mind
- Give out global PKs and cache heavily (memcached)
- Consider the notion of summary databases
- Large binary object storage is interesting
- Image data is best kept in the filesystem, just use metadata in DB to reference server and path to filename
- Try the Amazon S3 storage engine?