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

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.

1. Philips head, flat head or the rare Allen head.
2. Usually a Ultilikilt
3. 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

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

1. 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
2. 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.
3. mysqlslap --auto-generate-sql --concurrency=100

Performance tuning basics

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

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

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

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!

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

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 [email protected] 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

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

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.