0 to 60 in 3 1
Download
1 / 21

0 to 60 in 3.1 - PowerPoint PPT Presentation


  • 163 Views
  • Uploaded on

0 to 60 in 3.1. Tyler Carlton Cory Sessions. <Insert funny joke here>. The Project. Medium sized demographics data mining project 1,700,000+ User base Hundreds of data points per user. “Legacy” System – Why Upgrade?. +. Main DB (External Users) Offline backup (Internal Users)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '0 to 60 in 3.1' - Anita


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
0 to 60 in 3 1 l.jpg
0 to 60 in 3.1

Tyler Carlton

Cory Sessions



The project l.jpg
The Project

  • Medium sized demographics data mining project

  • 1,700,000+ User base

  • Hundreds of data points per user


Legacy system why upgrade l.jpg
“Legacy” System – Why Upgrade?

+

  • Main DB (External Users)

  • Offline backup (Internal Users)

  • Weekly manual copy backups

  • Max of 3 simultaneous data pulls

  • 8hr+ data pull times for complex data pulls

  • Random index corruption


Slide5 l.jpg

Notes:

Smaller is Better

On average, CPU usage with MySQL was 20% lower than our old database solution.


Why we chose mysql cluster l.jpg
Why We Chose MySQL Cluster

  • Scalable

  • Distributed processing

  • 5 – 9’s Reliability

  • Instant data availability between internal & external users


What we built ndb data nodes l.jpg
What We Built – NDB Data Nodes

  • 8 Node NDB cluster

    • Dual Core 2 Quad 1.8 ghz

    • 16 Gig ram (Data memory)

    • 6x Raid 10 SAS 15k RPM drives


What we built api mgmt nodes l.jpg
What We Built – API & MGMT Nodes

  • 3 API nodes + 1 management node

    • Dual Core 2 Quad 1.8 ghz

    • 8 Gig ram

    • 300 gig 7200rpm (Raid 0)


Ndb issues with a large data set l.jpg
NDB Issues with a Large Data Set

  • NDB load times

    • Loading from backup: ~ 1 hour

    • Restarting NDB nodes: ~ 1 hour

      Note: Load times differ depending on your data size


Ndb issues with a large data set10 l.jpg
NDB Issues with a Large Data Set

  • Indexing Issues

    • Force index (NDB picks wrong)

    • Index creation/modification order matters (Seriously!)

  • Local Checkpoint Tuning

    • TimeBetweenLocalCheckpoints - 20 means 4MB (4 × 220) of write operations

    • NoOfFragmentLogFiles – No. of 4 x 16MB files

      • None deleted until 3 local checkpoints

    • On startup: Local checkpoint buffers would overrun

    • RTFM (two, maybe three times)


Ndb network issues l.jpg
NDB Network Issues

  • Network transport packet size

    • Buffer would fill and overrun

    • This caused nodes to miss their heartbeats and drop

  • This would happen when:

    • A backup was running

    • A local checkpoint was running at the same time

  • Solved by : Increasing network packet buffer


Issues in statements l.jpg
Issues - IN Statements

  • IN statements die with engine_condition_pushdown=ON with a set of apx. 10,000 or more. (caused with zip codes)

  • Really need engine_condition_pushdown=ON, but this broke it for us, so… we had to disable it.


Structuring apps redundency l.jpg
Structuring Apps: Redundency

  • Redundant power supply + dual power sources

  • Port trunking w/ redundant Gig-E switches

  • # NDB Replicas: 2 (2x4 setup) 64 gig max data size

  • MySQL (API Nodes ) Heads: Load balanced with automatic fail over


Structuring apps internal apps l.jpg
Structuring Apps: Internal Apps

  • Ultimate goal: Offload the data intensive processing to the MySQL nodes


The good stuff stats l.jpg
The Good Stuff: Stats!

Queries per Second (over 20 days)

  • Average 1100-1500 Queries / Sec during our peak times

  • Average 250 Queries / Sec


Website traffic l.jpg
Website Traffic

Stats for March 2008


Net usage ndb node l.jpg
Net Usage: NDB Node

  • All NDB data nodes have nearly identical network bandwidth usage

MySQL ( API ) Nodes use about 9 MBs max under our current structure

Totaling 75 MBs during peak(600 Mbs)


Monitoring maintenance l.jpg
Monitoring & Maintenance

  • SNMP Monitoring: CPU, Network, Memory, Load, Disk

  • Cron Scripts:

    • Node status & Node down notification

    • Backups

    • Database maintenance routines

  • MySQL Clustering book provided the base the scripts


Net usage next steps l.jpg

Dolphin NIC Testing

4 node test cluster

4 x overall performance

Brand new patch to handle automatic Ethernet failover / Dolphin Fail Over

( beta as of March 28 )

Net Usage: Next steps…



Contact information l.jpg
Contact Information

  • Tyler Carlton

    www.qdial.com

    tcarlton@gmail.com

  • Cory Sessions

    CorySessions.com

    OrangeSoda.com


ad