1 / 18

Performance-Enhanced MySQL for Data Leverage

Performance-Enhanced MySQL for Data Leverage. Shivinder Singh Erick Franco. Get to Know Verizon Wireless. 74,000. Employees Nationwide at end of 1Q 2013. 98.9 Million. Retail Customers at end of 1Q 2013. Nation’s largest 4G LTE network. $75.9 Billion. Annual Revenue (2012).

infinity
Download Presentation

Performance-Enhanced MySQL for Data Leverage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance-Enhanced MySQL for Data Leverage Shivinder Singh Erick Franco

  2. Get to Know Verizon Wireless 74,000 Employees Nationwide at end of 1Q 2013 98.9 Million Retail Customers at end of 1Q 2013 Nation’s largest 4G LTE network $75.9 Billion Annual Revenue (2012) 2012 Awards & Honors

  3. Micro Sites

  4. Micro Sites

  5. Web Portal (VZWeb)

  6. Front Page

  7. Statistics ~500,000 Front page loads/day ~90 queries per front page load 45 MILLION queries per day For the front page only

  8. Business Drivers • Highly robust, elastic environment • Platform for social media promotion • User specific content • Centralized authentication • Ability to host huge number of articles, blogs, videos etc • Sub second response times • 99.9999% availability

  9. vIaaS Standards Operational Framework Database Middleware & Network • Multi Site • For all tier 1 apps • Complete DB integration • Ability to expand horizontally & vertically • DR testing with each deploy • Multiple DB flavours : Oracle, Sun DS, MySQL • Business centric app design • Multi Site • For all tier 1 apps • Complete DB integration • Ability to expand horizontally & vertically • DR testing with each deploy • Multiple DB flavours : Oracle, Sun DS, MySQL • Business centric app design • Five 9’s SLA goals • Automate Failover Testing • Minute growth trending • Reclaim Storage • Query Optimization • Focus on lowering or keeping CPU flat with growth • Health Checks • Active Sessions, CPU Usage • Five 9’s SLA goals • Automate Failover Testing • Minute growth trending • Reclaim Storage • Query Optimization • Focus on lowering or keeping CPU flat with growth • Health Checks • Active Sessions, CPU Usage • Failover Testing • Failover testing in case of database failure • Performed with each deploy • Rolling deploys: Zero Downtime • Datapower consolidation • Code Base Review • In House Benchmark Factory • Failover Testing • Failover testing in case of database failure • Performed with each deploy • Rolling deploys: Zero Downtime • Datapower consolidation • Code Base Review • In House Benchmark Factory

  10. VzWeb Architecture

  11. Research & Findings • Intensive OLTP transactions • Vendor Driven Code Base • Custom static content for entire user base • High Volume trending topics – Unpredictable customer behavior • Total Overhead = N/W + Authentication + Code

  12. Multi Step Approach • Identify erratic code • Identify technical + Business KPI’s • Build KPI centric operational framework • Benchmark MySQL versions ( 20% gain, still not sub sec) • Identify Constants ( N/W + Authentication) • Modify Variables ( Code Base and query Response Time) • Test multiple scenarios to change variables

  13. Plan Of Action & Implementation • Tune queries • High response time queries vs high volume queries • Identify possible queries for Memcache • Upgrade Drupal codebase from Drupal 6 to Drupal 7 • Potential Challenges • Complete Code revamp (Unpredictable in prod) • Thorough testing • Blending of multiple teams (DBA, Dev, N/w, h/w etc) • 41 Load Tests prior to sharing results with management • Over 100 Mock installs in Dev prior to Prod cutover • 48 hr prod cutover change control • Zero downtime • Deployment in two data centers

  14. Results & Benefits • Achieved sub second response times • Rows scanned over an hour decreased from 1 Billion to 155 Million • 85% efficiency improvement • Rows sent/rows examined % has decreased from 10% to 2.5% • 75% efficiency improvement • Average response times improved by 65% • CPU usage on the DB servers decreased to 97% idle in peak volumes from 80% idle • Helping Green Initiative

  15. Pre Upgrade Query Stats • Pre Upgrade • Post Upgrade

  16. Response Times per Min Overview: Performance & Business Benefits • Performance Benefits • No spikes observed post MSA implementation and performance fixes • Major performance improvements in most called queries • 1489% improvement in user calls to the DB • Front page response improvement even after adding additional functionalities • Ability to perform read-write transactions on both data centers • Business Milestones • Achieved five 9’s SLA • Website performed exceptionally well during iPhone launch • Live webcasts and blogging during iPhone launch • Live webcast during Iphone announcement to the press • Live webcast from CES

  17. Lessons Learned • Complete knowledge & blending is key to success • Like thought process achieve success technical and business • Through planning and focus on failure critical to success • Prod is never same as load test environment • Tailor technology to fit business needs and not vice versa

  18. Thank You

More Related