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 …

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 …

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 …

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.

Formed a MySQL Quiz team
Met all the requirements for the MySQL Quiz
Took a Certification exam

everyone root for Team Prokrasti Nation!

There are over 1600 people registered for the conference, a record for the conference (this is the 4th year)!

“It’s fun to be a bus driver when the bus is full” says Marten Mickos, CEO of MySQL. 1 billion in hte ‘net, 2 billion with a mobile phone, so there’s a lot of connectivity out there. 6.6 billion people in the world, so 15% are online (1 billion). And there are 20 million MySQL downloads per year, with 1/2 that remaining active. So what happens when the online population doubles?

Corporations are doing the same thing consumers are, and adopting for enterprise use. Corporate functions are hosted or put on-premise, because of the consumer experience (ie, google search).

Humans tend to overestimate the short term, and underestimate the long term. The internet has risen slowly since the bubble burst (which was the “hey, the short term isn’t meeting our expectations!”), and we’ve underestimated that. …

This happened to me 2 weeks ago on a site I run pro bono, and I forgot to blog about it. A comment in the “MySQL Automated Failover with Scripts” BOF reminded me about it, so here goes.

Basically, the web application stopped being able to talk to the database. I run the staging/test server AND the database, and the production site is run by someone else. So I checked out the logs, and indeed, there are many of the following:

050814 18:16:42 Aborted connection 241474 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got an error reading communication packets)
050814 23:58:43 Aborted connection 241487 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got timeout reading communication packets)

(and of course going up through the time it crashed, and even today). I believe –log-warnings is ON by default, because it’s not in my my.cnf and show variables shows that it’s on. But if …

Advanced Stored Routines techniques

Higher-order functions in math and computer science are functions which:
take one or more functions as an input
OR
output a function
in MySQL, we’re talking about stored routines that create other stored routines.

Why?
Pivot tables,
Global table checksums
Getters and setters
Multi-row insert query makers
Iterators
Dynamic functions allow you to access data from INFORMATION_SCHEMA and create dedicated dynamic routines and overcome syntax that doesn’t accept variables.

Officially, MySQL stored routines can’t do it.

However, Guiseppe is a really smart guy (as I’m learning firsthand this week)…

One way:
You can create a text definition for a stored procedure from a routine, even though you can’t actually create the stored procedure. Get the text back into the …

Why is this needed?
“world” database current sample is 3 tables, cities, countries and langs.
Not the Intellectual Property of MySQL, so it can’t be bundled with the software
No table type diversity, not a lot of data type diversity
No MySQL 5 features
Not a lot of data
Not really used in tutorials and articles because it’s not robust enough

Scenario
Basically, needed a sample database that was new, that allowed for many queries.
Used an idea from a Dell Sample DB
It’s 2 stores, in 2 different countries.

Designing the Database
Designed with MySQL Query Browser
Normalized
Community provided feedback and content
Trick was balance between simplicity and completeness
Learning db, so people need to understand it
After schema was approved, data was loaded
Stored …

MySQL Performance Tuning with Jay Pipes (MySQL Users Conference Workshop).

Standing room only — who’dve thunk performance tuning was so important!?!?!? (< / sarcasm>). Seriously though, there was a lot of typing happening.

Benchmark:
Get a baseline
Give yourself a target (”what’s good enough?”)
Change one thing at a time
Record everything (even the ‘trivial’ stuff)
Disable the query cache.

Profiling:
Profiling a currently running system (vs. benchmarking, on test)
EXPLAIN SELECT
slow query logs (mysqldumpslow)
low hanging fruit (you figure out what they are, if you’re a DBA it might be putting an index, if you’re a developer maybe it’s changing a query) and diminishing returns
mytop to catch …