Colin Charles Agenda

Joe Stump, Lead Architect, Digg. Slides should make its way at Joe’s website soon enough.

Mainly works on the backend, makes sure its scalable, can all the Digg buttons be served, et al.

Application layer is loosely coupled from your data. Whole point of SOA? You can put a service in front of the DB, and move between DB’s if required.

They do use MySQL, but its pretty vanilla.

Old habits die hard
- Data requests are sequential (I need foo, bar, bleh, ecky)
- Data requests are blocking (When you need foo, nothing else is happening)
- Tightly coupled (mysql_query, and if you’re using DB abstraction layer even, you’re still using SQL… you then can’t use CouchDB for instance)
- Scaling is not abstracted (a lot of caching are in the front end code. Its a problem when you start scaling your teams out). They use memcached from what I gather.

SOA
- Data is requested from a service (via HTTP, custom, etc.)
- Data requests are run in parallel (over non-blocking sockets. 10 data requests in 1 webpage, and each request takes 10ms. It might now only take 70ms now, maybe, over 100ms. Generally 1.5-2.5x faster now, for blocking parallel requests)
- Data requests are asynchronous (non-blocking parallel requests)
- Data layer is loosely coupled
- Scalability is abstracted (can find engineers anywhere, that can parse JSON or XML :P)

Options?
- Run requests over HTTP (Google (Java), Amazon (Java), etc.)
- New York Times’ DBSlayer (small little HTTP server that runs and provides parallel and async requests to mysql)
- Danga’s Gearman (binary protocol, has worked, its kind of a queuing system)
- Remember the wall clock goes down, but the CPU time is still happening, its still the same

HTTP w/PHP
1. Group requests for data at the top
2. Open a socket for each request
- Sockets must be non-blocking
- Make sure to use TCP_NODELAY
3. Use __get() to block for results
4. See Services_Digg_Request

Use a pear package, called Services_Digg for the above example. Note Digg’s API documentation as well.

HTTP is widely supported in all languages. Its very easy to get up and running, with lots of options for servers/tuning. Overhead in the protocol is great, and Apache itself has a lot of overhead.

DBSlayer
- small HTTP daemon written in C. You post JSON to it for communications
- connection pooling (benchmark mysql connection, and there’s a whole bunch of overhead in the mysql authentication; mysql proxy does this too)
- load balancing and failover (like mysql proxy)
- tightly coupled to MySQL (no migration)
- tightly coupled to SQL (no CouchDB)
- no intelligence

Gearman
- highly scalable queuing system (worker bees, like PHP scripts. Sockets open, client comes to gearman server to do foo, and it says it has n number of workers, and gearman gets ‘em to work. So it works linearly. Jobs can return results back, run in parallel on many gearman servers and many CPUs)
- simple and efficient binary protocol
- sets of jobs are run in parallel
- queue can scale linearly
- php, perl, python, ruby, c clients
- poorly documented (”I think poorly documented is giving them too much credit.. All danga stuff has next to no documentation”)
- livejournal uses this, instead of using HTTP running
- its not very “robust” (it scales, they at digg don’t see massive number of failing jobs. Queue isn’t persistent though. When pushing stuff, and gearman gets restarted, the queue goes away - there is a workaround, for this, so ask Joe - its an undocumented feature available though)
- digg uses it in the submission process for crawling
- Chris at Yahoo! uses Gearman requests to run multiple memcached GETs (if you’re not using multi-get, check them).
- Check out Net_Gearman, which is a PEAR package

DIY option?
- not recommended, unless you have a highly customised solution, i.e. what Flickr does
- they ran into a problem where uploading an image, and then getting the image resized, for large images, was a problem. So they use a custom binary protocol that is much more efficient for the datasets (think, an SLR has files that are 7MB in size or something)
- this requires more resources (humans, engineers!)

What goes in the Services layer?
- smart caching strategies
- data mapping and distribution
- intelligent grouping of data results
- partitioning logic

Remember to intelligently group data into endpoints, and version them! This will help you improve your software.

Consider bundling and grouping requests (bulk loading).

EPIC FAIL!
- sending SQL over for translation? Pfft. DBSlayer does this, but it tightly couples you
- hundreds of teeny tiny endpoints (cohesive endpoints that return a decent amount of data)
- running SOA requests sequentially! You then get no benefits from an SOA architecture, at all. Parallel requests are good.

Technorati Tags: mysql, mysqluc08, mysqluc2008, mysqlconf, joe stump, digg, services oriented architecture, soa, dbslayer, gearman, services_digg, net_gearman, pear, sql, couchdb, flickr, amazon, scalability, performance

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

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

In-between Marten’s keynote, Rich Green, EVP Software, Sun Microsystems, comes up on stage, and here’s my live-blog.

He talked about the famous dinner last year, for buying MySQL. Autonomy reigns supreme in MySQL. MySQL continue at their same course and speed. The plan is the plan, until there is a new plan - and there is no new plan.

Sun has a heterogeneous platform. Many partners, including Dell, IBM, Intel, and so on. Lots of open source in terms of software. The cultures, discourse, intellectual arguments, its an excellent mash up, this Sun and MySQL. Don’t Panic! There is no change for what has been an incredibly successful thing.

Sun’s Continued MySQL Commitment:

• profitable business on the principles of FOSS
• GPL - consider GPLv3 as it involves, and we understand the interest and evolution of the community. Its GPLv2 still, naturally
• Tuning for the most popular operating systems and hardware platforms
• Integrating with the most popular development environments and other related technologies

Technorati Tags: mysql, mysqluc08, mysqluc2008, sun, rich green

I first decided to try how things were in Windows Vista land, the moment I was told during a talk of mine that MySQL refused to install on Vista. I gave it a twirl (after disabling User Account Control - UAC) and found that I got a rather quaint error message from the MySQL Server Instance Config Wizard (MySQLInstanceConfig.exe) [this after it failed to start once the installer was complete]:

The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more details.

I hopped over to the event log (Event Viewer), and the error displayed was:

Activation context generation failed for “C:\Program Files\MySQL\MySQL Server 5.0\bin\MySQLInstanceConfig.exe”.Error in manifest or policy file “C:\Program Files\MySQL\MySQL Server 5.0\bin\MySQLInstanceConfig.exe” on line 6. The value “asAdministrator” of attribute “level” in element “urn:schemas-microsoft-com:asm.v1^requestedPrivileges” is invalid.

I then thought, that the best solution was to launch it manually. I tried:
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe

via cmd.exe, and lo and behold, it all just worked!

For a more permanent solution, look at Resource Hacker, and read the instructions in this forum post.

It turns out, this is largely related to mysql#30823. There is a problem with the software manifest, that doesn’t allow MySQLInstanceConfig.exe to run in Vista. This sadly affects MySQL 5.0.51a (refer to mysql#34340), and the hope is that the next release fixes this (the documentation has already mentioned that 5.1.24-rc should have this fixed, and 6.0.x will get a push soon.)

In other news? If you haven’t seen MySQL on Windows: Configuration and Install, take a look. Its from Microsoft’s Port25 website (i.e. the open source group within Microsoft). I was truly impressed/shocked when I came across it. I especially like the note: “This paper represents testing and documentation in a lab environment. User Account Control (UAC) is an essential security component to Windows and Microsoft does not recommend turning off UAC in production environments.” Sorry, UAC and MySQL just do not jive (though it seems to be OK, once the installation is compelte - i.e. you can enable UAC again).

Technorati Tags: mysql, windows, bug, UAC, instance config wizard, resource hacker, microsoft

Memcached by Brian Aker, Alan Kasindorf (dormando). Here are some quick, somewhat sparse notes. Follow the slides, it will help.

Slides: http://download.tangent.org/talks/Memcached%20Study.pdf

Memcached was actually created for LiveJournal. It has evolved a bit over time. Chaos to user based clustering, and then Brad implemented memcached. LiveJournal has about 30GB of cache available between 8-12 machines. The DB reads were down like 10x the moment they started using memcached (its much better now).

Its not only for simple objects (not just a single row)- you can use it for complex queries, and the result can be stored in memcached. Eins.de, Patrick Lenz, is also the freshmeat.net guy. He put memcached on the same machine as the MySQL database server (he has 32-bit machines, and MySQL can only use a certain amount of RAM, so the rest was for memcached). This is definitely not the recommended way. Have separate memcached servers.

PatG comes up to talk about Grazr, which is more of a write-through cache. Refer to Page 8 of the slides. Now, the thought is that maybe Pat should’ve used gearman, rather than writing their own software. Memcached has allowed them to do it asynchronously. They’re using bulk inserts now as well.

DownUnder GeoSolutions uses lustre, which is a clustering filesystem. They’re not a web-based solution. They extract data off lustre, store it in memcached. Processing happens on the memcached RRU.

memcached by itself does very little. There’s a simple daemon, and it responds to gets/sets/add/replace. It sits on top of a very simple slab allocator. Everytime you called it, it ran malloc() and it would free() it when done, during the early days. So, now, it makes one slab allocator for different types of objects.

memcached is event based. libevent is a generic wrapper around epoll/kqueue, and its very scalable for network connections. 10,000 connections to a memcached, is ok - it only cares for how many of them are “active”.

The protocol is very simple. Everyone hates it, but everyone uses it. You can even fire up telnet to talk to memcached. Its very easy to write to protocol.txt and to talk to it.

memcached? A big stupid hash table. In a grid, its a distributed hash table. memcached is 2 hash tables - from client, and one in the server. 30 memcached’s don’t need to know about each other - they’re blind from each other. There is no cross traffic. You just add more servers, to scale up.

Clients hash keys to the server list. Take a single key (250 bytes max), the client hashes it. You have a value, you want to access it, here’s a key. There is multiple hashing going on, as some clients do things like compressing data.

How do I dump data? You don’t. Its a cache.

How is it redundant? Its not. The server itself doesn’t know about other servers around it! PECL and the next version of libmemcached will understand replication. The redundancy happens in clients.

How does it handle failover? It doesn’t. If it dies, it dies. A client can of course, handle it.

How does it authenticate? It doesn’t at all. Don’t stick one of this, open faced, to the Internet - when you connect to it, you have full access to any commands in the server and all contents in the server. You don’t want folk just typing flush in the server ;)

A very simple service, very simple server.

Details on the Server? Page 14, is pretty much all the commands you can use in memcached. You can run this from telnet, even
- set operation throws data inside memcached (it doesn’t care if there’s other data in it)
- add is lightly atomic - it won’t add data that is already there
- stats can give you particular pieces of information, or give you a full dump. Hit ratio, cache efficiency, and lots more, can come out of this

All drivers you are seeing, are just basically extending all these commands. cas (compare and swap atomic!) today is pretty limited

memcached can even run on FreeBSD 4. Most people run memcached on Linux. No one has deployed memcahced on OSX in the audience.

There’s MySQL integration. Most users grab object from database,
store object to memcached. The UDF memcached functions are probably the most successful UDF in MySQL’s history :)

There’s pgmemcache() for Postgresql, but not much is known about it

Apache - mod_memcached, has CAS operations exposed. Different to the lighttpd implementation.

There are limitations (page 23). If you wanted to change things, you can recompile memcached, but you might not want to do that. Largest slab class in the system, is 1 megabyte. So data size is under 1 megabyte. Beware if you’re running on a 32-bit system (going over 4GB and you will segfault). A 64-bit system should be fine, in general.

memcached supports threads, thanks largely to Facebook. You probably don’t need this, unless you are Facebook. Memcached’s CPU footprint is tiny.

If you gave memcached 16GB, you will not get your memory back, even if you run flush. The memory is permanently allocated from the OS (much like how Vista does things?). There is mlockall() support, so you can guarantee there will be no paging. Or just disable swap.

jallspaw: memcached1: 22:02:00 up 992 days, 11:57, 0 users, load average: 0.35, 0.37, 0.37

(posted on IRC at #mysqlconf). memcached hardly every crashes.

You can disable the LRU if you want (there’s a command line option for this).

Hashing comes in 2 flavours - normal and consistent hashing. All drivers support CRC today.

A consistent hash means, that instead of doing a modular divide, you can interlace among many servers across the network. When you have a 100 servers running and add a server into the network, you want to add a server, and not lose the entire cache network at once.

libmemcached can do replicas, so it can take data from servers, and apply it to the ring. So if a server is taken out of the network, it can be found elsewhere on the ring. You can keep these networks up and running, and easily growing, with new servers, without losing cache coherency.

Don’t only look at the return value, look at the fact that zero may actually be a credible value, even. An actual value of zero, versus a “we didn’t find anything” is very different.

Slide 35, the ghetto locking implementation for memcache-client. Creates a pseudo-lock around a process. You’re the only process thats processing this area, so you add a key lock, where you ensure you test for nil, not zero (you’re testing for the existence of the lock). If your process dies, someone else will try in 30 seconds (lock expire). Add will only work if there’s no key existing at that point (remember, an add is not a set).

PHP is probably the best supported language, for memcached. PECL memcached library is C backed, standard, and works fine. libmemcached will probably take over most of its features, eventually, but its not there yet now.

Default, if you call increment by a key, it bumps by one. You can also step it up instead of 1, say like 500 or something. Refer to slide 41. Just like you can increment a key, you can decrement also.

C/libmemcached. C driver, there’s a C++ wrapper. Sync and async cached keys. It supports replication through the network. Has read through cache support.

You can not only store a value, but you can also store flags. Flags to keep track of generations, keep track of MIME type internally (so not only store object type, but MIME type). This is unique for libmemcached. Most other drivers use this flags value to see if its compressed or not (the flag = 1 for compression, 0 for no).

Multiget is 7-9x faster than just a get. Look at Page 48 for an example.

Memcached for MySQL? Uses the UDF API. You can now incorporate most of the memcached stuff, in the SQL server, so you can do deletions and get operations easily.

http://tangent.org/586/Memcached_Functions_for_MySQL.html

What do you think about persistent connections? Use them. libevent supports them.

Spaces to watch: MogileFS. HyperTable. HBase. People have stopped talking about POSIX filesystems, and are more talking about object filesystems. Its what all the cool kids are doing.

Technorati Tags: memcached, libmemcached, brian aker, alan kasindorf, dormando, krow, livejournal, grazr, mysql, mysqluc08, mysqluc2008