Top Ten Things to do before installing MySQL

I think it is very important that DBAs (especially new ones) do sufficient planning, physical design, risk analysis and backup/recovery planning before installing MySQL. MySQL is an easy database to install and configure on a wide range of platforms.

However if sufficient planning is not performed up front, a DBA is going to have problems as their databases grows, issues come up and more users start accessing the system. A top 10 list of things to do before installing a MySQL database include:

1. Scalability: Understand the purpose and potential growth of your database.
2. Physical Design: Determine the appropriate physical layout of your database. The defaults are not always the best long term layout.
3. Storage Engines: Select the appropriate storage engines and how they impact performance and your backup/recovery strategies.
4. Backup and Recovery: Layout your backup and recovery strategy. Pick the appropriate backup/recovery tools for your environment. Then test your backup and recovery strategies sufficiently. Test recovery as well as backups. Understand your recovery times.
   
5. Database Environment: Understand the number of databases that will be created and the maintenance ramifications of the database layout.
6. Disaster Recovery: What are the business costs and ramifications of this environment (database server, host server, site, network, ...) being down? Perform appropriate risk analysis.
7. Security: Understand how security is being managed and maintained for your users and your databases. Make sure you secure your userids and passwords.
   
8. SQL_MODE and Isolation Level: Set the appropiate SQL_MODE and Isolation Level and make sure you understand the ramifications of the these selections. If running an OLTP environment I highly recommend setting the SQL_MODE to a minimum of traditional.
   
9. Storage: Understand your physical storage layout. Make sure you know the ramifications, limitations, scalability and performance of using separate disks, storage arrays, etc.
10. Best Practices: Define a set of best practices that determine your physical layouts, naming conventions, management tools, infrastructure management and document everything.

A few areas that did not make my top ten but I would give honorable mention.

• Make sure and secure the hardware platforms, networks and operating systems where your databases will be running. Along with that take out operating system features you should not be using on a database server. This includes features such as Telnet, web services, print services, etc. These unnecessary OS features have security issues to watch out for and add additional weight (impact performance) to the operating system.
• Read the README files and research the release you are moving to.
• Determine what software you are going to use to manage your MySQL database environment. If you have multiple MySQL databases you should consider the MySQL Network Monitoring and Advisory Service Advisors.
  

Dependent on your enterprise software licensing, the following advisers are available from MySQL.

• Administration Advisor
• Schema Advisor
• Performance Advisor
• Security Advisor
• Memory Usage Advisor
• Replication Advisor
• Custom Advisor
  

The five P's apply to database configurations. Proper Planning Prevents Pitiful Performance. I had a higher pain threshold when I was younger versus today. I have found that proper planning can help you avoid a lot of pain, anguish and late evenings as a DBA.

Optimal MySQL configurations

I read a book one time called the "Seven Habits of Highly Successful People". After I read it I thought what a waste of money I already knew everything that was in the book. There were no new ideas in it. I then realized a number of the seven habits I was not doing. And if I did them I would be more successful. I then realized maybe there is a reason the book stays in the New York Times best seller list year after year.

The following points are similar to the seven habits. They are a set of good practices and guidelines that if you follow, will make your database management a lot easier to support. If you follow them, you'll spend a lot less time working nights and weekends.

Any set of database management best practices need to include:

• A standard way of organizing software and data on disks.
  
• Separate database software from operating system and other third party software.
• Separate data from log files. The files that store your data and the files need to recover your data should never reside on the same disk drives and controllers.
• Data, index files and log files should be distributed to reduce I/O bottlenecks and to maximize recovery and reduce administration.
• Having proven and tested (on a regular basis) backups.
• Facilitate the management of installs, upgrades and backups. The routine tasks required of DBAs.
• Prepare for and manage the growth of data and databases.
• Minimize the fragmentation of tables and indexes.
• Have appropriate safe guards so disk or hardware failures will have a minimal impact. DBAs have to work with the people making the purchases so they have the right hardware and disk configurations. Distributing data across multiple disk drives reduces the impact of individual disk failures.
• There should be a consistent pattern for directory structures for software, backups, data files, log files, administration scripts, start up files and other other files associated with MySQL databases.
• The change in software directories, data directories, login directories should have minimal if no impact on database scripts and applications.
• Administrative information for each database such as logs, backup scripts, administration scripts should be separate for each database in a well organized directory structure pattern.
• Files of different types should be organized in separate directory structures.
• Separate file systems should exist for software, data, log files, backups, etc. as much as possible.
• Protecting the hardware platforms and operating systems that are running MySQL databases.
• Use a consistent naming pattern for all data files, index files, log files, scripts, database names and file systems supporting files related to MySQL databases.
• If your organization is using storage drives, make sure you understand are are working closely with the storage management teams to optimize any striping and mirroring of database storage.
• Software to manage and monitor a complex environment. The Enterprise Monitor tool from MySQL can be found at http://mysql.com/products/enterprise/benefits.html.

Database and software configurations need to address the constant tasks a DBA performs.

• Adding new users.
• Duplicating databases.
• Managing changes for development, test and production database environments.
• Upgrades of operating systems, databases, applications and hardware.
• Adding storage for data increases.
• Creating new databases.
• Adding new hardware, disk drives, etc.
• Constantly distributing I/O workloads.

I realize if you are a new DBA this can be a little overwhelming. Starting simple can be the best approach and as you get more experience following more of the above practices will become important. If you are an experienced DBA review the above guidelines and your best practices to make sure your management practices are honed to a fine edge.

Skillsets for the successful MySQL DBA

DBAs have spent years developing their expertise in database management best practices, backup/recovery, performance tuning and writing really cool scripts. Today's successful DBA needs skills beyond just the management of their databases. These skills include:

• Operating Systems: Having sufficient knowledge in the operating systems their databases run in.
• Best Practices: Maintaining best practices for database management.
• Automation: Automating your database management.
• Supporting Multi-tiered Infrastructures: Being able to manage databases in a multi-tiered environment.
• Supporting Development: Being able to support or a least understand the development environment for your databases.

Operating Systems

If your database is running on a Windows, Linux or Unix systems the more knowledgeable you are in the operating systems of your database platforms the better you are at troubleshooting and performance tuning your database environment. The successful DBA will
optimize the operating systems their MySQL databases are running on.

• Todays operating systems such as Windows, Linux and Unix are very robust operating systems. Most operating systems are installed with services and RPMs that are not needed for the database platform. For most production servers running MySQL, OS features such as FTP, Telnet, Print Servers, etc. should not be running on the server platform running MySQL. Having the operating systems loaded with unnecessary features creates security violations and has an overweight operating system taking unnecessary resources away from the databases. A server running databases should have the operating system optimized for running database servers.

Best Practices

Most DBAs are not supporting a single database. They are probably part of a team supporting multiple databases. DBAs need to have a consistent set of management guide lines (see Optimal Configuration for MySQL article) for managing their databases. Setting up an optimal configuration for MySQL database environments and following a set of consistent guide lines will minimize mistakes, reduce down time and ensure optimal configurations for performance.

DBAs also need to have a method for managing their infrastructure information. Errors that occur once are likely to occur again. Each DBA should be logging errors and everything they do to manage their database environments. DBAs need to periodically review how they are spending their time and the issues they address on a daily basis. This approach will often have a DBA recognize consistent patterns of their management that can help them identify areas that need to be worked on. Sometimes seeing the forest through the trees can be difficult. Periodically (weekly, monthly) reviewing daily tasks can help identify trouble areas that need to be worked on.

Automation

The traditional DBA has worked on writing scripts to manage enterprise environments. DBAs get excited when they write a really cool script for managing their databases. They show this script to other DBAs, and its like a caveman showing other cavemen fire for the first time. There are a lot of ohhhs and aughhhs. But writing scripts is no longer the best way to manage databases. The more scripts you write:

• The more you are building a high maintenance environment. More scripts will keep adding to the complexity and maintenance of their environments. I was at a client site that was running over a hundred databases. They were upgrading their operating system and databases to a higher release in a multi-phased approach. They wanted me to go out and update their backup scripts on 140 platforms. It would be better to develop a centralized method to running backups versus having different scripts that have to be constantly updated. Using a scheduler to facilitate tasks that need to be automated is much better than constantly updating cron scripts across different platforms.
• The more you are dependent on the skills of an individual person. Once a new DBA moves in they often replace the scripts written by a previous DBA. They don't want to bet their job on scripts written by someone else. This reoccurring pattern can become a significant waste for organizations over a period of time.
  

Software should be used for:

• Scheduling reoccurring tasks. Backups, batch jobs, maintenance routines, reports, etc. should all be coordinated through a central repository.
• Monitoring your databases as well as the operating systems, file systems, application servers, software services and all other components of an environment that impact the database.

I realize smaller systems may have not the budget or infrastructure for automation software. However there is a lot of free software out there that can help you with automation and monitoring.

Supporting Multi-tiered Infrastructures

Web applications are the standard way to develop applications today. Most new applications are web applications and not client/server applications. Web applications include application servers. Web applications are written in Java, PHP, Ruby on Rails, Groovy, etc. DBAs don't have to be an expert in Java and the other web development environments. However they should at least be conversational about the technologies surrounding their databases.

The application server is also becoming a central piece of the architecture. Internet applications are retrieving thousands or millions of records across the network and then processing them on the application server. Middleware developers are very knowledgeable on developing applications for the middle tier, however their priority is usually not how to optimize these applications for database performance. The issues between the database server and the application server are creating a lot of issues in organizations. The more a MySQL DBA can support Apache, JBoss or other application servers accessing MySQL the more valuable they are in the market place.

Stored procedures can be a great way to reduce network traffic. Processing and filtering the database in the database server before sending the data to the application server can reduce the network traffic between the database server and the application server. Also a database server is usually much more efficient processing and filtering data that Java, PHP or any other web development language.

Supporting Development

Java, PHP, Ruby on Rails, Groovy and other web development languages are becoming common place in MySQL development environments. A DBA that can support developers provide a lot of increased value to an organization.

Conclusion

The one constant for MySQL DBAs is that the following areas are going to continue to grow in their MySQL environments:

• The number of databases they have to support.
• The volume of data in databases is going continue to increase at ever increasing rates.
• Clients are going to request faster and faster response times.
• Requirements for less and less down time.

The world of database administration requires that MySQL DBAs constantly work smarter and just not harder. MySQL DBAs that are constantly working on improving their best practices, automation and skills in the environments their MySQL databases run in are going to increase in value to their organizations and in the market place.

The good thing is that MySQL databases are becoming more and more popular in today's competitive environments. Open source environments using Linux, Apache, JBoss, PHP and Java are becoming more and more attractive to corporate America as well as the small business and government areas. The skill sets identified in this article will definitely help separate you from other DBAs in the marketplace.

Key Features in MySQL 5.1

For those of you new to MySQL 5.1 , I am going to provide a list of key features of MySQL so you can get a feel for the functionality of MySQL.

• Partitioning - Partitioning provides scalability, performance and administration flexibility to working with large tables and indexes.
  
• Row level replication - Row level replication adds to replication functionality in MySQL.
• Database events - Allow DBAs to schedule batch events from within the database.
• Table Logs - Give DBAs the option of logging to database tables instead of operating system files.
  
• Platform Independence - MySQL can run on most versions of Unix, Linux, Windows and Mac OS.
• Scalability - MySQL has grown so it can support 100s of gigabyte and terabyte sized databases. MySQL is known for very fast access.
• Stored Procedures and Triggers - MySQL supports a database programming language almost exactly like Oracle’s PL/SQL language. It is a very easy transition for Oracle developers.
• Powerful Storage Options - MySQL supports a good variety of storage engines (at the table level) for leveraging transactional, read performance etc. to allow a DBA to maximize the the configuration of the database for the business. For example, InnoDB contains strong transactional features, while MyISAM provides for very fast access for web based applications.
• Views - MySQL supports traditional views (logical tables) as well as in-line views (in FROM clause).
• Large Table Support- MySQL supports a number of partitioning options to support very large tables.
• Traditional relational database functionality - MySQL supports traditional transaction features such as commits, rollbacks, row-level locking and foreign keys. Joins, sub-queries are also supported.
• High Availability Architecture - MySQL supports a replication architecture for availability and scalability. This allows multiple MySQL databases to be spread across multiple systems using high-speed interconnects.
• Full-text Searches - MySQL supports full text searches. Very impressed with how easily it was to implement this functionality. Initial tests are very fast.
• Globalization - Strong support for Unicode for multiple character sets.
• Large variety programming interfaces - An extremely large variety of APIs for PHP, Java, Ruby on Rails, Groovy, .NET, Python, etc. Supports ODBC and JDBC as well.
• GIS Functionality - MySQL supports two-dimensional data for accessing and processing.
• Clustering - Clustering spreads software components across multiple machines for high availability and fail-over. Clustering has techniques for keeping the different platforms synchronized.

MySQL Licensing

MySQL is available under GNU Public License (GPL), so someone can download the source or the binaries and use them for free. Commercial licenses and support are available for higher profile and production environments. MySQL Enterprise is available and offers support, consulting and online knowledge base. More details can be found at www.mysql.com/support/. Make sure you read the license agreements carefully.

MySQL Releases

• Alpha - New release requiring testing. Allows organizations to be on the bleeding edge.
• Beta - Major changes are not expected but testing is required and bugs are likely to be found.
• Gamma - Beta release has stabilized and searching for final bugs before going GA.
• GA - General Availability (GA) is a mature stable version for production environments.

Before Going to Any New Release

Before going to any new release make sure you have looked at a bug report of the new features. Most customers are still on 4.0. You always want to make sure your eyes are wide open and you are aware of potential issues with new features. As an early adopter you may need to weigh the benefits of new features versus potential issues with a new release.

Excellent links on MySQL 5.1 Release

Below are a couple of links on MySQL 5.1 that you should definitely look at.
http://dev.mysql.com/tech-resources/articles/mysql-5.1-recap.html
http://www.planetmysql.org/kaj/?p=131

Middle-tier Terminology and Concepts

If you are at a party, barbecue, tail-gate or other social event the topic of middleware or middle-tier may come up. This article will teach you enough to join in or maybe more importantly to walk away.

There are a lot of different languages used to build web applications. The key point to remember is that all of them end up generating HTML code that is returned to a browser. This article is going to focus on key areas of Java environments.

Key technology components developers may need to learn independent of vendors:

• Java 2 Enterprise Edition (J2EE)
• Web Services
• Object Relational Mappers (ORM)
• Service Oriented Architecture (SOA)
• eXtensible Markup Language (XML)
• Security Assertion Markup Language (SAML)
• Business Process Execution Language (BPEL)
• Java Frameworks and Design Patterns
• Lightweight Directory Access Protocol (LDAP)
• Hyper Text Markup Language (HTML)

This technical foundation is based on proven industry standards. Defining this technical foundation is helping organizations understand the skill sets their teams will need to support current and future web-based applications.

The ability to quickly build composite Internet applications is very important to a lot of organizations. This can significantly reduce costs and time to market. The ability to have a high degree of reuse is also an important key for this. The tools we’ve listed have become a popular way to achieve these results.

Terminology and Concepts

The following are high level definitions of key technical features, concepts and terminology:

• Eclipse, NetBeans and JDeveloper - These are Integrated Development Environments (IDEs) used by developers to build web-based applications. There are wizards and interfaces for developing with J2EE, SQL, HTML, Web Services, SOA, JSF, JSP, Servlets, EJBs, Struts, etc. This IDE should be looked at as a development environment for developing database and Internet applications.

• Java 2 Enterprise Edition (J2EE) - J2EE is an industry standard for developing enterprise multi-tiered applications. J2EE is an architecture and framework for enterprise wide applications. J2EE applications include JSPs, JSFs, Servlets, ADF Faces, EJBs, etc.
• Struts and Frameworks (ADF) - Struts is a J2EE development framework that is based upon best practice design patterns for building Web applications. Struts is based upon the Model-View-Controller that separates User Interface, Business and Data logic. J2EE frameworks can be complex and it can be difficult to determine how to organize different components. Struts helps organize J2EE components into a well organized Web application. Spring is a multi-layered Java/J2EE application framework.
• Hibernate and TopLink - Object Relational mappers that manages the communication between Java (object-oriented) applications and the database (relational).
• XML - XML has become the universal language for transmitting data structures independent of the environment. J2EE, Web Services, SOA and BPEL use XML.
• Web Services - Web (software) Services uses XML standards and transport communication protocols to exchange data between applications. Web services allow different types of applications to communicate. Web services are standards that define the semantics for how software communicates.
• Service Oriented Architecture (SOA) - SOA is an architecture that defines how loosely coupled software services communicate with each other. SOA increases reusability, that allows different software services to identify and communicate with each other.
• Business Process Execution Language (BPEL) - BPEL is a standard for organizing reusable web services into more than one type of process flow.
• Identity Management - Identity Management provides enterprise management of user identities across resources inside and outside the firewall.
• Single Sign-On (SSO) - SSO provides unified authentication allowing a user to logon once and SSO will manage single sign-on capability across applications.
• Clustering - Clustering spreads software components across multiple machines for high availability and failover. Clustering has techniques for keeping the different platforms synchronized.

Conclusion

Current tools hide a lot of the technical complexity behind middle-tier solutions. However, dependent upon the customizations made to applications, developers may need a strong understanding of the technical components of middle-ware technology.

There are three types of developers that need to learn the products in the middle-tier Technology Platform:

• Traditional Developers - Developers that have used Oracle Forms, Reports, Discoverer, PowerBuilder, Cobol, Visual Basic, C/C++, etc. are going to need to learn the technologies supported in Internet development environments. Most Java developers are going to have to be knowledgeable with some or all of these technologies.
• Traditional Applicaiton Developers - If you are a developer who has customized Oracle, Siebel, PeopleSoft, JD Edwards, SAP, Retek, Stellent and you are not likely to hit the lottery jackpot, then learning to develop web applications is in your future.
• Internet Developers - Developers building Internet applications are likely to work with some or all of the products we have discussed in this article. Internet developers may be working with Eclipse instead of JDeveloper, or Hybernate instead of TopLink or Apache or JBoss. However the principles of Frameworks, ORMs, Design Patterns, J2EE, Managing Web Services, XML and SOA are still going to be technical areas that need to be understood. Internet developers may also be using PHP, Python, Javascript, Ruby on Rails, Groovy or other web development solutions.

It is important to understand that components such as J2EE, Web Services, XML, SOA, etc are not just tied to any vendor. They are based on open standards. Skills in the technology discussed in this article are incredibly valuable in any Internet development environment. LAMP or some variation of LAMP can provide some very powerful yet very inexpensive solutions compared to large vendors such as IBM and Microsoft.

These open standard components are moving into your future like a freight train. They require skills that are going to be incredibly marketable and valuable in the future.

Looking at MySQL and Oracle

I am going to build a list of features, commands and concepts from MySQL and Oracle. This comparison will not go into the pros and cons or which database has more options or features. The goal is to give someone a feel for the differences. I am a big evangelist for Oracle and MySQL technologies. This is a list I will build over a period of time.

The following are websites for downloads, documentation, code, blogs, forums, whitepapers, newsletters, etc. Be careful about going to these sites you will get hooked and spend hours on these two great sites.

• dev.mysql.com (MySQL)
• otn.oracle.com (Oracle)

Feature/Functionality

The Oracle Database Server is the flagship of large enterprise databases. Oracle excels at the level of data warehouses or running large enterprise applications like the financials for a Fortune 100 company. At the same time MySQL is generating a lot of momentum because customers are seeing they can implement it at the fraction of the cost and complexity of Oracle. Each database has their place in the market in which they fit well.

MySQL does not have all the functionality of the Oracle database server, but that is also what makes it so attractive. Not everybody needs all the functionality that comes with the Oracle database server. Most companies I go to are paying for all of Oracle's functionality but only using a very small percentage of what it can offer. Also, as web applications become more popular more functionality is being built in the middle-tier versus using database functionality.

With MySQL it is either free or its commercial licenses are a fraction of Oracle's cost. MySQL environments usually require less hardware, resources and money to get them up and running. The cost comparison makes it a great option for Small and Medium Businesses (SMB), government and state organizations and for projects that do not need the enterprise flagship functionality of Oracle. As more and more web applications are being built, J2EE and Internet developers are building more functionality in the middle-tier, negating a lot of the functionality that comes with an enterprise database server.

I've been very impressed with how fast MySQL can process different types of SQL statements and how good of a job the optimizer does in the MySQL environment. The code is lighter and more efficient because it does not have as many options to wade through. Sometimes being light and nimble can offer tremendous advantages for application developers that understand how to leverage the functionality of MySQL.

Products

Below is a list of popular products used with MySQL and Oracle:

MySQL is an open source solution. Products like Eclipse, Apache and JBoss are used to offer a multi-tiered web solution using MySQL. MySQL usually uses other open source products to provide a full multi-tiered offering. LAMP or a derivative of LAMP is seen in MySQL multi-tiered environments.

Oracle offers a complete line of products with JDeveloper, Oracle Application Server, SQL Developer, etc. Third party products are available as well (i.e. Toad, SQL Navigator, UniCenter, ...). Open source products such as JBoss and Eclipse can also be used in Oracle environments. The big difference is that Oracle offers a large line of vendor commercial products. The table below only shows Oracle specific products under the Oracle section.


This table will provide an example of what a MySQL and Oracle environment may look like.

MySQL	Oracle
Eclipse	Oracle JDeveloper
Struts, Spring	Application Development Framework
J2EE, PHP, Ruby on Rails, Groovy, .NET	J2EE
Hibernate	TopLink
Apache, JBoss	Oracle Application Server
TCP/IP, Unix Sockets, Shared Memory, NT Pipes	Oracle Net Services
Mysqladmin, MySQL Administrator, Phymyadmin Enterprise Monitor	OEM Database Control
MySQL Query Browser, Toad, Navicat	Oracle SQL Developer
mysql client tool mysqlimport mysqlcheck mysqldump myisamchk myisampack	SQL*Plus client tool SQL*Loader dbverify Data Pump, RMAN dbms_repair package
MySQL (GPL) MySQL Enterprise	Oracle Express Oracle Standard Oracle Enterprise
OpenLDAP, Fedora Directory Server	Oracle Internet Directory

Note: The reader needs to understand the products are usually not always equivalent. Some examples include:

• Oracle environments also support Ruby on Rails, .NET, Groovy, .NET, etc. However, the big push by Oracle is to use J2EE since Oracle Fusion applications run using J2EE. Also, organizations usually do not purchase a 100K or 1M Oracle license to run Ruby on Rails for their enterprise. Yet, Oracle does offer Oracle Express, a free database up to 4GB of storage where PHP, Ruby on Rails become a better option.
• Mysqldump is a very simple tool for creating a backup. Oracle Data Pump and Recovery Manager (RMAN) are tools that are more enterprise level tools.
• MySQL does not need additional networking software to work. It uses built-in networking protocols like TCP/IP. Oracle Net Services runs on top of TCP/IP. Oracle Net Services may or may not be free depending on what you are using on the client side. I realize Oracle has EZ Connect but it is not a replacement for Oracle Net Services.
  

Databases

One of the differences between MySQL and Oracle is how they treat the term database. A MySQL database is closer to an Oracle schema and all the storage for that schema. I’ll add an architectural picture of MySQL and Oracle that will help with this.

MySQL

1. The MySQL physical server environment is made up of all the individual databases, binary log, and additional files and logs.
   
2. A MySQL database is how to organize a group of database objects such as tables, views, triggers, etc.
3. There would be a separate MySQL database for a human resource and a sales system to organize each set of objects separately.
   

Oracle:

1. In Oracle the physical server environment is a database that encompasses all the physical files (storage) for a specific Oracle server (instance). This includes all files for data files, index files, redo log files, archive log files, etc.
2. Oracle organizes database objects in a schema. A schema is an Oracle user and all tables, views, sequences, synonyms, stored procedures, etc. the objects and resources owned by the user.
3. Oracle would use a separate “schema” for storing information for a human resource and a sales system if they were in the same database.
   

Summary:
A MySQL server environment is made up of server memory, background processes associated with multiple databases.

An Oracle server environment is made up of server memory and background processes (instance) associated with a single database organized with multiple schemas.

Startup Files
MySQL - The my.cfg or my.ini is the startup file for a MySQL server environment.

Oracle - An init.ora or spfile.ora file is used to start an Oracle server instance.

Metadata
The way MySQL and Oracle DBAs access metadata is similar but totally different. :)

With MySQL there are a large number of SHOW commands that will display detailed information on MySQL databases (data schemas), table structures etc. With Oracle the data dictionary views of USER_%, ALL_%, DBA_%, V$, etc. are used. The SHOW command in Oracle is only or the SQL*Plus client interface that will display local SQL*Plus variables in terms of how the SQL*Plus client is configured.

Both environments allow GLOBAL (instance) and LOCAL (session) variables to be defined. Below MySQL commands (mysql>) and Oracle commands (SQL) for accessing metadata will be compared.

-- Display server parameters.
mysql> SHOW VARIABLES;
SQL> SELECT name, value FROM V$parameter;

-- Set the sort buffer size.
mysql> SET GLOBAL sort_buffer_size=20 * 1024 * 1024;
SQL> ALTER SYSTEM SET sort_area_size=20971520;

-- Display information on triggers.
mysql> SHOW TRIGGERS LIKE 'fin%'\G
SQL> SELECT trigger_name, trigger_body FROM dba_triggers
WHERE trigger_name LIKE 'FIN%';

Storage Options

MySQL uses storage engines. Each of these storage engines deals with transactions, memory allocations, etc. differently. Very important a DBA understand the ramifications between the different types of storage. Some of the more popular storage engines include

• MyISAM
• InnoDB
• Memory
• Federated
• Archive
• Blackhole
• Falcon - New transactional storage available in MySQL 6.0.

MySQL stores data for tables in flat files that reside on the disk. RAW is available but not used much any more.
Oracle does not have the equvalent of storage engines. Oracle supports some of the following types of table storage:

• HEAP - regular table (default). Supports ACID transactions as well as being used able to be used for data warehouses.
  
• IOT - Index organized table. Storage is in a b-tree structure.
• External - Table definition that is mapped to an external file.

Oracle storage is managed in tablespaces. Tablespaces can store multiple data segments (tables) and index segments (indexes). Tablespaces organize Oracle storage structures. Tablespaces can store their data using RAW devices, operating system files and Automatic Storage Management (ASM). ASM uses Oracle internal storage mechanisms to write directly to physical devices without a file system. ASM can perform striping and mirroring using an Oracle Logical Volume Manager (ASM instance).

Creating Tables
Both MySQL and Oracle both support:
Regular tables
Indexes
Temporary tables
Partitioning

The big difference is that MySQL storage engines determine the functionality of MySQL tables. Another big difference is that MySQL uses AUTO_INCREMENT functionality to increment primary keys and unique identifiers. Oracle uses sequences to increment unique identifiers.

Stored procedures and triggers
MySQL and Oracle both support stored procedures and triggers. The MySQL stored procedure functionality is fairly new. However, the core of the language for writing stored procedures is available in MySQL. Oracle's procedural language has been around since 1991, so it obviously is more mature.

What's nice is that both of them use an ADA like syntax, so the core syntax of both languages looks almost exactly alike. Going from Oracle PL/SQL to MySQL's stored procedures would be a very easy transition.

The Growing Popularity of LAMP

LAMP is a proven software stack for developing and implementing multi-tiered web applications.

• Linux - operating system
• Apache - web server
• MySQL - database server
• PHP - programming language for dynamic web pages

The nice part of this stack is that it an extremely low cost solution for implementing web based solutions. This stack is also interchangeable.

• The operating system can be interchanged with Windows.
• The web server can be replaced with an application server such as JBoss.
• MySQL is also available in an Enterprise solution.
• PHP is growing in popularity for developing dynamic web pages. Other popular languages include Ruby on Rails, PERL and Java for building web applications.

LAMP or a modified version of LAMP can be an extremely strong solution for startups, Small and Medium sized Businesses (SMB) and even large organizations looking to implement web solutions with a low cost proven solution of software components.

LAMP derivatives can use Windows, different versions of Unix/Linux and JBoss instead of Apache to name a few options. Since is blog thread is focused on MySQL, MySQL will be the one constant in LAMP options.

A number of different programming environments and languages can be used to implement web-based applications for MySQL in a LAMP infrastructure. Some of the more popular ones include:

• PHP
• Perl
• Java (JSPs, Servlets, EJBs)
• AJAX
• Ruby on Rails
• Groovy
• Python
• Access

Eclipse is not mentioned in the LAMP acronym but it is a powerful IDE for developing Java applications. JDeveloper from Oracle is free and is another powerful solution for building Java applications.

Products like Toad and Navicat can also be included in this environment for interfacing with the MySQL database. Toad is available in free versions available for download.

There are a lot of interchangeable components I have just listed some of the more popular solutions.

An Oracle DBA’s Journey in Open Source

I’ve spent the last year and a half noticing the growth and popularity of open source solutions. Using open source to build multi-tiered web based solutions can greatly add to a company’s competitiveness and bottom line by greatly reducing costs and reduce the complexity of managing vendor relationships. I looked at a lot of open source products and companies and the one that stood out to me by far was MySQL. So this thread of my blog is going to focus on the open source database MySQL and surrounding technologies.

I have a very strong background in advanced Oracle database server technologies and Oracle Fusion middleware. I have a lot of experience with Oracle RAC, Data Guard, Streams, Advanced Queuing, Performance Tuning and Backup and Recovery. I have also have a strong background in Oracle Fusion Middleware technologies such as J2EE, ADF, Web Services, Design Patterns, SOA, BPEL, Oracle Business Rules, Grid Computing and XML.

I have felt it is important that an Oracle DBA have a very strong understanding of the environment Oracle runs in and that DBAs need to be able support Oracle developers. In today's world it is the Oracle Application Server and Oracle Fusion Middleware technology. There is a big gap in people that can understand and manage the entire infrastructure of a multi-tiered implementation. If performance is slow, is the problem in the database, network, applications, application server, OC4J containers, Oracle Rules Engine, etc. Also, high availability, backup and recovery and performance tuning are as big an issue in the middle-tier as they are the database tier.

Oracle DBAs can no longer work in the glass house of their databases. I go into companies and there are now 10 DBAs where five years ago there were 40 DBAs. These companies are looking for even more ways to reduce their DBA counts not increase them. DBAs today have to do more to be valuable to a company. In otherwords, they need to have skills in more areas. DBAs that can support multiple databases, the middle-tier, business applications, etc. Obvious paths for an Oracle DBA to increase their marketability include:

• Learn the Oracle Fusion Middle-tier or learn Oracle Applications to be more valuable in the Oracle space. This is a long road to do down and a lot of traditional DBAs are not going to survive the journey of J2EE, Web Services, BPEL, SOA, etc.
  
• Being able to support additional environments such as the operating system or storage management. With all the complex storage solutions out there, there is a strong demand in this area.
• Learn additional databases. I almost never go into a shop that is running just Oracle. Most companies have, DB2, SQL Server, MySQL, etc. in their environment. From the clients I go into (from Fortune 500 to SMBs) you see different database products.
  

With a very strong background in advanced Oracle database server technologies and Oracle Fusion middleware, I looked at an open source database from a lot of different perspectives. I weighed a lot of tangibles and intangibles. Despite Oracle Express, PostgreSQL, Ingres, SQL Server Express and DB2 express having strong functionality I picked MySQL for the following reasons:

• MySQL 5.1 supports key relational database technology features.
• Strong performance benchmarks from multiple sources.
• By far the most popular among clients that have moved to open source.
• A strong company behind it.
• Seems to be more expertise in the Internet versus other open source solutions.
• Encountering very strong growth in the market place.
• The coolness factor.
• A lot of similarities between Oracle and MySQL so I felt it would be an easier transition for me versus some of the other open source solutions. For example, the language used for stored procedures and triggers is almost exactly like Oracle’s PL/SQL. I am going to have to transition to the ANSI style syntax of joins but no journey occurs without some growth occurring.

The power of the Internet. MySQL is encountering over 50,000 downloads a day. When a new MySQL release comes out in Alpha, there are tens of thousands of users testing the new functionality. MySQL leverages the incredibly large volume of end-users on the Internet to help test their Alpha and Beta releases. The Internet can give open source organizations tremendous power that large commercial companies usually do not leverage.

I also believe MySQL is the one open source database that has the potential to replicate the success Red Hat has had in open source. For example, SUSE is an absolutely outstanding Linux OS, but almost everywhere I go, Red Hat is a lot more popular. I have found that same popularity with MySQL compared to the other open source databases. MySQL has a lot of the same factors for success that Red Hat has just on the database side versus the operating system side. There are other database options out there; it was my preference to choose MySQL over other alternatives. Just looking at a specification and technical sheet does not always give you the ultimate answer. I used some objective and some subjective reasons for choosing MySQL based upon my background, experience and preferences for environments I enjoy working in.

What was very cool for me is that last night I downloaded a free version of Linux, Apache and MySQL and deployed a Java web application in a few hour's time frame. This included the time to download the free software. It was all free except for the hardware and the coffee. I am going to also write the equivalent application in PHP. Amazing how LAMP can quickly and inexpensively provide a very powerful solution for deploying web applications. Large traditional vendors can no longer ignore the popularity and success of open source solutions.

It is very interesting to see the completely different culture that exists in open source environments. I find it very interesting and fun to slowly get more involved in this culture and environment. I look forward to sharing perspective and knowledge gained through this open source journey.