You are here: Home / Archives for Events

MySQL Speakers and Presenters at LinkedIn

by

There is a Linked In group I created some time ago but forgot to advertise that is for MySQL Speakers and Presenters.

If you a speaker or presenter of MySQL content, confirm your registration here.
You will need to have a reference to a website confirming you have been a speaker at a MySQL Event such as a User Conference, MySQL Camp or Local MySQL Users Group.

Hopefully overtime we can build a consolidated index at MySQL Forge Presently some pages exist including MySQL Conference & Expo and User Group Presentations but I’m accepting input for a model to have a central page, and link to or upload of presentation. Any input welcome.

Storage Engines at the MySQL Conference

by

I’ll be following closely the progression of Storage Engines available in the MySQL Database server, well soon to be available when 5.1 gets to GA (hopefully by end of Q2 which is what we have been told). Tick, Tick, time is running out.

PrimeBase XT (PBXT) and Blob Streaming is a focus as PrimeBase Technologies, the company which I want to note for people is an Open Source company, committed at providing an open source alternative to the other commercial players. You also have at the MySQL Conference talks on the the existing InnoDB from Innobase (a subsidiary of market RDBMS leader Oracle). There is a Nitro presentation, an Infobright presentation, no Solid presentation surprisingly (the IBM news happening after submissions closed). We also have from MySQL, presentations on the internally developed storage engines Falcon and Maria, both products that won’t even be in 5.1 but 6.0, however Maria is presently a different branch of 5.1 so I don’t know how that works. Will it be in 5.1?

But what I want to seek is more news of KickFire, a Diamond Sponsor, an engine with embedded H/W, something that’s been obviously worked on in reasonable stealth. For me it’s not just interesting, it’s a competitor in our technology space, so I’ve been researching Joseph Chamdani and some of his patents.

Plenty of news in the past few weeks on Kickfire including Kickfire Update by Keith Murphy on April 3, Kickfire: stream-processing SQL queries by Baron Schwartz on April 4, Kickfire looking to push MySQL limits by Farhan Mashraqi on April 4, and Kickfire Kickfire Kickfire by Peter Zaitsev on April 4, and myself back on March 23.

So what can I make from the lack of company information and posted information to date.

  • Hardware based acceleration.
  • No Solid State Drive (SSD) Technology, at least not yet but C2App mentions SSD.
  • Data Warehousing, lending to thinking it’s not a transactional storage engine
  • A new storage engine and a new approach to data storage. I find this surprising, as it takes years to develop a feature complete storage engine, and most new 5.1 storage engines are indeed existing products, take Nitro, Solid, Infobright and Falcon. Only PBXT has been written from the ground up for MySQL 5.1, so looking to know more about it’s development
  • Expensive, it’s dedicated H/W + (assuming) MySQL Enterprise + Storage Engine

Come and get a t-shirt at UC2008

by

Here I am at my desk sporting the PrimeBase supporters t-shirt that will be available at the exhibitors booth at the 2008 MySQL Conference. The front is rather uneventful with the official logo, but the back will be worth the experience. So everybody interested in supporting PBXT as the transactional storage engine for MySQL developed by the community and for the community, please come and see us and mention the secret password.

We have been placed way back in the right hand side of the exhibitors hall at booth 518, in front of the Open Source and OEM providers.

Learning SEO the painful way

by

Indeed I have a goal of launching a consolidated site of my online presence at ronaldbradford.com at some time soon, and even now I have found I’ve made some SEO 101 mistakes, just in my testing site, and my temporary placeholder.

As a database expert I see plenty of database 101 mistakes with most clients, so part of why my site is going nowhere is I don’t want to make SEO 101 mistakes, especially as I’m not launching a new site, but a migration of existing content to one site.

I see nobody at O’Reilly has made improvements to the redirection mess of the MySQL Conference website as described by Farhan Mashraqi in Someone please change mysqlconf.com redirection, and so rather then linking to www.mysqlconf.com which I have done, I’ve linked to the direct page, which I’m sure will probably change after the conference making this a broken link.

I am concerned that a larger organization can’t get this right. Is SEO/SEM not important to them? It will also be of interest to see what happens here with Sun acquiring MySQL. Sun did a rather detailed job of MySQL content on www.sun.com. Time will tell I guess.

Companies speaking at UC2008

by

The Conference Speakers of the 2008 MySQL Conference provides some common and interesting names of companies not common in MySQL circles such as eBay, Microsoft Corporation, HP, Symantec. I see speakers outside of MySQL from countries including USA, Canada, Brazil, Germany, Japan and Australia.

I did some data analysis of the speakers list. There are 150 speakers, there are 45 from MySQL. Other companies with multiple speakers include Sun Microsystems, Kickfire, Linbit, Cafepress, Open Query, Proven Scaling, Standford Linear Accelerate Center, UC Berkeley, Siz Apart, The Hive, Zmanda, MySQL Performance Blog, Infobright, Digg, Grazr and of course PrimeBase Technologies.

Only two MySQL speakers have listed “MySQL/Sun” the rest are “MySQL”. I wonder what the policy is here? You have “Oracle / Innobase” and “Innobase / Oracle Corp.” some identity crisis there, the guys from “MySQL Performance Blog” prefer this name over the company name “Percona”, obviously for brand exposure. You have “Grazr Corporation” and “Grazr Inc”. It’s only trivia but interesting.

Upcoming 2008 MySQL Conference

by

It’s just three weeks now before the 2008 MySQL Conference. Good to see my mug shot on the front page (see screen shot below).

I will still be presenting my session Top 20 DB Design Tips Every Architect Needs to Know, however as a departing MySQL Employee I’ve had to give up the chance to present the “MySQL for Oracle DBA’s Bootcamp” tutorial, content that I developed for MySQL specifically and have already presented three one day seminars in New York, San Francisco and Washington DC.
Update March 26 2008. I should clarify that I notified MySQL as part of my exit items that I would not be able to present the Tutorial. I would very much like to, and being the author of the content I am well qualified, however as this was developed for MySQL and will be again used by MySQL in the future I felt it was inappropriate that a non MySQL employee and a recent departed employee was presenting this content. I did not want for any attendees to be confused or see a potential conflict of interest and I wanted to ensure I kept my distance from the strict Sun Intellectual Policy procedures.

Kickfire formally C2 App a Diamond sponsor will be something I’m very interested in seeing, MySQL being written specifically in hardware. A few talks I’ll be interested in seeing include Securing MySQL for a Security Audit by Brian Miezejewski, Disaster is Inevitable—Are You Prepared? by Farhan Mashraqi, Developing Information Schema plugins by Mark Leith, Astronomy, Petabytes and MySQL and System Techniques to Remove I/O Bottlenecks in Large Query Intensive Applications.

What impact will Sun Microsystems a Platinum sponsor and acquirer of MySQL have at the conference? CEO Johnathan Schwartz will be following former CEO Marten Mickos’ open keynote with Open Source: The Heart of the Network Economy. There are a number of Sun related talks and Sun employees speaking. I’m sure attendance numbers of Sun employees will be up also.

PrimeBase Technologies will be out in force at the Exhibitors hall, so be sure to stop by and say hello, and get a free t-shirt from them.

MySQL Camp II – Memorable Quotes Day 1

by

Better late then never, this week I finally have the chance to catch up on some overdue posts. At the first MySQL Camp I made a list of memorable Quotes, see Day 1, Day 2, Day 3. I didn’t get as much chance as last time, however here are some pearls of the recent MySQL Camp II.

“I walked in of the street for the free food. I’m here for the free education” – Adrian.

“I’m his boss, I’m here to make sure he’s really here, and not playing hooky.”

“Mashups, collating the worse bugs from multiple API’s all in one place” – OSCON badge

“Compiles 114,000 combinations of MySQL that we are interested in.” Sandro – Skoll Project — You mean to say there are are combinations your not interested in.

“Ok, people this is the second site were are going to crash today. You have heard you have been slash dotted, now you have been camped.”

Jay turning down the lights, to the whole auditorm, not just the stage. “That won’t work. My paper isn’t backlit.” — Andrew

Jeremy: “It’s a porn site.”
Sheeri: “technocation.com it’s not a porn site, I work for a porn site.”

Jeremy: “You don’t realize how many Google properties you use, google reader was down, google search was down, google maps was down.”
Sheeri: “I had to use Yahoo maps, I feel so dirty.”

Jay: “How many people are interested in a tour of the MySQL Source Code.”
Jeremy II: “It is a guided tour, isn’t it.”

“I can’t remember if was the cold, hot or luke warm”. — Bob, In the backup talk.

“How many environments have it. They all have the presumption of it.”

A MySQL Story

by

The Taso Dump

I’ve gotto go, my time has come. (maintenance window needed)
Could not get a lock. (table lock)
I needed a blocker to ensure a single thread. (–single-transaction)

Finally, got a lock. (–lock-tables)
Need to ensure no transactions lost.
Dump, Dump, Dump. (mysqldump)

I’m having flow problems.
Was the buffer to small? (key_buffer_size)
Was it the query cache? (query_cache)
My Dirty Pages may be too high? (innodb_max_dirty_pages_pct)
Or was it just Too Many Connections? (max_connections)
But it was just waiting on the flush (flush tables with read lock)

Time passes, Time Passes. No output yet.
Is it network saturation?
Is it IO bound?
Do I need a better flushing method (innodb_flush_method)
No, it was just the lag? (Seconds_behind_master)

Dump is complete (unlock tables)
Now it’s time to Purge (purge logs)

If there is an attempt to restore.
The backup would be empty.
Blackhole would be found as the cause.
In the end, all transactions lost!

If you weren’t at Friday dinner after MySQL Camp II you missed it.
It’s not meant to be MySQL grammar correct, it’s just some random words we were throwing around.

MySQL Camp II – Post Dinner

by

MySQL Camp II is complete. A small group of about 18 had post dinner at Tiny Thai in New York City. Some elected to drive from Brooklyn, they arrived at least 30 minutes after those of us that the subway.

I have a lot of notes to write, if ever the time permits. For now, the following few that joined for drinks are below. I know other people took photos of the camp, for a change I actually took none. If you want to add a link in comments of photos from the camp that would be great.

MySQL Camp II Post Dinner Drinks

Other sizes here

MySQL Camp II – Introductions

by

We have started MySQL Camp II. The first session is Introductions.

I didn’t catch all the employers, but here is part of the list of attendees at the Introduction section. Great to see multiple people from many places including ESPN, priceline.com, Proven Scaling, Solid Tech – sponsors of Dorsal Source, ForSaleByOwner. fontshop.com, 9Mmedia, CafeMom, JP Morgan, Upoc, ClubMom, Stock Photo Finder, AmieStreet,LogicWorks, Skoll – Distributed Continuous QA , AOL, Minggl, Minggl New Test Site, Visibone. Others include OnlineBuddies.com, NT Snort User Group, DreamweaverNY User Group, A law firm. As well as a few people from MySQL.

Many people mentioned having an Oracle background, or working with Oracle now, at least 6 people that heard me speak at “MySQL DBABootcamp for the Oracle DBA” last week.

There were a lot of MySQL Beginners here which was really great.

And now we are onto the discussion of the sessions.

MySQL Camp II – It begins

by


Well readers, your either here or your not. MySQL Camp II starts today in Brooklyn, New York, at Polytechnic University. Last night’s pre drinks meetup in NYC went well, but today it’s brass tacks time. View Larger Map

For those of you not able to make it, IRC@Freenode #mysql-camp will be the place to hang out to hear what’s happening. If your not at the camp, please identify yourself. Be sure to also check out the Camp Web Site MySQL Camp II for the plans for today and tomorrow.

For those of you not here, MySQL Camp III is already in planning.

Everything fails, Monitor Everything

by


From the recent MySQL Conference a number of things resonate strongly almost daily with me. These included:

  1. Guy Kawasaki – Don’t let the bozos grind you down.
    • Boy, the bozos have ground me down this week. I slept for 16 hrs today, the first day of solid rest in 3 weeks.
  2. Paul Tuckfield – YouTube and his various caching tip insights.
    • I’ve seen the promising results of Paul Tuckfield’s comment of pre-fetching for Replication written recently by Farhan.
  3. Ramus – SSL is not secure — This still really scares me.
    • How do I tell rather computer illiterate friends about running multiple browsers, clearing caches, never visiting SSL sites after other sites that are insecure etc.
  4. Everything fails, Monitor Everything – Google

What I’ve been working on most briefly lately, and really want to be far more prepared everywhere I go is Monitor Everything.

It’s so easy on site to just do a vmstat 1 in one session and a mysqladmin -r -i extended-status | grep -v ” | 0 “ in another, and you may observe a trend, make some notes, say 25% CPU, 3000 Selects, 4000 Insert/Updates per second etc, but the problem is, the next day you don’t have actual figures to compare. What was the table_lock_waits yesterday, they seem high today.

I also only found a problem on a site when I graphed the results. I’ll give you a specific example. The average CPU for the system was 55%, the target was 50%. When graphing the CPU, it was plainly obvious something was not right. I could see with extremely regularity (and count 12 in one hour) a huge CPU spike for a second or two. It was so regular in the graph it was not possible it was random. So, after further investigation and testing, a 5 minute job on this production server (and not on previous testing servers) took 25% CPU for a second or two, and a huge amount of Page Faults. Did it effect the overall impact of the performance of the system. I don’t know, but it was a significant anonomoly that required investigation.

So, quite simply, always monitor and record so you can later reference, even if you don’t process the raw figures at the time. The question is then, “What do I monitor”. Answer, monitor everything.

The problem is with most monitoring, e.g. vmstat and mysqladmin is the lack of a timestamp for easy comparison. It’s really, really annoying that you can add this to the line output. The simple solution is to segment your data into both manageable chunks and consistent chunks.

Manageable chucks can be as easy as creating log files hourly, ensuring the start exactly at the top of the hour. Use a YYYYMMDD.HHMMSS naming standard for all files and you can never go wrong.
Consistent chunks is to ensure you start all manageable monitor (e.g. hourly) at the exact same time, so you can compare.

You need to monitor at least the following:

  • vmstat
  • mysqladmin extended-status
  • mysqladmin processlist
  • mysqladmin variables
  • mysqladmin -r -i [n] extended-status | grep -v ” | 0 “

I haven’t found an appropriate network monitoring, but you should also at that.

The issue here is frequency. Here are some guidelines. vmstat every 5 seconds. extended-status and processlist every 30 seconds, variables every hour, and extended-status differences is difficult, but it saves a lot of number crunching later for quick analysis. I do it every second, but not all the time, you need to work out a trigger to enable, or to say run it for 30 seconds every 15-30 minutes.

So in one hour I could have:

  • 20070519.160000.os.vmstat.log
  • 20070519.160000.mysql.variables.log
  • 20070519.160000.mysql.status.log
  • 20070519.160000.mysql.processlist.log
  • 20070519.160500.mysql.status.increment.log, then 1610, 1620, 1630 etc

I have my own scripts for monitoring under development, and I’ve been revising slowly, particularly to be able to load data into a MySQL database so I can easily use SQL for analysis. One thing I actually do is parse files into CSV for easy loading.

There are two tools out there that I’m reviewing and you should look at. Mark Leith has written a Aggregated SHOW STATUS stat pack, and there is also tool called mysqlreport. These both go some what to ultimately what I want.

I haven’t used it yet, but I’ve seen and been very impressed with the simplicity of Munin for graphing. I really need to get some free time to get this operational.

So Monitor Everything and Graph Everything. Plenty of work to do here.

The MySQL Conference recap

by

I recently had the opportunity to return and speak at the Brisbane MySQL Users Group. I spent some time talking about MySQL User Conference 2007 Summary and Life as a Consultant. My summary of included:

  • Overview
  • Keynotes
  • Marten Mickos – MySQL
  • Guy Kawasaki
  • Michael Evans – OLPC
  • Rasmus Lerdorf – PHP
  • Paul Tuckfield – YouTube
  • Community Awards
  • Product Road Map
  • Google
  • Storage Engines
  • Dorsal Source
  • What’s Next

One question was posed to me. “What new did MySQL do this year?” being from the last User Conference. MySQL did seem to not make a great impact at the conference over the successes of the previous year. I had to think some time to come up with the following list.

And most recently:

  • Open Source Database Vendor Partners with LINBIT to Jointly Promote & Support DRBD for MySQL Enterprise Read More
  • IBM DB2 as a Certified Storage Engine for MySQL on System i Read More

It’s hard to say if these are big ticket items or not, but it is definitely disappointing that 5.1 GA is still MIA. We stay tuned.

I also managed a much better response then from my Conference Presentation opening Slide.


“How can you tell an Oracle DBA has touched your MySQL Installation?”

MYSQL_HOME=/home/oracle/products/mysql-version
mysqld_safe –user=oracle &

MySQL Cluster Certified

by

Jonathon Coombes recently blogged in MySQL Cluster Certified that he passed the MySQL Cluster DBA Certification as was the first Australian. Lucky for him I passed the exam after my presentation on the second day of the conference. I guess us Australian’s are leading the world!

As Jonathon said it was rather hard, certainly more difficult then the other DBA exams but nothing for an experienced Cluster DBA.

MySQL Conference – YouTube

by

MySQL Conference 2007 Day 4 rolled quickly into the second keynote Scaling MySQL at YouTube by Paul Tuckfield.

The introduction by Paul Tuckfield was; “What do I know about anything, I was just the DBA at PayPal, now I’m just the DBA at youTube. There are only 3 DBA’s at YouTube.”

This talk had a number of great performance points, with various caching situations. Very interesting.

Scaling MySQL at YouTube

Top Reasons for YouTube Scalability

The technology stack:

  • Python
  • Memcache
  • MySQL Replication

Caching outside the database is huge.

It a display of numbers of hits per day it was said “I can neither confirm or deny the interpretation will work here (using an Alexa graph)”. This is not the first time I’ve heard this standard “Google” response. They must get lessons by lawyers in what you can say.

Standardizing on DB boxes (but they crash almost daily)

  • 4x2ghz opteron core
  • 16G RAM
  • 12x10k scsi
  • LSI hardware raid 10
  • Replication played a big part in fixing
  • Get a reliable H/W supplier

Replication Lessons

  • You don’t worry about it when a replicas fail.
  • One thing that sucks, Innodb doesn’t recover very fast. It does that durability think, but it takes hours to finish recovering (was it going to finish)
  • How many backups can you restore. When you switch you a replica, are you sure it’s right?
  • Did you test recovery, did you test your backups.
  • replication was key to trying different H/W permutations to identify incompatible H/W (combinations of controllers/disks)
  • we got good at re-parenting/promoting replicas, really fast
  • we built up ways to clone databases as fast as possible
  • Excellent way to test tuning changes or fixes (powerful place to test things)
  • Keep “intentional lag”/Stemcell replicas – Stop SQL thread, keeps a server a few hours or a day behind. Say if you drop a table you have a online backup.
  • When upgrading, always mysqldump then reload, rather then upgrade database.
  • Don’t care about CPU’s. I want as much memory as possible, I want as many spindles as possible.
  • For YouTube 2-3 second lag is acceptable.

If you db fits in ram, great otherwise

  • Cache is king
  • Writes should be cached by raid controller (buffered really) not the OS
  • Only the db should cache reads (not raid, not Linux buffer cache)

Only DB should cache reads

  • Hit in db cache means lower caches went unused.
  • Miss in db cache can only miss in other caches since they’re smaller.
  • Caching reads is worse then useless. It’s serialized writes.
  • Avoiding serialization in reads reaps compounds benefits under high concurrency

An important lesson learned. Do no cache reads in F/S and Raid Controller.

Caching Lessons
Overcoming Mystery Serialization

  • Use O_DIRECT
  • vm.swappiness=1-5
  • if you’re >80% buys — your not doing I/O concurrently look at other figures e.g. 80% busy 8 I/O’s, next configuration 80%, only 4 I/O’s
  • Mirror in H/W strip in S/W

Scale Out

  • Writes are parallel to master, but serialized to replicas. We need true horizontal partitioning.
  • We want true independent masters
  • EMD – Even More Databases — Extreme Makeover Database
  • Slave transactions must serialize to preserve commit order (this is why replication is always way slower)
  • The oracle caching algorithm (that’s a small o) — predicting the future
  • Replication lags: one IO bound thread. You do know the future, commands are coming up serially.
  • Write a script to do reads, before updates coming up (because they are cache hits).
  • The diamond. For golive, play shards binlogs back to original master for fallback.

MySQL Conference – Get Behind Dorsal Source

by


In a community session yesterday at MySQL Conference 2007, I first heard about Dorsal Source. A collaboration between Solid DB and Proven Scaling that allows for community people to upload patches to MySQL, get it compiled across multiple platforms, and have a downloadable distribution available on H/W individual contributors will never have access to.

That’s a great idea. I also hope we get the opportunity to get compiling of patches into multiple versions, as well to get builds of a lot of patches together. Personally, I’m running 3 versions just to diagnose one problem. 5.0.36 with a custom binary change, 5.0.37 so I have SHOW PROFILE, and 5.0.33 so I have microslow patch.

With new patches becoming available from the community, I hope I can see builds that combine all known patches that Dorsal Source may have.

I think this is going to be a great project.

MySQL Conference – PHP on Hormones

by

MySQL Conference 2007 Day 4 started early again at 8:20 am with PHP on Hormones by the father of PHP Ramus Lerdorf.

A very funny man, one of the best insightful talks of the conference (rather scary actually). Here are some opening comments.

  • In his own words as Keynote speaker. “I’m here because I’m old”.
  • Php 1 from 1994 started after seeing Mozilla in 1993. Because it was just me using it, I could change the language any time.
  • In 2005 the code looks like this (in comparison on 1995) — I’m not sure if this is worth 10 years of development
  • I wrote PHP to avoid programming
  • It’s changed to be more OO because people expect that. Universities teach this.
  • Hey, I was fixing bugs in my sleep. Iwould wake up, and in my mail box there would be bug fixes to bugs I didn’t even know I had.

Why do people contribute?

  • Self-interest
  • self expression
  • hormones
  • Improve the world

The slide included a great Chemical equation of “The Neuropeptide oxytocin” — Nature’s trust hormone

People need to attract other people, it makes you feel good, it comes out when you interact with people.

It’s not what people think about you, but rather what they think about themselves.

  • PHP was my baby, giving up control, just because I started it, doesn’t mean I have a bigger say in it.
  • Systems that harness network effects and get better the more people use them in a way that caters to their own self-interest. — Web 2.0
  • Once you build a framework your done, the users build the site, they drive the content.
  • The same people that work on open source projects, are the same people that use websites.
    • Self-interest
    • self expression
    • hormones
    • Improve the world

1. Performance
It your sites falls apart your done.

  • Benchmark
    • http_load
    • Callgrind inside valgrind
    • XDebug

valgrind –tool=callgrind

  • Excellent tool to see where time is spent in the code. You have to run a profiler.
  • Example of using Drupal. It turns out 50% of time was spent in the them, it had 47 SQL queries, 46 Selects.
  • Went from 4 per second to 80 per second, without any code changes. Some performance options, and some caching.
  • Guaranteed you can double the speed of your website by using a profiler.

2. Security
Critical problem areas.

  • 404 pages
  • Search page
  • PHP_SELF
  • $_GET, $_POST, $_COOKIE
  • $_SERVER
  • Lots of stupidity in IE (e.g. Always send a charset)

The web is broken you can all go home now.

People are venerable because people run older versions of browsers, and their data is not secure, and you can’t secure their data.

What can happen??
9 out of 10 of you have cross-site scripting hole on your site

Remote Greasemonkey
Profile Hacks
JS Trojans

Added a PHP logo to the MySQL User Website, it’s really the PHP website
IBM webpage, on article about security.

Tool to find holes, banks, insurance companies, CIA, even Yahoo where I work.

You know if they have been to bankofamerica.com, you can tell if they are a customer, you can tell if they are logged, you can then see their cookie credentials.

You don’t know if any sites have these problems.

JS trojan, iframe that captures
reconfigures your wireless router, moves it outside your DMZ, then uses traditional techniques to attack your machine (that you thought was secure inside a firewall)

You should never ever click on a link. It sort of defeats the purpose of the web.

Never use the same browser instance to do personal stuff and browsing.

So what are we doing about this?
There isn’t much we (PHP) can do to secure sites developed.
Built a filter extension in 5.2, back in 5.1.

http://php.net/filter *** YOU MUST IMPLEMENT THIS
filter.default=special_chars

3. APIs are Cool!

Two lines to grap the Atom feed from flickr of photos just uploaded.
That’s all I have to add to my code.

The really make you want to use the servers. It’s so easy.

API drives passion, drive people to use your site.
You can add a lot of cool things to your sites.

What to do

  • Avoid Participation Gimmicks
  • Get their Oxytocin flowing
  • Solve One Problem
  • Clean and Intuitive UI
  • API’s
  • Make it work

A full copy of the slides can be found at http://talks.php.net/show/mysql07key

MySQL Conference – Google

by

MySQL: The Real Grid Database

Introduction

  • Can’t work on performance problems until we solve the availability
  • We want MySQL to fix our problems first.

The problem

  • Deploy a DBMS for a workload with
    • too many queries
    • to many transactions
    • to much data

A well known solution

  • deploy a grid database

    • -use many replicas to scale read performance
    -shard your data over many master to scale write performance
    -sharding is easy, resharding is hard

  • availability and manageability trump performance

    • – make it easy to run many severs
    – unbretable aggregate perfomance

  • we describe problems that matter to us.

    • The grid database approach

    • Deploy a large number of small servers
    • use highly redundant commodity components
    • added capacity has a low incremental cost
    • not much capacity lost when a server fails
    • support many servers with a few DBAs

    Managability
    Make it easy to do the tasks that must be done. Reduce, Reduce.
    Make all tasks scriptable
    Why does it mater, support hundreds of servers, spend time solving more interesting problems. You generally have lots of problems to solve.

    Underutilize your severs
    Require less maintenance
    Requre less tuning
    tolerate load spikes better
    tolerate bad query plans better

    In a Perfect World
    Short running queries
    uses kill mistake and runaway queries
    accounts new use to many connections
    query plans are good
    new apps increase database workload by a small amount
    only appropiate date is stored in the database

    Reality

    • Long running transactions, create replication delays everywhere
    • servers with round robin DNS aliases make queries hard to find
    • applications create more connections where the database is slow
    • some storage engines use sampling to get query plan statistics
    • new applications create new database performance problems
    • applications use the database as long as rows are never deleted
    • many long running queries on replicas

    Solutions

    • Improve your ability to respond because prevention is impossible
    • Need tools to make monitoring easier
    • determine what is happening across servers
    • detemine what happened in the past

    Mantra

    • Monitor everything you can, and archive as long as possible. (vmstat 5 secs, iostat, mysql error logs)
    • You will need these to reconstruct failures
    • save as much as possible
    • script as much as possible

    Monitoring Matters

    • Display what is happening

      • -which table, account or statemsns caused most of the load
      -many fast queries can be as much a problem as one slow query

    • Record what happending

      • –archivce show status counters somweere
      – query data from the archive
      — visualise data from the archivce

    • record queries that have been run

      • — archive show processlist output (do every 30 seconds)
      — support queries on this archive

    • All of this much scale to an environment with many servers

    Monitoring Tools

    • Display counters and rate change for counters
    • aggregate values over many servers
    • visualize and rang results
    • display results over time

    Google mpgrep tools

    New Commands
    We changed mysql, three new commands
    SHOW USER _STATISTICS
    SHOW TABLE STATISTICS
    SHOW INDEX STATISTICS

    Per Account Activity
    USER_STATISTICS
    seconds executing commands
    number of rows fetched and changed
    total connections
    number of select/updates/other/commits/rollback/binlog bytes written.

    TABLE STATISTICS
    number of rows fetched/changed

    INDEX STATISTICS
    display number of rows fetched per index
    helps find indexes that are never used

    available in code.google.com in 4.0, porting to 5.0

    MySQL High Availability

    • Great options
      • Cluster
      • Replication
      • Middelware — e.g. continum
      • DRBD
    • We need some features right now
    • we are committed to innodb and mysql replication

      • *a lot of appplicaton code works on this
      *our tools and processed support this

    • We favor commodity hardware

    There are all great features but we are much more limited in what we can use.
    Management want to know we don’t loose transactions, not loose some transactions.

    Desired HA Functionality

    • Zero transaction loss on failures of a master
    • minimal downtime on failures of a master
    • reasonable cost in performance and dollars
    • fast and automatic failover to local or remove server
    • no changes to our programming model
      • does it support MVCC

        • does it support long running transactions (5 mins – populate temp table then use to update another table, changing rows), 5 mins on master, causes 5 mins on slave, causes code to failover from slaves to master

    • replication and reporting are concurrent on a slave

    MVCC must have update concurrent with query.

    Failures happen everywhere
    OS – kernal oom or panic (older 2.4 32 bit systems)
    mysqld – caused also by code we added
    disk, misdirected write, corrupt write (love innodb checksums)
    file system – inconsisted after unplanned hardware reboot (use ext2)
    server – bad RAM
    lan, switch – lose
    Rack – reboot
    Data center – power loss, overheading, lightning, fire
    People – things get killed or rebooted by mistake ( a typo can take out the wrong server, when names differ by a character or a digit)

    ext2 and 4.0 are great, there are the same generation.
    Trying not to use RAID, not battery backed raid etc, we try work around with software solutions. We do use RAID 0, but we also try software solution.
    When we have the right HA solution, we won’t need RAID.

    Mark. “Yes, Google programmers have bugs. Not me personally, it was my predecessor.”

    HA Features we want in MySQL
    Synchronous replication as an option
    a product that watches a master and initiates a failover
    archives of the master binlogs stored elsewhere
    state stored in the filesytstem t obe consistent after a crash
    . innodb and mysql dictionaries can get out of sync
    .replicatoin state on a slave can get out of sync

    We could not wait
    Features we added to MySQL 4.0.26
    We can do things a lot faster
    . we have more developers lying around
    . Our needs as specific, not a general product solution

    Transactional replications for slaves
    semi-synchronous replication
    mirrored binlogs
    fast and automated failover

    Transactional Replication
    Replication state on a slave is stored in files
    slave sql thread commits to storage engines and then updates a file
    a crash between the two can make replication state inconsistent
    transactional replication
    MySQL can solve this in the future by storing replication state in tables

    Semi-synchronous replication
    Block return from commit on a master until at least one slave has acknowledged receipt of
    slave io thread acknowledges receipt after buffering the changes
    modified mysql replication protocol to support acknowledgments
    conifuration options
    where to the master uses it
    where a slave used it
    how long the maser waits for an acknowledgement

    can run a server with some semi-sync replication slaves and some regulare replication salves
    this can be worked with any storage engines that supports commit, but we only use innodb

    * This is how we guarantee to management for Zero Transaction Loss.

    Latency single stream 1ms, multi-stream 10ms. This is acceptable for us.

    The MySQL Replication Protocol

    • The current replication protocol is efficient
    • a slaves makes one request

    Replication Acknowledgment

      Slaves register as semi-sync or async at connect time
      prepend flag bytes to all replication events sent to semi-sync clients
      the master sends the flag bytes to request acknowledged for replication events that represent the end of the transaction
      the slave use the existing connection for acknowledgments

    Mirrored Binlogs
    mysql does not provide a way to maintain a copy of a master’s binlog on a replica. By copy we me a file of same name and equivalent byte for byte.
    Hierarachial replication works much better where a slave can disconnect from one replication proxy and reconnect to another with adjusting binlog offsets.
    Hot backups taken before a failover and difficult to use after a failover

    Mirrored Binlog Implementions
    Slave IO threads write their own relay log and a copy of the bin log
    all events but the rotate log event are written

    After failover, start a new binlog on new master

    Fast Failover

    Slaves use a hostname, rather then an IP
    You can’t enable the binlog dynamically (in 4.0)
    Added new SQL STATEMENTS that does
    disconnect users with SUPER privilege
    disable new connections
    enable the bin log
    enable connections from all users

    Automatic failover
    Something must decided that a master has failed
    Something must choose the new master

    Q: What keeps up from moving to 5.0?
    A: Queries don’t parse (Joins)

    Data sets, 8GB servers, 50-100GB’s

    MySQL Roadmap

    by

    Here are some notes from the MySQL Server Roadmap session at the MySQL Conference 2007.

    MySQL: Past and Future

    • 2001: 3:23
    • 2003: 4.0 UNION query Cache Embedded
    • 2004: 41. Subqueries
    • 2005: 5.0 Stored Procedures, Triggers, Views
    • Now: 5.1.17 Partitioning, Events, Row-based replication
    • 2007?: 6.0 Falcon, Performance, Conflict detection
    • 2008?: 6.1 Online Backup, FK Constraints

    2007 Timeline

    • Q1: 5.1 Beta, 5.1 Telco Production Ready, Monitoring Service 1.1, MySQL 6.0 Alpha, Community GA
    • Q2: MySQL 6.0 Beta, New Connectors GA
    • Q3: 5.1 RC, 6.0 Beta, MS 2.0, Enterprise Dashboard beta
    • Q4: 5.1 GA, 6.0 Beta

    Where are we today?

    • We are by fare the most populate open source database
    • The Enterprise world is moving online and MySQL is well-positioned for that trend, But:
      • Transactional scalability
      • Manageability
      • Specific online features

    MySQL Server Vision – The Future

    • Always Online — 24×7, Online backup,online analytics, online schema changes
    • Dynamic Scale-out — online partitioning, add node, replication aides,
    • Reliable — fault-tolerant, easy disagnosis, stable memory, ultimately self-healing
    • High-performance — Interactive web, real-time response, apps, 10,000-100,000 clients
    • Ease of use — Portable, Best for development, multiple connectors, easy tuning
    • Modularity and Ubiquity — Storage engines, plug ins

    How can you help?

    • Bug finding and fixing — Community Quality Contributor
    • Feature/patch contribution
    • But, to expedite your patch

    The goal: “Be the Best Online Database for Modern Applications”

    MySQL Conference – For Oracle DBAs and Developers

    by


    I have just completed my presentation at the MySQL Conference 2007 on MySQL for Oracle DBAs and Developers.

    Not mentioned in my slides, but referenced during the presentation was what I consider the most important page to document from the MySQL Manual — 5.2.1. Option and Variable Reference

    You can download a PDF copy of my presentation here.

    MySQL Conference – Building a Vertical Search Engine in a Day

    by

    Moving into the user sessions on the first day at MySQL Conference 2007, I attended Building a Vertical Search Engine in a Day.

    Some of my notes for reference.

    Web Crawling 101

    • Injection List – What is it seed URL’s you are starting from
    • Fetching the pages
    • Parsing the content – words and links
    • Updating the crawl DB
    • Whitelist
    • Blacklist
    • Convergence — avoiding the honey pots
    • Index
    • Map-reduce — split a large problem into little pieces, process in parallel, then combine results

    Focused content == vertical crawl

    • 20 Billion Pages out there, a lot of junk
    • Bread-first would take years and cost millions of lives

    OPIC + Term Vectors = Depth-first

    • OPIC is “On-line Page Importance Calculation”. Fixing OPIC Scoring Paper
    • Pure OPIC means “Fetch well-linked pages first”
    • We modify it to “fetch pages about MySQL first”

    Nutch & Hadoop are the technologies that run on a 4 server cluster. Sample starting with www.mysql.com in 23 loops, 150k pages fetched, 2M URL’s found .

    Serving up the results

    MySQL Conference – RedHat Keynote – One Laptop Per Child

    by

    Our third keynote at MySQL Conference 2007 was titled Building the Ultimate Database Container with RHEL, MySQL, and Virtualization by Michael Evans.

    The presentation was on Red Hat & One Laptop Per Child. His initial Quote was “Thinking Past Platforms: The Next Challenge for Linux”, By Doc Sears, 2007-04-16 http://www.linuxjournal.com/node/1000210

    OLPC

    • A Non profit idea from Nicholas Negroponte.
    • Aim is to build & distribute inexpensive laptop systems to primary & secondary school students worldwide.
    • Sell to young children in developing countries.

    In summary at presentation to Red Hat — “Non-profit, run by a professor, we make hardware and sell to governments.”

    The overall dynamics have attracted a lot of interesting people in the world.

    The ability and goal is to make the device together, bringing all H/W and S/W people together.

    The people that get behind this project have the ethos — “I’m willing to jump into this to change the world.”

    This is the first time for a new opportunity in the last 10 years.

    The sugar user interface is a completely new experience.

    When the UI designer was presenting to a room of head executives. “What ever advice you got, keep it to yourself, your not the target market.”

    One key point — No backward compatibility needs.

    More information at www.laptop.org. Wikipedia Reference. Some videos at You Tube Inside One Laptop per Child: Episode one and Slightly better demo of the OLPC User Interface.

    MySQL Conference – The next keynote with Guy Kawasaki

    by

    Without missing a beat at MySQL Conference 2007, we moved from Marten’s keynote to The Art of Innovation by Guy Kawasaki.

    Extremely fun and entertaining. His 10 points.

    1. Make Meaning

    • “To change the world”
    • To a VC, do not say “you want to make money”, that is understood. You will attract the wrong team.

    2. Make Mantra

    • Not a Mission statement (50-60 words long), but 2 or 3 words.
      • Wendy’s – “Healthy fast food”
      • Mike – “Authentic Athletic Performance”
      • FedEx – “Peace of Mind”
      • eBay – “Democratize commerce”
    • Create a mantra — Why do you exist?

    If you get stuck try the Dilbert mission statement generator.

    3. Jump to the next curve

    • Not 50% or 100% better, but “Do things 10x better”

    4. Roll the DICEE

    • “Create great stuff”
      • Deep: Fanning (Reef) Sandal that open beer bottles
      • Intelligent: BF-104 Flashlight (Panasonic) (takes 3 sizes of batteries)
      • Complete: Lexus
      • Elegant: Nano (Apple)
      • Emotive: Harley Davidson (They generate strong emotions)

    5. Don’t worry, be crappy

    Get it out there.

    6. Polarize people

    People love it or hate it.

    7. Let a hundred flowers blossom

    • People that are not your target market are using it.
    • Take the money, ask the people why you are buying, ask what you can do better.

    8. Churn baby, churn.

    Ok to ship stuff with crappy stuff in it, but important to continually revised and improve.

    9. Niche thyself
    With a nice graph.

    • Vertical — Ability to provide unique product or service
    • Horizontal –Value to customer
    • bottom right — Price
    • top left — Stupid
    • bottom left — Dotcom
    • top right — X You need to be High and to the right.
    • Fandango — It’s either Fandango, or Clubbin.
    • Breitling Emergency – watch
    • Smart car – park perpendicular
    • LG Kimchi refrigerator

    You need to be like the President of the United States – You need to high and to the right. Got a great laugh from the crowd.

    10. Follow the 10/20/30 rule

    Innovative, you need to pitch for what you want.

    • The optimum number of slides in 10 slides.
    • Given the slides in 20 minutes.
    • Use 30 point font

    11. Don’t let the bozos grind you down

    A bonus to our friends in the community.

    • “I think there is world market for five computers”
    • “This telephone has too many shortcomings to be seriously considered as a means of communication. The device is inherently of no value to us.” –Western Union 1876
    • “There is no reason why anyone would want a computer in their home.” — Digital Equipment Corp 1977
    • “It’s too far to drive, and I don’t see how it can be a business.” – Guy Kawasaki – Bozo (The company was Yahoo)

    Guy commenting on his lost opportunity with Yahoo — “It only covers the first billion, it’s the second billion that pisses me off.”

    Read more about Guy at his website Guy Kawasaki.

    The Art of Innovation. If you a copy of slides, send an email to [email protected]

    MySQL Conference – Opening Keynote with Marton Mickos

    by

    It’s an early start this morning at 8:20am at MySQL Conference 2007 with CEO Mårten Mickos keynote talk Welcome and State of MySQL AB.

    Here are some of the key points that impressed on me.


    “The Participatory & Disruptive spirit of the Dolphin.”.

    Open Source disrupts inefficient models and produces new wealth.

    Architecture of Participation.

    • GPL and FOSS licenses
    • Google
    • del.ico.us

    MySQL Architecture of Participation
    You have the forge, the planet, community contributer program and many more.

    Production of Amateurs

    • You Tube
    • Second Life
    • Wikipedia

    Some really great Quotes:

    “Fail often to succeed soon.” IDEO company slogan

    “Noah’s Ark was built by amateurs, but the Titanic by professionals.”

    Innovation Happens Here

    MySQL Monitoring & Advisory Service

    In his presentation of MySQL Network Enterprise Dashboard, If you were quick you would have noticed the MySQL Version 6.0.0-alpha-pb518.log

    Leading Applications

    • Open Source ISVs
    • Web
    • Web 2.0
    • On-Demand
    • Hosting
    • Telco & VOIP
    • Security
    • Enterptise 2.0
    • Hub & Spoke

    We want to be known as The Online Database.

    Drawn to the Lamp

    • Microsoft
    • Oracle
    • IBM
    • Sun
    • HP
    • Dell
    • Unisys

    They all have an Open Source strategy, the develop open source products, they use and partner with MySQL.

    He also mentioned MySQL Enterprise Connection Alliance – MECA.

    Global Distributed Organization

    • 70% work from home
    • 100 major locations in nearly 30 different countries.
    • There is a great focus on culture – global culture.

    Disruptive Businesses

    • A smarter way to produce the goods
    • A smarter way to distribute them
    • Serve the underserved
    • Keep it simple
    • Innovate with technology to give customers more convenience
    • Make money
    • Gartner: 66% of enterprises are deploying MySQL or planning to
    • 750,000 newsletter subscribers
    • MySQL installed in over 11 million installations

    Going Forward

    • Scalability
    • Availability
    • Manageability
    MySQL – The best online database in the world.

    MySQL Conference – Rewarding the Community

    by

    At MySQL Conference 2007, CEO Mårten Mickos in his opening keynote Welcome and State of MySQL AB rewarded the community. Those that contributed to “The best database in the world”.

    2007 MySQL Applications of the Year
    #1 in Online Video
    #1 in 3G Mobile Entertainment
    #1 in Creative Software

    And the Winners- YouTube, Amp’d mobile, and Adobe

    2007 MySQL Partners of the Year
    #1 reseller of MySQL Enterprise to govt
    #1 in MySQL Enterprise integration
    #1 in Open Source

    And the Winners – Carasoft, HP, and RedHat

    2007 Community Members of the Year
    Quality Contributor
    Community Code Contributor
    Community Advocate

    And the Winners

    Martin Freibe
    Paul McCullagh
    Sheeri Kritzer