Woo, that was fun! I just finished "my talk":http://mysqluc.com/cs/mysqluc2006/view/e_sess/8294 half an hour ago. (update: I've updated the slides to be a page per, uh, page rather than a page per "build")

I got a couple of "that was the {best,most useful} talk at the conference so far!" comments, so that alone made it worth getting up at 5.45am to fly up here!

The slides from the talk are now on the talks page of my new shiny website that Vani put together for me.

I had about 70 slides for 45 minutes (made it in 47!) so it went pretty fast, but I really think that's a lot more fun. I am sitting in the "InnoDB: Designing and Configuring for Best …

This post dedicated to Edwin DeSouza.

Un-tuned SQL or stored procedures often fail to scale as table volumes increase, inefficiency increases exponentially with size.

Tune SQL/stored procedures and then buy new hardware.

use EXPLAIN to help optimize queries. Also use the slow query log.

EXPLAIN EXTENDED shows sql that was actually used — ie, optimizer may rewrite query, so it’s a neat tool.

you can always give optimizer hints, but they’re not recommended — keep checking them as your app grows — STRAIGHT_JOIN, FORCE INDEX, USE INDEX, and one other one.

SHOW STATUS gives you status variables. innodb_buffer_pool_read_requests and innodb_data_read will show how much data is being read from the buffer pool vs. data.

Index isn’t always used, if more than 20% or …

Ok, so here is a story:

I was standing with Giuseppe Maxia waiting to go into this mornings keynote when a smartly dressed man approached Giuseppe to congratulate him. The reason was that Giuseppe had won an award the day before for service to the MySQL community. So Giuseppe introduced the guy as Marten (the rest of the name I don't get, and he was not wearing a name tag).

Marten seemed to recognize my name and asked if I was a frequent visitor to the conferences. I said, "no, I am the PBXT guy". Thats when he realized why he had heard my name.

So then we chatted a bit, and he asked me how difficult it had been to write the engine, how long it had taken me and what my engine can do well. I said I was more concerned about what it could not do so well then what it could do well, but then I told him what was good in my performance tests, etc.

A little later Giuseppe and I moved into the hall …

Some of these may be conflicting, not applicable to everyone.

1) think horizontal — everything, not just the web servers. Micro optimizations are boring, as or other details
2) benchmarking techniques;. Not “how fast” but “how many”. test force, not speed.
3) bigger and faster vertical scaling is the enemy.
4) horizontal scaling = add another box
5) implementation, scale your system a few times, but scale your ARCHITECTUREa dozens or hundreds of time.
6) start from the beginning with architecture implementation.
7) don’t have “The server” for anything
8) stateless good, stateful bad
9) “shared nothing” good
10) don’t keep state within app server
11) caching good.
12) generate static pages periodically, works well for not millions of pages or changes.
13) cache full output in application
14) include cookies in the “cache key” so …

MySQL today introduced MySQL Forge a new online resource for all MySQL users and developers to communicate, collaborate and share MySQL code and applications. The company also announced new support for Ubuntu Linux. In a keynote address today at the MySQL Users Conference 2006, Mark Shuttleworth, the founder of the Ubuntu project, outlined the new partnership and technology collaboration between Ubuntu and MySQL.

I’ve finished my last of three sessions, finally getting through my hierarchies session. With the laptop failure, speaking just was not as enjoyable this year as in previous years when there were no such concerns to distract me from giving the audience a good session.

The Hierarchies session seemed well received, with the room packed and audience members up against the back wall. I do wish I had not been opposite the Second Life session, as I was quite interested in how they scale.

Materials

Managing Hierarchies

Sakila Sample Database - …

talk by Roland Mallmann

MaxDB is older than I am, in 1977 started at University of Berlin. Owned by SAP today. Today it’s open source under GPL, or commercial license from SAP or MySQL AB.

Why Max DB is so great:
Low cost of ownership
Few config parameters
no size estimates for indvidual db objects

no reorg — space management done automatically — space no longer needed is returned immediately to the db, data occupied vs. free (holes) ration is highest as possible. This is done by matching logical pages to physical on disk with the Converter, and I/O and space management.

Space management done automatically
No reorganization is needed (ie, OPTIMIZE TABLE)
Gaps are not allowed, therefore updates and deletes are in place, and sorts happen AFTER an insertion.
Space freed is immediately returned to DB
Done by Converter, matches …

A Wednesday afternoon presentation at the MySQL Conference was MySQL Migration Toolkit by Mike Zinner from MySQL AB.

In summary, the Migration Toolkit currently provides the following feature set.

• Wizard like interface
• Plug-in architecture
• Migration of Oracle/MS SQL Server/MS Access/Generic JDBC Data Sources. 2006 support for Sybase and DB2.
• Provides user support to expand to other databases
• Customisable the produced wizard scripts at any step
• Completely Scriptable (the wizard interface can produce command line replay scripts) -allows re-running, re-scheduling
• Binaries include an Eclipse Java Project to enable easy extension of the Migration Toolkit.

The product is built on the Generic Runtime …

I was told that teams had to have a physical instantiation of a mascot, so I said, “maybe I’ll knit something.” Well, I didn’t knit something, but I did hand-craft an origami butterfly for Team Prokrasti Nation’s mascot:

(click picture for larger image).

Oh, and I won a fun game from O’Reilly for submitting speaker evaluations.