JVM and MySQL Tuning

JVM and MySQL Tuning

Transcript

1. JVM & MySQL Tuning March 2013
2. About me • Name: Liu Xuan • Weibo: @Foredoomed • Twitter: @comomt
3. We are going to talk about… • JVM Performance Tuning • MySQL Perfromance Tuning
4. JVM Performance Tuning • Memory tuning • GC algorithm tuning …
5. Latency is our biggest enemy
6. Latency contributors • Garbage collector (main) • Locking • I/O
8. How does young generation work • All new allocation happens in eden • When eden fills up, stop-the-world and copy-collection into the survivor space • After several collections, survivors get tenured into old generation
9. • Serial GC (AKA Mark-Sweep-Compact) (-XX:+UseSerialGC) • Parallel GC (-XX:+UseParallelGC) • Parallel Old GC (-XX:+UseParallelOldGC) • Concurrent Mark & Sweep GC (AKA CMS) (-XX:+UseConcMarkSweepGC) • Garbage First GC (AKA G1, Introduced from JDK 7) Garbage Collection Algorithms
10. Young generation tuning • Run with –verbosegc • Enable -XX:+PrintGCDetails, -XX:+PrintHeapAtGC, - XX:+PrintTenuringDistribution • Observe numbers in <Full GC> messages [Full GC $before->$after($total), $time secs]
11. -XX:+PrintHeapAtGC Heap after GC invocations=7000 (full 87): par new generation total 4608000K, used 398455K eden space 4096000K, 0% used from space 512000K, 77% used to space 512000K, 0% used concurrent mark-sweep generation total 3072000K, used 1565157K concurrent-mark-sweep perm gen total 53256K, used 31889K }
12. -XX:+PrintTenuringDistribution Desired survivor size 262144000 bytes, new threshold 4 (max 4) - age 1: 137474336 bytes, 137474336 total - age 2: 37725496 bytes, 175199832 total - age 3: 23551752 bytes, 198751584 total - age 4: 14772272 bytes, 213523856 total
13. Tuning the old generation Goals: • Keep the fragmentation low • Avoid full GC stops
14. Tuning the old generation • Maximize the number of objects reclaimed in the young generation • Set –Xms==–Xmx (Heap growth or shrinking requires a Full GC) • Give young generation and survivor space large enough memory
15. Tuning the old generation • -XX:NewSize : initial young generation size • -XX:MaxNewSize: max young generation size • -XX:NewRatio : young generation to old generation ratio • -XX:TargetSurvivorRatio: how much the survivor space should be filled • -XX:SurvivorRatio: eden to survivor size ratio …
17. Turning MySQL
18. MEANs • Choose a most suitable engine • Tuning SQL statements • Configuration …
19. DB engines
20. Start turning SQL statements with explain mysql> explian select * from table_name where id = 1234 +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
21. 1. select_type 1. SIMPLE 2. PRIMARY 3. UNION 4. DEPENDENT UNION 5. UNION RESULT 6. SUBQUERY 7. DEPENDENT SUBQUERY 8. DERIVED
22. SIMPLE mysql> explain select * from t3 where id=1234; +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
23. PRIMARY mysql> explain select * from (select * from t3 where id=1234) a ; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
24. UNION mysql> explain select * from t3 where id=1234 union all select * from t3 ; +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | | | 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | | |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
25. DEPENDENT UNION mysql> explain select * from t3 where id in (select id from t3 where id=1234 union all select id from t3) ; +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index | | 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index | |NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
26. UNION RESULT mysql> explain select * from t3 where id=1234 union all select * from t3 ; +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | | | 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | | |NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
27. SUBQUERY mysql> explain select * from t3 where id = (select id from t3 where id=1234 ) ; +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | | | 2 | SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
28. DEPENDENT SUBQUERY mysql> explain select id from t3 where id in (select id from t3 where id=1234 ) ; +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+ | 1 | PRIMARY | t3 | index | NULL | PRIMARY | 4 | NULL | 1000 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index | +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
29. DERIVED mysql> explain select * from (select * from t3 where id=1234) a ; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
30. 2. table Which table the query is selecting from 3. type(ordered from the best to the worst) 1. system 2. const 3. eq_ref 4. ref 5. ref_or_null 6. index_merge 7. unique_subquery 8. index_subquery 9. range 10. index 11. ALL
31. 4. possible_keys Possible keys(index) can be used to find the row 5. key Key(index) is going to be used 6. key_len The length of key(index) 7. ref What column is going to be used with key
32. 8. rows Rows that must be scanned 9. extra 1. Distinct 2. Not exists 3. Range checked for each 4. Using filesort 5. Using index 6. Using temporary 7. Using where
34. Thank you