Best Environments for Running MySQL

Its interesting when you look at MySQL database environments. Extremely popular in the MySQL world is to use commodity hardware and implement the LAMP stack.  Smaller projects and startup companies love this environment.  A simple example, a company needs to set up a web application, they buy some commodity hardware, set up replication and scale horizontally.  Everybody loves this and it works great.

This environment requires floor space, cooling, electrical outlets, etc.  As new MySQL database servers get built, the environment needs to consider how this database environment will scale.  Organizations may take a step back and start looking at scaling with blade servers or large host systems to run large numbers of MySQL databases.

Database servers are I/O intensive.  Growing with cheap disks does not always scale with systems that have large I/O requirements.  Striping and mirroring starts to become more important.  So storage scalability and more expensive storage solutions become a consideration as I/O increases.

It will be interesting as Sun leads MySQL into more enterprise environments how the hardware and scalability strategies will evolve.

Learning MySQL DBA Best Practices

It is a challenge for new MySQL DBAs to learn best practices for managing database servers. There are no books that teach you everything you need to know. Key knowledge areas new MySQL DBAs need to learn:

• Best practices for installation and configuration.
• How to monitor and manage a database.
• How to monitor for tuning.
• Best practices for backup/recovery.
• Best security practices.
• What audit controls need to be put in place.
• Disaster planning.
• Growth projections and scalability planning.
• Change management.

Sources I would recommend a new DBA start with:

• Google - do searches on best DBA practices, etc.
• Read information on the MySQL Optimal Configuration Architecture (MOCA) and the Optimal Flexible Architecture (OFA - Oracle related). Both give examples of things to consider when planning databases.
• Go to conferences and download presentations from previous conferences.
• Find a mentor.
• Larger environments look at Sarbanes-Oxley, COBIT and ITIL in terms of best practices for managing IT infrastructures. Waters get deep here for new DBAs.
• http://www.planetmysql.org

Maximize your ROI at the MySQL Conference

The MySQL conference is coming up. It's one of highest ROIs you can get for your training dollar. Just like anything else you do that is successful, plan on how you can maximize your attendance at a conference.

If you don't attend conferences on a regular basis here are a few things you can do to maximize your experience:

1. Volunteer! Best way to meet new people and feel a part of the conference.
2. Join! There are often Special Interest Groups (SIGs) or other groups within a conference that try to bring people with common interests together.
3. Introduce yourself! Try to meet as many people as you can at the conference. Challenge yourself to meet as many people as you can as possible. Ask them questions like: What's the best presentation you've attended so far? Are there any speakers you really like and would recommend to someone new to a conference attend? What area are you focusing on at the conference? A conference is the best way to work on your communication and networking skills. Buy a book on networking and try some of the techniques. If you are new to the conference no one knows you, what do you have to lose? Ask them about what environments they are running? What challenges are they having with their technology?
   
4. Attend get togethers! Every conference has breakfast, unconference and birds of a feather meetings and other ways to network. If one doesn't work out, don't get discouraged. Remember you don't know anyone there how can you make a mistake.
5. Business cards. Bring business cards and everyone you meet give them a business card. Who knows, someone might even give you there card. :)
6. Prepare! Get organized. There are tons of great books, blogs, and Internet information on 10 ten tips for introducing yourself, networking and socializing. Try them.
   
7. Submit! Submit a presentation. Awesome way to expand your comfort level and for people to get to know you. Most important thing people want at a conference is to learn from other people's experiences. You don't have to be a top expert. Do not underestimate what you have to say?
   

I consider myself a pretty social person. However, I remember when I first starting going to conferences it was pretty lonely. I wasn't comfortable walking up and talking to people. The first birds of a feather meeting I went to, I walked in the door, everybody looked up at me, I froze and I walked out. I realized I was too shy but was too embarrassed to walk back in. It can get really lonely if it seems everybody else knows each other.

Since then I have ran different areas of a conference, been on expert panels, given key note presentations and been on board of directors for conferences. All of these things occurred because I was willing to push myself past my comfort zone and began to submit papers for presenting and to join different groups at conferences.

Networking is always rated first or second in terms of benefits for people that attend conferences. If you would like to increase your networking skills I would recommend you read the book "Million Dollar Networking" by Andrea Nierenberg. This will give you a lot of great ideas, recommendations and things to say to greatly increase your networking skills. You are going to spend thousands of dollars to attend the conference, why not spend another $30 and maximize the networking you can do.

Everybody attending a conference has been in your shoes. Go introduce yourself to volunteers or speakers. Ask them about ways to maximize the time at the conference and what speakers they like to see. All volunteers I guarantee you want you to have fun and enjoy the conference and they know what its like to go to a conference for the first time. Most importantly, find out if any MySQL instructors are around, they are some of the coolest, most enjoyable and high energetic people you'll find at the conference. :)

The Right Strategy for Customers

There have been significant trends and changes in the IT industry. Some of them include:

• Main Frame systems
• Client/Server
• Distributed systems
  
• Multi-tiered platforms with application servers
• Open Source

It's important to understand each of these is a technology to help meet business goals. Open source is currently an area of high growth and interest in the industry. The cost benefits provide organizations with a lot of flexibility in how they put their infrastructures together. Global markets and competition are putting even more pressure on IT organizations to make the right decisions.

I was talking to a friend about what organizations are doing with open source strategies. The result of the conversation ended with it is not about an open source strategy. It is more about developing the "right customer strategy" and if and how open source can play a role in helping a customer meet their business objectives and goals.

I've always felt as a DBA/manager, the more options I have the better decisions I can make. Including open source solutions in the decision process helps make sure customers are looking at their overall strategy in addressing challenges they are facing. No one technology solution is the best solution for every problem that exists. Oracle Enterprise Edition, Oracle Standard Edition, Oracle Express, MySQL, Oracle Application Server, Apache, JBoss, BEA, Eclipse, JDeveloper, Linux, PHP, Ruby on Rails, etc. all have features that can leverage what customers are trying to do. I have nothing against Windows or IBM it has just been my preference to work with Oracle and MySQL environments. Oracle and BEA have played well in the large enterprise commercial environments. MySQL has played well in Internet and web applications. For anyone to say other vendors do not provide a good solution is pretty closed minded. What I like to do is understand what a customer is trying to do and weigh the pros and cons and let them make the decision that is best for customer.

Most companies are running different types of applications.

• Java and SOA are extremely scalable and powerful solutions that are great when those benefits are needed.
• Ruby on Rails and PHP are great for web based applications that do not need a lot of database features.

There are current and future costs and ramifications associated with hardware, database, application server and application development decisions. With all the options out there customers need to make sure they align their purchases with their business objectives.

Trends for DBAs

I grew up in the old days when the primary tools used to manage databases was Unix shells scripts and my development tool was vi. This was back in the day when if you showed other DBAs a script for monitoring and tuning, they would respond to it the way cavemen would look at fire. Those days are long gone.

Whether it is open source, vendor or 3rd party tools DBAs need to use software to help them manage their environments. Whatever the tool: Nagios, Mon, Zenoss, Cacti, MySQL Enterprise Monitor, etc. it is important to use tools to manage a database environment. In the monitoring and management side this includes the entire infrastructure which includes the application server and applications environment. As a DBA it is very important to be in a proactive environment where the administration infrastructure is scalable as more database instances and database schemas are added to the environment. The negative ramifications of using scripts and not software to monitor and manage a database environment include:

• The more manual scripts (Unix, Perl, etc.) the more you are building a high maintenance environment that has to be supported.
• As your environment changes with different software, application servers, applications, etc the more the scripts need to be modified.
• New DBAs usually will not want to bet their jobs on someone else's scripts. Working scripts often get thrown away by new DBAs which will replace existing scripts with their own scripts.
• Manual scripts are not as scalable as using software.
• Using manual scripts are not cost-effective compared to using software for monitoring and management.
• Manual scripts are more error-prone than using software.
• Manual script environments are usually more reactive versus proactive. Reactive environments are not conducive to high availability.
  

Companies are looking more for DBAs that can help make strategic decisions, understand the business, have strong communication and management skills and understand how to manage databases from an enterprise perspective. This is different that the traditional heads down, perform technical tasks and write scripts type of DBA.

Leveraging MySQL Environments for Scalability

One of the biggest mistakes organizations make when they look at MySQL, they look at just the MySQL Database Server. There are a lot of tools available in MySQL environments that can offer tremendous scalability to MySQL database servers while keeping the total cost of ownership down. Some of my favorites include:

• Memcache - Memcache is a high performance distributed caching system. This offers tremendous benefits to dynamic web appliations. This caching system can take large loads off the database server and distribute across low-cost servers with a global cache. Most large scale MySQL database environments use memcache.
• Replication - MySQL replication supports the distribution of MySQL across multiple low cost servers. Replication can be implemented with no additional license fees.
• LAMP - Linux, Apache, MySQL, PHP/Perl/Python (LAMP) is a low cost proven platform for implementing web applications.
• Monitoring - Nagios, Mon, Zenoss and Cacti are excellent monitoring tools for MySQL database environments. Zabbix is a product I have not looked at but their website information on their product definitely got my attention. The MySQL Enterprise Monitor is a licensable product but it can be used at a fraction of the cost of other database vendor monitoring tools and 3rd party solutions.
  

MySQL Migration Toolkit

The MySQL Migration Toolkit is a nice easy to use Wizard GUI tool for migrating databases to MySQL. The following site http://www.mysql.com/products/tools/migration-toolkit/ has some really nice tutorials for how to migrate the following databases to MySQL:

• SQL Server
• Oracle
• Access

The MySQL Migration Toolkit will facilitate the migration of the following database schema objects:

• Tables
• Indexes
• Views
• Assists with Stored Procedure Migration
• Assists with Trigger Migration

Some features that stand out about the MySQL Migration Toolkit:

• The MySQL Migration Toolkit allows direct server to server migration. The migration tookkit can run remotely.
• Data can be transfered online or a snapshot of the data can be stored for executing a transfer offline at a later time. A backup can be created of the stored data as well.

Migrating Access to MySQL
MySQL is an excellent migration strategy for organizations using Access. Migrating to MySQL provides the following benefits to existing Access environments:

• Extremely low startup costs.
• Ability to leverage the LAMP stack and Web 2.0.
• Portability to major releases of Linux, Unix and Mac OS platforms.
• Increased operational flexibility from a closed proprietary environment to open source.
  
• Significantly increased scalability.
  

Migrating SQL Server to MySQL

MySQL is an excellent migration strategy for organizations using SQL Server. Migrating to MySQL provides the following benefits to existing SQL Server environments:

• Extremely low startup costs.
• Ability to leverage the LAMP stack and Web 2.0.
• Portability to major releases of Linux, Unix and Mac OS platforms.
• Increased operational flexibility from a closed proprietary environment to open source.
• Relatively low cost increased scalability compared to SQL Server scalability costs.
  

Oracle and MySQL Environments

I believe Oracle and MySQL play well together more than any other two RDBMS systems.

• MySQL does well in the small and medium database range and supports the LAMP stack and Web 2.0.
• For web applications that do not leverage a lot of database features. Using MySQL at the lower end can greatly reduce the total cost of ownership.
  
• Oracle and MySQL can play very well together to maximize what a customer needs to do while minimizing cost.
• Oracle offers tremendous scalability and an incredible amount of features to address complex problems in large organizations.
  
• MySQL offers relatively low cost increased scalability compared to Oracle scalability costs. No costs associated with using partitioning and replication. MySQL Cluster is a fraction of the cost of running Oracle RAC.
  

MySQL and Oracle Parallels

When I look at the current and future releases of MySQL I see a lot of parallels to Oracle releases. Although MySQL and Oracle have taken different approaches there is a thread of commonality.

• MySQL 4 and Oracle 6 releases were acceptable releases for their time but their features limited the software from having a larger impact in the database market. I always joke that Oracle Version 6 made Sybase and Oracle 7 along with Sybase 10 helped destroy Sybase.
• MySQL 5 and Oracle 7 took the database releases so they could take new large steps in the database market.
• MySQL 6 and Oracle 8 are releases that take the database software for each company to gigantic new levels in terms of scalability and the ability to handle significantly larger systems. In terms of performance, high availability and reduces administration.

I believe the future of MySQL with version 5 and the upcoming version 6 is very bright.

MySQL in the Enterprise

In the last few months MySQL has taken serious steps to have it play a larger role in the enterprise. For MySQL to take the next steps the following needs to occur on the technology side:

• Help customers understand the scalability of MySQL.
  
• MySQL needs to expand in the small to medium size data warehouse market. MySQL excels on delivering large volumes of Internet content at high speed. High read rates are what you need to make a data warehouse work. Using MySQL replication, organizations can scale horizontally at inexpensive incremental rates.
  
• MySQL needs an inexpensive striping and mirroring storage strategy to help organizations that can't afford an expensive EMC or Veritas solution. Hmmm, I wonder if there is a company out there who could help MySQL's storage strategy? :-)
  
• Organizations need to realize there are commercial open source solutions like Pentaho that can deliver business solutions, BI and reporting capabilities in the small and medium sized database environments. There are open source commercial solutions that can help MySQL environments deliver medium sized enterprise solutions. Saving 6 and 7 figures on licensing of software can provide a lot of flexibility in terms of hardware and software options.
  
• Organizations new to MySQL need a better understanding of how OS snapshots and InnoDB Hot Backup can be used to provide hot backups in OLTP environments.
• More MySQL DBAs that have an enterprise perspective. Oracle DBAs are the perfect source. Too many organizations look at MySQL and do not understand how to set it up properly to leverage what it can do.
  
• Organizations like Google, Yahoo, YouTube, Flicker, etc. know how to build highly scalable MySQL environments. Products like Memcache can greatly benefit MySQL scalability. Scaling MySQL is still a skill not known to a lot of companies.
  
• MySQL and industry analysts need more bench marks on exactly where MySQL reaches it's limit on the OLTP side. The knowledgeable industry expert understands MySQL has proven to deliver high read rates. For customers to feel comfortable using MySQL in more OLTP environments, customers need to understand where MySQL tops out on the OLTP side.
  

Pentaho's Bee

Has anyone else been mesmerized by looking at the bee on Pentaho's website. Something about that bee is pretty interesting. I wish they'd take it further and create a screen saver where you can configure multiple bees on your desktop. I'm not even a bee fan.

Oracle and MySQL - A winning team

Oracle is definitely the Mercedes Benz of database servers. We can debate the merits of different databases but in terms of the most features, Oracle definitely stands alone. Excellent high availability features such as RAC, Data Guard and Streams offer excellent enterprise solutions. So for running enterprise business applications such as PeopleSoft, JD Edwards, Siebel and the Oracle EBusiness Suite the Oracle Database Server is in a class by itself.

When you look at all the applications running in an organization, what percentage are enterprise class applications that require an Oracle database server. I would say the major percentage of applications running in an organization do not need an enterprise database server like Oracle. This is where MySQL plays nice. Heterogeneous environments running Oracle and MySQL will run at a fraction of the costs of running Oracle-SQL Server, Oracle -DB2 or SQL Server-DB2. Also MySQL being a friendly open source company does not bring in the religious issues Oracle DBAs would have with SQL Server or DB2. Some examples of how Oracle and MySQL would be a cost effective solution:

• Greatly reduced software costs. For example backup and recovery is free in Oracle (Recovery Manager - RMAN). In a heterogeneous environment, a company can buy a great online backup solution such as InnoDB Hot Backup that is roughly $1000 per server. If running Oracle with SQL Server or DB2 the 3rd party backup solutions or vendor add-ons can easily run into six figures.
• If an organization has already purchased Oracle Grid Control it would be very easy to have Oracle Grid Control monitor MySQL database servers.
• Oracle SQL Developer could easily support MySQL database environments. Someone told me they got Oracle's SQL Developer to hook into MySQL easily. I'll have to try and find time to test this.
  
• Oracle JDeveloper can deploy into JBoss environments and other open source application servers.
• Oracle's stored procedure language PL/SQL is very close in syntax to MySQL's stored routine language. You could easily write a parser in Perl that would address most of the syntax differences.
  

If I could ask Larry one question it would be if he regrets not trying to go after MySQL. I wonder if the Oracle acquisition team was so focused on the business applications market they did not look into the database market as closely as they should have. If Oracle had purchased MySQL could a strategy have been developed to use MySQL to keep SQL Server out of the lower end database market. Would the purchase price of MySQL have generated a significant ROI to justify the purchase by being able to help keep SQL Server out of Oracle's lower end market? Hmmm. Too bad, I guess we'll never know.

Understanding the Total Cost of Ownership for Databases

Vendors often talk about the Total Cost of Ownership (TCO). A MySQL environment offers significantly reduced overall costs and greatly reducing the total cost of ownership. When purchasing a database companies look at the license numbers of the database but often underestimate the surrounding costs of running a particular database. Overall TCO also includes the following costs:

• Hardware
• Application Servers
• GUI development tools
• Monitoring tools
• Administration costs
• 3rd party software
• Technical skills
• Scalability
  

MySQL offers a TCO that is a fraction of other database vendors. The following are some examples of how the costs of running a MySQL environment are significantly reduced:

• Hardware: MySQL database servers take significantly less hardware resources compared to Oracle DB2 or SQL Server. There is also more flexibility in choosing Windows, Linux, Unix or Mac OS platforms.
  
• Application Servers: MySQL environments usually use Apache or JBoss run at a fraction of the costs of running application servers from IBM, Oracle, BEA and Microsoft.
• Monitoring Tools: MySQL environments use tools like Nagios, Mon, Zenoss, Cacti are free or a fraction of the costs compared to vendor or 3rd party tools that often have licenses in the 6 and 7 figure range.
• GUI Development Tools: MySQL environments often run SQLYog, Navicat, MySQL Query Browser that are free or run at a fraction of the cost of 3rd party tools for the larger database vendors.
• GUI Admin Tools: MySQL Administrator, phpMyAdmin and Enterprise Monitor are free or run at a fraction of the cost of 3rd party tools for the larger database vendors.
• BI and Reporting: BI Open Source tools from companies like Pentaho run at a fraction of the costs of BI solutions from the major vendors.
• Scalability: Features like partitioning and replication are free in MySQL. So as your environment grows there are no additional license costs. MySQL Cluster is a licensable feature but it also is a fraction of the clustering solutions from the larger database vendors. Database servers are going to grow, so its great to know that a MySQL environment allows you to use free features or features that do not have large incremental increases the larger your databases grow.

When looking at a database vendor make sure and consider the overall Total Cost of Ownership and not just the costs of running a database server.

Database Market Share

Mark Twain once said, "Figures often beguile me, particularly when I have the arranging of them myself; in which case the remark attributed to Disraeli would often apply with justice and force: "There are three kinds of lies: lies, damned lies and statistics."



Whenever I look at statistics and numbers I not only take them with a grain of salt but I also try to understand how they arrived at the numbers. When you look at the analyst's reports on the top database vendors it is often based on total revenue or licensable revenue. So how does open source numbers come into play. The same industry analyst's that show MySQL as having very small market share based on revenue also state estimates of between 8 - 10 million MySQL instances are currently running. Analysts also estimate MySQL binaries are downloaded 40 - 50 thousand times a day.

MySQL is encountering incredible growth but the growth is often hidden in the way numbers are collected. There is a lot of momentum and energy around open source, it will be interesting to see how the industry analysts use numbers to track this growth.

George Trujillo at RMOUG Oracle Conference in Denver

I attended the RMOUG Oracle Users Conference in Denver, CO this week. RMOUG is an excellent local Oracle users group conference to attend. I delivered a presentation on:

Understanding the MySQL Architecture for Oracle DBAs

• This is a fast paced presentation focused on showing Oracle DBAs and Developers key features and benefits of using MySQL and the LAMP stack for projects. Topics will focus on: best practices for configuring a MySQL database server, understanding key differences between Oracle and MySQL and OFA versus MOCA. Attendees will also be introduced to MySQL replication and Cluster configurations. This is an excellent bootstrap presentation for developers and DBAs that may be looking at using MySQL and the LAMP stack for future projects.
• Audience: Developers and DBAs
• Summary: A bootstrap presentation focused on showing Oracle DBAs and developers key features of MySQL and the LAMP stack.

I delivered it entirely from an Oracle perspective, using Oracle examples and it was very well received. I love delivering this presentation because I always get some great comments from Oracle DBAs who are really surprised how much MySQL can do. I show them MySQL replication and I alway get at least a couple of wows.

I also delivered the following presentation:

Demystifying Middleware Technologies

• This presentation is designed to demystify popular Middleware technologies and explain how these technology components work together. Topics include: J2EE, Application Servers, Web Services, XML, Application Frameworks, WSDL, BPEL, SOA and business rule engines. Attendees will learn how each of these different Middleware components work together in application servers. This is an important presentation for developers and DBAs who would like to learn more about the middle-tier.

I'm very comfortable in the Java and SOA world so I had a lot of fun with this presentation. Not sure why, I delivered the first presentation with energy and fire and the second presentation as a fire side chat. It may have been due to the high octane coffee I had at the coffee stand in the Colorado Convention center. :)