Using INNODB_FILE_PER_TABLE

DBAs are always try to determine the best way to manage their storage for InnoDB. The three main options include:

1. Having one shared InnoDB tablespace data file.
2. Setting up individual files per InnoDB table.
3. Setting up a shared tablespace across multiple files.

Option 1: Having one shared InnoDB tablespace data file.
This is fine if you have a simple and small MySQL database. Great for new DBAs to get comfortable with managing MySQL storage.

Option 2: Setting up individual files per InnoDB table.
I like this option when I need to create large transaction tables. This option works well with partitioned objects. Separating your core tables offers more flexibility in administration, backup and recovery and from a storage perspective makes good common sense. I however, do not like using this for all of my InnoDB tables. The more individual files I have to manage the more a DBA will get nickel and dimed from an administration perspective.

Option 3: Setting up a shared tablespace across multiple files.
I like this option because this gives me more flexibility in separating out my I/O if I do not have a storage array doing the striping for me. This allows me to get a lot of my core tables and separate them from other tables by defining their storage in separate data files where I control their location by defining the path of the tablespace data files.

Obviously having a storage array that does your striping and mirroring makes disk I/O management for databases a lot easier. :)

Top Ten DBA Interview Questions

I'm often asked what interview questions should be asked of a new DBA you are considering to hire. Mistakes in a DBA hire can cost an organization a lot of money and hurt morale. There are a lot of factors that determine how well a DBA will fit into an environment. I thought I would share some of the things I consider when I interview a new DBA. These questions can be asked to any type of DBA (MySQL, Oracle, SQL Server, DB2, ..).

Questions you have to answer for yourself before interviewing a new DBA include:

• What skill sets are you looking for in a DBA? What skills are need to have and which are nice to have?
• How much time do they have to get up to speed?
• How important is it you hire the right person? (Or what investment are you willing to make to hire the right person).

When interviewing a potential new DBA it is important to understand:

• What is their technical depth and experience.
• What type of environments have they worked in.
• How well do they interact with customers.
• How well will they fit in to the team and the company environment.
• What techniques and methodologies do they use to manage databases.
• How will they impact the current database management practices.

The type of DBA you are trying to hire greatly impacts how you interview the candidates. I don't care to ask a lot of syntax questions or a lot of technical minutia. I want to understand:

• The depth and breadth of their knowledge.
• Their methodology and approach to managing databases.
• How they solve problems.
• Their commitment and work ethic.

I could write a white paper on explaining the rationale behind each of these questions, but I'll cut to the quick and give a ten step process to interview a new DBA candidate. These questions assume you are looking for a production DBA. If your organization is new and you do not have the expertise to hire the right candidate, you need to hire a consultant that can help your organization through this process.

1. What are four errors found in an alert (error) log that can ruin a DBA's appetite and how does the candidate avoid them?
2. What are four performance bottlenecks that can occur in a database server and how are they detected?
   
3. Ask them to explain their philosophy for database management and how they implement best practices and guidelines. This should include questions related to the configuration you are running (replication, cluster, OLTP, data warehouse, etc.).
   
4. What area of database internals do they understand the best that helps them troubleshoot problems. Ask them to explain this area so you can get an understanding of their depth of knowledge in this area.
5. Get ten common types of trouble tickets DBAs have solved in your organization and ask them how they would solve them.
6. Get five difficult problems your DBAs have had to solve and ask them how they would solve them.
7. Ask them to explain five things they have learned recently that makes them a better DBA and where they learned them.
8. Set up a simple database environment. Ask them to use two different methods to back up the database. Break the database. Have them recover the database using the backups they created.
9. Ask them to give a detailed explanation of setting up an environment for disaster recovery or an upgrade for an important database.
10. Have them write or build a few things in the sample database to demonstrate their knowledge and experience. This should include typical tasks they will be expected to perform to manage the environment.
    

I like a three or four interview process. Each interview should only leave the candidates that meet the profile of the type of person you want to hire.

• First interview I like to get a baseline if they match the resume they sent and do they meet the profile of what you are looking for.
• Second interview I like to make very technical to see if they meet the technical profile of what you are looking for. Make sure you make this sufficiently in-depth for the position you are hiring. There should be no doubt at the end of this interview as to whether they have the technical skills or not for the position you have open.
  
• Third interview I like different members of the team determine how well they will fit into your environment and then perform one more level of technical evaluation to make sure you have the right candidate.

Have fun and good luck. What are some of your favorite questions?

Setting up Apache and PHP on Mac OS Leopard

Apache is a fairly straight forward setup if you use some utility or distribution to help you. A manual installation will take more time. You can Google installation instructions or use a utility like XAMPP to simplify the installation. XAMPP is a distribution that will walk you through an Apache, MySQL, PHP, phpMyAdmin installation. Once you get comfortable with XAMPP these software components can be installed on a Linux or Windows platform in a few minutes.

I'm running Mac OS (Leopard) which comes with a built-in Apache web server (2.2.6) and PHP (5.2.4) preinstalled. On Mac OS (Leopard), perform the following steps to get the Apache Web Server (AWS) and PHP up and running in minutes.

1. Go to System Preferences. Under Internet and Network select the Sharing icon.
2. Select the checkbox under Web Sharing. It will return your computer's website URL (IP address or host name) (i.e. http://xxx.xxx.xxx.xxx/). This will also start AWS.
   
3. Open up a browser and type your computer's website URL displayed in the Web Sharing page in System Preferences. After typing this URL in a browser. You should get a browser page that shows you your Apache web server is up and running. This is too cool. Mac OS's built in Apache web server takes just a few clicks to have Apache up and running.
4. Once you've confirmed the Apache web server is up and running, type the httpd -V command to get information on your Apache installation.

Running httpd -V.
$ httpd -V

The httpd -V command returns important information

• The Apache Web Server version you are running.
• The location of the Apache configuration file (httpd.conf).
• The location of the access and error log file.
• Apache configuration options.

Example snippet of output from the httpd -V command:
Server version: Apache/2.2.6 (Unix)
-D SERVER_CONFIG_FILE="/private/etc/apache2/httpd.conf"
-D DEFAULT_ERRORLOG="logs/error_log"

With Mac OS (Leopard) you can find the access_log and error_log files in the following location:
/private/var/log/apache2/

Turn on PHP 5 on Leopard by uncommenting the following line in your httpd.conf file using a text editor.
# LoadModule php5_module libexec/apache2/libphp5.so

AWS needs to be stopped and restarted for the changes to take effect. Starting and stopping the AWS can be done through the System Preferences | Sharing window. Clicking the Web Sharing checkbox can be used to stop and start Apache. Restart the Apache Web Server so the configuration changes take effect.

Create a PHP configuration file. Then change the error reporting option to display all errors. Instructions:
$ cd /private/etc
$ sudo cp php.ini.default php.ini
$ sudo vi php.ini
Change the error reporting line using vi or a text editor to display all error messages.
error_reporting = E_ALL & -E_NOTICE
error_reporting = E_ALL

Test your PHP configuration

1. In a browser type your computer's website displayed in the Sharing page. This should launch Safari and display an Apache web page.
2. Create a page called test.php (lots of examples on Google). Save it in the directory Macintosh HD:Library:WebServer:Documents.
   
3. Go to your browser and type your computer URL with a test php (lots on Google) program. If you get the PHP info your configuration is working.

http://xxx.xxx.xxx.xxx/test.php


Apache and PHP should now be up and running on your Mac. Have fun!

Sun - A very positive beginning

During the last month, I have had the opportunity to be involved in three MySQL training classes that have included Sun employees. Without exception, every Sun employee has shown a lot of enthusiasm about the potential for MySQL and Sun. Every Sun employee has gone out of their way to welcome MySQL employees. There have been a lot of excellent discussions on the potential of Sun and MySQL technology and what they potentially can do together. I wish we could have podcast some of these discussions. The sharing of ideas and the collaboration was fantastic. Getting a number of knowledgeable people in database, messaging, identity management, storage, cluster and application server technology in one room and the exchange of ideas was a great experience. Thanks Pedro, Maggie, Walter, Ram, Andrea, Malek, Mitch, Seumas and everyone for all the positive energy and warm welcome!

Next week the Sun on-board process begins for most U.S. MySQL employees. Meeting a number of fantastic people at Sun is definitely a great beginning to the Sun experience.

Sun - A very positive beginning

During the last month, I have had the privilege of being part of three MySQL training classes in a row that have included Sun employees. I can't understate how great the Sun employees have been in welcoming MySQL employees to Sun. Without exception, Sun employees have stated their excitement in the possibilities and potential of MySQL and Sun. It's been great to see how every Sun employee I've met has gone out of their way to make MySQL employees feel part of Sun. To Pedro, Maggie, Walter, Seumas, Mitch and everyone thanks for all the positive energy and the warm welcome!

Next week begins the Sun on boarding process for most U.S. MySQL employees. Meeting a number of great people at Sun is definitely starting the Sun journey on a very positive note.

MySQL, Blade Servers and Storage

When people think of MySQL they normally think of MySQL running across multiple Intel servers running Red Hat, SuSE or Windows. This is great for small and medium sized organizations. However, adding a number of Intel boxes and dealing with heating, electricity, power and storage is not an ideal scenario for larger organizations.

As MySQL grows in popularity, I believe more organizations are going to look at using blade servers and storage arrays to manage MySQL databases. Will Sun put together a solution that can generate enthusiasm for using Sun Blade Servers and Sun storage solutions. It's kind of funny that I say this. With over twenty years of database and Unix administration experience, to me there is no better operating system for running database servers than Solaris. My personal perspective is that since the days of SunOS, Sun Microsystems has by far the "best" operating system for databases. Does that matter in today's world? I'm not sure.

When you look at Solaris compared to Linux, Solaris is by far the better operating system in my humble opinion. Yet, that does not seem to matter. Linux provides the core functionality people are looking for at the fraction of the cost, so Linux is by far more popular. Actually, this is one way MySQL is gaining market share compared to other proprietary databases. MySQL has the core functionality that people want, at a fraction of the cost of proprietary systems. Other database vendors add more and more functionality, yet this new functionality does not impact the growing popularity of MySQL. Cost is not the only reason MySQL and Linux are popular, yet cost is a strong factor in the popularity equation.

Can Sun package a complete solution with MySQL that can excite the IT industry? This may be one of the key questions for 2008.

MySQL Growth in the Database Market

MySQL can be used 64,000 different ways with just about any type of database. Despite this flexibility, here is how I see the growth of MySQL in the database market:

• Web-based applications are an area of strength for MySQL. MySQL will continue to grow and remain popular in this space.
• Data Warehousing is the big potential growth area for MySQL. In the next few years it will be very interesting to monitor MySQL in the data warehousing market. Fast reads, large horizontal scalability and strong yet relative low-cost solutions make MySQL ideal for data warehousing.
• OLTP is an area that MySQL works well in for small and medium sized solutions. MySQL version 6 is where MySQL can grow significantly in the OLTP market. The Falcon (OLTP) storage engine, designed for modern large memory and multi CPU systems, increased on-line functionality and improved high availability offers significant potential for MySQL in the area of OLTP applications. MySQL version 6 will make MySQL more popular in the on-line Internet database market. You can also expect Oracle (InnoDB) and other 3rd party companies to also offer new scalable OLTP storage engines in the next few years.

The future looks bright for MySQL. It's interesting to look at the database market. It is Oracle and MySQL that are growing in popularity that can be used in multiple platforms.

• DB2 is typically an IBM solution.
• SQL Server is typically a Microsoft or low-cost simple solution.
• Oracle and MySQL are the two database solutions that are popular with multi-platform solutions. HP-UX, AIX, Solaris, Red Hat, SuSE , Windows, etc. are all strong and popular solutions for both Oracle and MySQL. It is only Oracle and MySQL that continue to remain popular on multiple platforms.

Oracle will continue to dominate the large aircraft carrier database solutions and the business applications market. SAP will continue to try and use DB2 and SQL Server with their business applications and use Oracle only when they have to.

As for the web-based applications, small/medium data warehousing applications and small/medium OLTP environments, MySQL will continue to grow in popularity.

Data Warehousing - The Next Step for MySQL

MySQL excels as a strong solution for web-based solutions. MySQL’s extremely fast read rates and ability to scale horizontally with replication makes MySQL a popular low cost of ownership platform for web-based applications. The next area I expect MySQL to encounter significant growth is in the data warehousing market. MySQL’s fast reads and horizontal scalability makes it a strong consideration for the following environments:

• Real Time and Archive Data Warehouses
• Data Marts
• Large Reporting Systems (DSS)

There are a number of strong solutions in the area of BI reporting, Analytics and data integration solutions that can be used with MySQL. Using MySQL for a data warehousing solution allows an organization to build small and medium sized data warehouses without the large startup costs of proprietary databases as well as the extremely expensive BI and data integration solutions that come with proprietary databases.

They say the number one reason restaurants fail is not being able to overcome large startup costs. Using MySQL as the database platform for small and medium data warehouses allows an organization to deliver a strong data warehousing solution at a fraction of the cost of proprietary solutions. This is why I expect data warehousing to be an area of significant growth for MySQL databases in the next few years.

Important URLs for Sun and MySQL Documentation

MySQL Documentation: dev.mysql.com

Sun Documentation: http://docs.sun.com/app/docs

Zones: http://www.sun.com/bigadmin/content/zones/

Resource Pools: http://docs.sun.com/app/docs/doc/817-1592/6mhahuolg?a=view

ZFS (New File System): http://www.sun.com/software/solaris/ds/zfs.jsp

SMF (Service Management Facility): http://www.oreilly.com/pub/a/sysadmin/2006/04/13/using-solaris-smf.html

Very cool class in San Francisco

It's an incredible privilege to be able to teach other people. I've always found that knowledge is like love. No matter how much you give you always get more back.

I'm teaching a class this week in San Francisco to a very cool group. Students are from all over the world: China, Malaysia, Germany, Spain, Italy, India and the U.S. It is great to have people from all over the world and we all speak the same language, databases. We're all having a lot of fun singing "Kumbaya". :)

So far the top rated restaurants among the class this week include:

• Colibri
• House of NanKing
• Great Eastern Restaurant
• Sultan
• Puccini and Pinetti's
• Magnolia Pub and Brewery

MySQL and Sun

I don't know if Sun still offers those Sun leather jackets, they were pretty cool. I need to figure out how to get one. Sun areas of interest for MySQL DBAs include:

• Sun Clusters and how they work with MySQL Cluster.
• MySQL work and Sun Zones.
• MySQL works and ZFS.
• SMF.
  

Books for new MySQL DBAs

These are two books I would highly recommend for new MySQL DBAs. Both are very well written with a nice writing style. I really like the writing style of the person that wrote the Head First SQL class.

• Head First MySQL
  
• MySQL 5.0 Certification Study Guide
  

MySQL - An IT Tipping Point

A ''tipping point'' is a concept, product or idea that becomes a hot commodity that attracts everyone's attention, interest or inspiration. There are always specific reasons and factors that are not easily identifiable why one product becomes a tipping point and others do not. Products not considered a tipping point usually never become a tipping point and ofter never understand why they didn't. There are tons of examples of this. VHS versus Beta, MP3 players versus iPods, Blue Ray versus HD DVD. The list is endless.

MySQL is "the" hot tipping point product in the IT industry. MySQL is an incredibly popular open source part of the LAMP stack. There are additional open source products like PostgreSQL, MaxDB, Firebird, Ingres, Apache Derby, etc. No database in the industry is creating the buzz, excitement and interest that MySQL does. People can debate the feature/functionality, performance, etc. between the different databases. Make any comparison you want, none of the other databases that are compared to MySQL are going to be the "tipping point" MySQL is.

MySQL is a very unique organization with employees that believe in disrupting the industry with innovativeness and new ways of doing things. MySQL employees have been raised with the concept of open source, sharing and caring about their contributions to open source. I do not believe their are many companies in the world that have the "global team" concept that MySQL does. I interact with MySQL team members in Sweden and Germany as easily as team members in my home city. It is the uniqueness of the MySQL employees that are creating the tipping point.

Web 2.0 environments are increasing exponentially in popularity and MySQL is in the center of the storm of this exciting growth. MySQL has the potential to be the center of innovativeness in Sun the way NeXT was the center of innovation in Apple. Sun has a very powerful engine for future growth in MySQL. There is one thing MySQL has that no other database has. It has the tipping point.

Installing MySQL Administrator on Linux

Installing the MySQL GUI tools are pretty easy on Linux (Fedora - 2.6.18-1.2798.fc6) but there are a few prerequisites for setting them up. The following listed RPMs are required. I did a google search and then performed a quick download of them.

• libsigc++20-2.0.6-1.i386.rpm
• glibmm-2.4.7-1.rhfc3.nr.i386.rpm
• gtkmm24-2.8.5-1.i386.rpm
  

Load the RPMs. MySQL Administor is loaded in /usr/bin by default.
# rpm -i libsigc++20-2.0.6-1.i386.rpm
# rpm -i glibmm-2.4.7-1.rhfc3.nr.i386.rpm
# rpm -i gtkmm24-2.8.5-1.i386.rpm
# rpm mysql-gui-tools-5.0r12-1fc5.i386.rpm
# rpm mysql-administrator-5.0r12-1fc5.i386.rpm

Go to the Desktop Applications Menu and select the Programming Tab. You will see the MySQL Administor Icon. Select this icon and it will startup up MySQL Administrator in a windows environment. Then have fun! :)

MySQL Priority Poll

This poll did not get a lot of voters but I thought I would post the results to show you the priority of the individuals that did vote.

Poll: What is your highest priority with MySQL?

• Performance Tuning 20%
  
• Backup and Recovery 20%
  
• Monitoring and Management 16 %
  
• High Availability 16%
  
• Scalability 12%
  
• Storage 10%
  
• BI and Reporting 3%
  
• Business Strategy 3%
  

Choosing a MySQL Configuration Strategy

MySQL configurations cover the range from developers installing a simple MySQL database for a LAMP application to the largest Internet companies using MySQL to deliver web content.

There are two different profiles that cover this range for installing, configuring and managing MySQL database servers:

1. Profile 1: Individuals or small organizations that wants to use MySQL to create a simple database for web applications and the LAMP stack.
   
2. Profile 2: Individuals or organizations that are looking at creating larger MySQL databases or may be creating a large number of MySQL databases.

Profile 1:

For this profile, the person can be a non-DBA. This profile can use the default install using the Windows Installation Manager or the Unix or Linux RPMs. The GUI interfaces will take you through a very simple install. MySQL database servers do not take a lot of resources to run. The template files (my.small, my.medium, my.large) can show startup files that contain larger configurations. This is a simple environment to install and manage. I have seen non-DBAs and developers use this environment for years and it works great for them.

This type of DBA is typically using some simple GUI interfaces, doing backups with mysqldump and taking a simple approach for managing MySQL databases. MySQL's basic configuration can make it very popular for the small and medium sized organization.

Profile2:

This profile involves more complex and larger MySQL databases. This environment often benefits from installing and configuring using best practices. This environment is more successful with someone that has DBA experience or training. Implementing a MySQL environment with the MySQL Optimal Configuration Architecture (MOCA) where a DBA organizes a database for performance, high availability, ease of management becomes important for this environment being successful.

Dependent on the size and complexity of the MySQL environment, the DBA for this environment may be more concerned with one or more of these areas:

• How the MySQL servers will be monitored and managed. Using the MySQL Enterprise Monitor, open source monitors or 3rd party monitors becomes important.
  
• What backup strategy to use: Snapshots, InnoDB Hot Backup, Replication, etc.
  
• High Availability (MySQL cluster, other clustering strategies).
• How to balance I/O by spreading data across multiple disks, storage arrays, etc.
  
• Performance Tuning.
• The establishment of database standards, guidelines and best practices (MOCA).
• Change control.
• Having to much coffee.
• Managing test, development and production databases. Developing rollout strategies.
• Security, auditing, Sarbanes-Oxley, COBIT, ITIL.
• Information Lifecycle Management

These two profiles show two ends of the spectrum for MySQL database administration.