Category: Backup

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

everyone root for Team Prokrasti Nation!
Formed a MySQL Quiz team
Met all the requirements for the MySQL Quiz
Took a Certification exam

everyone root for Team Prokrasti Nation!
I was told that teams had to have a physical instantiation of a mascot, 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.

State of the industry — everyone wants everything online, with people making $$ from subscription-based services. Free and open-sourced software is the fabric of this world (FOSS).

There’s a lot of competition and freedom, which means technology innovation increases, and people are happy because they can follow their passion. We have to defend this freedom, from patents.

To have a successful open source project, you need modularity, so folks can work on a part of a system. This encourages participation, crucial for success.

Pluggable storage engines mean that it’s easy to add storage engines to do what you as an individual needs. No need to recompile to add a new one, just download it, plug it in….and it’s just that easy to remove it too. Of course, you can build your own, too.

Falcon will probably becfome the best general purpose OLTP storage engine.

MySQL has a very active community! Many LAMP startups, many distributors (Dell, Novell and HP), even Oracle has an open source solution.

Awards: yesterday they did community service awards. Applications of the year have been chosen, too, and they are:

Nokia: World leader in mobile communications, using MySQL Cluster to maintain realtime info about mobile network uers online.

Flickr: Leading Online Photo Management and Sharing

NetQS: Fastest Growing Network Management Company.

Partners of the Year:
Business Objects: Leading Business Intelligence Solutions

Dell: High Performance Computer Systems

Oracle: Maker of storage engine for MySQL (ie, innodb)

Congrats to all!

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 it’s not on, you won’t see those errors.

The answer was to send a FLUSH HOSTS command, which I did, and it worked. Of course I have to figure out what is causing the errors, likely really bad PHP code without sessions and people hitting ESC when loading a page. (that site has a lot of downloaded code)

But I figured I’d blog about it, because it was rare enough to make me notice. This can be the result of a DOS attack, too — but in my case, it wasn’t.

I’m not sure how MySQL figures out how many errors have occurred — the default is 10, and there were more than that in the logs. I’m guessing it’s “10 failed in a row” from the same user@host, but I’m not sure. It’d be great to figure that out, although if your server has log-warnings set to ON, you can check the error logs.

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 does not 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 client, and you’re all set! ie, pass text back to client, which client just passes back to MySQL. (genius, just genius).

ie
CREATE FUNCTION make_f_test()
returns text
return
'CREATE FUNCTION hi()
returns text return "hi" ';

get output of code into a variable and then execute that string.

Another way:
Don’t do this!
not recommended by MySQL AB, may not work in the future, may damage your data.
DON’T USE IT.

That being said, if you get:
ERROR 1457 (HY000):Failed to load routine xxx.yyyy.
The table mysql.proc is missig, corrupt or containts bad data (internal code -6)
then you did it wrong. Fixing this error is NOT trivial.

Create text, like in the last example, and change the mysql.proc table. Close the mysql connection, and then open a new one. When you open a new connection, the existing stored procedures and functions are cached. So you can only make 1 function with the make_routine function per session.

To change the mysql.proc code:
http://www.stardata.it/code/

Install the make_routine function (need full “mysql” db access)
Make a limited user who has access to the lib database.

(the make_routine function takes the following parameters)
database
name
type
params
return_type
actual code

He showed an example: given a set of values, find a record and give the primary key of the record; if the record does not exist, insert it and give the new primary key.

Another example: A function that gets all values for one field from a table and joins into a string (called COLUMN_CONCAT). One can, of course, do this with GROUP_CONCAT but only if the total character count is 1024 (otherwise it truncates it).

1. create a function that accepts paramaters
2. create a string (a ‘template’) with the function with placeholders
3. replace placeholders in template with paramaters (using REPLACE)
4. call make_routine using the template string

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 procedures, triggers, UDF’s and VIEWS were used

Schema
2 files in installation — one for schema & routines and one for data, so you can just look at schema if you want.
16 tables, 8 views,
Data is never deleted — it’s marked as deleted, but not actually deleted, for data integrity reasons.
Contains most (all?) data types, including SET and ENUM.

VIEWS
VIEWS used to simplify queries. ie, there’s a table for actors and one for films, so actor_info contains a list of type of movie and name for each actor. So it would show a list of actors with a list of films that they’ve done — 1 row per actor, and a comma-separated list of films they’ve done (sorted by type).
There are a lot of good views in there, which show that that’s what you should do for popular queries (like “show me all the movies that are rented, by customer”).

Stored Procedures
For common procedures — ie, is a film in stock?

Stored Functions
get_customer_balance — it does what you think it does.

Triggers
There’s one to sync 2 tables, for example.

Licensing
PostgreSQL want to do
Documentation copyright MySQL
BSD licensing

Goals Going Forward
Make as few changes as possible
Implement 5.1 changes if necessary

Download it at: http://www.openwin.org/mike/download/sakila-0.8.zip

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 excessive locking/contention and long-running queries.

Usual problem sources:
Bad indexing choices
too many indexes
missing indexes
Look for covering indexes (indexes where all the info is in the index, so it never has to go to the data for it, only the index)
Order of fields in multi-column index
Ensure good selectivity on index fields (if there is bad selectivity, the optimizer won’t use the index. Bad selectivity is records that are “true” or “false”; “active” or “closed”. If most of the records are one or a few types and others aren’t used much, then it will just do a table scan, if you’re looking for the many data type (ie, “closed” orders when the database is large).
Removing redundant indexes (look for column + multi-column indexes. No need to index “lastname” and “lastname, firstname”)
InnoDB puts the primary key in each index

Not using storage engines effectively, bloated/inefficient schema
Use appropriate datatypes. Do you need a BIGINT for the autoincrement? Index records are narrower with smaller datatypes.
Consider horizontally splitting multi-column tables. If you use some fields but not others, make 2 tables, and index the heck out of the one used more. 🙂
Vertically splitting using MERGE tables or partitioning
InnoDB: choose the smallest possible primary key because it’s appended to EACH secondary index. Also ALWAYS GIVE A PRIMARY KEY because InnoDB is going to put one in.
Don’t use surrogate keys if a natural primary key occurs.

Bad coding practices
Break things down into the smallest chunks possible (that’s a good practice). Break large SELECT statements into smaller queries — are you doing an intersect? a union?)
Use Stored Procedures for BIG performance boost (26%)
InnoDB: use counter tables — ie make a separate table that updates count when a row is inserted or deleted.
Isolate index fields on one side of the table if you have a function. If you want orders from last 7 days, WHERE TO_DAYS(order_created) - TO_DAYS(CURRENT_DATE()) >= 7
can be improved to:
WHERE order_created >= CURRENT_DATE() = INTERVAL 7 DAY
or, even better, put the CURRENT_DATE() into a variable (MySQL or otherwise) and just use that, so the query can be cached.
Use calculated fields: For example, wildcard on left of string can’t use index. WHERE email LIKE "%.mysql.com" won’t use index. But if you add another field, reverse_email, and add the REVERSE(email) into it, and then WHERE reverse_email LIKE "moc.lqsym.%"
Join hints like STRAIGHT JOIN (tells the optimizer what order to use and ignore optimization) should not be used, if they are, they should be re-examined regularly to make sure they’re still valid.
Convert correlated subqueries to a standard join. Use INNER or LEFT JOIN instead. Or, use a derived table — a subquery in the FROM clause.

Server variables not tuned properly — although Jay stated that you should tune the application FIRST (ie, queries) and THEN the server
Know what’s global vs. per thread
Make small changes and test (ie, one var at a time)
Usually temporary solution
Query cache defaults to size 0. Increase if you need to!
key_buffer_size for MyISAM only, innodb_buffer_pool_size is for InnoDB
Put more memory in. Cheapest, fastest, and easiest way to boost performance

Hardware/network bottlenecks

Trivia: MySQL engineers represent 12 countries.

Today’s first session — “The Java Advantage in MySQL” by Philip Antoniades. I’ve seen Philip speak before (at the Boston MySQL User Group) so I knew it was going to be good.

Enterprise level Java
Connector/J
Bulk of the code in Connector/J code is that it’s tested and works with most major application servers.
Pure Java
SSL

Entirely in-house code base (java/mysql)
open source, gpl
created by Mark Matthews
need java 1.4 to compile
small footprint driver

can set up config to cache prepared statements.

Profiling tools for developers:
Time executions
Time prepared statements
log prepare vs. execute occurrences (are you executing more than preparing?)

Debugging tools for developers:
Logging — StandardLogger or roll your own

http://dev.mysql.com/doc/refman/5.0/en/cj-configuration-properties.html

profileSQL — trace execution times, including latency, to logger

bad SQL warnings — useUsageAdvisor

Developer extensions are light, less than 1% additional load

ReplicationDriver — substitute for standard driver for replication/cluster aware apps or use standard with options. Works for asynchronous replication and synchronous clustering. Basic round-robin load balancing. Automated load balancing and automatic failover (works [best] for readonly stuff on replication). If master and 3 slaves and one slave goes down it will automatically use another slave. If cluster, automatic failover, can do sticky or load balanced.

use Connection.setReadOnly(). (ie, I’m doing read only)

Connection con = ....
con.setReadOnly(true);
ResultSet rs = con.createStatement().executeQuery("select * from foo");

can setReadOnly(false) when you want to execute DML.


Driver string goes from
jdbc:mysql:\\DB1\appdb
to
jdbc:mysql\\DB1,slave1,slave2,slave3/appdb

Small Form Java (ie, desktop)
Goals — simplified maintenance, easy deployment, ease of use. instead of using a java db, use mysql with connector/J.

Connector/MXJ = wrapper around MySQL, platform neutral
what does it do?
1 jar file. auto-detects platform, auto-installs MySQL (has copies of ’em all), starts server on command/connect, stops on command/connect, 1-line db delete/server uninstall.

connect using
Connection conn=null;
try { conn=DriverManager.getConnection(url,userName,password)
} catch (Exception never){}


String url="jdbc:mysql:mxj://localhost:" + port + "\test" + "?" + "server.basedir=" + dbDir;

Therefore, easy to embed MySQL in a java application. .jar with Sun, Windows, Linux is 40MB. For smaller, you can make your own .jar.

Can configure to run always or on demand.

Can uninstall/data delete when application terminates (or is deleted).

jUDF
UDF’s in Java — ie, write UDF’s in Java instead of C++.
(would have to install Java server side, of course)
Also has hooks that could be extended to other langs. Imagine writing UDF’s in perl or PHP!
GPL available
Written by MySQL Engineers
linux-only
alpha status
not being worked on now, but you can cast your vote, because the engineers want to work on it (but it’s lower priority).

Future Directions for Java in MySQL
Next JDBC driver version is 5.0, to work with MySQL 5.0.
Will support major java frameworks, ie Struts, Hybernate
MySQL will continue to be the core Open Source java development database

I left the tutorial thinking, “wow! PHP can’t do that!”

Tangent: Carl Zimmer, “Parasite Rex” is a great book about parasites according to Philip.

The Buddha is my DBA

I thought this was neat….

Jay Pipes (co-author of Pro MySQL) is in town and will speak about “MySQL Performance Tuning Best Practices”. This is the workshop he’ll be giving at the MySQL Users Conference, so if you can’t go don’t miss this user group meeting! There will be FREE pizza and soda.

We will have giveaways of Pro Mysql and gift certificates for free Apress books, and other swag like T-shirts, buttons, etc. RSVP for a headcount of soda and pizza at http://mysql.meetup.com/137/events/4875276/ (you will have to register; sorry…. 🙁 ) Mike Kruckenberg will be there, so if you want your copy (or the copy you win!) signed by both authors, you can.

Description of the workshop: Learn where to best focus your attention when tuning the performance of your applications and database servers, and how to effectively find the “low hanging fruit” on the tree of bottlenecks. It’s not rocket science, but with a bit of acquired skill and experience, and of course good habits, you too can do this magic! Jay Pipes is MySQL’s Community Relations Manager for North America.

We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.

If you join the user group on the Meetup site, you’ll get these messages automatically and great things like 30% discounts on O’reilly books, discounts to conferences, etc. It’s OK to repost/forward this message.

Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg ?selection=E51&Buildings=go

This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg ?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)

Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg ?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg ?selection=P5&Parking=go

Pizza and soda will be served, so please RSVP accurately.

When:
Monday, April 10, 2006, 7:00 PM 2006-04-10 07:00:00
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117