I get asked this question often. It was mentioned again recently in a NYTECH executive breakfast with RedHat CIO Lee Congdon.

The short answer is No.

There is clear evidence that in the short to medium term Oracle will continue to promote and enhance MySQL. Some of these indicators include:

• EU 10 point commitment in December 2009 – See Oracle Makes Commitments to Customers, Developers and Users of MySQL
• MySQL Conference April 2010 – Opening keynote by Edward Screven State of the Dolphin
• Oracle Magazine Jul/Aug 2010 – Interview with Edward Screven Open for Business.

It is clear from these sources that Oracle intends to incorporate MySQL into Oracle Backup and Security Vault products. Both a practical and necessary step. There is also a clear mention of focusing on the Microsoft platform, a clear indicator that SQL Server is in their sights without actually saying it.

What is unknown is exact how and when features will be implemented. Also important is how much these may cost the end user. Oracle is in the business of selling, now an entire H/W and S/W stack. They also have a complicated pricing model of different components with product offerings. I assume this will continue. There are already two indications, InnoDBbackup included for Enterprise Backup (from April Keynote) and 5.1 enterprise split. (Note: while this split may have existed prior to Oracle, it is now more clearly obvious).

MySQL can never be seen as drawing away from any Oracle sales of the core entry level database product. It is likely Oracle will provide a SQL Syntax compatibility layer for MySQL within 2 years, however it will I’m sure be a commercial add-on. Likewise, I would suspect a PL/SQL lite layer within 5 years, but again at a significant cost to offset the potential loss of sales in the low end of the server market. There continues to be active development in the MySQL Enterprise Monitor, MySQL Workbench and MySQL Connectors which is all excellent news for users.

Moving forward, how long will this ancillary development of free tools continue? What will happen to the commercial storage engine, OEM and licensing model after the 5 year commitment? How will the MySQL ecosystem survive.? There is active development in Percona, MariaDB and Drizzle forks, however unless all players that want to provide a close MySQL compatible solution work together, progress will continue to be a disappointing disjointed approach. The 2011 conference season will also see a clear line with competing MySQL conferences in April scheduled at the same time, the O’Reilly MySQL conference in Santa Clara California and the Oracle supported(*) Collaborate 2011 in Orlando, Florida.

I have a number of predictions on what Oracle ME MySQL may look like in 5 years however this is a topic for a personal discussion.

PlanetMySQL Voting: Vote UP / Vote DOWN

I will be joining a great list of quality speakers including John Allspaw, Theo Schlossnagle, Rasmus Lerdorf and Tom Cook at Surge 2010 in Baltimore, Maryland on Thu 30 Sep, and Fri Oct 1st 2010.

My presentation on “The most common MySQL scalability mistakes, and how to avoid them.” will include discussing various experiences observed in the field as a MySQL Consultant and MySQL Performance Tuning expert.

Abstract:

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environments to provide a suitable architecture and application design to support these essential needs.

Overview:

Some details of the presentation would include:

• The different types of accessible data  (e.g. R/W, R, none)
• What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
• The three components of scalability – Read Scalability/Write Scalability/Caching
• Design practices for increasing scalability and not physical resources
• Disaster is inevitable. Having a tested and functional failover strategy
• When other products are better (e.g. Static files, Session management via Key/Value store)
• What a lack of accurate monitoring causes
• What a lack of breakability testing causes
• What does “No Downtime” mean to your organization.
• Implementing a successful “failed whale” approach with preemptive analysis
• Identifying when MySQL is not your bottleneck

PlanetMySQL Voting: Vote UP / Vote DOWN

To my shame I must admit, I missed it. It happened on June 29, 2005 when db4free.net was first available to the public. At that time it was running MySQL 5.0.7 beta. Quite a lot has happened since then, MySQL 5.0 made its way up to 5.0.91 and the current MySQL GA version is 5.1.49, which is also the version db4free.net is running as of today. The first phpMyAdmin version that db4free.net was offering to provide easy access to the user’s databases was 2.6.3. Today I updated phpMyAdmin to 3.3.5.

Statistics are not necessarily 100 % accurate, but here is the best I can come up with. Since its launch, db4free.net had 528,900 visits. The ratio registrations per visits is at about 22 %, so more than every fifth visit ends in signing up for a new database. Which means, that about between 110,000 and 120,000 database accounts (meaning database and user) have ever been created. There have been some cleanups since then to make resources available to people who actively use their databases. Which is why the current number of databases is much lower, at slightly above 13,400.

Today I must (also) admit that the code behind db4free.net’s web application was initially quite poor and it stayed poor for quite long. Why it was poor from my today’s point of view is probably due to my learning process in these 5 years. This year I did a lot of cleanup on the code behind db4free.net’s web application and removed a few bottlenecks which often made the website painfully slow. Now it’s in a fairly reasonable state again, and I have some ideas in the back of my head how to further improve it. So db4free.net is far from its end of life. Quite the opposite is the case, it’s time to give it a new boost so that the balance after its 10th anniversary is even more impressive than today’s 5 year balance.

PlanetMySQL Voting: Vote UP / Vote DOWN

I want to limit the maximum number of concurrent queries by MySQL account. This will allow some accounts to have many concurrent connections without the risk of having many concurrent queries. The feature is enabled by adding admission_control to my.cnf. Early results for it are promising but I have much more testing to do. Results for sysbench doing fetch by primary key with 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048 and 4096 concurrent clients are in this graph. The results for admission control have the label new. Both new and original use MySQL 5.1.47 with the Facebook patch. MySQL with admission control is much better at sustaining peak QPS when the server is saturated. I have a bit of work to make this ready for production. The implementation requires a count of running queries per account. This count must be adjusted when a thread blocks within the server. Things that can make a thread block include LOCK TABLES, GET_LOCK() and InnoDB row lock waits. I need to find all places in the server where a thread might block. When this feature is enabled, this output from SHOW PROCESSLIST displays the result of a query blocked by admission control: | 1182 | root | db1:56546 | test | Query | 0 | NULL | HANDLER sbtest READ `PRIMARY` = (3331584) | | 1188 | root | db1:56552 | test | Sleep | 0 | wait for max concurrent queries | NULL |
PlanetMySQL Voting: Vote UP / Vote DOWN

Download your favourite version of MySQL Server from your local mirror. We can use the latest GA (5.1.49) or development release (5.5.5m3). Move your tarball to the /usr/local directory. I'm assuming that you're logged into your box using the root account. If you're not then you might need to utilize sudo.

Add the mysql user and group
   groupadd mysql
   useradd -g mysql mysql
Untar and Create a symbolic link
   cd /usr/local/
   tar -zxvf mysql-{version}.tar.gz
   ln -s mysql-{version} mysql
File and Directory creation/permissions 
   mkdir -p /var/lib/mysql *
   chown -R mysql:mysql /usr/local/mysql
   chown -R mysql:mysql /var/lib/mysql
Install the system databases and place my.cnf
  cp /usr/local/mysql/support-files/my-medium.cnf  /etc/my.cnf**
  cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql***   scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr/local/mysql ****
Start mysqld
   /usr/local/mysql/bin/mysqld_safe &
Check your error log file to keep abreast of any issues that might arise.
Some house keeping is needed to ensure that MySQL will start a boot and also adding mysql into the path. These are basic Linux tasks. Please let me know if you'd like to see my recommendations for these tasks.

* This command is going to create your datadir location. This might not fit your preferences so this is something that you will choose to include or omit. You should review your my.cnf and make any changes to accommodate your desired location.
** This will copy a default my.cnf to the preferred my.cnf location. If you have written your own or have something you wish to use, copy that and not the example file. Your my.cnf is a pretty big deal as it's going configure your server to run like a two-legged dog or Usain Bolt!
*** Enables the 'service mysql [start|stop|status]' command to be executed
**** These parameters can be augmented, omitted or kept. You're going to want to use the values in your my.cnf or change your my.cnf to reflect what you use here.
PlanetMySQL Voting: Vote UP / Vote DOWN

One of the issues that has arisen from the ongoing debate about the open core licensing strategy is the continuing confusion about open core compared to the use of open source components in a larger proprietary product – such as IBM’s use of Apache within WebSphere.

To some people there is no difference between the two (since they both result in products that make use of open source but are not open source), however it is clear to me that while the end result might be the same these are very different strategies that involve different approaches to engaging with open source communities/projects.

While open core has a clear definition there is no agreed term or definition for the latter category.

Over the years we have used a variety of terms to describe it, including “open and closed”, “embedded open source”, “open inside” and “open complement”, while Jack Repenning has referred to it as “open infrastructure”.

Our next categorization of open source-related business strategies is still a work in progress but the current thinking is as follows:

• There are a variety of complementary strategies employed by vendors to generate revenue from open source software indirectly.

• The simplest of these is open complement which is selling other products and services that are related to but separate from, and not reliant upon, the open source project.

• Then there is encouraging open source development on top of proprietary products to retain develop interest in that product. This is known as open edge.

• Then there is using open source software to create a platform for the provision of SaaS or cloud or social networking services (for example), which I am referring to as open platform.

• Then there is using open source components as building blocks for a larger proprietary software product, which I am calling an open foundation licensing strategy.

(This categorization is a work in progress, we welcome and encourage any feedback)

Open core and open foundation have different evolutionary lineages: open core is a variation on dual licensing as practiced by the likes of MySQL and Sleepycat that also borrows heavily on the value-added subscription model as practiced by Red Hat and JBoss. Meanwhile open foundation has its roots in the commercialization of BSD, which pre-dates the concepts of open source and free software, as well as Apache.

From a practical perspective, the easiest way to think of the distinction between open core and open foundation is via an example:

PostgreSQL is an independent, community-developed open source project. EnterpriseDB offers extensions to the PostgreSQL core, such as Oracle-compatibility, in the form of Postgres Plus Advanced Server.

PostgreSQL has also been used by many other vendors to create commercial products. For example Greenplum used PostgreSQL as the foundation of its Greenplum Database (for other examples see this post). This allowed the company to build on proven database technology and avoid reinventing the wheel, but it also involved the creation of an entirely new product, rather than extensions to an open source project (the company initially actually started a new project, Bizgres, and created extensions to that but Bizgres was last seen in August 2008).

So while open core involves offering proprietary extensions targeted at a segment of the open source project user base, open foundation involves using open source software to create entirely new products, targeted at a different user base.

The example used above highlights three important points to consider when comparing open core and open foundation strategies:

1/ While open core is most readily associated with vendor-controlled projects it can also be used as a strategy to monetize community-controlled projects.

2/ Open core strategies can be used in conjunction with complementary strategies. In the Greenplum example the company’s relationship with Bizgres was open core, while the relationship with PostgreSQL was open foundation. Similarly there is an open core relationship between Actuate’s BIRT products and the Eclipse BIRT project, and an open complement relationship between Actuate 10 and the Eclipse BIRT project. Meanwhile there is an open core relationship between Day Software’s CRX content repository and the Apache Jackrabbit and Sling projects, and a open foundation relationship between CQ5 and Jackrabbit, Felix and Sling – as well as the numerous other Apache projects that Day contributes to.

3/ Open core and open foundation are licensing strategies used as part of a larger business strategy for engaging with and commercializing open source software, which highlights the futility in trying to pigeon-hole companies as “open core vendors” or “open source vendors”.

Finally it is worth thinking about the different tensions that the open core and open foundation strategies create with their respective communities.

As Jorg Janke notes, “looking for an income stream as an open source vendor always results in some sort of conflict with the community. So, you have to pick the community you want to ‘offend’.”

With a vendor-controlled open core strategy the community is a user community, and as we have previously discussed the conflict is in deciding what features belong in the core and what features don’t.

With an open foundation strategy the community is the open source project developer community, and the conflict lies in deciding what features and resources to contribute to that project.

A community-controlled open core strategy arguable results in conflict with both the user and developer communities, although since the vendor does not own or control the project the relationship is much more comparable to the open foundation strategy.

We will be writing more about other strategies for generating revenue from open source software, in a follow-up to our Open Source is Not a Business Model report, which is due to be published latter this year. It will provide more context for the economic motivators and issues involved in the various models, as well as updated research on which vendors are following which strategies, and why, as well as a survey to uncover what software users make of it all. The report will be freely available to CAOS subscribers. For more details of the CAOS research practice, and to apply for trial access, click here.

PlanetMySQL Voting: Vote UP / Vote DOWN

========================================================================================================= REGRESSION REPORT ========================================================================================================= MACHINE: drizzle-build-n01.wc1.dfw1.stabletransit.com RUN ID: 324 RUN DATE: 2010-07-27T21:48:07.932094 WORKLOAD: innodb_1000K_readonly SERVER: drizzled VERSION: lp:drizzle/staging REVISION: 1669 COMMENT: 1669: Brian Aker 2010-07-27 This patch turns the table_cache into boost::unordered_multimap. ========================================================================================================= TRENDING OVER LAST 5 runs Conc TPS % Diff from Avg Diff Norm? Min Max Avg STD ========================================================================================================= 16 1993.10 +1.19% 23.37 within norms 1905.44 2010.28 1969.73 26.56 32 2676.28 +1.75% 46.11 outside norms 2568.20 2685.05 2630.17 33.22 64 2622.82 -2.09% -56.00 outside norms 2613.02 2737.89 2678.82 43.09 128 2621.23 -0.61% -16.15 within norms 2609.74 2662.30 2637.38 16.41 256 2598.18 +5.50% 135.46 outside norms 2406.50 2599.34 2462.72 68.67 512 2343.83 +52.30% 804.91 outside norms 1318.38 2347.49 1538.92 402.80 1024 1722.08 +104.66% 880.65 outside norms 614.65 1725.52 841.43 440.35

Boost::unordered_multimap versus the hand crafted MySQL HASH?

Boost wins by a long shot. The above is from sysbench. We run it on each and every push that goes into the main tree looking for regression.

Every so often we get to see the opposite happen :)

Thanks to this patch, "Table" now joins the ranks of what we call a "trusted object". This means we can start safely assuming that the destructor on it is working (most of the MySQL codebase was crafted in such a way that you can only use a very limited subset of C++). In all of the new code in Drizzle we can easily make use of C++. There are still a few older bits where we cannot. Having "Table" now work means we can safely work in a number of new areas in the server. One of the biggest changes that will be coming soon is the removal of LOCK_open for a number of new cases.
PlanetMySQL Voting: Vote UP / Vote DOWN

I recently gave a webinar to the LAOUC and NZOUG user groups on MySQL Idiosyncrasies that BITE.

For the benefit of many viewers that do not use English as a first language my slides include some additional information from my ODTUG Kaleidoscope presentation in June.

Thanks to Francisco Munoz Alvarez for organizing.

MySQL Idiosyncrasies That Bite 2010.07 View more presentations from Ronald Bradford.
PlanetMySQL Voting: Vote UP / Vote DOWN

If you own a Nokia N900 cellular device you might be interested in the ability to control all of your IM accounts from the command line. For those that do not know, the N900 runs Maemo Linux and is capable of running MySQL embedded if you so choose. Here’s a quick script I wrote to provide that functionality for IM accounts. It’s at the bottom of the page, called “im-connections”.

wiki: http://wiki.maemo.org/N900_Mission_Control#Set_all_SIP_accounts_to_online_or_offline
pastebin: http://pastebin.com/qAC57E1N

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.3.3, a new version of the all-managed .NET driver for MySQL has been released. This is a beta release and is intended to introduce you to the new features and enhancements we are planning. This release should not be used in a production environment.  It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/6.3.html] and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.)

The new features or changes in this release are:

• Visual Studio 2010 RTM support
• New sql editor.  Create a new file with a .mysql extension to see it in action
• Please check the changelog and release notes for more information

What we know may be broken
Documentation is not yet integrated into VS 2010.

Please let us know what else we broke and how we can make it better!

PlanetMySQL Voting: Vote UP / Vote DOWN

Josh Berkus gave a great talk at linux.conf.au 2010 (the CFP for linux.conf.au 2011 is open until August 7th) entitled “How to destroy your community” (lwn coverage). It was a simple, patented, 10 step program, finely homed over time to have maximum effect. Each step is simple and we can all name a dozen companies that have done at least three of them.

Simon Phipps this past week at OSCON talked about Open Source Continuity in practice – specifically mentioning some open source software projects that were at Sun but have since been abandoned by Oracle and different strategies you can put in place to ensure your software survives, and check lists for software you use to see if it will survive.

So what can you do to not destroy your community, but ensure you never get one to begin with?

Similar to destroying your community, you can just make it hard: “#1 is to make the project depend as much as possible on difficult tools.”

#1 A Contributor License Agreement and Copyright Assignment.

If you happen to be in the unfortunate situation of being employed, this means you get to talk to lawyers. While your employer may well have an excellent Open Source Contribution Policy that lets you hack on GPL software on nights and weekends without a problem – if you’re handing over all the rights to another company – there gets to be lawyer time.

Your 1hr of contribution has now just ballooned. You’re going to use up resources of your employer (hey, lawyers are not cheap), it’s going to suck up your work time talking to them, and if you can get away from this in under several hours over a few weeks, you’re doing amazingly well – especially if you work for a large company.

If you are the kind of person with strong moral convictions, this is a non-starter. It is completely valid to not want to waste your employers’ time and money for a weekend project.

People scratching their own itch, however small is how free software gets to be so awesome.

I think we got this almost right with OpenStack. If you compare the agreement to the Apache License, there’s so much common wording it ends up pretty much saying that you agree you are able to submit things to the project under the Apache license.  This (of course) makes the entire thing pretty redundant as if people are going to be dishonest about submitting things under the Apache licnese there’s no reason they’re not going to be dishonest and sign this too.

You could also never make it about people – just make it about your company.

#2 Make it all about the company, and never about the project

People are not going to show up, do free work for you to make your company big, huge and yourself rich.

People are self serving. They see software they want only a few patches away, they see software that serves their company only a few patches away. They see software that is an excellent starting point for something totally different.

I’m not sure why this is down at number three… it’s possibly the biggest one for danger signs that you’re going to destroy something that doesn’t even yet exist…

#3 Open Core

This pretty much automatically means that you’re not going to accept certain patches for reasons of increasing your own company’s short term profit. i.e. software is no longer judged on technical merits, but rather political ones.

There is enough politics in free software as it is, creating more is not a feature.

So when people ask me about how I think the OpenStack launch went, I really want people to know how amazing it can be to just not fuck it up to begin with. Initial damage is very, very hard to ever undo. The number of Open Source software projects originally coming out of a company that are long running, have a wide variety of contributors and survive the original company are much smaller than you think.

PostgreSQL has survived many companies coming and going around it, and is stronger than ever. MySQL only has a developer community around it almost in spite of the companies that have shepherded the project. With Drizzle I think we’ve been doing okay – I think we need to work on some things, but they’re more generic to teams of people working on software in general rather than anything to do with a company.

• Share this on Facebook
• Tweet This!
• Share this on del.icio.us
• Digg this!
• Post on Google Buzz

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1; +-----+ | 1+1 | +-----+ |   2 | +-----+ 1 row in set (0.00 sec) mysql> select "1"+"1"; +---------+ | "1"+"1" | +---------+ |     2 | +---------+ 1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MySQL processes the expression. The result value is always 0.

mysql> select benchmark(1000000000, 1); +--------------------------+ | benchmark(1000000000, 1) | +--------------------------+ |                        0 | +--------------------------+ 1 row in set (5.42 sec) mysql> select benchmark(1000000000, "1" ); +-----------------------------+ | benchmark(1000000000, "1" ) | +-----------------------------+ |                           0 | +-----------------------------+ 1 row in set (5.40 sec)

So maybe type doesn’t matter? About five seconds just to loop but the type didn’t change it.   What if we add 1+”1″?

mysql> select benchmark(1000000000, 1+1); +----------------------------+ | benchmark(1000000000, 1+1) | +----------------------------+ |                          0 | +----------------------------+ 1 row in set (12.65 sec) mysql> select benchmark(1000000000, 1+"1"); +------------------------------+ | benchmark(1000000000, 1+"1") | +------------------------------+ |                            0 | +------------------------------+ 1 row in set (35.58 sec) mysql> select benchmark(1000000000, "1"+"1"); +--------------------------------+ | benchmark(1000000000, "1"+"1") | +--------------------------------+ |                              0 | +--------------------------------+ 1 row in set (51.59 sec)

It looks like type does matter.  But does it always matter?

mysql> select benchmark(1000000000, sum(1+1)); +---------------------------------+ | benchmark(1000000000, sum(1+1)) | +---------------------------------+ |                               0 | +---------------------------------+ 1 row in set (9.69 sec) mysql> select benchmark(1000000000, sum("1"+"1")); +-------------------------------------+ | benchmark(1000000000, sum("1"+"1")) | +-------------------------------------+ |                                   0 | +-------------------------------------+ 1 row in set (9.94 sec) mysql> select benchmark(1000000000, sum("1.23456789"+"1.23456789")); +-------------------------------------------------------+ | benchmark(1000000000, sum("1.23456789"+"1.23456789")) | +-------------------------------------------------------+ |                                                     0 | +-------------------------------------------------------+ 1 row in set (10.32 sec)

So, not all functions are the same.  But it looks like size might matter!

mysql> select benchmark(1000000000, 1.1+1.1); +--------------------------------+ | benchmark(1000000000, 1.1+1.1) | +--------------------------------+ |                              0 | +--------------------------------+ 1 row in set (34.90 sec) mysql> select benchmark(1000000000, "1.1"+"1.1"); +------------------------------------+ | benchmark(1000000000, "1.1"+"1.1") | +------------------------------------+ |                                  0 | +------------------------------------+ 1 row in set (1 min 15.32 sec) mysql> select  benchmark(1000000000, "1.123456789"+"1.123456789"); +----------------------------------------------------+ | benchmark(1000000000, "1.123456789"+"1.123456789") | +----------------------------------------------------+ |                                                  0 | +----------------------------------------------------+ 1 row in set (1 min 53.32 sec)

Sorry.  Looks like size does matter.
This doesn't seem logical.

mysql> select benchmark(1000000000, 1=1); +----------------------------+ | benchmark(1000000000, 1=1) | +----------------------------+ |                          0 | +----------------------------+ 1 row in set (12.75 sec) mysql> select benchmark(1000000000, 1="1"); +------------------------------+ | benchmark(1000000000, 1="1") | +------------------------------+ |                            0 | +------------------------------+ 1 row in set (40.78 sec) mysql> select benchmark(1000000000, 1=true); +-------------------------------+ | benchmark(1000000000, 1=true) | +-------------------------------+ |                             0 | +-------------------------------+ 1 row in set (12.73 sec) mysql> select benchmark(1000000000, 1="true"); +---------------------------------+ | benchmark(1000000000, 1="true") | +---------------------------------+ |                               0 | +---------------------------------+ 1 row in set, 65535 warnings (3 min 5.72 sec) mysql> select benchmark(1000000000, "true"="true"); +--------------------------------------+ | benchmark(1000000000, "true"="true") | +--------------------------------------+ |                                    0 | +--------------------------------------+ 1 row in set (57.25 sec)

Maybe we should CAST our work?

mysql> select benchmark(1000000000, cast("1" as unsigned)); +----------------------------------------------+ | benchmark(1000000000, cast("1" as unsigned)) | +----------------------------------------------+ |                                            0 | +----------------------------------------------+ 1 row in set (32.27 sec) mysql> select benchmark(1000000000, cast("1" as unsigned) + cast("1" as unsigned)); +----------------------------------------------------------------------+ | benchmark(1000000000, cast("1" as unsigned) + cast("1" as unsigned)) | +----------------------------------------------------------------------+ |                                                                    0 | +----------------------------------------------------------------------+ 1 row in set (1 min 7.24 sec)

Maybe not!
Conclusion:  Be careful with your data types.  If you are taking user input, do the type conversion ONCE in your program.  Don’t let MySQL do the type conversions for you.
query = “SELECT * FROM table where $INPUT = 1″;   could be doing your wrong.

References: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
PlanetMySQL Voting: Vote UP / Vote DOWN

In October 2008, Baron posted How to Check MySQL Replication Integrity Continually. Here at Pythian we have developed a method based on that post, and added “verifying that masters and slaves are in sync” to our standard battery of tests.

We call it “Continual replication sync checking”. This article will explain how it works, how to test and make the procedure non-blocking, benchmarks from the real world, issues we encountered along the way, and finally Pythian’s procedure of setting up continual replication sync in a new environment.

At the 2010 MySQL User Conference & Expo, my co-worker Danil Zburivsky did a presentation about this, and you can get the slides in ODP (Open Office) format or watch the 46-minute video on YouTube.

How it works

On the master, mk-table-checksum is run. In order to make the checksum operation online, we use the modulo and offset features of mk-table-checksum to checksum only part of the data at a time. The checksum is run (from cron) on the master and replicates to the slave. The results are captured in a result table, and a separate process checks the result table and notifies us of any discrepancies.

Testing resource usage for non-blocking replication sync checking

Each environment has a different data size, different resources, and a different level of what “intrusive” means. We start with a baseline of the size of the database, which we get from:

SELECT SUM(INDEX_LENGTH+DATA_LENGTH)/1024/1024/1024 as sizeGb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE';

I included INDEX_LENGTH because the queries that generate the checksum information may or may not depend on indexes. Note that for InnoDB tables, the metadata is approximate, so this will not necessarily be the exact size of the database, but it will be in the right range. In general, running this query in the INFORMATION_SCHEMA did not consume too many resources, though in many cases the query took a few minutes to complete. On databases with several thousand tables, this query can take hours to complete.

Of course there is the caveat that it may take a VERY long time to run this in your environment, and some folks have reported crashing (this is VERY rare, I personally have not seen it even with the high demand client we have had, but others have reported it). So if you prefer to be on the safe side, you could look at the size of your ibdata files (whether you are innodb_file_per_table or not) plus the sizes of all the tables on disk (MyISAM, CSV, etc). That will give you a sense of how big the data is, although the size of the files on disk also includes any fragmentation. In practice, I have seen fragmentation reduce the size of a large database by 40%, so beware that neither of these methods is perfect.

Once we get the data size in gigabytes, we can come up with a modulo to test. In general, we have found that mk-table-checksum is quick enough that it is non-blocking when we use a modulo value that corresponds to approximately 1 Gb of data, and a chunk-size of 100. We actually started out using a checksum of 1000, but locking 1000 rows to test a checksum proved to be longer than was acceptable for clients with lots of transactions — the problem was not the locking on the master, the problem was that it created a slave lag on the slaves. We tried using the --sleep option to mk-table-checksum, which worked, but changing the chunksize to be smaller caused less slave lag than adding a sleep parameter.

Another issue when using chunk-size is that it requires a numeric (but non-decimal) index. We ran across clients using VARCHAR(32) as a PRIMARY key (yes, on InnoDB, and yes, performance was an issue there), or tables that did not have any indexes at all (such as a logging table). If mk-table-checksum does not find a suitable index it will just do a checksum on the whole table.

To find tables that would be problematic, here is an INFORMATION_SCHEMA query that can be run (again, the usual caveats about INFORMATION_SCHEMA apply):

SELECT CONCAT("SHOW CREATE TABLE ", table_schema,".",table_name,"\\G") FROM TABLES LEFT JOIN ( SELECT distinct table_schema,table_name FROM STATISTICS INNER JOIN COLUMNS USING (table_schema,table_name,column_name) WHERE (data_type like '%int' or data_type like 'date%' or data_type like 'time%' or data_type='float') and seq_in_index=1 ) AS good_for_checksum USING (table_schema,table_name) WHERE good_for_checksum.table_name IS NULL and table_schema not in ('information_schema'); Frequency of checksum runs
The frequency that we run the checksum is also very flexible, so we take the size and translate that into a modulo that is “even” in a time-based way. For example, on a server that reported 113 Gb in size from the INFORMATION_SCHEMA query above, we set the modulo to 120. The checksum took 10 minutes and 6 seconds from the time the master started to the time the slave finished. There was no excessive slave lag caused and other resource checks showed that this test was acceptable, including application response time for non-administrative queries.

Given a modulo of 120 that takes about 10 minutes to run and the environment, we decided to run the checksum 9 times per day (hourly for 9 hours during off-peak time). This resulted in the entire data set being checked during a period of just under 2 weeks (120 parts / 9 times per day = 13.333 days).

This means that if there is a data discrepancy, it is discovered within 2 weeks in this particular environment. Though that is not ideal, it is much better than not discovering data discrepancies at all, which is how replication currently works.

Benchmarks From the Real World

The first row in the table below is the example we just went through. The subsequent rows of the table are results from some of the other production environments we run the checksum in. As you can see, we try to keep the data checked at one time to about 1 Gb.

Total Data SizeModuloTest timeChecksum RateFrequencyPeriod
113 Gb12010 min 6 seconds1.59 Mb / sec9x / day2 weeks
9 Gb2151 seconds8.4 Mb / sec3x / day1 week
29 Gb219 min 16 seconds2.6 Mb / sec3x / day1 week
70 Gb2128 seconds2650 Mb / sec
(data was
freshly defragmented!)3x / day1 week
5.1 Gb214 min 22 sec0.958 Mb / sec3x / day1 week
314.5 Gb33636 min 3 seconds0.44 Mb / sec16x / day3 weeks

In all of these environments, slave lag was 10 seconds or less at any given point on the slaves.

Issues Encountered
Some of the issues we encountered have workarounds, so I wanted to discuss and explain them here before giving our procedure, which contains the workarounds.

1. Bug 304 – mk-table-checksum deletes all prior checksum results in the result table on every run. There is no fix for this yet, but if you are using mk-table-checksum only for the procedure described in this article (and in particular are not using the --resume-replication option), you can comment out the following code from mk-table-checksum:

   # Clean out the replication table entry for this table.
   if ( (my $replicate_table = $final_o->get('replicate'))
   && !$final_o->get('explain') ) {
   use_repl_db(%args); # USE the proper replicate db
   my $del_sql = "DELETE FROM $replicate_table WHERE db=? AND tbl=?";
   MKDEBUG && _d($dbh, $del_sql, $db, $table->{table});
   $dbh->do($del_sql, {}, $db, $table->{table});
   }
   

   It is in different places in different versions, but last I checked, searching for "DELETE FROM" in mk-table-checksum only matched three lines of code, and it was pretty clear (due to the inline comment) which block of code to delete. The block shown above is from lines 5154 – 5161 in mk-table-checksum changeset 6647.

2. Running the checksum may cause “statement not safe for replication” errors, especially in 5.1.
This is usually OK to ignore, because mk-table-checksum works specifically because you can run the same command on the master and slave and get different results. In MySQL 5.1, CSV tables for the general and slow logs exist by default, even if they are not being written to, and “Statement is not safe to log in statement format” errors show up.

You will need to redirect stderr and expect to see those statements in the MySQL error log. Note that mk-table-checksum works regardless of whether you are using statement-based, row-based or mixed replication.

3. mk-table-checksum is not perfect, and sometimes shows false positives and false negatives.
This is a hard to deal with problem, and we encourage making bug reports when they are found. However, I will note that if mk-table-checksum finds even one undetected data integrity issue, then it is useful, because right now there is no other way of detecting issues in an automated fashion. As more people use mk-table-checksum and can help the developers figure out how to fix the false positives/false negatives, I am sure it will be even better.

Pythian’s procedure to set up continual replication sync

1. Check to make sure all tables have appropriate indexes, as above
. If they do not, the nibbling algorithm can be used, though as a caveat I have not tested nibbling with mk-table-checksum.
2. Figure out the modulo value based on data size, as above


3. Decide what database in which to put the tables that mk-table-checksum uses. We either use our monitoring database or a database called “maatkit”. Note that it is important to use a database that actually gets replicated!
4. Get mk-table-checksum and comment out the lines that always delete from the replicated table, as above
5. Create and populate the table mk-table-checksum will need for the modulo value:
   CREATE TABLE IF NOT EXISTS `checksum_modulo` (
   `modulo_offset` smallint(5) unsigned NOT NULL default '0' primary key
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   INSERT IGNORE INTO checksum_modulo (modulo_offset) VALUES (0);

6. Do a test run of mk-table-checksum:

perl mk-table-checksum -u avail --ask-pass --algorithm BIT_XOR \
--replicate maatkit.checksum --create-replicate-table \
--modulo 120 --chunk-size 100 \
--offset 'modulo_offset FROM maatkit.checksum_modulo' localhost

And update the modulo table:
update maatkit.checksum_modulo set modulo_offset = ((modulo_offset+1)%21);

And check the results on the slave:
SELECT * FROM maatkit.checksum
WHERE this_crc!=master_crc AND db not in ('maatkit','mysql');

7. If the test run came out OK, set up a script to run via cron such as:

# run checksum table 9 times per day, during off-peak times:
30 0,1,2,3,4,5,21,22,23 * * * /home/pythian/bin/checksumtbls.sh >> /home/pythian/logs/checksum.log 2>&1

And the checksumtbls.sh script looks like:

#!/bin/sh

DB=maatkit
USER=maatkit_user
PASS=password
REPLTBL="$DB.checksum"
MODULO=120
CHUNKSIZE=100
OFFSETTBL="checksum_modulo"
ALGORITHM=BIT_XOR
LOG=/home/pythian/logs/checksum.log

/usr/bin/perl /home/pythian/bin/mk-table-checksum -u $USER -p $PASS \
--modulo $MODULO \ --algorithm $ALGORITHM --chunk-size $CHUNKSIZE \
--offset "modulo_offset FROM $DB.$OFFSETTBL" \
--replicate $REPLTBL --create-replicate-table localhost >> $LOG

/usr/bin/mysql -u $USER -p$PASS -e "update $DB.$OFFSETTBL set modulo_offset=((modulo_offset+1)%$MODULO)" >> $LOG


And of course, do not forget to periodically check on the slave to see where the issues are:

SELECT * FROM maatkit.checksum
WHERE this_crc!=master_crc AND db not in ('maatkit','mysql');

I hope this helps; It is extremely important to make sure


PlanetMySQL Voting: Vote UP / Vote DOWN

New projects. Old arguments. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

New projects
# Gemini Mobile Technologies released Hibari, a new open source non-relational database for big data.

# Lockheed Martin launched the Eureka Streams open source project for enterprise social networking.

# Sony Pictures Imageworks expanded its open source initiative with the release of OpenColorIO.

Old arguments
# Kirk Wylie discussed the importance of natural split in open core , OpenGamma’s approach.

# Alan Shimel offered 10 commandments for open core. Mostly sensible, #6 will ruffle some feathers though.

# Simon Phipps maintained that open source does not need “monetising”.

# Carlo Daffara discussed property and efficiency as the basis of OSS business models.

# Jorg Janke continued his discussion of various open source business strategies in relation to Compiere.

# Henrik Ingo explained what you can do to help get rid of open core if you are so inclined.

# dotCMS went open core with the release of version 1.9.

# IBM faces EU antitrust investigation linked to TurboHercules complaint.

# The FT reported that IBM is blaming Microsoft for the EU investigation into its mainframe business practices.

# TechDirt explained how WordPress and Thesis have settled their differences over themes and the GPL.

The best of the rest
# Novell introduced SUSE Gallery for publishing and sharing Linux-based appliances.

# VoltDB released version 1.1 of its open source database.

# EnterpriseDB released Postgres Plus Advanced Server 8.4 and added Rob Bearden to its board.

# SAP has adopted Black Duck’s Suite to manage the use of open source software in its software development process.

# Oracle provided details of the MySQL Sunday event at Oracle Open World.

# SearchEnterpriseLinux reported that Ubuntu is gaining ground as a data center OS at the expense of SUSE Linux.

# Physorg.com explained how Georgia Institute of Technology researchers are helping the US military benefit from OSS.

# GENIVI Allianced has reportedly opted for MeeGo for its in-vehicle infotainment platform.

PlanetMySQL Voting: Vote UP / Vote DOWN

Tokutek tests its TokuDB Fractal Tree storage engine with multiple MySQL distributions. We make extensive use of the MySQL Sandbox in our test automation. We tweaked the regular expressions that match binary tarball names in the MySQL Sandbox so that MariaDB releases can be easily loaded by the MySQL Sandbox. These changes can be found in launchpad lp:~prohaska7/mysql-sandbox/mariadb.

PlanetMySQL Voting: Vote UP / Vote DOWN

IOUG has a free series of three webinars on upgrading MySQL. Each webinar is an hour long, and it starts with a webinar by me tomorrow at 12 noon Central time (GMT-5) on “Why and How to Upgrade to MySQL 5.1″. The webinar assumes you are upgrading from MySQL 5.0 to MySQL 5.1, and talks a little bit about the new features, server variables, and what you need to know when upgrading to MySQL 5.1.

The software used is GoToWebinar (formerly GoToMeeting), so you will need to install that software. To register, use the links on the IOUG MySQL Upgrade Webinar Series page.

The complete list of webinars in the MySQL Upgrade Series is:
* MySQL 5.1: Why and How to Upgrade
Sheeri Cabral, The Pythian Group
Tuesday, July 27, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

* MySQL Upgrades With No Downtime
Sean Hull, Heavyweight Internet Group
Wednesday, July 28, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

* MySQL Upgrade Best Practices
Matt Yonkovit, Percona
Thursday, July 29, 12:00 p.m. – 1:00 p.m. CT (GMT-5)

(note, I am not sure if it is free for everyone or just free for IOUG members; my apologies if it is the latter)

PlanetMySQL Voting: Vote UP / Vote DOWN

The following is a very, very specific subject matter, but I wish this blog post had existed when I was looking for the solution to a problem I was having. Most likely it has no interest to anyone who is not specifically looking for it, so feel free to stop reading...

MySQL has a few built-in functions for handling binary data. One of them is HEX which converts any data into hexadecimal representation. The function which you would expect to do the opposite (as the manual states) is UNHEX which takes a hexadecimal representation and turns it into characters.

So, if you try to do:
SELECT UNHEX(HEX('a'));You get an "a" back. But if you try that on a integer:
SELECT UNHEX(HEX(1)); You get back the char corresponding to the ASCII value of 1, which is not what was intended.
The correct way to do it, and the real opposite of HEX is the CONV function which converts betweens bases:
SELECT CONV(HEX(1), 16, 10);This time the result is the number "1" as expected.
PlanetMySQL Voting: Vote UP / Vote DOWN

The past two weeks have been both exciting and extremely busy, first traveling to Austin, TX for the first OpenStack Design Summit, and then back home to Portland, OR for The O’Reilly Open Source Conference (OSCON) and Community Leadership Summit. The events were great in different ways, and there was some overlap with OpenStack since we announced it on the first day of OSCON and created quite a bit of buzz around the conference. I want to comment on a few things that came up during these two weeks.

New Role

I’m now focusing on OpenStack related projects at Rackspace. I’m no longer working on Drizzle, but I will still be involved in the MySQL and database ecosystems through future projects and conferences (see you at OpenSQL Camp). I will also still be working on a couple of Gearman related projects in my spare time. At OSCON I gave two presentations on Gearman and Drizzle, you can find the slides here.

The Five Steps to Open

One question that came up a few times over the past couple weeks is what the term “Open” means when a business or organization decides to adopt the open source philosophy. It turns out this means many different things to folks, and when an organization decides to go open, they need to make a decision on how open they are willing to be. Here are the various layers we’ve seen over the years:

• Open API – You’ve decided to take the first step to being open and released a well documented API to work with your web service or project. Everything behind the API is still a black-box though.
• Open Core – Beyond the APIs, you’ve decided to release part of the code open source, but you still keep some of the bits proprietary in an attempt to keep a competitive advantage. This is a hot debate lately on whether it is a viable Open Source business model.
• Open Source – You’ve decided keeping some code proprietary doesn’t help, and actually even hurts your project or adoption. You put all of the code out in the open for everyone to see. While everyone can see all of the source code, there still isn’t a whole lot of interaction going on.
• Open Development – Putting the source code out wasn’t enough. You want to enable users and external developers to be able to file bugs, submit patches, and track the development process to see what to expect next. This usually involves running your project on a public project site such as github or Launchpad.
• Open Decision Making – You’ve postponed the inevitable for long enough. Feature requests and bug reports are pouring in, and the community wants to have a say in what gets prioritized. Should we focus only on stability? Performance? New features? Porting to mobile platforms? Let the community decided the direction of the project.

There have been examples of success for organizations who have stopped at each of these steps. Given the proper environment, any can work. My preference is to work on projects that are fully open, where company and organizational boundaries do not exist between developers and users. I’m thrilled to say that we’ve gone all in with OpenStack. We’re hosted on Launchpad and have a governance structure that allows all parties within the community to have a say in the future of the project.

Preventing Vendor Lock-in

During the Cloud Summit at OSCON, there was a debate titled: “Are Open APIs Enough to Prevent Lock-in?”. Most folks came to the conclusion that the answer is “no,” and I agree. While I feel open APIs are necessary, they are by no means sufficient. Even if a project is open source and allows for open development, it probably will not prevent vendor lock-in. The key is to provide some incentive for vendors to adopt and invest resources within a project. Much like customers don’t want vendor lock-in when choosing a platform, vendors do not want project or feature lock-in when choosing the software to power their business. Each vendor who chooses to participate must have the ability to voice their opinion on the direction of APIs, features, and other project priorities. This is why it is critical that any open source project must take all the steps described above to give the project a chance of being adopted and becoming the de facto standard. There is of course no guarantee that adoption and prevention of vendor lock-in will happen, but I see them as necessary steps.

This is another area where OpenStack has done the correct thing. We are planning on having another developer summit in November, and then once every six months after that time. All design discussions and decision making will happen in public forums such as the mailing list and IRC. We want all participants in the community to have a chance to respond to topics being discussed, and we believe the more we have, the more successful the project will be. Having many voices allows the project to be more applicable to different environments. For example, Rackspace and NASA have different requirements for their compute architectures, but they also share many components as well. Through open participation we can ensure all needs are accounted for. Much like the LAMP stack has powered universities, governments, and competing business, we hope OpenStack can do the same.

Contributor License Agreement (CLA)

During the past couple of weeks a few folks asked what the CLA was all about. When the foundations of OpenStack were forming, the requirement of having a CLA came up from the legal side. Having been involved with open source projects that had very invasive CLAs, initially I had quite a bit of concern. The CLA is actually quite innocuous, and it does NOT require assignment or dual-ownership of copyright. You are the sole owner of code you contribute. For all intents and purposes it is a signed version of the Apache 2.0 license, the CLA just makes these terms more explicit. The CLA is handled through digital signatures, so no papers, pens, or faxing is required.

Get Involved!

Expect to see more posts on my blog related to OpenStack topics. If you would like to get involved, you can join the IRC channel (#openstack on irc.freenode.net), join the mailing list, or start contributing code! There are even jobs around OpenStack popping up already!

PlanetMySQL Voting: Vote UP / Vote DOWN

An update on some recent News on JavaOne 2010. As you know JavaOne San Francisco is Sep 19-23, 2010. The Official page has links to the Registration Page and the Online Catalog. News updates include:

• A surprisingly useful & manageable Catalog-as-tweets via @javaoneconf
• Availability of Schedule Builder (post)
• Open enrollment in Java University (post)
• Announcement of dates for JavaOne Brazil and JavaOne China (post).
• The day before there is a MySQL Sunday!
• And, the Duke Awards submissions page seems to still be active.

Also, this year will be the 15th anniversary for Java, and the 5th for GlassFish.  Don't know if there will be a BDay party for Java; still hoping we can put something together for GlassFish, we will see!

More related news are tagged JavaOne.

PlanetMySQL Voting: Vote UP / Vote DOWN

Sometimes I am asked why don't I use X instead of official MySQL. The answer is simple. I like to use it because I have been using it, the MySQL development team (including InnoDB) has done great work this year and because change is expensive. The cost of change includes the cost of evaluating the alternatives and the cost of deploying them. The cost of change also includes the features I won't work on because I am doing an evaluation. I also use it because the quality of new 5.1 releases has been very high this year. I know because I test them and some of the alternatives.

My initial evaluation criteria are simple. I don't like compiler or valgrind warnings. The alternative should not introduce new ones. I like regression tests. The alternative should not disable or fail existing tests. If the existing test is somewhat bogus, then it should be fixed. I love buildbot as done by MariaDB, fixes in official MySQL to reduce compiler warnings and all of the work done by Drizzle to not tolerate compiler warnings. When the alternative adds new features it must add new regression tests (hooray for status_user.test in MariaDB).

I spent a lot of time debugging valgrind warnings that occur in MySQL 5.1.47. All of them were bogus and I think future versions of MySQL will prevent these. That is good news as I prefer to not repeat that effort the next time I upgrade to a new MySQL release.

Percona and MariaDB confront the same issues and more when considering code to incorporate. In addition to what I wrote about above, they must review patches to make sure the code is reasonable. There is a lot of duplicate effort done by groups that patch or fork MySQL. I wish this weren't the case. If the external fork & patch effort is consolidated around MariaDB, then we can reduce some of this.
PlanetMySQL Voting: Vote UP / Vote DOWN