Saturday, October 23, 2010

MySQL Most Popular Database and Growing

The great thing about Open Source is that despite proprietary vendors best efforts, they can't control the market for open source.  When I first joined MySQL AB years ago, industry analysts estimated there were over 12 million MySQL database servers running.  Which is why MySQL has been considered the most popular database.  Now conservative estimates are now at 80 million MySQL database servers running.

MySQL is also the most popular database for the cloud.   All major efforts you see from companies like Amazon and Google, are all supporting MySQL as the database engine behind the cloud.  Companies are seeing that if it works for some of the biggest Cloud companies in the world, it can work for them also.  A friend of mine (Tom) refers to MySQL as the "secret sauce" of the Internet and the Cloud.  I couldn't agree more.

Sunday, September 19, 2010

MySQL Sunday at Oracle Open World 2010

MySQL Sunday is an open series and looks like a great start to the Oracle Open World conference.  Everyone is waiting to see if there are any announcements for MySQL at the conference from Oracle.  I like that they are starting with Edward Scriven and closing with Marten Mickos.  Hopefully there will be a lot of questions answered on stated directions and the MySQL 5.5 release and product updates.

List of key events in the opening Sunday series include:

  • What's Next for MySQL? Edward Scriven
  • InnoDB Enhancements and Roadmap
  • Performance and Scalability in MySQL 5.5
  • Advanced MySQL Replication Techniques
  • MySQL Closing Session - Marten Mickos

I won't be able to attend the presentation but Sarah Sproehnle's Cloudera presentation looks to be a great one to attend also.

Thursday, August 19, 2010

MySQL at Oracle Open World - San Francisco 2010

Everybody is very interested in understanding the Oracle message about MySQL.  Oracle is responding with a "MySQL Sunday"   and a number of excellent presentations throughout the week in San Francisco at Oracle Open World.  My session on MySQL is:
  • Session S316920 - The Ultimate Bootstrap for MySQL on Windows
There are also a number of excellent old MySQLers and friends presenting like:
  • Sarah Sproehnle - Cloudera
  • Brian Miezejewski - Oracle
  • Harrison Fisk - Facebook
  • Giuseppe Maxia - Oracle
  • Lars Thalmann - Oracle
  • Chris Schneider - Ning
  • Mark Matthews - Oracle
  • Sheeri Cabral - Pythian Group
It's looking like a good layout for Oracle Open World.   So it's going to be good to see how things go from a MySQL perspective and how that projects into the next IOUG Collaborate Conference 2011 in Orlando. 

Wednesday, July 21, 2010

MySQL and Open Source at Oracle Open World

Oracle Open World is fast approaching.   I am definitely looking forward to the MySQL and open source activity at the upcoming Oracle Open World conference in San Francisco.   I'm scheduled to deliver a presentation on implementing MySQL on Windows.   I am also looking forward to Oracle's first Open World conference with MySQL under the Oracle family umbrella.

Tuesday, April 13, 2010

MYSQL 5.5 highlights and MySQL Cluster 7.1

MySQL 5.5 Highlights - It's a lot faster!
  • InnoDB
    • Multiple Buffer Pool Instances
    • Multiple Rollback Segments
    • Extended change buffering and purge scheduling
    • Improved Log Sys and Flush List mutex
    • Improved locking
    • Improved statistics on InnoDB mutexes, rw-locks, threads and I/O operations.
  • Improved performance/scale with Win32, 64
  • Scales to 32 cores 
  • Semi-synchronoous replication
  • Performance Schema
  • SIGNAL/RESIGNAL (finally!)
  • New Partitioning enhancements
  • Configuring the heartbeat period
  • More than 10x improvement in recovery times
  • 200% performance gain for MySQL 5.5 over 5.1.40
MySQL Cluster 7.1 (GA)
  • NDBINFO - improved real time status and usage statistics
  • MYSQL Cluster Manager (CGE only)
  • Sub-second failover and self healing recovery
  • Parallel multi-master architecture
  • Low latency - real time responsiveness
Additional links:

    MySQL Users Conference 2010

    Since the Sun acquisition and the following announcement of the Oracle acquisition, the owner of MySQL have been fairly silent in terms of stated directions of MySQL.   This has allowed a lot of FUD to be spread throughout the user community.  At the MySQL users conference there are going to be some excellent keynotes helping customers get an update on MySQL technology.  It will be very interesting to see how the mind share of the user community is influenced by the keynotes this week.  We can also expect in the future the Independent Oracle Users Group (IOUG) which is the Oracle technology user group for DBAs, Unix Administrators and Developers increases its involvement with MySQL  Then at Oracle Open World this fall we can also expect an more activity with MySQL and updates on the MySQL technology areas.

    There was a lot of internal discussions on the vendor attendance, the mood and feel of the conference compared to last year, overall attendance and who is capturing the mind share of MySQL customers, developers and DBAs.  Internal discussions by dolphins were also very, very interesting.  It also appears that the maturity and popularity of MySQL has it breaking into different versions similar to how Unix separated into different derivatives back in the old days.

    After Conference Links:

    Conference Keynotes::

    Tuesday, April 13

    8:30am - 9:15am
    State of the Dolphin Edward Screven

    Highlights include MySQL 5.5 performance gains, MySQL Cluster 7.1, MySQL Enterprise Monitor enhancements and Oracle's LAMP stack.

    9:15 - 10:00   Tim O'Reilly - Insights on cloud services, mobile devices, future. may find interest.  "Internet is really a data operating system".

    Wednesday, April 14  Brian and Monty's keynotes are on two different visions of the MySQL and open source future.

    8:30am - 9:15am
    State of Drizzle  Brian Aker

    SQLite ubiquitous with embedded devices.
    In two years SSD is going to be the common device
    Why spend $3000 on consulting and instead spend $500 on a SSD device.
    Drizzle removes a lot of unneeded features (bloat) in MySQL.
    There will be no "Drizzle Inc/" for this MySQL fork.  There will be people and companies who provide services around it.

    They removed locks, go to lockless designs.

    Future will have many core systems.
    College students learning OO so C++ more important the C.
    Drizzle - C++, Booth, SDL?
    They have one blog type, don't use 3 byte integer.
    Web is UTF-8. not Swedish, etc.
    Drizzle 64bit, SSD, highly multicore, use external libraries, use C++/STL/Boost
    Every two weeks a new release of drizzle.  They use launch pad.  All source code is there.
    Everything is open source.
    No table level locks.
    Data dictionary is federated. No .frm file, no corruption in this case. Don't' want storage engine out of synch.

    Highlights.  80+ code contributors within two years.   This is never happened in two years at MySQL.
    4 companies with core developers.
    They have no warnings with GCC. If its a warning, its an error.   Compiler is usually right.

    Status of Drizzle today:
    Default Engine has been transactional for over a year and passes all test cases.
    Upgrades still require reload.
    Replication is still under testing.

    Presentation had vision, passion and a lot of interesting perspectives.  Vision was very well received by attending audience.

    9:15am - 10:00am
    State of MariaDB Michael Widenius
    Drizzle - is the only true fork.  It won't be backward compatible with MySQL.
    Maria is the glue that will put everything together.
    Maria - MySQL descendants and cousins
    Widenius announced an all-you-can-eat MariaDBa  support model from Monty
    Progam Ab: $36,000 for unlimited, company wide support for MySQL 3.3 upwards.
    The next version of MariaDB is 5.2, will add transactional storage engines - Spider and Spinx - group commit and virtual columns.
    MariaDB includes MariaDB, XtraDB and PBXT storage engines.

    Thursday, April 15

    8:30am - 9:15am
    Discussed some history of MySQL. Introduced new MySQL Oracle ACE Directors.
    Presentation slides.

    9:15am - 10:00am    The Engines Of Community  Jono Bacon

    Discussing community.  "People join communities is to have a sense of belonging".
    Talking about the mechanics of community: Communication channels, Structure, Collaboration and Environment.  Don't want to create a community of fiefdoms.  Need to make teams feel important.

    "A shift in thinking and actions of citizens is more vital than a shift of thinking and actions of institutions and formal leaders."  Community leaders need to understand technical work flow.  Important not to over travel.  Democracy does not work in a collaborate environment. Collaborate environments need to be based on meritocracies.
    Author of the "Art of Community". 

    Saturday, February 13, 2010

    The Journey Continues ...

    I was a leader in the Oracle community who joined MySQL two years ago to learn more about the open source world.  The two years at MySQL were fantastic.   The extraordinary camaraderie, spirit and energy of the company was unbelievable.  I worked with some fantastic people and made a number of life time friends.

    On Monday I become a full time employee of Oracle, so I've come full cycle.   Everybody is very excited about the incredible potential of Oracle and Sun and what we are going to accomplish together.   Oracle employees are enthusiastically welcoming Sun and MySQLers into the Oracle family.  

    I am really looking forward to continuing my journey.

    Monday, February 8, 2010

    Ken Jacobs a great advocate of the database user communities

    Ken Jacobs has been a fantastic advocate of the Oracle and MySQL user communites.  I met Ken on the board of the Independent Oracle Users Group (IOUG).  While Ken was the board liasson on the IOUG board, he was always supporting the Oracle user groups and made very important contributions throughout his time on the board and afterwards.  After serving time on the board, Ken was still always available and continued to be a great resource to the user community.

    Ken continued his important contributions in the open source community through his work with InnoDB and MySQL.

    Ken thank you for always being there.  Your contributions made a big difference and continue to be felt today. I was very disappointed that Ken will no longer be serving in role with InnoDB, MySQL and Open Source.

    Take care and thanks for all the great memories.  Your selfless efforts and friendship will always be appreciated.

    Best wishes in all your future endeavors.

    Sunday, February 7, 2010

    Oracle's Commitment to MySQL, MySQL Releases and Development Cycles

    The last few weeks I am still being asked what is going on with Oracle and MySQL and where is MySQL with it's software releases.  So I am going to include some URLs to hopefully answer some of your questions regarding MySQL.

    Oracle's press release on December 14, 2009 regarding MySQL.
    Summary list MySQL software releases. A more detailed list can be found at Lenz grimmer's blog on February 5, 2010.

    You can learn more about MySQL's development cycles at:

    Tuesday, February 2, 2010

    Configuring the InnoDB Plugin (1.0.6) in MySQL 5.1.43

    Configuring the InnoDB Plugin (1.0.6) is just as easy in the MySQL 5.1.43 release.  There are a few subtle changes in the new release.  Set the following parameters to configure the InnoDB plugin in 5.1.43.  A few notes:

    • Set the PLUGIN_DIR parameter to the location of the plugin libraries.
    • Verify all the libraries listed below are in the PLUGIN_DIR directory.
    • The PLUGIN_LOAD parameter needs to be set properly. Make sure the definition is one line and there are no spaces.

    my.cnf configuration 

    Verify the new PLUGIN metadata objects are now available. 
    mysql>  SHOW PLUGINS;
    Have fun with the new InnoDB Plugin features. :) 


    Thursday, January 14, 2010

    Installing MySQL 5.1 on Linux using MOCA

    The following instructions will lay out an installation of MySQL on Linux using the MySQL Optimal Configuration Architecture (MOCA) for someone with fundamental knowledge of MySQL and basic Linux administration skills. MOCA is a set of best practices I put together to lay out a set of guidelines for installing and configuring a MySQL database server.  MOCA is designed for someone with some experience with MySQL, it is not for someone brand new to MySQL.  MOCA is okay if you have a strong database background and good solid Linux skills.

    If you are new to MySQL or to Linux, I recommend using a default install with a  rpm install or use yum to do the install for you. The MySQL default install is recommended for someone new to MySQL or the operating system platform. If the default package install makes more sense for you, then you can stop reading.  This install is for someone wanting to setup a flexible and scalable configuration.  This configuration is for MySQL 5.1.42 and it will work for any 5.1.x install.

    If you are ready to get started you can skip down to the Start the Installation section.   If you scroll to the very bottom of this blog, I have a summary of the specific commands for the setup.  

    A Default Install using RPM or YUM
    The default install with MySQL is great for users new to MySQL. It is simple, requires a few point and clicks and you are up and running. The problem with a default install is that it is designed to be a very simple install and take minimum resources. This simple layout is excellent in its simplicity but it is not how a production server should be set up.  The default install also spreads MySQL files in different locations like /etc, /usr/bin, /usr/local that is not flexible and violates standard best practices for database servers.  The default install is not how an experienced DBA would want to set up a production database environment.

    Why Perform a Manual Install Using a Tar Ball 
    It is much better to be able to control the layout and configuration of the database software for production database environments and for platforms where multiple MySQL servers may be installed in the future.  Defining consistent standards, guidelines and best practices that are flexible and scalable are the key goals of MOCA.
      This install assumes you have a basic understanding of Linux and  MySQL database administration. Oracle DBAs will find this installation very similar to the concepts of the Optimal Flexible Architecture (OFA).
      For experienced MySQL DBAs a manual install is much better. For this purpose I created a best practices configuration and white paper called MOCA (MySQL Optimal Configuration Architecture).  I wanted to call it GOCA (George's Optimal Flexible Architecture) but MOCA seemed to flow better.  MOCA is based on DBA best practices and should be very similar to Oracle, DB2 and SQL Server production DBAs. There are certain fundamental truths about how database servers should be installed, configured and managed. My MOCA whitepaper addresses these fundamental truths. This manual install will follow MOCA standards and conventions.
    Why MOCA? 
    Visit to get the details of the reasons behind MOCA and why it is based on best practices. There is also an example of installing MySQL on Mac OS that is very similar to a Linux install. A Solaris install using MOCA can be found at  MOCA  focuses on:
    1. Separating database software from other software and files.
    2. Separating data and index files, log files for recovery, administration and backup files.
    3. Developing standard naming conventions.
    4. Defines a flexible configuration that can support multiple database servers on same platform.
    5. A consistent configuration for multiple servers and versions of MySQL database software across an enterprise.
    Installation Summary
    This installation looks more complex than it is.  I use this configuration for all  MySQL DBA classes.
    1. Remove old versions of MySQL if they exist.  Setup an operating system (OS) user called "mysql" and the environment for this OS user.
    2. Set up directories and directory permissions for all MySQL data files.
    3. Setup MySQL software and install MySQL software as mysql operating system user (not as root).  Configure the my.cnf configuration file.
    4. Create the mysql database (mysql_install_db) and . Start the mysql database server (mysqld_safe).  
    5. Setup the security environment (mysql_secure_installation)
    6. Test the shutdown and startup of the database server to test the server setup. 

    Start the Installation

    The environment for this installation is:
    • Enterprise Linux downloaded from Oracle's OTN website.  Red Hat, Fedora and CentOS can be used as well.  I chose Enterprise Linux because I like it and it is great for running Oracle and MySQL on same system so I can do ETL and play with the two database servers.  I installed the MySQL tar package (ball) x86/x64 image mysql-5.1.42-linux-x86_64-glibc23.tar.gz. Downloaded from the http:/ site.
      Before installing MySQL on any platform, make sure there are no previous versions of MySQL preinstalled that were loaded with the OS. Unless you want the older version of MySQL, your life will be much easier if you remove any previous releases that are not being used.
      Read through this installation a few times before starting.
      Note:  I use the # prompt to signify I am performing steps as root and the $ prompt to show I am performing steps as the mysql OS user.
    Look for existing MySQL software
    This install uses MySQL 5.1.42,  although these installation procedures can be used for any 5.1.x installation. Dependent on the version of Linux and your hardware, different packages may need to be installed or removed (old MySQL installations).

    So you can see my Linux environment I ran the following command at the shell.
    # more /etc/redhat-release
    Red Hat Enteprise Linux Server release 5.4 (Tikanga)

    Check to see if MySQL is installed on your current system.
    # grep mysql /etc/passwd
    # rpm -q mysql
    # find /usr/local -name '*mysql*' - print 
    # find /usr/bin -name '*mysql*' - print 
    # find / -name "*mysql*' - print # look everywhere for MySQL installations

    Remove any MySQL old files or packages. Then verify old MySQL files are gone.  If you leave the old MySQL software that is okay, but make sure MySQL is finding your MySQL files and not the old software (prime example, using the /etc/my.cnf file instead of yours).
    # rpm -e mysql

    Downloading MySQL 
    Go to and find the Downloads tab. Find the distributions and choose the install release you want.  I prefer a manual install so I choose the Linux Tar Packages file mysql-5.1.42-linux-x86_64-glibc23.tar.gz. Select a mirror. On the Select a Mirror page, I choose No thanks, just take me to the downloads!

    Note: Be aware that this MySQL image worked for me with the OS and hardware I was using.  Make sure you have the right image for your environment.  If commands fail because you can't run the mysql executables, you probably have the wrong image for your environment.

    For a separate Centos 5.2 install on different hardware I used this image:

    For my Linux environment the file downloaded to the /root/Desktop directory.

    Setup the MySQL software 
    The first set of commands are run as the root OS user (# prompt) to set up the environment.    In the /opt/mysql directory unzip and untar the MySQL software and build a symbolic link. This will set up the MYSQL_HOME directory to be in /opt/mysql/5.1.42 location.
    # mkdir -p /opt/mysql

    Copy tar file to /opt/mysql directory and setup up MySQL software.
    # cp /root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
    # cd /opt/mysql
    # tar zxvf mysql-5.1.42-linux-x86_64-glibc23.tar.gz
    # ln -s mysql-5.1.42-linux-x86_64-glibc23     5.1.42

    Setup the MySQL directory locations.  Here we are going to put all files below the /db01/mysql01 mount point.  The naming pattern mysql01 signifies database storage for a specific MySQL database server in this layout.  I like to put my InnoDB data and log files in their own location.
    # mkdir -p /db01/mysql01
    # cd /db01/mysql01
    # mkdir data binlogs admin backups innodata innologs

    Setup new mysql user if one does not exist.  If a mysql user does exist, set up a password, default shell, default directory, etc.
    No mysql user was found so I added one. Add the mysql group, mysql user, password and home directory.
    # groupadd -g 300 mysql
    # useradd -u 300 -g 300 -d /home/mysql -s /bin/bash -c "MySQL DBA" mysql
    # passwd mysql
    # chown -R mysql:mysql /opt/mysql    /db01/mysql01

    Login and verify the mysql user setup.  From this point, once you login as mysql user, complete the database server as the mysql user and not as the root OS user.

    # su - mysql (or exec login mysql)

    Then define a default profile file using your favorite text editor.  I chose the bash shell.

    --- .bash_profile file ------
    PS1='$PWD: '
    --- end of .bash_profile file -------

    Set your environment by sourcing your profile file.
    $ cd $MYSQL_HOME
    $ .   ./.bash_profile

    MySQL Directory Organization 
    A good way to separate MySQL files and software:
    • /opt/mysql/5.1.42             - Symbolic link to software directory location
    • /db01/mysql01/data          - Data directory for MySQL
    • /db01/mysql01/binlogs     - Binary log files location
    • /db01/mysql01/admin       - Administration files location
    • /db01/mysql01/backups    - Backup files location
    • /db01/mysql01/innodata    - InnoDB shared location
    • /db01/mysql01/innologs   - InnoDB transaction logs location

    Before going further

    Double (triple) check all directory paths and permissions.   99.99% of issues with manual installs  are typos in the directory paths, typos in the file names or permission issues with directories.  To common gotchas:
    • Using the wrong binary for your hardware or OS.
    • Search paths find a preinstalled version of mysql that was loaded with the OS.  This command will show you the default search path MySQL uses.
     $ mysqld --help  --verbose | more
    When you try to bring up database server, if it defaults to the default areas its because it cannot find a directory or doesn't have permission for directories specified so it will then try the default locations.

    Use a template file in the support-files directory.
    $ cd /opt/mysql/5.1.42
    $ cp support-files/my-small.cnf   my.cnf

    Add the following entries to the my.cnf file in the [mysqld] group. This separates all your dynamic administration files, data files, and binary log files to different locations.


    I would also recommend changing the parameter thread_stack=128k   to a minimum of 256k.

    Create the mysql database files for the MySQL database server (instance.) This will create the default database schemas and the physical file layer.

    $ cd $MYSQL_HOME
    $ scripts/mysql_install_db --datadir=/db01/mysql01/data  --basedir=$MYSQL_HOME

    Start the MySQL database server pointing to the defined locations.
    $ cd /opt/mysql/5.1.42
    $ bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf  &

    Verify the mysqld background process is running as well as the mysqld_safe monitoring process. The mysqld background process should be up and running.
    $ ps -ef |grep mysql

    Verify you can get into the server.  Initial setup has no passwords.  If this works you have a good server.  The show command should display the mysql, test and information_schema databases.
    $ mysql
    mysql>  show databases;
    mysql>  exit

    Clean up the database server by adding passwords and getting rid of anonymous users.  The MySQL database super user is called root. This step will add a password for this MySQL database user.
    $ cd $MYSQL_HOME
    $ bin/mysql_secure_installation

    Shutdown the MySQL server to verify you can shutdown and startup the MySQL instance.
    $ mysqladmin -uroot -p  shutdown
    $ bin/mysqld_safe --defaults-file= $MYSQL_HOME/my.cnf &

    You're up and running have fun. Once you are comfortable with this configuration layout, you can create a Unix shell script that will automate almost the entire process. With a shell script automation the install takes about five minutes.

    Summary of steps to perform setup
    Below is a summary of steps minus all the text.

    # ---- Steps performed as root OS user. ---------------
    # mkdir -p /opt/mysql
    # cp /root/Desktop/mysql-5.1.42-linux-x86_64-glibc23.tar.gz /opt/mysql
    # cd /opt/mysql
    # tar zxvf mysql-5.1.42-linux-x86_64-glibc23.tar.gz
    # ln -s mysql-5.1.42-linux-x86_64-glibc23.tar.gz     5.1.42

    # mkdir -p /db01/mysql01
    # cd /db01/mysql01
    # mkdir data binlogs admin backups innodata innologs

    # groupadd -g 300 mysql
    # useradd -u 300 -g 300 -d /home/mysql -s /bin/bash -c "MySQL DBA" mysql
    # passwd mysql
    # chown -R mysql:mysql /opt/mysql    /db01/mysql01

    # su - mysql 

    # ---- Steps performed as mysql OS user ---------------
    $ cd $MYSQL_HOME
    $ cp support-files/my-small.cnf   my.cnf

    # Make these additions in my.cnf file below [mysqld]


    $ scripts/mysql_install_db --datadir=/db01/mysql01/data  --basedir=$MYSQL_HOME

    $ bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &
    $ bin/mysql_secure_installation

    Tuesday, January 12, 2010

    MySQL Conference and Expo 2010

    The  MySQL Users Conference and Expo 2010 scheduled for April 12 - 15, 2010 in Santa Clara is definitely a conference I highly recommend you attend if you can.  The insights, networking and knowledge gained is invaluable.  The fantastic dynamics of the open source community has to be experienced to be understood.  :)