Colin Charles Agenda

Baron Schwartz gave a most interesting talk about EXPLAIN. You will definitely want to read his slides (filled with detail), when they make their way online. These notes are very sparse, just bits that I didn’t see in the slides, that Baron mentioned verbally. Plenty of good questions, and plenty of interaction.

EXPLAIN only works for SELECT queries.

How does MySQL execute queries? Optimisation happens even as the query is being executed. As the query is being optimised, some execution happens as well. Execution Plan is a data structure, not bytecode.

When EXPLAIN’s output is generated, MySQL actually executes the query. It just set’s DESCRIBE on it, rather than executing it. Everything is a JOIN to MySQL (union, SELECT 1 [simplest base case join], etc…).

key_len – to know if your table is indexed well.

rows: estimated number of rows to read, but not the number of rows in the result set. In 5.1 and greater, it reflects LIMIT, but not before.

Maatkit includes mk-visual, so you can have a visual explain. This is also, very machine readable.

Update: Artem has good notes too.

Technorati Tags: mysqluc08, mysqluc2008, mysql, baron schwartz, explain

Download the PDF: http://www.mysqlfulltextsearch.com/full_text.pdf

Default search by relevance, default sort is by relevance

Boolean search is also popular. cats AND dogs. No default sorting, so you need to order the results yourself

Phrase search

MySQL Full Text Index, only available with MyISAM, and it supports natural language and Boolean search. ft_min_word_len – 4 characters per word by default is indexed. Frequency based ranking, doesn’t count distance between words

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’ IN NATURAL LANGUAGE MODE);

For Boolean, you use AGAINST (‘cat AND dog’ IN BOOLEAN MODE).

n-gram fulltext plugin for CJK languages are available as plugins

DRBD and MySQL FullText search? DRBD requires InnoDB, when there is a failover, DRBD needs to perform a reovery. Fulltext only works for MyISAM. So ou create a “FullText” slave MyISAM table with FullText indexes. The slide (diagram) is most useful for this, naturally.

Speed up FT search? Fit the index into memory. key_buffer = total size of full text index (max=4GB). You can preload FT indexes into buffer.

You can manually partition. Partitioning decreases index and table size, so search is faster. Application needs changing of course. MySQL 5.1 partitioning features, do not support FTs.

Order by/Group by is a performance killer. Using order by date, is much slower than with no order by.

Real World Performance Killer
SELECT … FROM `ft` WHERE MATCH `album` AGAINST (‘the way i am’)
The above query, is very slow! It took like 13 seconds or so.

Note the stopword list and ft_min_word_len. I is not a stopword, but “the”, “way”, and “am” are stopwords.

ft_min_word_len = 1 will mean that all words except “i” will be filtered out with the standard stoplist. “i” is contained in lots of text!

Search with error correction? Use soundex() MySQL function (sounds similar). select soundex(“Dilane”) should equate to Dylan. You can sort it either by popularity or Levenstein distance (either by a stored procedure or a UDF).

Sphinx – nice, open source, can be faster than MySQL full text index on a large dataset, supports multi-node clustering out of the box. It is however an external solution that isn’t built-in, and needs to be integrated.

MySQL 5.0: need to patch source code. MySQL 5.1: copy Sphinx plugin to the plugin_dir.

You can set Sphinx to be MySQL’s storage engine if you like.

Resources

• Full-Text Search Functions
• Fine-Tuning MySQL Full-Text Search
• MySQL Full Text Search site, including links to worklog entries, etc.

Technorati Tags: mysql, mysqluc08, mysqluc2008, full text search, alex rubin, boolean, performance, partitioning, sphinx

Ahead in the Cloud – The power of Infrastructure as a Service
CTO Amazon.com, Dr. Werner Vogels

Pretty much everyone in the audience uses Amazon!

Announced: Persistent Storage for Amazon EC2.

Hitting one page, might actually go to 250 different services, before the page is generated for you. Shows the use of a tool (Amazon internal), that graphs it.

SaaS: Develop -> Test -> Operate

Hardware costs? Software costs? Maintenance? Load balancing? Scaling? Utilisation? Idle machines? Bandwidth management? Server hosting? Storage management? High availability? All this is the differentiated heavy lifting that Amazon bases their services on.

SaaS comes at a very big cost that you have to address.

70/30 switch: 30% of time, energy and dollars on differentiated value creation; 70% of time, energy and dollars on differentiated heavy lifting.

At Amazon, we expect data centres to fail. But we also expect software to tolerate this failure.

“Scalable Infrastructure that allows applications to meet infinite demand, cheaply and reliably” (statement, made with picture of large amount of Sun hardware)

Amazon S3 (storage), SimpleDB, EC2 (computer power), FPS (payment service). All this is scalable (increase/decrease capacity on demand).

Scalability. Availability. Performance. Cost-Effectiveness.

Growth: largest selection on earth, good customer experience, drives prices down, drives traffic, sellers, selection, and this is a cycle for growth. It brings a lower cost structure, that also lowers prices down then.

This means that incremental scalability is key to Amazon’s business. Grow one step at a time, consistently. Turn a fixed cost, into a variable cost, as your business grows seamlessly.

Elastic cloud: grow and shrink on demand, with minimal disruption to performance. Operational efficiency, fault-tolerant, and remember, everyone has different growth paths. Heterogeneity – do not believe that all your nodes have the same capacity! A year from now, you will have more powerful machines, your software must scale for this.

Everything fails, all the time. An epic truth.

Failures are highly correlated. By every possible worst way! Systems do not fail by stopping – they will fail by sending out garbage ;) Your system must be able to deal with that.

Determinism is an illusion. An illusion created in a very small space. “Let go of control!”

Engineer for performance at 99.9%. Remember, address uncertainty – acquire resources on demand, pay for what you use, leverage other’s core competencies, turn fixed costs into variable costs. Never every pay again for something sitting in your data centre doing nothing for you.

All data access at Amazon is primary key based. Eventual consistent, for high read volume and always writeable. Query-based access, was non-relational.

Primary Key Access: Amazon S3; Query-based Access: SimpleDB; EC2 with persistent storage for a dedicated solution

Persistent storage? Raw disk, attach a volume to EC2. You can also detach. Infinite scalability in terms of data. From snapshots, you can create new volumes.

“All you need is a credit card” – for AWS. Lots of laughter :)

Technorati Tags: keynote, infrastructure, amazon, aws, mysql, mysqluc08, mysqluc2008, verner vogels, saas

I wish I had better notes, but I was enthralled by Jonathan Schwartz’s (CEO, President, Sun Microsystems) keynote. It was truly, very amazing an influential. He’s a great speaker, and very motivated (and I think he’s motivated a lot of the audience).

What is Sun’s Agenda?
Similar values, cultures, and similar dysfunction’s like any family.

The Texas Advanced Computing Center – 500 teraflop “open” super-comptuing facility.

“Computational science is the third mode of discovery, complementing physical experimentation and theory” — Daniel Atkins III, Director, Office of Cyberinfrastruture, National Science Foundation

The industry has valid, legitimate scientific purpose.

Improving society as a result of that understanding. What does it take to fuel developing economies? Or to make new scientific discoveries?

In Africa, a bank is giving mobile phones, to allow folk to increase wealth! The wealth is in the network, it can’t be stolen anymore.

An open source phone? Stay tuned, Sun thinks that industry needs to be disrupted.

What does it take to connect with your friends? MySQL :)

Like electricity, like clean water… “The Network has become a Social Utility”

I want Sun to be a Great Company, and a good company. You can’t buy the community. The greater you are, the “gooder” you are. Work with communities, drive innovation, and more opportunity is created for all.

Jonathan shows a map of places where all the blue dots are where people download MySQL. Take the map away, and you’ll see a beautiful picture. Its already the majority of the planet for instance. These folk, have decided that there is a demand for open source software.

Free software is taking over the world.

Today ZFS is under a CDDL license, but rumour has it, that this will change in the future.

“The Future Will Be Defined by Free… and Freedom”

“We want to be in control of our own destiny” – all those places downloading software… Freedom matters to me, because I want access to my own stuff.

The Amazon. Comprises of 10,000 rivers. They all fuel the Amazon River. Open source, is an ecosystem of many rivers… Sun is saying we’re pro-opensource, pro-free software. Put commitment behind many communities.

Individuals are making choices… MySQL might be used even without CIOs knowing!

Secret plot? Let people download, try it, use it, and they’ll change their view of how good it is. And there’s economic benefit (for Sun).

Technorati Tags: mysql, sun, mysqluc08, mysqluc2008, jonathan schwartz, keynote

Live blogging from Marten Mickos’ (SVP Database Group) keynote at the MySQL Conference and Expo 2008.

Why did Sun acquire us?
The culture and the vision. Biggest match with Sun. “Network is the computer” and “Best Online Database”. Great affinity.

Its a 1 billion dollar vote for the LAMP stack.

Integrating better with OpenOffice.org, run better with Glassfish, and so on. Now, performance and scaling is number one, to make databases run faster. It will take time, but it will be very positive.

This conference itself is just as big as LinuxWorld was seven years ago.

An amazing photo of burning the IPO Prospectus – a bonfire in Santa Cruz. Getting rid of the past!

Scale exponentially, but keep the cost growth at a linear scale. World is flat – build your software in Russia, China, etc. and deploy elsewhere even. Fail fast? Remember to scale fast.

If the whole world is online, how do you think about business? If the world is online, what is the best way to develop software? Its open source, so do it online. Best way to deploy? Maybe not software as a service, but maybe a platform as a service?

Everytime MySQL makes a business model, someone tells us that its stupid and the blogosphere bursts with discussion. The subscription model is the winning model for us. We do see advertising models for example for the online world, but for us, its subscription.

If the whole world is online, how do you organise working? 70% work at home, in over 30 countries. Our organisation is completely spread out. But remember, you get to hire the best people wherever they are in the world.

Customers have choice. The ISV market was the software market, when Marten grew up. That’s not the case anymore. Enterprise 2.0 is building new application. You can but the application (OEM). Now there’s a huge market around buying the service (SaaS). And now, there’s also the market of using the service – you just use Google, or Amazon on the web for instance. Just a major shift in how the software world works.

Remember, It’s Your Data. This is probably the most thing… Data is the Intel inside of the next generation of computer applications (so says Tim O’Reilly). Data drives the web. Vendors might attempt to close the data and lock you in – don’t. Hold on to your data, it is yours. Keep using an open source database, keep using ODF. Its your data. Avoid vendor lock-in.

3 design priorities: reliability, performance and ease of use.

Reliability: Bugs fixed in 5.1: 997 in 2007 plus, 386 so far this year.
Performance: DBT2 performance tests on 5.1.24-rc vs 5.0 shows 10-15% throughput improvements at medium concurrency
Ease of Use: MySQL Workbench is GA as of this morning, and Mike Zinner is on stage.

If you still write SQL query scripts, just turn around, use MySQL Workbench and have a beer. Lots of clapping :) This is in reference to the t-shirt…

Storage Engines: Kickfire, Infobright (for datawarehousing, now resold by Sun), InnoDB (contract renewed!), PBXT (blob streaming coming out), Nitro Security (datewarehousing), SacleDB and Tokutek, Maria (Monty’s baby, MyISAM with transactions and crash recovery), Falcon (superior performance on 16-way Intel Caneland at all DBT2 workloads).

Technology direction?
Marten is leader of an open source group, and innovation happens elsewhere. Our direction?

• Scale (read, write scalability, scale out, scale up, sharding, etc.). No matter what Ferrari models you have, your customer’s continue to want more horsepower.
• Data by SQL or not – we work with memcached now, as well.
• Database as software or as a service (look at db4free.net)
• Opening up Architecture of Participation (inviting folk to meetings, worklog entries open, et al) – building up a strong ecosystem.

Technorati Tags: mysql, mysqluc08, mysqluc2008, marten mickos, keynote, workbench, mike zinner, lamp

2008 MySQL Application of the Year
Social Network – MySQL powered with over 70 million active users (Facebook)
Mobile operator – Highly available LAMP platform at the heart of SMS, mobile and CRM applications (Virgin Mobile France)
eCommerce site – Built caching tier using MySQL for 4 billion transactions per day (eBay)

2008 MySQL Partner of the Year
Open source backup solution (Zmanda)
Expanding support for open source (Microsoft)
Leading reseller (Computercenter)

2008 MySQL Community Member of the Year
Code Contributor (Baron Schwartz)
Quality Contributor (Diego Medina)
Community Advocate (Sheeri Kritzer Cabral (again!))

I have a photo of a representative of Virgin Mobile France, which I’ll upload soon :) Turns out I was sitting next to him…

Technorati Tags: mysql, mysqluc08, mysqluc2008