Tweet
Tweet

Slide 1

Slide 1 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Common MySQL Scalability Mistakes Ronald Bradford UKOUG Birmingham, UK 2010.11 www.RonaldBradford.com Ronald Bradford http://ronaldbradford.com @RonaldBradford #MySQL #UKOUG

Slide 2

Slide 2 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 2010 - Oracle ACE Director (first in MySQL) 2009 - MySQL community member of the year Co Author of Expert PHP & MySQL Top individual blog contributor to Planet MySQL MySQL Inc (2006-2008), Oracle Corporation (96-99) Provide independent consulting Available for work now AUTHOR

Slide 3

Slide 3 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 9

Slide 4

Slide 4 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 My website seems to freeze or responds randomly? PROBLEM

Slide 5

Slide 5 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 The default MyISAM storage engine uses exclusive table locks for DML. CAUSE

Slide 6

Slide 6 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Optimize blocking query performance Use a transactional engine with MVCC and row based locking to address the LOCKING issue SOLUTION

Slide 7

Slide 7 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 End user report that selects all customer, order, order lines and order history data and performs poor joins. This takes shared read locks blocking future write locks then future reads. EXAMPLE

Slide 8

Slide 8 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 MySQL is unique in that it offers different mechanisms for storing and retrieving data, each with strengths and weaknesses. The DEFAULT is not always the best. WHY

Slide 9

Slide 9 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 MySQL PROCESSLIST Blocked have State = Locked Blocker - Same table, larger Time HOW mysql> SHOW PROCESSLIST; +----+------+-----------+-------+---------+------+------------+--------------- | Id | User | Host | db | Command | Time | State | Info +----+------+-----------+-------+---------+------+------------+--------------- | 13 | app1 | localhost | odtug | Query | 144 | User sleep | UPDATE emp ... | 14 | app1 | localhost | odtug | Query | 116 | Locked | select c from emp | 15 | app1 | localhost | odtug | Query | 89 | Locked | select c from emp

Slide 10

Slide 10 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Optimize Blocker Indexes Limit query Summary table Change storage engine HOW

Slide 11

Slide 11 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 8

Slide 12

Slide 12 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Why is my database so large? PROBLEM

Slide 13

Slide 13 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Don’t store large static objects in the database SOLUTION

Slide 14

Slide 14 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 80% of data is email content/ attachments 60% of data is PDF documents 30% of data is uncompressed large XML objects EXAMPLE

Slide 15

Slide 15 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Maximize memory usage for important data Reduce database recovery time WHY

Slide 16

Slide 16 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Compress large text data 90% saving on XML data Store static data in files Avoids DB handling overhead HOW

Slide 17

Slide 17 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 HOW Table Size per schema # Schema Table Usage SELECT table_schema,table_name,engine,row_format, table_rows, avg_row_length, (data_length+index_length)/1024/1024 as total_mb, (data_length)/1024/1024 as data_mb, (index_length)/1024/1024 as index_mb, CURDATE() AS today FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY 7 DESC; http://ronaldbradford.com/mysql-dba/

Slide 18

Slide 18 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 7

Slide 19

Slide 19 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 I can't access my website? PROBLEM

Slide 20

Slide 20 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 TRUE STORY

Slide 21

Slide 21 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Question: TRUE STORY

Slide 22

Slide 22 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Question: How do you know when your server is down or not accessible? TRUE STORY

Slide 23

Slide 23 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Question: How do you know when your server is down or not accessible? Answer: TRUE STORY

Slide 24

Slide 24 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Question: How do you know when your server is down or not accessible? Answer: The users will let us know. TRUE STORY

Slide 25

Slide 25 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 SOLUTION Integrated monitoring including graphical interface, real time analysis and notification

Slide 26

Slide 26 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Monitoring/Alerting Graphical Historical Necessary Generally missing/incomplete Useless for real-time analysis HOW

Slide 27

Slide 27 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Dashboard The state of NOW Sampling at 1s/3s/5s e.g. 0.1% of throughput HOW

Slide 28

Slide 28 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Instrumentation Important to business viability e.g. orders per minute page load time Seamless implementation i.e. no code changes to view real-time extensible HOW

Slide 29

Slide 29 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 6

Slide 30

Slide 30 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 My replication slave can't keep up? PROBLEM

Slide 31

Slide 31 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Know the weakest link(s) of MySQL replication and don't exceed that, or cheat. SOLUTION

Slide 32

Slide 32 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 If replication can't catchup, slaves are useless. Backup & recovery may also suffer. WHY

Slide 33

Slide 33 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Master DML Statement Write Data/Redo Log Write Binary Log Return OK to client HOW

Slide 34

Slide 34 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Slave Detect master log change Retrieve binary log entry Write relay log (IO_THREAD) Read relay log Apply DML (SQL_THREAD) Write Data/redo log HOW

Slide 35

Slide 35 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Replication workarounds Restrict queries executed --ignore Different storage engines Different index structures HOW

Slide 36

Slide 36 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Advanced workarounds RAID 0 (large number of slaves) Pre fetch thread EXPERT TIP

Slide 37

Slide 37 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 5

Slide 38

Slide 38 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 My server has crashed with a hard drive failure PROBLEM

Slide 39

Slide 39 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Question: Have you ever performed a database recovery? Answer: No, why? TRUE STORY

Slide 40

Slide 40 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Consultant: Do you know that your daily backups only recover the data up to that time, e.g. 1am. You know you have lost all your sales and changes since then. Customer: No, I didn’t know that. TRUE STORY

Slide 41

Slide 41 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Have a DR Plan Documented Tested Timed Verified - End to End SOLUTION

Slide 42

Slide 42 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Do you pass the MySQL backup/ recovery quiz? http://rb42.com/mysql-backup-quiz HOW

Slide 43

Slide 43 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 1. Do you have MySQL backups in place? 2. Do you backup ALL your MySQL data? 3. Do you have consistent MySQL backups? 4. Do you have backups that include both static snapshot and point in time transactions? 5. Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place? 6. Do you perform a test recovery of your static backup? 7. Do you perform a test recovery to point in time? 8. Do you time your backup and recovery process and review over time? 9. Do you have off-site copies of your backups? 10. Do you backup your primary binary logs? QUIZ http://rb42.com/mysql-backup-quiz

Slide 44

Slide 44 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 4

Slide 45

Slide 45 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Why is my database executing 1,200 qps for 50 users? PROBLEM

Slide 46

Slide 46 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Determine what queries are running and why they are running? SOLUTION

Slide 47

Slide 47 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Excessive SQL statements Duplicate Redundant Cachable Row at a time (RAT) CAUSE

Slide 48

Slide 48 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Reducing SQL load both improves performance now and provides greater capacity as you scale WHY

Slide 49

Slide 49 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 EXAMPLE http://ronaldbradford.com/blog/optimizing-sql-performance-the-art-of-elimination-2010-07-08/ SELECT * FROM activities_theme WHERE theme_parent_id=0 SELECT * FROM activities_theme WHERE theme_parent_id=1 SELECT * FROM activities_theme WHERE theme_parent_id=2 SELECT * FROM activities_theme WHERE theme_parent_id=11 SELECT * FROM activities_theme WHERE theme_parent_id=16 SELECT * FROM activities_theme WHERE theme_parent_id in (0,1,2,11,16) CAT RAT

Slide 50

Slide 50 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 EXAMPLE http://ronaldbradford.com/blog/optimizing-sql-performance-the-art-of-elimination-2010-07-08/ 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` WHERE (ArtistID = 196 ) 5 Query SELECT * FROM `artist` WHERE (ArtistID = 2188 ) 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` Duplicate Unnecessary Only 2 queries necessary or 1 CAT

Slide 51

Slide 51 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 EXAMPLE http://ronaldbradford.com/blog/optimizing-sql-performance-the-art-of-elimination-2010-07-08/ SELECT pages_id, pages_livestats_code, pages_title, pages_parent, pages_exhibid, pages_theme, pages_accession_num FROM pages WHERE pages_id = 0 5 minutes 6000 executions 0 is out of bounds Unnecessary

Slide 52

Slide 52 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Capture & Analyze DML is easy SELECT is harder HOW http://www.slideshare.net/ronaldbradford/capturing-analyzing-and-optimizing-mysql

Slide 53

Slide 53 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 MySQL Binary Log (Archive Redo) mysqlbinlog mk-query-digest One Liner HOW http://ronaldbradford.com/blog/mysql-dml-stats-per-table-2009-09-09/

Slide 54

Slide 54 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 HOW 55463 update sessions 25574 insert into sessions 12820 update items 11636 insert into item_categories 7532 update users 5168 delete from item_categories 4076 update extended_item_infos 3701 insert into sphinx_new_items 3701 insert into mini_items 2190 update sweet_bars 1922 update chat_users 1662 update item_shipping_infos 1265 update search_terms 1260 insert into images 931 delete from item_shipping_infos 825 update booths 713 update booth_stats 574 update topics 540 update offers 81k of 141k Top 2 queries = 57%

Slide 55

Slide 55 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Process List General Log tcpdump Application HOW

Slide 56

Slide 56 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Capturing, Analyzing and Optimizing your SQL http://www.slideshare.net/ronaldbradford/capturing-analyzing-and- optimizing-mysql HOW

Slide 57

Slide 57 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 /* Comment your queries */ The more products you have, the more developers you have, the more time you spend in code identification before you can even determine a resolution EXPERT TIP

Slide 58

Slide 58 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 3

Slide 59

Slide 59 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 The database is slow. My webpage takes five seconds to load. PROBLEM

Slide 60

Slide 60 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Evaluate the time taken in the database and all stages in between SOLUTION

Slide 61

Slide 61 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Client example showed a webpage taking 5 seconds to load. The html component was taking only 400 ms. Any MySQL performance improvement will only tune 8% of the total time. EXAMPLE

Slide 62

Slide 62 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Performance is important to end user Performance is perception WHY

Slide 63

Slide 63 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Firebug - http://getfirebug.com/ Httpwatch - http://httpwatch.com/ Page speed - http://code.google.com/speed/page-speed/ YSlow - http://developer.yahoo.com/yslow/ wget/curl Application code instrumentation HOW

Slide 64

Slide 64 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 http://www.stevesouders.com/ http://developer.yahoo.com/ performance/rules.html EXPERT TIP

Slide 65

Slide 65 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 2

Slide 66

Slide 66 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 I want to add new H/W. How do I change my application to support this? PROBLEM

Slide 67

Slide 67 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Develop a seamless integration that requires no code changes, no downtime and very little additional physical resources. SOLUTION

Slide 68

Slide 68 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Integrated monitoring and instrumentation Deployed from Day 1 HOW

Slide 69

Slide 69 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Seamless automated server deployment Version Control Build & Release Runtime config management Automated discovery HOW

Slide 70

Slide 70 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 API One code path for business functionality Implied business documentation Enforced data exchange standard Testability HOW

Slide 71

Slide 71 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Different levels of data availability Read & Write Read Only No Access Cached HOW

Slide 72

Slide 72 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Different principles for scalability Read Scalability Write Scalability Caching HOW

Slide 73

Slide 73 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Successful MySQL Scalability 1. Integrated monitoring & instrumentation 2. Seamless automated server deployment 3. Disaster is inevitable 4. Application Programming Interface 5. Support different levels of data availability 6. Support different scalability principles REFERENCE http://ronaldbradford.com/blog/successful-mysql-scalability-presentation-2010-09-17/

Slide 74

Slide 74 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 1

Slide 75

Slide 75 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 My website is slow? PROBLEM

Slide 76

Slide 76 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Seek professional advice. Hire for example Ronald Bradford. 20+ years of system architecture, database design and performance tuning. Employment as Consultant for Oracle Corporation (96-99) Employment as Senior Consultant for MySQL Inc (06-08) SOLUTION

Slide 77

Slide 77 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 R

Slide 78

Slide 78 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 Monitoring. Before, during and after NOW. You may not be able to predict the future but you can preempt the future. Choose the best product and features for you needs. The best SQL statement is the one you never have to execute. RECAP

Slide 79

Slide 79 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 3 levels of real time data access. Read/Write, Read and no access 3 aspects of scalability. Read, Write and Caching Operate below 90% capacity. That 10% is your insurance. RECAP

Slide 80

Slide 80 text

RonaldBradford.com - Common MySQL Scalability Mistakes - 2010.11 http://ronaldbradford.com [email protected] CONTACT