Skoll is a Community-Based Testing project out of the University of Maryland. Their first testing framework comes for MySQL. Watch Sandro Fouché, graduate researcher on this project, take you through what Skoll is, how it's beneficial, and how you can use it with an actual demo. The Skoll testing client for MySQL can be downloaded here:
http://www.cs.umd.edu/projects/skoll/contribute/

The video can be played or downloaded using the "play" or "download" link from the original article at http://www.technocation.org.

MySQLCamp is a free event available to the public, though geared towards the MySQL Community. There is no fee for any participant, and workshops are presented by participants and chosen by the community.

Last year, Google was kind enough to sponsor all of the logistics, from food to meeting space. This year, Polytechnic University is providing the location -- and we're opening up sponsorship for the rest!

Technocation, Inc. -- a US not-for-profit providing educational resources for IT professionals --  is sponsoring a donor campaign for MySQLCamp II, to take place from August 23-24, 2007 at Polytechnic University in Brooklyn, NY, USA.

By sponsoring MySQL Camp, you will not only help out the community and get a tax deduction, but your name and company's name will be mentioned throughout MySQL Camp, on the MySQL Camp website, and you will be allowed to have a banner ad on www.technocation.org. You will be contacted at your e-mail address to discuss banner details.

Technocation, Inc. is a not-for-profit organization. Your contributions are tax-deductible to the fullest extent of the law. Proof of donation will be mailed. Money may be donated through PayPal by sending payment to donate@technocation.org, or by using the links below.

Gowanda Level, $100

Ecco Level, $250

Flipper Level, $500

Dan Marino Level, $1000

Sakila Level, $2000 

To send a check or money order by mail:

MySQLCamp II Campaign
c/o Technocation, Inc.
PO Box 380221
Cambridge, MA 02238
United States

Technocation's EIN/Tax ID is 20-5445375

For folks to know -- to create the page with links to conference material, I took the slides from the O’Reilly official page, combines it with the myriad of “here are my slides” posts to Planet MySQL, and links to Baron, Kevin and Mike’s audio and video as well as the video and audio I processed (Because Baron made statements about bandwidth, I downloaded the .ogg files and technocation.org is hosting them, whereas Kevin and Mike’s files are linked to).

I know I hate going to 20 places to find everything I want. There’s no need for folks to have to go to more than one site, just because the content was provided by more than one person. The referenced page is one-stop shopping, as I feel it should be. If folks have anything to add (or change), I’m happy to update the page, just comment here.

So, I have updated the page at:

http://technocation.org/content/2007-mysql-user-conference-and-expo-presentations-and-videos

that fixes a link, adds some slides, and also adds the Quiz Show footage that I have. I got a late start to the Quiz Show, but I did get it in time to catch Solomon Chang's infamous "Coder McKinnan o' The Cubicles". Sadly, I did not have video of the dance, but you can see that at http://people.warp.es/~nacho/blog/?p=225 -- that post also contains a comment by Solomon himself with the lyrics (which he also sent to me, but I'm a bad videographer and took too long to process all the video...).

(updates to the page: Fixed the "Clash of the Database Egos" wmv link, thanks to Hakan Kücükyilmaz for pointing out the brokenness. Added the link to download slides (pdf and swf) for the SQL Kitchen talk, courtesy of Damien Seguy. Added the Quiz Show and links to audio and video.)

Go forth and enjoy!

http://technocation.org/content/2007-mysql-user-conference-and-expo-presentations-and-videos

Need I say more? Go download the slides, video and audio from the 2007 MySQL Users Conference & Expo. I have no plans to take anything down, so please download wisely, and take only what you need. If there's demand, I can make higher-quality versions available. I can also burn DVD's of the content if that's desired.

Enjoy!

Today I upgraded the blog software at sheeri.com (and sheeri.net and sheeri.org). Please let me know if you find something that doesn't work as expected -- awfief@gmail.com.

At the MySQL Users Conference, my good friend Mark Atwood (creator of the free Amazon S3 Storage Engine) mentioned that any site with a login should have OpenID as an option.

Mark, I upgraded for you! I was using Wordpress 1.5.2, now I'm at the "latest" version. Anyway, this is just to let folks know that if you so choose, you may now use OpenId if you wish to login and make comments.

Of course, I do not require login (and have a great spam filter) so that's just another choice you have.

It's not easy to do a DW in MySQL -- but it's not impossible either. Easier to go to Teradata than to write your own.

DW characteristics:

1) Organic, evolves over time from OLTP systems -- issues, locking, large queries, # of userss.

2) Starts as a copy of OLTP, but changes over time -- schema evolution, replication lag, duplicate data issues

3) Custom -- designed from the ground up for DW -- issues with getting it started, growth, aggregations, backup.

4) How do you update the data in the warehouse? -- write/update/read/delete, write/read/delete, or write only -- which means that roll out requires partitions or merge tables.

The secret to DW is partitioning -- can be based on:
data -- date, groups like department, company, etc.
functional -- sales, HR, etc.
random -- hash, mod on a primary key.

You can partition:
manually -- unions, application logic, etc.
using MERGE tables and MyISAM
MySQL 5.1 using partitions

You can load, backup and purge by partition, so perhaps keeping that logic intact -- if it takes too much work to load a partition because you've grouped it oddly, then your partitioning schema isn't so great.

Make sure your partitioning is flexible -- you need to plan for growth from day 1. So don't just partition once and forget about it, make sure you can change the partitioning schema without too much trouble. Hash and modulo partitioning aren't very flexible, and you have to restructure your data to do so.

Use MyISAM for data warehousing -- 3-4 times faster than InnoDB, data 2-3 times smaller, MyISAM table files can be easily copied from one server to another, MERGE tables available only over MyISAM tables (scans are 10-15% faster with merge tables), and you can make read-only tables (compressed with indexes) to reduce data size further. ie, compress older data (a year ago, or a week ago if it doesn't change!)

Issues for using MyISAM for DW -- Table locking for high volumes of real-time data (concurrent inserts are allowed when there is ONLY insertions going on, not deletions). This is where partitioning comes in! REPAIR TABLE also takes a long time -- better to backup frequently, saving tables, loadset and logs, and then instead of REPAIR TABLE do a point-in-time recovery. For write-only DW, save your write-loads and use that as part of your backup strategy.

Deletes will break concurrent inserts -- delayed inserts still work, but they're not as efficient. You also have to program that in, you can't, say, replicate using INSERT DELAYED where the master had INSERT.

[Baron's idea -- take current data in InnoDB format, and UNION over other DW tables]

No index clustering for queries that need it -- OPTIMIZE TABLE will fix this but it can take a long time to run.

When to use InnoDB -- if you must have a high volume of realtime loads -- InnoDB record locking is better.

If ALL of your queries can take advantage of index clustering -- most or all queries access the data using the primary key (bec. all indexes are clustered together with the primary key, so non-primary key lookups are much faster than regular non-primary key lookups in MySIAM). BUT this means you want to keep your primary keys small. Plus, the more indexes you have, the slower your inserts are, and moreso because of the clustering.

MEMORY storage engine: Use it when you have smaller tables that aren't updated very often; they're faster and support hash indexes, which are better for doing single record lookups.

Store the data for the MEMORY engine twice, once in the MEMORY table and once in MyISAM or InnoDB, add queries to the MySQL init script to copy the data from the disk tables to the MEMORY tables upon restart using --init-file=< file name >

ARCHIVE storage engine -- use to store older data. More compression than compressed MyISAM, fast inserts, 5.1 supports limited indexes, good performance for full table scans.

Nitro Storage Engine -- very high INSERT rates w/ simultaneous queries. Ultra high performance on aggregate operations on index values. Doesn't require 64-bit server, runs well on 32-bit machines. High performance scans on temporal data, can use partial indexes in ways other engines can't. http://www.nitrosecurity.com

InfoBright Storage Engine -- best compression of all storage engines -- 10:1 compression, peak can be as high as 30:1 -- includes equivalent of indexes for complex analysis queries. High batch load rates -- up to 65GB per hour! Right now it's Windows only, Linux and other to come. Very good performance for analysis type queries, even working with >5TB data. http://www.infobright.com

Backup -- For small tables, just back up. Best option for large tables is copying the data files. If you have a write-only/roll out DB you only need to copy the newly added tables. So you don't need to keep backing up the same data, just backup the new stuff. Or, just save the load sets. Just backup what changes, and partition smartly.

Tips:
Use INSERT . . . ON DUPLICATE KEY UPDATE to build aggregate tables, when the tables are very large and sorts go to disk, or when you need it real time.

Emulating Star Schema Optimization & Hash Joins -- MySQL doesn't do these, except MEMORY tables can use has indexes. So use a MEMORY INDEX table and optimizer hints to manually do a star schema optimized hash join. Steps:

1) Create a query to filter the fact table
to select all sales from week 1-5 and display by region & store type:

SELECT D.week, S.totalsales, S.locationID, S.storeID
FROM sales S INNER JOIN date D USING (dateID)
WHERE D.week BETWEEN 1 AND 5;

Access only the tables you need for filtering the data, but select the foreign key ID's.

2) Join the result from step 1 with other facts/tables needed for the report

(SELECT D.week, S.totalsales, S.locationID, S.storeID
FROM sales S INNER JOIN date D USING (dateID)
WHERE D.week BETWEEN 1 AND 5) AS R
INNER JOIN location AS L ON (L.locationID=R.locationID) INNER JOIN store AS S ON (S.storeId=R.storeId);

3) Aggregate the results

(SELECT D.week, S.totalsales, S.locationID, S.storeID
FROM sales S INNER JOIN date D USING (dateID)
WHERE D.week BETWEEN 1 AND 5) AS R
INNER JOIN location AS L ON (L.locationID=R.locationID) INNER JOIN store AS S ON (S.storeId=R.storeId)
GROUP BY week, region, store_type;

Critical configuration options for DW -- sort_buffer_size -- used to do SELECT DISTINCT, GROUP BY, ORDER BY, UNION DISTINCT (or just UNION)

Watch the value of sort_merge_passes (more than 1 per second or 4-5 per minute) to see if you need to increase sort_buffer_size. sort_buffer_size is a PER-CONNECTION parameter, so don't be too too greedy.....but it can also be increased dynamically before running a large query, and reduced afterwards.

key_buffer_size - use multiplekey buffer caches. Use difference caches for hot, warm & cold indexes. Preload your key caches at server startup. Try to use 1/4 of memory (up to 4G per key_buffer) for your total key buffer space. Monitor the cache hit rate by watching:

Read hit rate = key_reads/key_read_requests
Write hit rate = key_writes/key_write_requests
Key_reads & key_writes per second are also important.

hot_cache.key_buffer_size = 1G
fred.key_buffer_size = 1G
fred.key_cache_division_limit = 80
key_cache_size = 2G
key_cache_division_limit = 60
init-file = my_init_file.sql

in the init file:

CACHE INDEX T1,T2,T3 INDEX (I1,I2) INTO hot_cache;
CACHE INDEX T4,T5,T3 INDEX (I3,I4) INTO fred;
LOAD INDEX INTO CACHE T1,T3 NO LEAVES; -- use when cache isn't big enough to hold the whole index.
LOAD INDEX INTO CACHE T10, T11, T2, T4, T5

http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html

This was implemented in MySQL 4.1.1

Temporary table sizes -- monitor created_disk_tmp_tables -- more than a few per minute is bad, one a minute could be bad depending on the query. tmp tables start in memory and then go to disk...increase tmp_table_size and max_heap_table_size -- can by done by session, for queries that need >64MB or so of space.

ALWAYS turn on the slow query log! save them for a few logs, use mysqldumpslow to analyze queries daily. Best to have an automated script to run mysqldumpslow and e-mail a report with the 10-25 worst queries.

log_queries_not_using_indexes unless your DW is designed to use explicit full-table scans.

Learn what the explain plan output means & how the optimizer works:
http://forge.mysql.com/wiki/MySQL_Internals_Optimizer

Other key status variables to watch
select_scan -- full scan of first table
select_full_join -- # of joins doing full table scan 'cause not using indexes
sort_scan -- # of sorts that require
table_locks_waited
uptime

mysqladmin extended:
mysqladmin -u user -ppasswd ex =i60 -r | tee states.log | grep -v '0'

(runs every 60 seconds, display only status variables that have changed, logs full status to stats.log every 60 seconds).

There are too many Japanese characters to be able to use one byte to handle all of them.

Hiragana -- over 50 characters

Katakana -- over 50 characters

Kanji -- over 6,000 characters

So the Japanese Character set has to be multi-byte. JIS=Japan Industrial Standard, this specifies it.

JIS X 0208 in 1990, updated in 1997 -- covers widely used characters, not all characters
JIS X 0213 in 2000, updated in 2004

There are also vendor defined Japanese charsets -- NEC Kanji and IBM Kanji -- these supplement JIS X 0208.

Cellphone specific symbols have been introduced, so the # of characters is actually increasing!

For JIS X 0208, there are multiple encodings -- Shift_JIS (all characters are 2 bytes), EUC-JP (most are 2 bytes, some are 3 bytes), and Unicode (all characters are 3 bytes, this makes people not want to use UTF-8 for ). Shift_JIS is most widely used, but they are moving to Unicode gradually (Vista is using UTF-8 as the standard now). Each code mapping is different, with different hex values for the same character in different encodings.

Similarly, there are different encodings for the other charsets.

MySQL supports only some of these. (get the graph from the slides)

char_length() returns the length by # of characters, length() returns the length by # of bytes.

The connection charset and the server charset have to match otherwise...mojibake!

Windows -- Shift_JIS is standard encoding, linux EUC-JP is standard. So conversion may be needed.

MySQL Code Conversion algorithm -- UCS-2 facilitates conversion between encodings. MySQL converts mappings to and from UCS-2. If client and server encoding are the same, there's no conversion. If the conversion fails (ie, trying to convert to latin1), the character is converted to ? and you get mojibake.

You can set a my.cnf paramater for "skip-character-set-client-handshake", this forces the use of the server side charset (for the column(s) in question).

Issues:

Unicode is supposed to support worldwide characters.

UCS-2 is 2-byte fixed length, takes 2^16 = 65,536 characters. This is one Basic Multilingual Plane (BMP). Some Japanese (and Chinese) characters are not covered by UCS-2. Windows Visa supports JIS X 0213:2004 as a standard character set in Japan (available for Windows XP with the right )

UCS-4 is 4-byte fixed length, can encode 2^31 characters (~2 billion) This covers many BMP's (128?)

UTF-16 is 2 or 4 byte length, all UCS-2 are mapped to 2 bytes, not all UCS-4 characters are supported -- 1 million are. Supported UCS-4 characters are mapped to 4 bytes

UTF-8 from 1-6 bytes is fully compliant with UCS-4. This is out of date. 1-4 byte UTF-8 is fully compliant with UTF-16. From 1-3 bytes, UTF-8 is compliant with UCS-2.

MySQL interally handles all characters as UCS-2, UCS-4 is not supported. This is not enough. Plus, UCS-2 is not supported for client encoding. UTF-8 support is up to 3 bytes -- this is not just a MySQL problem though.

CREATE TABLE t1 (c1 VARCHAR(30)) CHARSET=utf8;
INSERT INTO T1 VALUES (0x6162F0A0808B63646566); -- this inserts 'ab' + 4-byte UTF-8 translation of cdef

SELECT c1,HEX(c1) from t1;
if you get ab,6162 back it means that the invalid character was truncated. MySQL does throw up a warning for this.

Possible workarounds -- using VARBINARY/BLOB types. Can store any binary data, but this is always case-sensitive (and yes, Japanese characters do have case). FULLTEXT index is not supported, and application code may need to be modified to handle UTF-8 -- ie, String.getBytes may need "UTF-8" parameter in it.

Alternatively, use UCS-2 for column encoding:

CREATE TABLE t1 (c1 VARCHAR(30)) CHARSET=ucs2;

INSERT INTO t1 VALUES (_utf8 0x6162F0A0808B63646566);

SELECT ... now gives you ?? instead of truncating.

Another alternative: use Shift_JIS or EUC-JP. Code conversion of JIS X 0213:2004 characters is not currently supported.

Shift_JIS is the most widely used encoding, 1 or 2 byte encoding. All ASCII and 1/2 width katakana are 1-byte, the rest are 2-byte. If the first byte value is between 0x00 and 0x7F it's ASCII 1 byte, 0XA0 - 0XDf is 1-byte, 1/2 width katakana. all the rest are 2-byte characters.

The 2nd byte might be in the ASCII graphic code area 0x40 for example.

0x5C is the escape sequence (backslash in the US). Some Shift_JIS characters contain 0x5C in the 2nd byte. If the charset is specified incorrectly, you'll end up getting different values -- for instance, hex value 0X5C6e will conver to hex value 0X0A. The backslash at the end of the string, hex value 0X5C, will be removed (truncated) if charset is specified incorrectly.

Native MySQL does not support FULLTEXT search in Japanese, Korean and Chinese (CJK issue).

Japanese words do not delimit by space, so it can't work. 2 ways to do this: dictionary based indexing, dividing words using a pre-installed dictionary. Also N-gram indexing -- divide text by N letters (n could be 1, 2, 3 etc). MySQL + Senna implements this, supported by Sumisho Computer Systems.

If you can find me today during the MySQL Conference & Expo, I have a limited amount of DVD's that contain all 15 podcasts on them. If you have been thinking you wanted to listen to the podcast but haven't gotten around to downloading the episodes yet, here's your chance! Just find me -- Today I'm in a red top and black skirt....

In this episode, we take a walk through the Expo part of the MySQL Conference and Expo. We spoke with 3 companies about their solutions for backup and reporting.

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Download all podcasts at:
http://technocation.org/podcasts/oursql/

R1 Soft
http://r1soft.com

Actuate
http://www.actuate.com/birt
or
http://www.eclipse.org/birt

FiveRuns
http://www.fiveruns.com

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Or use the Technocation forums:
http://technocation.org/forum

Tony (my fiancee) says it best -- he's an amazing writer. For some history, I work for a dating site that caters to gay men:

MySQL is database software. Whenever a computer program or system (like, say, a gay man's online dating service) needs to randomly access, store, and keep track of a bunch of data "stuff" (like, say, a bunch of fruits, their personal information and, uh, "vital statistics"), it puts it into and maintains a database.

MySQL (http://www.mysql.com) is a very popular, very good database system. It's the one Sheeri uses at her job, and my company is currently test-driving a new way to put together web sites that relies on a MySQL database.

An interesting feature of it is that it's what's called "open source" software. That means that the community of users is also largely the community of developers. Anyone using the software who says, "You know, I'd really like if it did this better" and figures out a way to do it can actually change the software and then tell everyone, "Hey, I did this thing to it". Or you can just say, "Hey, I figured out how to do this other thing with it" and every learns a new thing. It's very socialist.

So, Sheeri works with MySQL, started up and runs the Boston MySQL users group, and runs a podcast detailing and sharing her expertise and experience using MySQL. Plus, she's actually giving a couple of workshops at this conference she's at:

http://mysqlconf.com/cs/mysqluc2007/view/e_spkr/2731

Hence, she is a MySQL community advocate. In fact, the 2007 MySQL Community Advocate of the Year. It didn't come with an oversized novelty check, but it's all very computer geek sexy. It means my wife-thing is very good at what she does and people respect her intelligence and her efforts. Meanwhile, her husband-lump-thing knows how to make playing cards appear in his pockets and occasionally leaves his shoes on the bed. But he's pretty.

Click on an image for a larger picture:

"What societies value is what they memorize. And how they memorize it and who has access to its memorized form determines who has power."

We're starting to become a society that "memorizes" private facts -- not just public records being written down, but private thoughts, dreams and wishes.

"Living largely in a world of expensive written material and seeking to build a private database of things experienced and learned, early modern Europeans built in their minds memory palaces -- imaginary rooms furnished with complex bric-a-brac and decorations. . . By walking through the rooms of the memory palace in their minds, [they] remembered things they needed to know."

Photographs took a factual and emotional snapshot of experience and put it into a form that could be held and shared, unlike a memory palace.

"The private photograph isn't private any more."

I can't do Eben's speech justice. I will post the video when I get to it, but he's really saying some great stuff about privacy.

Technocation is proud to announce its first grant to help further the goals of IT professionals. We have helped Proven Scaling's "Free Ride" to give three people all-expense paid trips to the MySQL conference happening at the end of April. We are proud to have been able to grant Proven Scaling $300 to help, and we hope this is the first of many monetary grants we will give.

Congratulations to the Free Ride winners:
Jan Lehnardt, a student from Münster, Germany; J.R. Bullington, from a non-profit in Sterling Heights, Michigan, USA; and Carlos Proal Aguilar, from a non-profit in Puebla, Mexico. For more details on the contest winners, see Proven Scaling's announcement at http://jcole.us/blog/archives/2007/03/31/mysql-conference-expo-free-ride-winners/

This grant was made possible by everyone who donated to the MySQL Conference Scholarship Fund, announced January 30th, 2007 at http://technocation.org/content/donate-mysql-users-conference-scholarship-fund%21

If you would like to donate to Technocation, please visit our donation page at http://technocation.org/content/donate-now. Your contribution is tax-deductible to the fullest extent of the law. Technocation is a not-for-profit organization incorporated in Massachusetts, USA, dedicated to providing resources and grants for IT professionals. Learn more about us by visiting Technocation's FAQ page at: http://technocation.org/category/topics/faqs.

This week I spoke with Jay Pipes about the upcoming MySQL Conference, April 23-26 in Santa Clara, California, USA.

Direct play the podcast here:
http://technocation.org/content/oursql-episode-9%3A-jay-speaks-about-mysql-conference

Subscribe to the podcast by clicking:
http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewPodcast?id=206806301

You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/

I had a setting wrong in my recording program and ended up having a very different sound quality than what I had wanted. I played with it as much as I could, but I apologize for the bad quality audio, especially the breathing that you can hear, the mouse clicks, and when you can hear me swallow and such. (It's probably not as bad as it sounds, but I shudder at it all, because I want to provide y'all with the best quality possible).

On with the show notes!

Tutorials are 3 hours long (and there are two that have two parts each). The tutorial links and descriptions are here. Tutorials in bold are ones that were mentioned in the podcast:
http://www.mysqlconf.com/pub/w/54/tutorials.html

MySQL Cluster: The Complete Tutorial, Part I
MySQL Cluster: The Complete Tutorial, Part II

MySQL Replication: The Complete Tutorial, Part I
MySQL Replication, The Complete Tutorial, Part II

MySQL 5.0 DBA I Certification Primer
MySQL 5.0 DBA II Certification Primer

Managing Hierarchical Data in MySQL: The Extended Director's Cut
Vital Rails: An Introduction to the Ruby on Rails Framework
Scaling and High Availability Architectures
MySQL Cluster Certification Primer
MySQL 5.1 In-depth
Writing Your Own Storage Engine
Wikipedia: Site Internals, Configuration and Code Examples, and Management Issues
Real-World MySQL Performance Tuning
MySQL Network Monitoring and Advisory Services: from Soup to Nuts

Registration (and information about discounts, including the $200 discount if you register by March 14th)
http://www.mysqlconf.com/pub/w/54/register.html

There are too many sessions to list, however the conference home page at http://www.mysqlconf.com (scroll to the bottom), and the links take you to a page with all the sessions in that track.

We spoke on the podcast about serving three audiences: DBA's, Developers, and the General Audience. I've taken the liberty of grouping the tracks:

DBA:
Architecture and Technology
Performance Tuning and Benchmarks
Data Warehousing and Business Intelligence
Migration
MySQL Cluster and High Availability
Replication and Scale-Out
Security and Database Administration

Developers:
Java
LAMP
.NET/Windows
PHP and MySQL
Ruby and MySQL

General Audience
Business and Case Studies
Storage Engine Development and Optimization
Web 2.0, Ajax, and Emerging Technologies
The General Track

Well, I have a lot to say about MySQL Camp, but for now, I just want to say thank you to Jay Pipes for organizing the Camp, and to Google for hosting and feeding us, and to MySQL for support.

I have a few lost and found items -- if you lost or found something, please contact me by e-mailing me at awfief@gmail.com and I will see to it that you get your item back, or that found items get to the right place.

LOST:
white 4-port USB hub

FOUND:
a charger (looks like it's for a phone, it's small-duty)
small stack of Avery blank printer sheets, for file folder labels
Sunglasses

If you ended up with something that's not yours, or are missing something, contact me and we'll do as much as we can to get items to their owners.