Dave's Stuff

Wordpress from a MySQL DBA view

2011-11-18T13:51:00.000-08:00

Wordpress is a very popular Content Management System, usually built on top of a MySQL Database. Recently I attended a local Wordpress user group an that started me wondering what the database tables look like. So I installed a new Wordpress installation on a Ubuntu test box running MySQL 5.6.2.

Wordpress creates eleven tables: wp-commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms, wp_usermeta, and wp_users.

MySQL Workbench generated an EER Model you can see nearby. The first thing that caught me eye was the use of unsigned BIGINTs in every table as keys. Usually you want to use the smallest data type you can for keys. The smaller the key, usually the fast the system can evaluate it. But then maybe there are blog out there that need 18,446,744,073,709,551,615 posts or authors or links.

Hmm, can I start a side business making Wordpress faster by converting all those BIGINTs for those who only to expect 4,294,967,295 posts??

Since I am using MySQl 5.6, the table formats defaulted to InnoDB. Some plugins for Wordpress require full-text search indexes which is now available in the 5.6 preview release.

MySQL for Oracle DBAs, or How to Speak MySQL for Beginners

2011-08-31T12:49:00.000-07:00

I will be speaking at Oracle Open World on MySQL for Oracle DBAs, or How to Speak MySQL for Beginners on Tuesday, October 4th in the Golden Gate B room in the Marriot Marquis. And at the same time down the hall, Sunny Bains will be presenting on InnoDB Performance Tuning. And just a short time later there will be a MySQL Community Reception.

Bem-vindo ao Meetup MySQL Brasil!

2011-08-14T19:20:00.000-07:00

For those of you in around Sao Paulo this week, Aiton Lastori send me the following:

Next week we will host a MySQL Happy Hour at Oracle's Sao Paulo office.
We are exited because there are room for 100 people and over 500
subscribed! Oracle marketin provided the money for the free beer and Ana, our lead dev in Brazil, and is doing an amazing job organizing
everything. I've created a group in meetup.com http://www.meetup.com/MySQL-BR/.

FISL12 -- Open Source show with Music and Dancing in Brazil.

2011-07-05T08:37:00.000-07:00

Not so long ago, it was easy to judge a technical conference by the number of small screwdrivers¹ you picked up as swag from the show. Open source shows usually have similar elements in common with each other like someone in a penguin costume, at least one guy in a kilt², piles of Linux distro CDROMs, and bonus points for either Mad Dog or Linus in person. Last week I was in Porto Alegre, Brazil for FISL where they had all that plus folk songs and dancing³. They really raised the bar for open source shows.

The MySQL Community is large, vibrant, and inquisitive. Davi Arnaut, one of our developers who lives in the North of Brazil, presented on MySQL 5.5 and 5.6 to a full room. I was in the Oracle booth answering questions about new features, workbench and the Enterprise edition. During the four days, there was a constant flow to the booth and we got to meet many developer and students who rely on MySQL.

I enjoyed meeting all the 'MySQ-elle' users at FISL.

And next year I will have more MySQL swag for the attendees. The stickers and t-shirts were popular and went quickly.

Philips head, flat head or the rare Allen head.
Usually a Ultilikilt
Most of the show attendees on the floor were gathered around the booth for a local restaurant belting out songs.

Performance tuning -- option files from the distribution and a simple benchmark

2011-05-20T13:27:00.000-07:00

Hopefully you are caught up on the recommended reading from the last post as today we will look at creating a simple benchmark using a common tool to determine which option file to use. The MySQL server will use default values if it can not find an option file. Under the /usr/local/mysql/support-files for MySQL 5.6 are eight¹
sample configuration files that can be renamed and dropped in place as a staring point. See the Using Option File page for details.

But these files have not kept pace with hardware. For instance, a small system is listed as being under 64m of memory while huge is 1-2G. More modern settings will be covered in a later post but we can use these files in a simple test on a fairly simple test machine².

So how do we know which configuration is the best for the environment? A simple test will be run that accesses the database on the system and the time will be recorded. Usually you want to use a test that as closely mimics your 'normal' load but this is an introductory series and we will grow into more rigorous tests. Testing these various configuration files was done with mysqlslap and the next post will go into details on mysqlslap and how to use it with queries from your logs.

A simple mysqlslap test was run³ and the times recorded. The various option files were copied to /etc/mysql/my.cnf, mysqld_safe --user=mysq& was used to start the server, and then the test was run. We change one variable, the option file, repeat the test, and not the differences.

Config file	Avg Run Time
None	1.362
Small	1.604
Medium	1.373
Large	1.348
Huge	1.337
InnoDB Heavy	1.526

So the system with 1.5G of memory runs best on the option file designed for system with 1-2G of memory! That result will probably not shock too many people. This was an overly simple example and next time we will go a bit deeper on using mysqlslap and then start to look at some other tools. Then we will create a simple web based application for testing.

To be continues

Next time: Using your data with mysqlslap

Three files for Windows: config.small.ini, config.medium.ini, and config.huge.ini and five for UNIX-like systems: my-small.cnf, my-medium.cnf, my-large.cnf, my-huge.cnf, and my-innoidb-heavy-4g.cnf
The test box is a IBM Thinkpad R50e that I use for testing and runs a recent version of Fedora with 1.5G of memory.
mysqlslap --auto-generate-sql --concurrency=100

Performance tuning basics

2011-05-19T11:58:00.000-07:00

I have been getting a lot of questions about performance tuning this month. So this will be the first in a series on the basics.

Go get your copies of High Performance MySQL: Optimization, Backups, Replication, and More and MySQL Administrator's Bible¹ and read up on benchmarking. Also take a peek at George Trujillo's MOCA. These readings will provide you with a overview of the methods and approaches you will see in later entries. The goal is to get a baseline measurement so that a change can be evaluated as an improvement or a determent.

Then do a fresh install of MySQL server on a non production system.

And lastly get a copy of your new installation's option file, usually my.cnf or my.ini for Windows systems. But do not be surprised if your system does not have one.

To be continued

Next time: A simple test using mysqlslap

1. You ~~should have~~ need to have copies of these books in your library.

MySQL Lunch-n-Learn

2011-05-06T13:10:00.000-07:00

MySQL Lunch-n-Learn is not a new performance schema table or a new BLOB data type for Chef Gordon Ramsey. It is where a MySQL engineer comes to your office bringing lunch to talk about the most popular open source database. I was asked to participate in one this morning at a major company headquartered in Dallas. The company wanted some information on updates to the MySQL server, a comparison between InnoDB and MyISAM, and some company specific questions.

It is often hard to get new product news to our users and this program provides us with a way to answer specific questions. And the customer gets to select the agenda items to make certain they get what they need form this event.

So if you want to get the latest on MySQL 5.6, Memcahched-to-InnoDB, or replication strategies, use the link at the top of this entry to set up your Lunch-n-Learn.

Dallas MySQL Users Meeting May 10th

2011-05-04T10:05:00.000-07:00

May 10th meeting -- Join us for a presentation on the new features in MySQL 5.6

Tuesday May 10th 4:30 PM to 6:00 PM (doors lock at 5PM)

Please RSVP!

Refreshments provided!!
Oracle Offices
7460 Warren Parkway
Suite 300.
Frisco, TX 75034

OTN MySQL Developer Day in Santa Clara

2011-05-03T08:58:00.000-07:00

Today is the MySQL Developer Day in Santa Clara. We have a full day of sessions on everything from basics to performance tuning.

OTN MySQL Developer Day in Santa Clara-- RSVP before the seats are all gone!

2011-04-26T09:52:00.000-07:00

The OTN MySQL Developer Day in Santa Clara is one week away. Be sure to register as space is going fast.

Get the Technical Know-how to Succeed with the World’s Most Popular Open Source Database

MySQL is the world’s most popular open-source database, and powers websites and applications of numerous leading and fast-growing organizations.

The OTN Developer Day MySQL is a one-stop shop for you to learn all the essential skills about MySQL. In this free, one-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You’ll also learn the guidelines and best practices in performance tuning and scalability.

Attend this event and gain the knowledge to:

Develop your new applications cost-effectively using MySQL
Improve performance of your existing MySQL databases
Manage your MySQL environment more efficiently

Don’t miss this exclusive opportunity to network with your peers, and hear the tips and tricks from the MySQL experts at Oracle. No matter you’re a DBA or a developer, come find out how you can make the best of MySQL in your IT infrastructure.

Agenda
8:00 a.m. – 9:00 a.m.	Registration
9:00 a.m. – 10:00 a.m.	Keynote: MySQL Essentials
10:00 a.m. – 11:00 a.m.	Session: MySQL Replication and Scalability
11:00 a.m. – 11:15 a.m.	Break
11:15 a.m. – 12:15 p.m.	Session: MySQL on Windows
12:15 p.m. – 1:00 p.m.	Lunch
1:00 p.m. – 2:00 p.m.	Session: MySQL Performance Tuning
2:00 p.m. – 3:30 p.m.	Hands-On Lab: MySQL Administration and Management Tools
3:30 p.m. – 4:00 p.m.	Raffle and Closing

Dallas Area -- Learn about MySQL 5.6 on May 10th

2011-04-24T16:39:00.000-07:00

Please join us at the DOUG (DALLAS ORACLE USERS GROUP) MySQL Forum meeting on Tuesday May 10th, 2011 from 4:30 pm – 6 pm (different than the usual meeting time).

Presentation Overview

MySQL 5.6 -- Stronger, faster, and it does NoSQL too!

Refreshments will be served.

To RSVP for this meeting, click on http://memberservices.membee.com/538/irmevents.aspx?id=49. Please contact dougadministrative@gmail.com if your plans change, so we can order the right amount of food.

Location: Oracle Offices (free parking)
7460 Warren Parkway
Suite 300
Frisco, TX 75034

Review of Sessions at O'Reilly MySQL Conference

2011-04-18T10:26:00.000-07:00

The annual trip to Santa Clara for the O'Reilly MySQL Users Conference is like trying to get a sip of water from a full on fire hose. So much is happening at once and cruel fate steps in to make sure that there are always two 'must attend' sessions going on at the same time.

First, Tomas Ulin's keynote was great. Sorry to my European buddies, but it was like watching a major league baseball player at batting practice. Clearly, he articulated what Oracle has been doing with MySQL and then started smacking home runs out of the park with the new features. And a big thanks to Dups for getting up on stage to explain how a rushed upgrade to 5.5 produces a 25% performance gain for Empire Avenue.

The regular sessions where very good. The one that really sticks out was Baron Schwartz talk on modeling scalability. Everyone claims linear scaling but this talk explained why we do not see it.

Memachce and 5.6 and More Stable Query Execution Times by Improving InnoDB Statistics are two of the blog entries on Planet.MySQL.COM that are recommended for reading now that the conference is over. heck, it is worth scrolling back to see what you missed during the conference as a lot of good material gets lost in the flood from the UC. All the InnoDB sessions were filled and I need to hunt down the decks from those talks.

And thank you for all who ventured across the parking lot Wednesday night to attend the MySQL Community Party. A good time was had by all, even the MySQL founder who acquired a pair of dolphin balloons.

Talk today on Open Source BI Reporting Tools

2011-04-12T05:46:00.000-07:00

First full day at the O'Reilly MySQL Conference after a great MySQL Community Dinner (Thanks to Sarah Novotny for her organization skills for getting 50 plus folks into Pedro's)

I will be talking on Open Source BI Reporting Tools in Ballroom A at 5:15. Very useful session for DBAs who need anything from a quick ~~ad hoc~~ report to a large multi-segment printer killer.

Speaking at POSSCON 2011 on Wednesday

2011-03-22T14:37:00.000-07:00

Palmetto Open Source Software Conference opens tomorrow in Columbia, South Carolina and I have the privilege at the 4:10 PM Technical Breakout on What's new in MySQL. If you are attending the conference, please drop buy to say 'hi'.

MySQL & Oracle presentation -- Dallas, March 19th

2011-03-17T08:47:00.000-07:00

I will be speaking at the North Texas Linux¹ Users Group Saturday, March 19th on MySQL and Oracle at 11AM. Directions here.

NTLUG features an install project, a beginners lecture, and then the feature presentation. Hopefully I will have some time to demo MySQL Workbench and more.

1. Opps! First time I posted I posed MySQL instead of Linux. Sorry for the confusion!!

Oracle Open World MySQL Track call for papers open

2011-03-15T08:52:00.000-07:00

Oracle Open World will feature this year a dedicated MySQL track under the Database stream. The call for papers was announced for the show which will be held October 2-6, 2011 at the Moscone Center in San Francisco. So polish up your material on your MySQL storage engine, success story, tips, research, projects, implementation and/or experience so you can submit your proposal as soon as possible. The call for papers closes March 27th!

From the press release: “From C-level executives to developers, Oracle OpenWorld is a venue for customers and partners to share their ideas, experiences, and accomplishments regarding Oracle products and solutions. This year we look forward to receiving thousands of presentation proposals to help us deliver a unique experience to a diverse global audience,” said Tania Weidick, vice president, Oracle Corporate Event Marketing. “We’re also excited to welcome the MySQL community in a dedicated track designed to provide MySQL users with practical tips and best practices from the vibrant ecosystems of the MySQL community, customers, partners and Oracle's MySQL engineers.”

Free Hadoop class in Dallas

2011-03-09T12:51:00.000-08:00

Cloudera Instructor Tom Hanlon will be presenting a free class on Hadoop Tuesday March 15th on Dallas. Tom is a familiar face to MySQLers in the North Texas area having previous taught many MySQL classes.

Pizza and Drinks will be provided.

This will be an excellent opportunity for MySQL DBAs to learn from booth a MySQL and Hadoop expert. Hadoop is a computational paradigm named Map/Reduce, where the application is divided into many small fragments of work which may be executed any node in the cluster.

Register Here as they may need to shift locations to find the anticipated crowd.

When did that statement finish?

2011-03-02T14:00:00.000-08:00

A friend wanted to go to lunch but need to time (roughly) how long a UPDATE took on one of his instances.

The friend had a co-worker who said the statement would take X time and the friend was pretty sure it was X/2. Me? I was hungry.

I edited the ˜.my.cnf and added the following:


[mysql]
prompt="\\r:\\m:\\s\\P>"

This changes the mysql client prompt from mysql> to 11:42:22am>. The friend started the mysql client, entered the query, and we went to lunch. We when returned, there on the prompt line was the time the command finished.

An old simple trick but it did what was needed. The documentation on the prompt string details more options. I probably would not want to use this trick for more than the crudest of benchmarks. I do use something similar to display the hostname when dealing with slaves in replication.

SCALE Conference

2011-02-17T11:36:00.000-08:00

On Sunday February 27th, I will be speaking on What's New In MySQLin the Carmel room at 11:30AM as part of the SCaLE (Southern California Linux Expo) near LAX Airport. If you are in the area, this is a great event. And if you are attending, please say 'hi' to me.

Collaborate Sneak Preview: MySQL

2011-02-15T13:46:00.000-08:00

The IOUG is presenting a free webinar on Wednesday, February 16th that will be a sneak peek of a session that will be presented at Collaborate in April. Kaj Arnö will describe the essentials of MySQL's background and history, to the extent its relevant for today's needs of an IOUG member. Hopefully it will be fine for non-IOUG members to listen in also.

Register

Dallas area meeting March 1st

2011-02-11T14:45:00.000-08:00

March 1st will be the first meeting of the North Texas MySQL Users Group for 2011. A presentation on What is new in MySQL 5.5 will start at 4:30 PM and finish and we can go for a 'post-meeting' at one of the establishments nearby. This group is a now a Special Interest Group of the Dallas Oracle Users Group.

There will be soft drinks and cookies served!

Room 3014
Oracle Offices
7460 Warren Parkway
Suite 300
Frisco, TX 75034

The San Francisco MySQL Group

2011-01-27T06:54:00.000-08:00

Last night, Lenz Grimmer, Lenz's posse, and I left the Oracle Leaders Summit to go north into San Francisco.

He had been invited by the San Francisco MySQL Meetup Group to present on MySQL High Availability. We had been warned that over 140 people had RSVP-ed and there were people on the waiting list. Mike and Erin, the organizers greeted us warmly and took us to the very large room. Soon the place was packed -well over a hundred people. The crowd was thoughtful, curious, and engaging. Lenz did his usual stellar job with the presentation and the following discussion with the audience was equally impressive.

If you find yourself in the San Francisco area, please make the effort to visit on of their meetings.

Free webinar Wednesday on MySQL Cluster

2011-01-10T09:16:00.000-08:00

Do not forget the What's New in Managing MySQL Cluster webinar on Wednesday the 11th. Learn about NDBINFO, MySQL Cluster Manager, and MySQL Cluster Adviser & Graphs -- three new enhancements. And the webinar is free!

Please register at http://mysql.com/news-and-events/web-seminars/display-599.html

Speaking at the O'Reilly MySQL Conference - April 2011

2010-12-15T14:06:00.000-08:00

I will be speaking at least once at the 2011 O'Reilly MySQL conference. I
have not heard back on all my proposed session but I will be talking
about Open Source BI tools. There are several great packages to help
DBAs make reports from their MySQL instances. These tools are great for
simple ad-hoc reports to highly detailed data dumps.

MySQL 5.5 Authentication Goodies

2010-11-29T15:53:00.000-08:00

MySQL 5.5 is currently in the Release Candidate phase and making good
progress on the way to being a Generally Available release. There
are many new features that will improve performance, make service
more robust, and generally make life better for DBAs. But since
5.5.7 was released for evaluation in October, there has not been a
lot of attention given to the changes in authentication.

To greatly simplify, MySQL has a table with a list of users and a
list of hosts from which those users are allowed access. So user
'jones' and the host they connect from are checked to make sure they
are allowed access. If they match, they can access the instance.

As of 5.5.7, MySQL authentication now supports pluggable
authentication and proxies. So now you can use PAM, Windows native
authentication, LDAP, or something similar to control user accounts.
Or use proxies for group of users. For example user 'smith' is in the
'programmer' group in the LDAP directory. But there is no entry in
the MySQL.user table for that user. The server will now use the
pluggable authentication to reference an outside authentication
service to determine the group. The connection will be established
with the USER() set to 'smith@host' and CURRENT_USER() set to
'programmer@%'. Please refer to
http://dev.mysql.com/doc/refman/5.5/en/proxy-users.html for details.

New job as MySQL Community Manager, North America

2010-11-15T14:17:00.000-08:00

As of today I am the MySQL Community Manager for North America. Part of the job description is work as part of a team that acts as the central resource and driving force for the design, process, manufacturing, test, quality and marketing of products(s) as they move from conception to distribution. So, you now have a focal point (me) to get your ideas into MySQL.

So help me gather and analyze information to define product specifications to move MySQL forward.

Calpont InfiniDB 2.0 and BI QuickStarts

2010-11-04T10:40:00.000-07:00

The 2.0 release of Calpont InfiniDB
is ready for download. New features for the columnar database storage engine for MySQL include data compression, fully parallelized & scalable UDFs, and partition drop has been added to the automatic vertical & horizontal data partitioning.

20-50% query performance improvement when reading from disk
Distributed in-database calculations provide greater flexibility to the data analyst, and enable faster performance for deep analytics
Removing obsolete data from the database quickly frees up disk storage and improves query response

And for those of you new to data warehousing and business intelligence, there are QuickStart for data reporting tools from Japersoft, BIRT, and Pentaho plus a guide for using Pentaho spoon with the Calpont Infinidb bulk loader.

Who certified MySQL what?

2010-10-21T12:27:00.000-07:00

Some of you in the MySQL world may have missed FAQ: Sun Certifications and Oracle-Branded Certificates from the Oracle Certification Blog. The synopsis is that anyone earning a MySQL certification before September 1st will be a SUN Certified MySQL {associate|DBA|Cluster DBA|Developer}. And starting September 1st you will be an ORACLE Certified MySQL {associate|DBA|Cluster DBA|Developer}. There is no need to replace your old certificates to get the Oracle branding and will continue to be recognized under the Oracle certification program and will be valid without expiration.

Want a new certificate anyway? Well, Oracle is planning an upgrade path in the future. Please see the linked article above for details.

Time to try Drizzle

2010-10-05T17:45:00.000-07:00

The Drizzle Beta was announced last week. It took me the better part of an hour to download and install Drizzle, mainly apt-geting packages not on the fresh install of Ubuntu I was using. Be sure to read the Drizzle Wiki on how to start Drizzle after building.

Anyone who has built MySQL or Postgres from source can build Drizzle. Anyone who has not done so needs to pay attention to the output of the make command and read through the documentation.

The default storage engine is the InnoDB plugin. The client program (pictured) will look familiar to MySQL-ers. Drizzle worked as expected on the simple tasks it perform. And now that I have driven it around the block, I will have to take it for a longer trip this weekend.

So if you have a few spare cycles and are curious, give Drizzle a spin.

Pentaho Kettle Solutions

2010-09-29T18:38:00.000-07:00

I have several favorite authors -- Tim Dorsey, Clive Cussler, and few others that I buy their latest book just because I trust the quality of their work. Now on that list are Roland Bouman, Jos van Dongen, and Matt Casters. In a follow up to Bouman's and van Dongen's Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL, the have now produced Pentaho Kettle Solutions which explores the often murky world of ETL and data integration.

Kettle can be confusing as there are many components with names such as spoon and pan but these tools are valuable to any DBA who has to feed data into an instance. If you have a data ware house the tools are invaluable.

This book covers initial steps with Kettle to cleaning up the worst raw data and even getting the information into the now ubiquitous cloud. Having stumbled along with a few tutorials and the Kettle docs, I can testify that this book is a must have for anyone wanting to use Kettle to feed a database. The text is clear, the example concise, and the book progresses logically.

Kettle is wonderful for modifying raw data before being sent to your database. need to clean up telephone numbers from xxx-xxx-xxxx, (xxx) xxx-xxxx, or xxxxxxxxxx in your format of choice? Or are state or country abbreviations (Ca, CA, Cal., & California) causing problems. Then you need Kettle and this book.

MySQL Certification Study Group in Austin

2010-09-15T09:33:00.000-07:00

It looks like the good folks in Austin are keeping busy:

Announcing a new Meetup for The Austin MySQL Meetup Group!

What: GeekAustin Fall MySQL Happy Hour

When: Wednesday, September 22, 2010 6:30 PM

Where: Triumph Cafe
3808 Spicewood Springs Rd
Austin, TX 78759
512-343-1875

Mark and Boyd just finished GeekAustin's 10 week MySQL Associate Certification Study Group, and now we're wondering what should be next. Boyd would like to lead a study group for the MySQL Developer I exam, and Mark would like to host a few meetups on replication strategies and other topics of interest.

The Austin MySQL Meetup and the GeekAustin MySQL SIG are hosting a happy hour next Wednesday, September 22, to meet with everyone and find out what sort of events/study groups people would like to see in the coming months.

The happy hour will be hosted at Triumph Cafe on Anderson Lane.

Austin has a great MySQL community. If you're looking to sharpen your skills, or just spend an evening with folks who share your interest, come join us.

RSVP at EventBrite:
http://mysqlhappyhour.eventbrite.com/

RSVP to this Meetup:
http://www.meetup.com/mysql-36/calendar/14779596/

Oracle Certified MySQL Certifications

2010-09-09T12:50:00.000-07:00

I may no longer be in charge of MySQL Certification but it does not mean I am not looking out for those of us with those certs. I am still moderating the MySQL Certified Professionals Group on LinkedIn and running the North Texas MySQL USers Group. But I missed Oracle University re-branding the Sun certifications as Oracle certifications earlier this month. This includes the MySQL certifications that were re-branded as Sun.

And for those wanting to take the certification exams, they retain the Prometric numbers set up by Sun. But you will find them listed as:

310-810 - Oracle Certified MySQL 5.0 Database Administrator Part 1 
310-811 - Oracle Certified MySQL 5.0 Database Administrator Part 2 
310-812 - Oracle Certified MySQL 5.0 Developer Part 1

310-813 - Oracle Certified MySQL 5.0 Developer Part 2

310-815 - Oracle Certified MySQL 5.1 Cluster Database Administrator

310-814 - Oracle Certified MySQL Associate

Redis -- key pair and replication

2010-08-11T07:24:00.000-07:00

DBAs seem to be getting hit over the head with the NoSQL message while trying to keep their SQL systems going. SQL does have its place in the world¹ and much of the NoSQL push seems to be a way to get past some of SQL's limitations. But simply moving from a proven technology and infrastructure to something new with fad overtones is not going to make life easier for Joe Average DBA. Redis is one of those project that will get notice from a lot of DBAs looking for a very fast key-value datastore.

Redis keeps the dataset in memory but writes to disk asynchronously and reloaded when Redis is restarted. Or the data can be saved each time a command is issued or on schedule to minimize data loss.

Redis also has master-slave replication and setup consists of a 'slave of x.x.x.x' line in the slave's config file. And is the only trivial thing about Redis.

The Command Reference shows a lot of thought and hard work has gone into Redis. Redis has a lot to offer Joe Average DBA without making him forsake his comfortable base.

SQL is not going away. And not like COBOL, FORTRAN or I Love Lucy not going away. SQL solves too many problems to be pushed aside. It does have limitations but cleaver engineering will mitigate them.

Open Source BI Report tools

2010-08-06T12:49:00.000-07:00

I am going to take a mulligan on a previous Blog Post on Open Source BI reporting tools. Part of my job at Calpont is getting familiar with tools folks with use with the InfiniDB storage engine. After all, what is the point of having all those terabytes in a data warehouse if you can not produce some sort of report from them. Three popular open source BI reporting tools are available from BIRT, Pentaho, and Jaspersoft.

Proceeding alphabetically, BIRT is part of the Eclipse IDE world. A BI report is a new project or new report under a project. And if you like Eclipse or Java IDEs, then you will probably like BIRT for reports.

Pentaho's Rerport Designer is a stand alone program. As is iReport from Jaspersoft.

I had no problems connection to a data mart in a InfiniDB instance with the old JDBC connection with any of the products. And all three produced reports of various levels of complexity from my SQL. Plus you can pretty much format the exams to your heart's content.

So which is better? That would be up to the user having to run the software. For those of you really interested, there will be quick start guides up soon on each of the three BI tools on the Calpont InfiniDB website. If I missed your favorite BI reporting tool, please let me know.

Now I am digging through the ETL tools. I was getting a millions and a half rows a second into an InfiniDB table using the cpimport tool. Now to get the tools and cpimport working together!

North Texans -- vote for your new meeting location

2010-07-29T14:04:00.000-07:00

The North Texas MySQL Users Group is now a special interest group withing the Dallas Oracle Users Group. As such, we can meet in oracle's offices in Plano or Irving. In the past there has been demand for meetings in the northern part of the Metroplex and demand for meetings more near the DFW Airport. So we can meet in either office or alternate between the two. Please state you preference by voting on the North Texas MySQL Users Group website.

Open Source BI -- Pentaho and Jaspersoft Part I

2010-07-13T16:03:00.000-07:00

Hey DBAs! Are you seeking more efficient ways of shifting through your data to aid your business operations? Two popular Business Intelligence products have community Open Source software are Pentaho and JasperSoft. And both work with MySQL.

Both are easy to download and install. Both will use a JDBC connector to connect to MySQL. But how easy are the two to configure and run a simple report against a running instance of MySQL?

Setting up a JDBC connection with JasperSoft or Pentaho is pretty much like using any other JDBC connection.

The next step is to setup a query like SELECT name, job_title, department FROM employees, departments WHERE employees.emp_id = departments.emp_id. Either package will let you pick a variety of output templates. Then you have the BI software merge your query with the template. I honestly think an average MySQL DBA could fairly quickly generate a nice looking report from their instance and that JasperSoft would be just a little bit faster.

In part two of this series, the steps will be more detailed and documented. There will also being comparing and contrasting of the two products. Both products are part of larger projects and there are many useful tools that work with the BI software that you will want to investigate. More on those in later posts.

And in a short time you should be able to download a Virtual Box image with both community BI programs and a InfiniDB instance with some data sets. This way you can test all three simply. I would also consider doing a VMWare version if there is demand for it.

New home for Dallas MySQL Users Group

2010-07-09T08:06:00.000-07:00

The Dallas Oracle Users Group has voted unanimously to accept the North Texas MySQL Users Group as a special interest group within their organization. This will give us wide exposure to the local DBA world, provide more venues for presentation (Irving and Plano), and the ability to have food & drink at meetings.

New presentations are being worked on for presentations including 'No SQL -- is traditional row based SQL in trouble'? Watch here for meeting information.

Doing your own on-time flight time analysis Part III

2010-06-30T09:17:00.000-07:00

In the last post, the data from the on-time flight database was loaded in a column-orientated storage engine. Now the numbers can be crunched.

The original goal of this exercise was to find the flight from Los Angeles International Airport, LAX, to Dallas Fort Worth International Airport, DFW, that was the most likely to arrive on-time.

The data is 'opportunity rich' in that there is a lot information in there. It is easy to start wondering about the various nuggets of information in there. Are their certain aircraft (tail numbers) that are routinely bad performers? Are some days of the week better than others? Do national holidays have an effect on the on-time performance? If you are delayed, is there a 'regular amount' of delay? Does early departure make for an early arrival? Can the flight crew make up for a late departure? How much time is usually spend on runways?

But to look for the flight from LAX to DFW with the lowest arrival delay¹.


SELECT FlightNum, SUM(ArrDelayMinutes) as Delay
FROM flight
WHERE Origin = 'LAX'
AND Dest = 'DFW'
GROUP BY FlighNum
ORDER BY FlightNum;

So there are lots of good questions to ask with this data but it ends this series on doing your own on-time flight analysis. If anyone digs into the flight data further, please let the rest of us know what you are finding.

1. For the data on hand the answer is American Airlines flight 2463.

Doing your own on-time flight analysis, Part II

2010-06-24T10:29:00.000-07:00

The On Time Flight Data (see previous entry) has 93 rows per flight and the first line of the CSV file has the names of the columns. Use head -1 on that file to obtain that information and to build a schema by piping the output to a file (and do not forget to remove that top line before loading the data).

"Year", "Quarter", "Month", "DayofMonth", ... "Div5LongestGTime", "Div5WheelsOff", "Div5TailNum",

Strip out the quotation marks, add CREATE TABLE flight ( at the front and ) ENGINE=InfiniDB; at the end. Year is the title of the first column and it has been renamed to FlightYear to avoid confusion with the function of the same name. The next part is to assign a data type to each column and a quick perusal of the data will show which fields are numeric, which ones are alphanumeric, and the one column of type DATE. Personal preference on the designation are left up to the reader as an exercise in data architecture.

CREATE TABLE flight (FlightYear smallint, Quarter tinyint, Month tinyint, ... Div5WheelsOff int, Div5TailNum int) engine=InfiniDB;

Create the table by running MySQL and sourcing the file created. Now the data needs some attention.

The InfiniDB bulk loader cpimport can use any character to delimit fields. In CSV files it is painfully obvious that the fields are separated by commas. However the On Time Flight Data has a column, OriginCityName, that contains a comma. A extra column can be added after OriginCityName, the comma can be removed from that one column, OR the other commas need to be changed to another character. The easiest for most will be adding an extra column.

Next a XML file needs to be created using colxml, colxml -j 50 -d "," -l On_Time_Performance_2010_1.csv -t flight air. The the data can be loaded with cpimport, cpimport -j 50.

So for a quick test! Can I find the flights from LA back to Texas?

mysql> SELECT FlightNum, COUNT(FlightNum) FROM flight WHERE Origin = 'LAX' and Dest = 'DFW' GROUP BY FlightNum ORDER BY FlightNum; +-----------+------------------+ | FlightNum | COUNT(FlightNum) | +-----------+------------------+ | 2400 | 31 | | 2404 | 30 | | 2410 | 31 | | 2412 | 31 | | 2416 | 31 | | 2422 | 31 | | 2428 | 31 | | 2434 | 31 | | 2436 | 31 | | 2440 | 31 | | 2444 | 21 | | 2446 | 31 | | 2448 | 31 | | 2450 | 26 | | 2464 | 31 | | 2470 | 31 | | 2501 | 1 | | 6219 | 31 | | 6234 | 31 | +-----------+------------------+ 19 rows in set (0.52 sec)

Next time, find the flight least likely to be delayed!

Doing your own on-time flight analysis, Part I

2010-06-22T14:28:00.000-07:00

This will be a quick tutorial on looking at on-time flight analysis. This material will be part of a lab for a class on InfiniDB that I am developing. The information is from Data.Gov Website and you are free to follow the steps presented.

What I want to know is what flight from a certain airport arrives at my local airport on time the most frequently. Traveling from LAX to DFW can often be a combination of cancellations, flight delays, and being the nth plane in line for takeoff. So what is the best flight choice for that route?

The first step is getting the data. And is is available for free from Airline On-Time Performance and Causes of Flight Delays. Be sure to select the check box for documentation so that there will be a readme.html to described the file fields included in the zipped file you will download. If you pick all the available information you will receive over 500,000 lines in a .CSV file.

It is worth comparing what the readme.html file and a few sample lines of data from the .CSV file. Any programmer who has written to the specification and then found the 'real world' data is different from what was expected will expound on the value of double checking what you are 'supposed to have' and 'what you got'. In this case, for example, the FlightDate is documented as yyyymmdd but is yyyy-mm-dd in the data. This in itself is not a big deal but a little time can save aggravation later.

So in Part II, we will create a table and load up the data.

Anybody have a few millions lines of Apache Log files they can share?

2010-06-15T13:54:00.000-07:00

Anybody have a few millions lines of Apache Log files they can share? I am working on a lab/demo on InfiniDB and need a few millions lines of an Apache HTTPD log file. I no longer run any large websites and would rather use real data over creating something. I will sanitize your URL so you will be anonymous, so www.YourCompanyHere.com will end up as www.abc123.com or something similar.

The demo/lab will show how to load data into the columnar InfiniDB storage engine and run some analytics against the data. Please let me know if you can help.

InfinDB Extent Maps saving I/O

2010-06-09T12:38:00.000-07:00

InfiniDB uses Extent Maps to retrieve data. Data is stored in extents and each extent is a logical block of space that holds the same number of rows.

The Extent Map catalogs all extents and their corresponding blocks. The Extent Map also maintains minimum and maximum values for a column’s data within an extent.

The Extent Map lets InfiniDB to retrieve only the blocks needed to satisfy a query. There is another benefit – that of logical range partitioning. This is accomplished via the minimum and maximum values for each extent that are contained within the Extent Map. Extent elimination is first accomplished in InfiniDB via the column-oriented architecture (only needed columns are scanned), but the process is accelerated because of the logical horizontal partitioning that is implemented in the Extent Map. So only the needed sections of the column are read. So if only the data from Col1 between 105 and 180 is requested, InfiniDB will return data from Extent 2, skipping Extents 1, 3 and 4. That saves a 75% of I/O compared to a row based storage engine.

The savings in I/O also carries over to more complex queries. If a column WHERE filter of “col1 BETWEEN 220 AND 250 AND col2 < 10000” is specified, InfiniDB will eliminate extents 1, 2 and 4 from the first column filter, then, looking at just the matching extents for col2 (i.e. just extent 3), it will determine that no extents match and return zero rows without doing any I/O at all.

Upon system startup, the Extent Map file is read into memory and updated automatically as needed.

InfiniDB Alpha 1.1.2 on 64-bit Windows and MySQL Workbench

2010-05-27T08:58:00.000-07:00

This week has been rough on my computers and I had to reinstall Vista on my main Windows system. But being a 'glass half full' guy, I took it as an opportunity to load the new alphas for both InfiniDB and MySQl Workbench.

I downloaded the InfiniDB software from http://infinidb.org/downloads/cat_view/40-binary-release/137-112-alpha-binary-releases and Workbench from http://dev.mysql.com/downloads/workbench/ respectively. In stalled them in that order and started the database and then Workbench.

I was expecting to have to fiddle with configuring one or the other to get them to work together. But I received a delightful surprise. Both worked out of the chute with no customization. Congratulations to the folks writing the install scripts at both companies!

Getting started with InfiniDB Part I

2010-05-04T11:22:00.000-07:00

My new job with Calpont has me scrambling to learn all the facets of the InfiniDB storage engine. For those of you new to the scene, InfiniDB is column-orientated as opposed to row-orientated and is designed for data warehouses and business intelligence. Most MySQL DBAs are probably not used to thinking sideways and hopefully this series of postings will change that.

To make things interesting, I am using real data from BP's Statistical Review of World Energy 2009. The data is a historical information on various forms of energy. And it is free to download and comes in Excel workbook format. I wanted data that was unfamiliar to me and in a format that would be commonly used in a BI setting.

The first step is to obtain and install the InfiniDB community software. Since the install instructions are extremely clear, I will not go over those steps.

Next I exported the data from the Crude oil prices 1861 to 2008 worksheet into a CSV file. Now you have choices on how to load the data into your table. The first is the good old MySQL LOAD DATA INFILE statement.

mysql>USE energy;
LOAD DATA INFILE '/tmp/oil.csv'
INTO TABLE oil 
FIELDS TERMINATED BY ',';

Or you can use the supplied bulk loading tool. In this example the table is simple and there are only 148 rows. LOAD DATA INFLE works fine for small sets. I copied CSV file into the directory InfiniDB used for the bulk loading tool -- /usr/local/Calpont/data/bulk/data/import --, changed the commas to pipes (s/,/|/g) preferred by the bulk loader, and prepare it for the columns tructure:


# /usr/local/Calpont/bin/colxml energy -t oil -j04

Bulkload root directory : /usr/local/Calpont/data/bulk
job description file : Job_04.xml
2010-05-04 12:42:21 (5364) INFO : successfully load job file /usr/local/Calpont/data/bulk/job/Job_04.xml
2010-05-04 12:42:21 (5364) INFO : PreProcessing check starts
2010-05-04 12:42:21 (5364) INFO : PreProcessing check completed
2010-05-04 12:42:21 (5364) INFO : preProcess completed, total run time : 0 seconds
2010-05-04 12:42:21 (5364) INFO : No of Read Threads Spawned = 1
2010-05-04 12:42:21 (5364) INFO : No of Parse Threads Spawned = 3
2010-05-04 12:42:21 (5364) INFO : For table energy.oil: 148 rows processed and 148 rows inserted.
2010-05-04 12:42:22 (5364) INFO : Bulk load completed, total run time : 1 seconds

[root@localhost import]#

Now to load the data:

 /usr/local/Calpont/bin/cpimport -j 04
Running colxml with the following parameters:
2010-05-04 12:42:13 (5312) INFO : 
 Schema: energy
 Tables: oil  
 Load Files: 
 -b 0
 -c 1048576
 -d |
 -e 10
 -f CSV
 -j 04
 -n 
 -p /usr/local/Calpont/data/bulk/job/
 -r 5
 -s 
 -u
 -w 10485760
 -x tbl

File completed for tables:
 energy.oil
 
Normal exit.

Almost forgot to show you the table!
CREATE TABLE oil (`year` INT(4), price_historical DECIMAL(5,2), price_current(5,2)) ENGINE=InfiniDB;

So now we can ask for the historical average price of a barrel of oil.

mysql>SELECT AVG(price_historical) AS 'Historical Avg Price' FROM oil\g

+----------------------+
| Historical Avg Price |
+----------------------+
|             7.772297 |
+----------------------+

Many of you reading this are probably muttering 'I could have done this with any storage engine except Blackhole! and you are right. The beauty behind the column orientated approach does not appear until you are working with large sets of data. In the next post we will get into large sets of data and compare speeds with other storage engines.

High Availability MySQL Cookbook

2010-05-03T15:57:00.000-07:00

MySQL is an easy database to get running initially but it can be tricky to run in demanding environments. High Availability MySQL Cookbook is a relatively thin book packed with information.

The first four chapters are on the care and feeding of a MySQL 7.x Cluster. It starts simply with an initial configuration, covers backups & replication, covers user defined partitioning, and covers troubleshooting node failures. The examples are short, to the point, and devoid of any unneeded filler.

Next come chapters on replication including the very tricky Multi Master Replication. Used shared storage is covered in a separate chapter from DRBD. The book finishes strongly with performance tuning.

The good points -- the book is concise and not padded with filler. The bad is that it is too short. Considering how well the author covers the topics that may not be too big a of a problem. For a novice to intermediate level MySQL DBA, this is a valuable guide on making your databases highly available. Long time MySQL DBAs will find nothing new but might appreciate the clear and concise explanations presented.

No North Texas Users Group Meeting in May, new job

2010-04-30T11:28:00.000-07:00

There will not be a May meeting of the North Texas MySQL Users Group but I am scrambling to get us a great location for the June meeting. I have left my Job with MySQL/Sun/Oracle and am no longer responsible for MySQL Certifications. That means my access to the Sun office were we have been meeting is ended also.

I am now the Customer Service Manager for Calpont and the InfiniDB storage engine. InfiniDB is column based, multi-threaded, and the community edition is open source. If you run intensive reports against InnoDB or MyIsam databases, you need to investigate this product. And if you regularly run massive queries in data warehousing or business analytic operation that you need to see how the massively parallel process architecture of the enterprise product can make life easier.

Book Review: MySQL Admin Cookbook

2010-04-19T15:08:00.000-07:00

Usually I try to avoid the cookbook type of computer books because usually the 'recipies', often messy scripts or pages of obfuscated code, seem only to work for the authors and not for me. So I had a little trepidation when I was asked to review the MySQL Admin Cookbook. Daniel Schneller and Udo Schwedt manage to pack a lot of very solid information into 360 pages of text that would work for novice to intermediate MySQL DBAs and provide some food for thought for seasoned DBAs.

What I liked: The material was presented with the reasons behind the recipe and pointers to useful tools. Yes, all the material is in the manuals but sometimes there are too many trees in the way for a novice so see the trees. In very calm ,concise language, the authors tackle successfully a wide range of DBA chores in a way that is easy to follow.

I would not hesitate to give this book to a novice to intermediate DBA as a tool to help them work through replication, configuration, indexes and tools.

What I did not like: Time has marched on and the references to MySQL Administrator make a few tiny sections seem dated but I am sure only the Workbench team knew what that product would evolve into. Also the section on monitoring is a bit light but then most DBAs do not need to wade in as deeply as the book goes let alone into Enterprise Monitor or other tools.

So this book is a solid four out of five stars and well worth being brought home by those new to MySQL DBA work and those seeking 'recipes' for thie instances.

2010 UC Cert Wrap-up

2010-04-16T08:02:00.000-07:00

The 2010 MySQL is over but many have left with certifications among their souvenirs. 150 exams were given this year and we now have three new Associates, two new cluster DBAs, two new 5.1 DBAs, twelve new developers, and thirty-eight new 5.0 DBAs! Some folks walked away with two or three new certifications. Those were just the folks who tested this year and does not count those who finished up a 5.0 DBA or 5.0 Developer cert at the UC!

Thank you to all who took exams this year. It takes a few days for the exam data to peculate into the Sun Certification Database but your certification kits will be posted ASAP.

A big thank you to Carey Hardey and Shankar Kondur who ran certifications this year. An Oracle travel ban kept me from attending this year and Carey, from the Oracle University Certification Department volunteered to travel down the treacherous Highway 101 to oversee the exams. Shankar is from Devsquare and made sure the new plaftform for the hands on exam worked properly for the UC.

Last day of testing at the MySQL Users Conference

2010-04-15T09:43:00.000-07:00

Today is you last day to take MySQL Certification Exams at a deeply discounted price. So if you want to test, head over before the doors close on the Magnolia room for 2010!!

Results of Certification Exams from First Day of the MySQL Users Conference

2010-04-14T06:33:00.000-07:00

Please note that Oracle Univerisity has placed a hard limit on number of exams at the Users Conference this year. If you wait to the last moment, you may not get a chance to show your knowledge!

Congratulations to those who took certification exams at the MySQL Users Conference this year. We have 18 new 5.0 DBAs and 2 new 5.0 Developers after Monday.

Testing is in the Magnolia Room in the Hyatt, near the front desk and starts at 8:30 AM PDT.

Certification at the MySQL Users Conference

2010-04-06T05:16:00.000-07:00

If you are planning to take a certification exam at the MySQL Users conference next week, please bring a laptop with WiFi and plan to come early! Testing will be in the Magnolia Room by the escalators in the Hyatt Lobby from 8:30 AM to 3:00 PM starting Tuesday.

The exams are online which means you will need a laptop.

And this year there is a limit of 250 exams total. Once that hard limit is reached, there will be no more exams given at the UC in 2010. So do not wait to the last minute to finish off that certification.

North Texas Users -- InnoDB Plugin

2010-04-01T13:51:00.000-07:00

The InnoDB plugin will be the subject of the next North Texas MySQL Users Group on Monday April 5th. The InnoDB plugin has some big performance and scaling benefits. But there are some messy things you have to take care of before it will work.

All are welcome!

Monday April 5th
7:00 PM
Suite 700
16000 North Dallas Tollway
Dallas, TX

InnoDB plugin training

2010-03-08T10:27:00.000-08:00

MySQL is a constantly moving target and keeping up on the latest changes can be a difficult chore. A lot of folks seeking certification are often swamped by evolutions in the MySQL product. On Tuesday there is a FREE web seminar where MySQL Professional Services experts will walk you through best practices for achieving performance and scalability improvements using MySQL 5.1 and the new InnoDB Plugin. This is part two of the series and you do not have to have seen part one to join.

List of Web Seminars

Dallas MySQL Users Group meeting March 1st

2010-02-22T07:53:00.000-08:00

March 1st Meeting
Workbench and Future

Workbench suddenly has a new bunch of features and that will be the main subject of Monday's meeting. This visual design, documentation, and all around database tool is quickly evolving into a 'must have' tool in your arsenal.

Plus, the North Texas MySQL Users Group Will be losing our home in a few months. We need to discuss if we will seek another, join the Dallas Oracle Users Group as a Special Interest Group or ???

Seven PM Sharp
Suite 700
16000 Dallas Tollway
Dallas Texas

DOUG Presentation -- An Introduction to MySQL

2010-02-11T14:36:00.001-08:00

I will be presenting to the Dallas Oracle Users Group on An Introduction to MySQL : 99 Things You Probably Did Not Know on Thursday, February 18, 2010 from 4 pm – 6 pm. Refreshments sponsored by Sun. More details on the DOUG site or North Texas MySQL Users Group.

Exams ARE US$200

2010-02-10T10:47:00.000-08:00

Whew! The Sun MySQL exams (310-810 through 310-815) are back to $200. I just checked the Prometric Site and was able to confirm.

N. Texas Users Group meeting

2010-01-28T08:02:00.000-08:00

The next meeting of the North Texas MySQL Users Group is Monday February 1st. There will be a give away of soon to be collectible Sun memorabilia! The presentation is An Introduction to MySQL: 99 Things You Probably Did Not Know and we will talk about the future of the group in 2010 (some exiting opportunities).

Monday February 1st, 7:00 PM, Suite 700,Sun Offices 16000 Dallas Tollway, Dallas, Texas

MySQL Certification Exam Price Raised

2010-01-21T08:31:00.001-08:00

Sun has made changes in the certification procedures for all their exams including the MySQL exams. Exam vouchers are no longer offered through local Sun Sales Offices but exams can be purchased directly from Prometric. The price has been raised to $300 per exam.

The exam codes are:
Sun 310-810 Sun Certified MySQL DBA 5.0 part I
Sun 310-811 Sun Certified MySQL DBA 5.0 part II
Sun 310-812 Sun Certified MySQL Developer 5.0 part I
Sun 310-813 Sun Certified MySQL Developer 5.0 part II
Sun 310-814 Sun Certified MySQL Associate
Sun 310-815 Sun Certified MySQL 5.1 Cluster DBA

The new prices went into effect January 19th.

Publishing your MySQL Certs from the Sun Certification system

2010-01-14T06:22:00.000-08:00

I received a question from my last entry on how to let others see your MySQL Certifications. In the past, the MySQL site had a list of those with the various certifications. Sun's certification system is different.

Go to the Sun Certification Database

Enter your ID and password. The format for the ID is usually SUNnnnnnnn but it may be different. There is a First Time Users link and a link to get your ID and password (you do remember the email you used when you took that MySQL, don't you?). And if you get stuck, contact Certificiation@SUN.Com

On the left hand menu, under Certifications will be a link for Publish Credentials.

Here you provide an email address for where you want your certifications sent. You can also select which certification you want to share.

You will get a chance to preview of what will be sent. The Void -- Sample Only Water mark will not be on the sent message.

So what will the recipient receive? They will receive an email from The Sun Credential Verification System with a link and a authorization code to view your credentials.

MySQL Certs up 48% in 2009

2010-01-13T13:21:00.000-08:00

Despite a bad economy and switching testing partners, MySQL certifications were up 48% in 2009. Most of the growth was from the new hands-on DBA 5.1 exam with the multiple choice exams gaining roughly 10 percent. The only exception was the Associates certification which actually decreased slightly.

Congratulations to all of you how earned a certification in 2009 and thank you for your support of MySQL.

Fulfillment Issues -- getting back on the right foot

2010-01-07T14:41:00.000-08:00

MySQL Certification had a very busy 2009 with a great deal of change. Not all of the changes happened gracefully and now I am correcting problems with the fulfillment system. Regular readers will remember that MySQL Certification was integrated into the systems used by Sun Learning in mid 2009. Where things got shaky was in the processing of DBA 5.1 certificates.

Previously Ricky Ho and I oversaw data entry, database updates, and fulfillment. The deeply missed Ricky did the heavy lifting in the printing and mailing of certificates. The test data from the multiple choice exams was flowing properly into the new (for MySQL) system but not for the hands-on DBA 5.1 exam. I have been going through the certification records and manually entering the data for the 5.1 DBA certificate holders. It will take a week or so to get things caught up. Meanwhile, if you are still waiting for a certificate for your MySQL 5.1 certification, please let me know at Certification@MySQL.com.

MySQl Certification exams will be offered at the 2010 O'Reilly MySQL Conference. More details will be posted here as they become available.

North Texas 1st meeting of the year -- Workbench & Cookies

2010-01-02T08:45:00.000-08:00

Happy New Year! Monday night is the first meeting of the North Texas MySQL Users Group for 2010! And the topic for discussion will be MySQL Workbench.

Workbench is a tool for visual database design, schema documentation, change management, and is replacing the old MySQL GUI tools. If you have not tried the latest releases, you will be for a pleasant surprise.

NorthTexasMySQL.org meets at 7:00PM sharp at the Sun Offices, Suite 700, 16000 Dallas Tollway, Dallas TX. We welcome all who want to know more about MySQL, databases, or Open Source. Admission is free and I will bring cookies!

Monday's Meeting

2009-12-08T09:40:00.001-08:00

Thanks to all who showed up at the North Texas MySQL Users Group Meeting on Monday night. We had two speakers, Pizza, and beer.

Benjamin Wood talked on the latest news on MySQL products and Robin Schumacher from Calpont discussed building high performance analytic databases.

And Basil won the drawing for the Amazon Kindle!

See you at the next meeting January 4th!

Books for your Holiday reading

2009-11-30T10:33:00.000-08:00

Once again the holidays season is upon us and many of you will be looking for gifts. I would like to recommended the following.

For many years I felt the best overall MySQL book was The MySQL 5.0 Study Guide. It is still a very good book but it does not cover third party tools and is getting slightly dated. My new favorite MySQL book is MySQL Administrators Bible . It covers everything from installing and monitoring MySQL servers to tuning and scaling. This book is written in a clear, easy to read style and deserves to be on the bookshelf of any MySQL DBA. This will be one of those books that you will dog-ear pages and festoon with PostIt notes.

The next book is for those of you wanting to expand your PHP knowledge . I have been using PHP back when it was called Personal Home Page (and using it with a free database from tcx.se called MySQL) and have a habit of skimming every PHP book I see in bookstores (and databases, Linux, and a few others; My family knows this and avoid my book store trips). Particularly interesting for those in the MySQL world is the information under Workload-driven Performance Tuning. I started skimming this book and realized that there was a lot of good information inside the covers that made it worthy of purchase.

Please note that I get no kickbacks from Amazon and use their links as anyone in the world seem to have ubiquitous access to Amazon. And that I have met half the authors pairs for each book. I worked with Morgan Tocker and have met Sheeri Cabral at the last few Users Conferences. Nothing against John Coggeshall or Keith Murphy!

And lastly I recommend Fool for light riding. It is a re-telling of King Lear as written by the court fool. There is nothing in this book that will make your code run better or your server return data more efficiently but it a very funny read.

North Texas MySQL Meeting -- now with pizza!

2009-11-13T12:01:00.000-08:00

At the next North Texas MySQL Users Group meeting we will be featuring pizza, soft drinks and beer. We will also have TWO presenters. Do if you are planning to be in Dallas on December 7th, please drop by!

Sun Microsystems will host the next Dallas MySQL User Group Meeting

When: Monday, December 7, 2009 | 6:00 to 8:00 pm

Where: Sun Microsystems, 7th Floor, 16000 Dallas Tollway, Dallas, TX

Map: http://bit.ly/ZnYoS

Are you interested in learning more about MySQL's new server release cycle and how it impacts the MySQL roadmap? Or are you looking to get the latest update on storage engines or development and administration tools? Are you investigating data warehousing and analytic solutions?

Then come join the North Texas MySQL Users Group, Benjamin Wood, Senior MySQL Systems Engineer, and Kathy Guzman, Senior Account Manager, from MySQL and Robin Schumacher, VP Products/Calpont for an interactive discussion on these MySQL hot topics and more. Pizza, beer, and soda and the opportunity to connect with other local MySQL Users provided!

Agenda

* How MySQL's New Server Release Cycle works and what's next
* Storage Engine Update
* What's New with the MySQL Query Analyzer and MySQL Workbench
* Building High Performance Analytic Databases
* Q & A / Roundtable
* Beer/Soda/Pizza

Featured Speakers:

- Benjamin Wood is a Systems Engineer with the MySQL group of Sun Microsystems. He has spent the last 12 years working with database technologies, including 8 years as a production DBA.

- Robin Schumacher, VP Products/CalPont, will lead a discussion on building high performance analytic databases.

Learn more about thse North Texas MySQL Users Group »

Register now to reserve your spot! http://www.mysql.com//news-and-events/events/dallasmeetup.php

Oracle Express Edition first steps for MySQL DBAs

2009-11-06T11:01:00.000-08:00

I have had a few MySQL DBAs ask about how to get started learning Oracle. I will admit that it has been on my to-do list for quite a while¹. It never hurts to know more than one database system and a great deal of DBA help wanted ads mention Oracle. Someone once said that you must make sure your capabilities exceed your limitations² and recently I have been feeling limited when others have started to talk about Oracle capabilities.

So what does it take for a MySQL DBA to get their hands on their own Oracle instance? I used my Ubuntu box to go to Oracle's web site to get the free Oracle XE software.

Download and feed to package manager

Add my account to dba group

As root, /etc/init.d/oracle-xe configure to set passwords and ports

I pointed my web browser to http://127.0.01/apex and got the page you see in image with this blog. Now I need to find my copy of Hands-On Oracle Database 10g Express Edition for Linux.

1. I also have a stack of DB-2 and SQL Servers books that were picked up at Half Price Books to read through. I am always looking for better ways to express database concepts for MySql exams. Writing up something similar to this entry for DB2 and SQL Server is also on the list.

2. This quote has attributed to Bruce Lee and several others. And it always gota groan from my kids when I used it on them.

Cardinality

2009-11-03T06:54:00.000-08:00

Last night I was asked about index cardinality. One of the members of the North Texas MySQL Users Group was using phpMyadmin and noticed an element marked 'cardinality' and asked me what it meant. And I will admit I was stumped.

The manual says:

ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.

MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE does not produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. See Section 5.1.3, “Server System Variables”, and Section B.5.6, “Optimizer-Related Issues”.

Clear, huh? Well not 100% for me so I went back to searching. Other finds on the net say that cardinality is a measure of how accurate an index is and that a UNIQUE index would have the highest cardinality. So the more unique the index entries are, the more higher the cardinality.

Which brings up another question: How often do you maintain your indexes? Whay clues tell you to do maintenance? Please share your recommendations!

BTW we will have pizza at the next meeting of the North Texas Users Group, so see you December 7 at 7PM at the Sun offices, 16000 Dallas Tollway in suite 700!

List of MySQL Certified Candidates -- Update

2009-10-30T08:33:00.000-07:00

The list of MySQL Certified Candidates will pass into history at the end of November. The data was moved into Sun's system last July and what remains on the MySQL certification list is not aging well.

The Sun Certmanager has most of the same features but does not let us pull records for display on a web page. You can still get verification of your credentials for a third part, such as a job interview or customer, but it now requires having an email sent to the third party with the information on how they can view the information.

We will live the old information up for the coming month for those of you who wish to double check your data in the Sun system.

On behalf of the MySQL Certification Team, both past and present, we have enjoyed watching the list expand over time. It was a very valuable tool with many uses but sadly it now goes into history. And thank you to all of you listed there.

Want to know how Yahoo does their capacity planning?

2009-10-26T13:55:00.000-07:00

Every wonder how Google plans for growth? Well, if you are in the Dallas / Fort Worth area on Monday, November 2nd you can find out exactly how they do it!

Strategic MySQL Planning for Complexity & Growth (i.e. MySQL Scaling for Dummies) will be presented by Tommy Falgout at the North Texas MySQL Users Group Meeting

Sometimes a data driven website is a simple matter. Sometimes it only starts out that way. Membership records can grow from dozens to hundreds to thousands (or more). Performance or historic logs can grow astronomically. The ongoing need to coordinate different sets of data can lead to outrageously complex schema and duplicate data. How can you avoid those troubles? There are ways. Tommy Falgout will talk about designing your database for growth and performance before those challenges become bottlenecks on your website.

Reference:
http://dallasphp.org/html/Topics.htm?article_id=178

The next meeting of the North Texas MySQL Users group will be Monday, November 2nd at 7:00 PM (Daylight savings time change on Sunday the 1st).

Tommy Falgout is a Sr. Application Developer at Yahoo! He started his journey at Y! with a background in PHP & MySQL. Since then he has expanded his horizon by becoming a certified MySQL DBA, managing the Yahoo! Live Flash Video architecture, dabbling in Oracle, MSSQL, Java, Flash, Flex and the occasional Perl when noone's looking. He welcomes challenges, questions and not afraid to admit he owns a scooter. On the side, he is actively trying to get rid of 1000+ rubber duckies, left at his house from a prank, at duckiehunt.com.

The previously scheduled presentation will be on making good queries with JOINS, subqueries, and UNIONs will be moved to December. If you want to learn how to get your data from your database faster, more efficiently, and easier then this is the session for you.

We meet in the Sun offices, Suite 700, 16000 Dallas Tollway. All are welcome, beginners encouraged to attend!

Saying goodbye to the old certification lists, part 2

2009-10-13T08:08:00.001-07:00

I did not mean to stir up a hornets nest!

I received a lot of feedback on the last blog entry on shutting down the old MySQL system and that included the lists of certified candidates. Yes, I know how valuable these lists are. But you might want to check this out ORACLE UNIVERSITY LAUNCHES CERTVIEW. I will see if I can get some details the next time we speak to the fine Oracle Certification folks.

The certification industry have started initiatives like the ITCC to allow you to have your certifications listed for viewing by a potential employers or the world at large. So do not worry that we are taking some draconian action and the knowledge of your accomplishments is being shoved into the great bit bucket in the sky. We just need a little time to transition from system to system (to system).

And feel free to ping us for updates at Certification@MySQL.com or by commenting below.

Saying goodbye to the list of Certified MySQL Users

2009-10-07T07:15:00.000-07:00

The MySQL Certification: Listing of MySQL Certified Users has been a valuable resource for many years. I have a small stack of emails from people who were recruited for jobs after someone spotted their names on the lists. But the lists are going away.

The vast majority of MySQL Certification information is now part of Sun's certification system and has been since the end of July. Prometric exam results feed into that system and not the MySQL system. The lists of certified users pulls from the now out of date MySQL system. That means the data from the listings are not up to date. There is no easy way to cross-tie the information from Sun's systems into the MySQL web system. So please spread the word that the lists are going away, most likely by the end of the calendar year.

Teaching table design with EXPECT

2009-09-21T10:55:00.000-07:00

The next meeting of the North Texas MySQL Users Group is next Monday and the presentation will be on designing database tables. This is part of a series for novices with MySQL and/or databases. Too often novices commit obvious sins like BIGINTs for any numeric storage field or indexing every column. EXPLAIN is a valuable tool in optimizing SELECT statements but too many DBAs do not discover it until well after they are stuck with a small, unruly group of badly designed tables.

Using EXPLAIN is seen as a 'dark art' by too many and hopefully we can get the novices in North Texas off on the right foot. The meeting is free and starts at 7:00 PM in the Sun Offices, Suite 700, 16000 Dallas Tollway, in Dallas. All are welcome and please try to be a bit early as the downstairs door automatically lock at seven sharp.

What makes a MySQL Developer?

2009-09-18T13:25:00.000-07:00

I would like to hear some opinions from the MySQL worldon what knowledge does it require to be a MySQL Developer?

Not the folks who write the MySQL server code but a developer who really knows their stuff when programming with MySQL? The MySQL 5.0 developer exams going under a review. The hands on exam for 5.1, which has been hindered by other chores, is also under construction. But I still have a fundamental question : What makes someone a MySQL Developer?

The course book from the MySQL for Developers class is very thick and filled with a lot of information. It covers a broad spectrum from basic SQL to query optimization. None of it is programming language specific (or covers approaches like using PDO versus mysqli in PHP). And the contents are all good things for anyone trying get data in or out of a MySQL database. But nothing in it says that factor [X] makes a good MySQL Developer.

So what do you look for when hiring a developer for a project that will require heavy MySQL server interaction?

Do you have them optimize queries or do you leave that for a DBA? How much do you value the use or understanding of stored procedures, triggers, or partitioning?

Employers and project managers: If you could use the MySQL Developer certification to screen applicants for a position, what do you want stressed on the exam? And what do you not want stressed?

What knowledge or skills makes the difference between a good and a great MySQL developer?

This is your chance to make your opinions known.

Jackalopes and certification questions

2009-09-16T08:58:00.001-07:00

Most folks do not know about Jackalopes. They are rabbits, usually Cotton Tails, with antlers from some sort of deer. The one pictured is from the Austin Museum of the Weird. Jackalopes, like unicorns, are fabled creatures may be based on real creatures with injuries, growths, or encounters with taxidermists with too much spare time. What does that have to do with MySQL Certification you ask?

I updating the MySQL Cluster DBA certification exam from the 5.1 to 7.0/7.1 product level. The current exam has not kept pace with the product and the question bank needs updates. The question bank has roughly 180 questions of which we use 130. The extra 50 are the questions that were either not fully developed or did not make it to the exam for a variety of reasons.

Of the 130 that are used, about ten percent are passed by all who take the exam. Roughly half of the questions are passed by at least seventy percent of the certification candidates. And 20% of the questions are getting very low correct rates.

So now I dig into those 20% to see if the questions are wrong, badly phrased, or if I have the question equivalent of a Jackalope. On occasion, despite the best efforts of all involved, a question that will slip through that is just the right mix of obscurity, mangled intention, and linguistic lethargy that combines to make a question that does not test or measure knowledge.

Sometime the questions can be salvaged. Other times they are replaced with new questions, hopefully concerning some stunning new feature in the cluster software. And hopefully we can vanish the Jackalopes from the MySQL exams.

Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL

2009-09-01T12:53:00.000-07:00

I get several emails each week that are from folks who have the basics mechanics of being a DBA but are looking to learn how to manage data. They can administer a database server but want to know how to get more out of that data. I now have an excellent book for them to refer.

My copy of Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL arrived last week. I need to disclose that I was originally hired by MySQL to replace one of this books authors, Roland Bouman. He told me a year ago that he was working on this book. And I have found that he is always investigating something interesting.

Now to buy a pair of those cheap terabyte disk drives and load up some data.

This book will end up being one of those tomes that DBAs will have dog-eared, stuffed with Post-It^tm notes, and kept within easy grabbing distance from their seat. Anyone interested in Business Intelligence, Data Warehouses, or who has a boss who wants 'more' from their data needs to get a copy of this book. This is one of the clearest, concise book on data warehousing and shows how to quickly get your data ready for deeper analysis. The book is not only a great introduction to the subject but clearly shows how to progress from the basics to advanced analysis.

Cluster exam update, part II

2009-08-25T11:58:00.000-07:00

I am going through all the MySQL Cluster 5.1 DBA exam questions to bring up up to date with the latest release of the software. Part of this is reading all the comments exam candidates have left for a question, looking at what percentage of folks answered it correctly, and then making sure that the questions is still valid.

MySQL Cluster has been rapidly evolving. It used to be impossible to use OPTIMIZE TABLES or create TEMPORARY tables with Cluster. But now both work and these two questions with high pass rates need to be removed.

Oddly some of the lowest percentage correct questions are some of the most generic questions. And these go into the pile to be updated.

The vast majority of the questions on the exam are generic and still are valid today. The goal is to have the exam updates ready to go by the end of the calendar year.

Updating the Cluster DBA Exam

2009-08-19T08:58:00.000-07:00

It is time to update the MySQL Cluster exam. The current exam was written when the Cluster software was at version 5.1 and 7.0.5 is available for download. Reviewing the old exam questions shows some of them are badly dated.

But updating the exam requires more than simple edits of the questions. The MySQl 5.1 Cluster DBA Certification Study Guide is the book to use for studying for the exam. And having a study guide is a vital tool for those of us who prefer to self study for an exam. Sadly, some of the authors are no longer with Sun and I am sure those still around have minimal spare cycles in their schedule.

And it would be a waste of time if the revised exam is based on a version of software that is soon to be superseded. The Cluster product has evolved rapidly since it was introduced at a users conference years ago in Orlando and each release provides more features.

My 'virtual cluster', a Mac laptop with VirtualBox data nodes, is running a 6.0.x version and needs to be updated. I encourage those using any of the MySQL Study Guides to use the software with the examples in the book. Sometimes you can learn more by 'fat fingering' a command or finding something destined for the book's errata than reading the text alone.

But the first step is reviewing each question and determining if it requires updating. And, as the old adage goes, even the longest journey starts with the first step.

July was one of the best months for MySQL Certification

2009-08-03T09:09:00.000-07:00

270 folks took MySQL Certification exams in July and 118 earned certifications. This is our best month outside of our annual Users Conference. For the fiscal year, MySQL Certifications were up 150.27% over the previous year.

Since the program inception, we had almost 8,000 exams taken and over 3,300 certifications. Not bad but it pales in comparison to Java exams and certifications.

Prometric is our new testing partner. Those of you with unexpired Pearson VUE vouchers can request a exchange by contacting Certification@sun.com with your Voucher ID number. And those of you wishing to sign up for the exams (they are part of the Sun 310-series at http://www.prometric.com/Sun/default.htm) need to contact you local Sun sales office to purchase a voucher.

And the North Texas MySQL Users Group meets tonight -- see http://www.northtexasmysql.org for details.

North Texas MySQL Meeting

2009-07-27T12:56:00.000-07:00

The next meeting of the North Texas MySQL Users Group will be Monday, August 3rd 2009 at 7PM sharp. We will meet at the Sun Offices, Suite 700, 16000 Dallas Tollway, Dallas.

The presentation will be on MySQL Basics -- How to install and run a MySQL database. This is an introductory level talk but experienced folks are invited to contribute their insights also. We hope to have a series of talks for those just starting with MySQL so that later we can build demand for more advanced topics.

Prometric MySQL exam numbers; plus watch out for that giraffe on the road

2009-07-09T09:44:00.000-07:00

Prometric is beginning to offer the MySQL Exams as part of the Sun catalog. Currently only the Associate and Developer exams are listed but the DBA 5.0 and cluster exams are on the way.

ID	Exam
310-810	DBA 5.0 I
310-811	DBA 5.0 II
310-812	Dev 5.0 I
310-813	Dev 5.0 II
310-814	Associate
310-815	Cluster DBA 5.1

I find myself working while on vacation this week. Originally I planned to give my younger son some 'seat time' so he can practice for his driving test. But he is resting after a frantic day driving the hills and valleys of the Fossil Rim Wildlife Park. Managing an unfamiliar vehicle on poor to bad roads while watching out for zebras, Emus, deer, and ostriches is not easy. There is something about teaching a person in their mid-teens to drive that points out how much experience at an activity brings proficiency. Many common tasks done over and over again become ingrained and automatic. Live avoiding animals in the road while driving or checking on data backups as a DBA. It is easy to forget all the little skills that need to be built to be able to handle a car or a database well.

That reminds me of the old joke about the DBA who asked the system admin is the systems were backed up every night. The sysadmin then walked over to the server rack and physically pushes the rack before answering 'yup'!

Meetup Mess for North Texas MySQL Users Group

2009-06-18T05:32:00.000-07:00

The Meetup.com folks have created a lot of unnecessary confusion lately. Their rejection of sponsorship money from Sun and MySQL has caused many of their MySQL user groups to scramble. The North Texas MySQL Meetup became North Texas MySQL Users Group and we though all was done. Well, no. Meetup.com sent out 'do not let this group die' emails that caused a flurry a messages to flood my email.

The next meeting of the North Texas MySQL Users Group will Monday August 3rd (We are taking July off) at 7:00PM in Suite 700, 16000 Dallas Tollway, Dallas TX. The presentation will be MySQL Basics -- how to install, configure, and start using MySQL

So we will meet up without Meetup.com!

Texans - learn to tune your MySQL Servers

2009-05-26T10:58:00.000-07:00

Brian Miezejewski will be presenting at the North Texas MySQL Users Group on June 1st on performance tuning on your system. Brian is a top-level tuning guru and this is your chance to get an expert to examine your system.

So on your system, run the following:

mysqladmin -u -p va >varis.txt

Then during your peak usage time (if possible):

mysqladmin -u -p ex -i 15 -r >stats.txt

Let it run for 10 minutes before hitting ctrl-c to kill it. Bring in the varis.txt and the stats.txt files for tuning and review.

Meeting: June 1st
7:00 PM
Sun Offices
Suite 700
16000 Dallas Tollway
Dallas

NorthTexasMySQL.org

Changing Testing Partners from Pearson VUE to Prometric

2009-05-26T10:40:00.000-07:00

As part of integration with Sun Learning, MySQL Certification will be moving from using Pearson VUE to Prometric as our testing partner. July 31st, 2009 will be the last day that candidates will be able to take exams or use MySQl exam vouchers at a Pearson VUE test center. Those with outstanding exam vouchers that they can not use by July 31st, 2009 should contact certification@sun.com for an exchange.

Cluster presentation -- Richardson, Texas

2009-05-12T07:08:00.000-07:00

The kind folks at The Dallas PHP/MySQL Users Group have asked me to repeat my 'MySQL Cluster -- a gentle introduction' presentation tonight at their meeting. This was after sitting through the original performance where I talked about how to set up complete cluster on a laptop. The presentation has been updated to reference Cluster 7.0 and the demo will work now that I have static IP addresses figured out with Virtualbox. So you know where to be if you are in the DFW Metroples and are not watching American Idol tonight.

The presentation in PDF format is available on NorthTexasMySQL.org

DataBox from ReportMill

2009-05-05T07:58:00.000-07:00

Last night at the North Texas MySQL Users Group meeting, Jeff Martin from ReportMill.com showed off their DataBox tool.

This is a Java based tool for developing rich Internet applications. It has the hooks to create MySQL databases and link the data to applications. Jeff demonstrated creating tables from scratch, importing files into a database, and then using that data in a full function application that took a very shot time to create. It is much faster than compatible frameworks and features a rich set of Java enhancements for effects.

So if you quickly need to get from raw data to full application, check out DataBox.

305 exams taken at the UC, new crop of DBAs and Developers on the loose!

2009-04-28T12:44:00.000-07:00

Over 300 exams were taken at the 2009 MySQL Users Conference. Many more DBAs were certified this year then last and the Developer cert was not as popular.

We had problems with network bandwidth (Thanks to Patrick at O'Reilly for getting us more as soon as he could) and some software glitches with the new software. Some folks asked to take their exam at a location with better bandwidth and their results are still trickling in to the Certification Team.

The new 5.1 DBA exam was offered and the general review is that it is a much more realistic test of a DBA's skills than the older version. There were a few edits that did not make it into the system that cause some confusion but those taking the exam carried on bravely.

Thanks for all who made the journey to the Magnolia room, especially those who made it through the 1,200 teenagers who showed up for the conference after ours!

Cert Exams finished at the UC

2009-04-23T16:26:00.000-07:00

Around 300 certification exam were given this year at the MySQL Users Conference! That is 50% more than last year. Much of that is due to having the exams online. But the biggest part is from the dedication of Ricky Ho who not only did a fantastic job keeping the exams running smoothly but will spend most of the next week printing up certificates and getting them in the postal system.

Last chance for $25 UC certifications

2009-04-23T09:25:00.000-07:00

Today is the final day to take a MyQL Certification exam at the Users Conference. Save money with the special $25 price from the regular $200.

Are you new to MySQL -- try the associate exam? It is designed for those with 6 weeks to 6 months of experience with MySQL.

Or do you consider yourself at Guru level but do not have your Cluster DBA cert? Today is your chance to prove your skill.

Day two winding to an end

2009-04-22T15:30:00.000-07:00

The penultimate day of UC exams is slowly coming to an end. A fresh crop of Developer, DBA, and Cluster DBAs certifications have been earned in the eighty plus exams taken today. Tomorrow is the last day for the deeply discounted price of $25 for the examinations. Your Certification Team will be ready at eight tomorrow!

Certification first day - 110 exams

2009-04-22T09:13:00.000-07:00

110 exams delivered on the first day of the UC! That was despite Wifi Saturation. O'Reilly has provided a few more 'hard line' to help fight the problem.

Exams are in the Magnolia room (opposite the Hyatt registration desk, jut past the escalators) from 8:00 AM to 3:00PM. And the cost is still $25!

Certification at the UC start

2009-04-21T08:03:00.000-07:00

Your intrepid MySQL Cart Team opened the doors thirty minutes early to the Magnolia room and Robert Bochenski was the first one to start an exam this morning. Exams are $25 and we will be letting folks into until 3:00PM. Some come by and take a certification exam.

And at 1PM, I am hosting a 30 minute Certification Q&A session about current exams, future exams, and anything else you want to cover.

Cert Team at the UC

2009-04-20T10:52:00.000-07:00

Ricky Ho and I are busy at the UC ... pulling our hair out. There are no certification exams on Monday but we found all sorts of network glitches. The Hyatt had some network ports blocked that just happened to be the ones we use for online exams. And the Sun Remote Lab Data Center had a glitch over the weekend that kept the virtual images from starting.

The picture is from Kai Voigt's tutorial on DBA certification. The room is full and I pop n and out between 'hair pulling' sessions. The attendees are asking good questions and I am sure we will have some new certified DBAs by the end of the conference.

MySQL Certification in first quarter of 2009

2009-04-06T07:46:00.000-07:00

The first quarter of 2009 was very good for MySQL Certifications. 496 Exams taken and 208 certifications earned. But in regards to the current economic problems, how does this compare?

1st three months of each year
Year	Exams	Certs
2005	134	100
2006	248	121
2007	348	101
2008	450	153
2009	496	208

Not too badly, as it turns out!

MySQL Certification Exams at the Users Conference

2009-04-02T08:00:00.000-07:00

There will be big changes for those taking certification exams at the MySQL Users Conference. Once again we are offering $25 MySQL exams which is a big discount from the regular $200 price.

Last year we ran the exams as a drop-in service and it was well received. So we will continue to run it as a drop-in service. No need for an appointment. We will be in the Magnolia Room from 8:30 AM to about 4:00 PM but will not let anyone start an exam later than 3:00PM.

Be sure to bring your WiFi enabled laptop! Last year we killed a lot of trees and got a big workout printing thousands of exams and moving them around. This year the exams will be online. We will be reserving some hard lines for those taking the hands-on DBA 5.1 exam as they need to connect to the virtual servers in Colorado (that is if the O'Reilly folks can get the network gear) but everyone else will be using the wireless connections. Last year the wireless was heavily saturated at lunch and break times but the O'Reilly folks say that is a thing of the past (but please note this as a warning that the wisest of the exam takers will plan their exam times not to overlap major breaks and lunch).

What exams are offered? The Associate exam, the 5.1 Cluster DBA exam, the 5.0 DBA exams (both I and II), the 5.0 Developer exams (both I and II), and the new DBA 5.1 hands-on exam. We will not have study guides on hands so plan accordingly. And Kai Voight is presenting a DBA Certification tutorial on Monday and I highly recommend it to those taking any of our exams.

I had hope to offer the new 5.1 Developer exam but it is still being incubated.

If you have never taken a MySQL exam before, please go to http://www.pearsonvue.com/mysql and create an account. We hand the exam records off the information you enter there and not having and account will slow down processing for your exam. So take it easy on the MySQL Certification Team and your results will be sped to you.

And I am supposed to talk on Tuesday in the Magnolia room (where we have the exams) on MySQL Certification. If you have questions or want to help plot the future, please be there.

DBA 5.1 Exam Contents, Part 3 of 3

2009-04-01T11:56:00.000-07:00

The optional section of the MySQL 5.1 DBA exam is a series of ten tasks and the candidate has to complete at least five of them. The five mandatory tasks are tests of core MySQL skills and the optional section is a test of other skills a candidate may have acquired.

All of these tasks can be completed in many different ways. For instance the first mandatory task includes removing three accounts. This can be done in one or more SQL statements. The candidate is graded on IF they accomplished the task and not the method used to accomplish the task.

The Optional Tasks

Copy a database

Create a fulltext search

Create a view to select certain data from a table

Alter a table

Update a compressed table

Create a recurring event

Update a global variable

Use the INFORMATION_SCHEMA data in views to return instance information

Import a CSV file

Partition or repartition a table

Once again, do at least five of these optional tasks. It is suggested that a candidate do more that just in case the task is a little more complex or has a subtle twist not apparent at first site.

What the heck does that mean? For instance, one early tested created an event following the information from the help command on the mysql command line client. But it was not a recurring event, just a one time event. That was a simple mistake but costly.

Next up: Getting ready for exams at the User Conference! Yes, there are short cuts to make your testing experience EASIER at the UC.

DBA 5.1 exam contents, Part 2 of 3

2009-03-30T15:52:00.000-07:00

The MyQl 5.1 DAB exam is series of tasks. The material was divided into a mandatory section and an optional section. The mandatory section is a test of core MySQL DBA skills and you must pass all five. The option section, to be detailed in the next post, is made up of ten tasks and the candidate must past at least five of them. This may sound simple but try cramming a full day of DBA work into two hours and you will have a good idea of what taking this new exam is like.

The Mandatory Tasks

Account maintenance

Add, delete and modify accounts using the mysql database.

Assign privileges for a user on a table and column basis.

Reset passwords

Locate 'anonymous' accounts (where password AND user are both = '')

Index management

Add, modify, and delete indexes.

MyIsam table maintenance

Check MyIsam tables for problems and fix them.

InnoDB table maintenance

Know how to restore an InnoDB table from a backup.
Use the binary log to recover lost data

Backups

Be able to backup up entire databases or explicit tables.

Know options for separating data and SQL.

Be able to use options to drop tables, drop databases, save stored routines or triggers, and save files to specific locations.

This new exam is very straight forward but requires the candidate to demonstrate their skill. Next posting will cover the option section.

DBA 5.1 Exam Contents, Part 1 of 3

2009-03-23T13:51:00.000-07:00

The MySQL DBA 5.1 Certification Exam is a big change from previous exams.

No multiple choice, true/false questions
Must be able to use MySQL
You are given the answers (in most cases) and must change the database to meet expectations
No study guide

The exam is divided into two sections -- mandatory and optional tasks. The mandatory tasks are core knowledge that every DBA should be able to perform even under heavy pressure, early in the morning, while suffering from sleep deprivation, or with a Dilbert style PHB looking over your shoulder. The optional tasks cover many different facets of MySQL.

Exam candidates will get their own MySQL instance with which to do their assignments. When they have completed all they can or time runs out, the instance is checked to see how well the DBA certification candidate was able to shape MySQL to what was desired. No more will you have to remember if the -p switch is for the port, password, or something else that begins with the letter 'p'.

Right now hands-on or performance based tests are on the bleeding edge of technology for testing companies (Pearson Vue, Prometric, Kryterion, etc.) are looking for robust and secure ways to offer these exams. So the DBA 5.1 Exam will have only limited availability until the exam vendors can support us. But the exam will be at the April MySQL Users Conference and part of MySQL for Database Administrator Classes.

If you are studying the MySQL 5.0 DBA material and are ready to take an exam, please do so! We will not be expiring these certifications and they are still a good judge of your knowledge of MySQL.

So why change the format at this time? How many times have you heard that a certification is not worth the paper it is written on? Funny the same folks do not say the same thing about car drivers licenses or pilot licenses. The reason is that for those certification there is a major component of hands-on testing. The folks who earn the MySQl DBA 5.1 Certification can really administer a database.

Employers will know that a job candidate with a MySQL 5.1 Certification has proven their proficiency with a real database under demanding circumstances. This certification is earned by hard work and not rote memorization.

I will miss the study guides and have found both editions to be valuable references. MySQl Certification will no longer have a hand in the process of creating the study guides and the previous authors are extremely busy. And with hands-on exams we can tell you want you need to do to pass the exam and that you will see in parts 2 and 3 of this blog.

How to become a better MySQL Developer

2009-03-23T10:36:00.000-07:00

How do you get better with MySQL if you are in a small shop (or you are the shop)? Reading the code of other folks only goes so far if you can not ask them about the details. The more advanced books on MySQL are great but sometimes your environment does not allow you to follow their examples. Time and experience will help eventually but what if you need or want to get better in a hurry?

The MySQL Developer Techniques class is a new offering from MySQL Training is designed to 'want to utilize advanced SQL features to create queries that will result in better results and improved performance of their database queries'. I can guarantee that the curriculum crew have put a lot of effort into developing this class to make sure you really get the best value for your money.

This is a three day class lead by an instructor and includes many hands-on labs. I get emails from folks who have passed the Developer Certification but are seeking better performance out of their databases and themselves. So if you are looking to improve your skills, I highly recommend this course.

DBA 5.1 Exam

2009-03-13T11:43:00.000-07:00

The first public offering of the MySQL DBA 5.1 Exam was given in Atlanta this week. After completing the week long MySQL for Database Administrators, an intrepid group agreed to be 'guinea pigs' and take the daunting exam.

The photo shows the test candidates working hard. Thanks to instructor Ian Wrigley and the students for taking up the challenge of this initial exam offering.

Previous MySQL exams are multiple choice while the DBA 5.1 exam is a performance based test. This means they had to login to a virtual server, administer several databases, and fix problems on a running MySQL instance.

Look for more updates on the DBA 5.1 exams (and others) here soon.

Elance Cites Surging Demand for MySQL Experts

2009-02-25T09:08:00.000-08:00

Elance Cites Surging Demand for MySQL Experts is an interesting article for several reasons. For many years advocating Open Source products was seen in many circles of management as akin to promotion of some sort of social anarchy. But now they see the cost savings, the performance gains, and the flexibility that a product like MySQL can provide.

Check out the jobs Elance has for MySQL talent.

DFW MySQL Users!

Monday night at seven will be the next meeting of North Texas MySQL Meetup. This meeting will not be MySQL-centric but will discuss creating Facebooks apps and using the MySQL Quiz as an example. The address is suite 700, 16000 Dallas Tollway. Please RSVP at the website so we we know to keep an eye out stragglers.