Ronald Bradford
MySQL Expert

MySQL Expert Ronald Bradford shares valuable input in MySQL Performance Tuning, MySQL Scalability and general MySQL Help from his two decades of working with MySQL, Oracle, Ingres and development technologies.

Archive for the ‘PBXT’ Category

« Older Entries

Understanding the various MySQL Products & Variants

Friday, March 13th, 2009

The MySQL marketplace today is far more complex then simply choosing between a particular version of MySQL that Sun/MySQL produces.
The MySQL server product in general is released under the GNU General Public License (GPL) v2, however you should carefully review the MySQL Legal Policies as a number of exceptions and different license agreements operate for companion tools such as MySQL Cluster, MySQL client libraries and documentation for example.

Looking into the MySQL ecosystem for products, I’ve produced the following categories:

  • Sun/MySQL Official Products
    • MySQL Versions
  • MySQL Variants
    • Community
    • Enterprise
  • MySQL Plugins
  • MySQL Patches
  • MySQL Alternatives

Why does such a diversification occur?  I attribute this to three primary causes:

  • The GPL license by nature allows for an organization to take the product, modify it and use it for their specific needs. They can also provide these patches under GPL for others to use and incorporate. While this has occurred for example  Google , FaceBook, eBay , Proven Scaling and Percona to name a few, Sun/MySQL has elected not to undertake any proactive process of incorporating these in any timely fashion.
  • The policy of Sun/MySQL to allow for contributions was so strict, and combined with a properietory Version Control System BitKeeper you had to purchase, there was little incentive for community contributions in relation to so many other open source projects
  • The Sun/MySQL management and decision makers didn’t listen to the community and paying customers, and over the past 3-5 years the product life cycle, features, release schedule and quality can be questioned.

Sun/MySQL Official Products

Sun/MySQL holds the license to the MySQL server products. They release official binaries and the source code (due to GPL).  Even within MySQL, there are several products that differ subtly and to the untrained eye it can be confusing to understand and determine what is best. Your can download from www.mysql.com the following versions:

  • MySQL Server 5.1  GA
  • MySQL Community Server 5.0 GA
  • MySQL Enterprise Server 5.0 GA
  • MySQL Cluster NDB 6.3

  • MySQL Server 4.1 (EOL)
  • MYSQL Server 6.0 (Alpha)

MySQL Versions

It is important that you understand the MySQL Versions, especially in evaluating any of the following referenced variants, patches etc.
The common path for MySQL Server versions is with a generally linear numbering systems including historical versions 3.23, 4.0 and 4.1.  These versions have now reached End Of Life (EOL) for support, however emergency security patches are applied where necessary.
Continuing from 4.1, you have the 5.0, 5.1 versions which are both Generally Available (GA), and then version 6.0 which is currently Alpha.

Further complexity happens when within the Sun/MySQL Official products, several forks/branches have occurred.  These include:

  • The MySQL 5.0 Community & Enterprise split occurred at MySQL Version 5.0.27
  • At this time, the Community version (free to download) continued with the intention of allowing for community contributions. Only one patch was ever accepted, and SHOW PROFILES was introduced in MySQL 5.0.37.  To date, 11 versions have been released to the current 5.0.77 version.
  • MySQL Enterprise (available under subscription) is itself comprised of three subtypes, these are Rapid Update Service Packs(monthly), Quarterly Service Packs (quarterly) and Hot-fix releases.  To date 37 versions have been released to the current 5.0.78 version.
  • MySQL Cluster, was part of the MySQL Server product until this was branched/forked at  MySQL Version 5.1.23. This enabled MySQL Cluster to be labeled as Production Ready for Cluster clients, and not be held back by continued delays in the 5.1 server release.   Starting with a new versioning scheme with 6.1, the MySQL Cluster NDB produces new versions far exceeding the volume of the server, with to date 23 versions in 6.1 , 18 in version 6.2 and 24 in version 6.3.      I am not advocating that features and quality are better or worse, simply that activity and interaction with community and users is far greater.
  • MySQL 5.1 Maria is a special branch starting at MySQL Version 5.1.24 that includes the Maria Storage Engine. This is the next generation of the MyISAM Storage Engine, both architected by the creator of MySQL, Monty Widenius.  It is undercertain this will continue as a product released officially by Sun/MySQL.

In Review

With just reading this introduction you can understand the confusion that exists when new customers/clients are beginning to evaluate the different MySQL Versions.

In my next post, I’ll talk more about:

  • MySQL Variants, those I consider variants use the MySQL Interface, protocol and support the standard connectors.  These include community versions (e.g. Solid, Infobright, Sphinx) and commercial versions (e.g. KickFire, InfoBright, Nitro).  
  • MySQL patches are improvements that have been released to the community and are now becoming part of common third party MySQL packages, such as Percona, Proven Scaling and Out Delta
  • MySQL Plugins are a feature of MySQL 5.1, and allow for pluggable storage engines into MySQL.  While several companies have had to produce custom binaries due to the API limitions (especially with the optimizer), a number of engines support the API including Innodb, PBXT and filesystem engine.
  • MySQL Alternatives include any MySQL related products that have now deviated from being supported under the MySQL protocol.  Most notably here is Drizzle.

More Information

Ronald Bradford is Principal at 42SQL. We provide consulting and advisory services for the MySQL ecosystem with a focus on MySQL database performance, architecture and scalability. 42SQL also provides education in MySQL including the “MySQL Essentials” training course. You can find more information regarding this offering and an upcoming schedule at 42SQL Education.

Posted in Databases, Drizzle, General, Infobright, InnoDB, KickFire, Maria, MySQL, Nitro, Open Source, PBXT, Professional, Solid, Storage Engines | 2 Comments »

A few words from Jonathan Schwartz

Wednesday, April 16th, 2008

Following Marten Mickos, the second opening keynote at the 2008 MySQL Conference and Expo was by Jonathan Schwartz CEO and President of Sun Microsystems. Blog

His opening joke was about dinner with Marten, to which Marten said “You not going to get a keynote, unless you buy the company.”

So what was striking for me in his presentation “What is Sun’s Agenda?”

  • There is no open-source phone yet, but that’s an industry that needs disrupting.
  • Like the need for water or electricity, The Network Has Become A Social Utility.
  • We want to work with the community, create greater innovation.
  • The future, the price tag of Free, the philosophy of Freedom

I had a chance to meet Jonathan and Rich Green on Sunday night, and it was great to see Jonathan learning about, and getting behind the product PBXT – The Community Engine a MySQL 5.1 open source engine.

Posted in Databases, MySQL, MySQL User Conferences, PBXT, PrimeBase Technologies, Professional, Sun, mysqluc08 | No Comments »

The pursuit of a synchronous world

Friday, April 11th, 2008

Well at least your MySQL database world.

As Paul eluded to, PrimeBase Technologies has a project to provide synchronous replication for MySQL in a High Availability environment. It is more then an idea, there is a plan.

Is it possible?
What are the use cases?
How can you use it?
Would you use it?

Some input to date. We need these questions and more, and we seeking more input for discussion.

Unfortunately the opportunity to hear any input during a presentation is left to the last day of the conference, so there has been created a BoF session on Tuesday night for a round table discussion if necessary. People are encouraged to bring specific cases and situations for feedback, the reasons why MySQL Replication, MySQL Cluster, DRBD/HeatBeat or any other solution does not satisfy your needs, and what would.

If you can’t wait you may need to seek out Booth #518, and make a time to seek out the Technology Expert.

Just today, Peter Zaitsev of MySQL Performance Blog also writes in State of MySQL Market and will Replication live ?, “Customers are constantly asking me if there is something which would help them to scale MySQL and get some HA out of the box even on the medium level. Seriously – MySQL Cluster, Continuent, Master-Master Replication, DRBD or SAN based HA architectures all have their limits which makes neither of them used for very wide class of applications.” to confirm this pursuit.

Posted in Databases, MySQL, MySQL User Conferences, PBXT, PrimeBase Technologies, Professional, Storage Engines, Transactional, mysqluc08 | No Comments »

Storage Engines at the MySQL Conference

Tuesday, April 8th, 2008

I’ll be following closely the progression of Storage Engines available in the MySQL Database server, well soon to be available when 5.1 gets to GA (hopefully by end of Q2 which is what we have been told). Tick, Tick, time is running out.

PrimeBase XT (PBXT) and Blob Streaming is a focus as PrimeBase Technologies, the company which I want to note for people is an Open Source company, committed at providing an open source alternative to the other commercial players. You also have at the MySQL Conference talks on the the existing InnoDB from Innobase (a subsidiary of market RDBMS leader Oracle). There is a Nitro presentation, an Infobright presentation, no Solid presentation surprisingly (the IBM news happening after submissions closed). We also have from MySQL, presentations on the internally developed storage engines Falcon and Maria, both products that won’t even be in 5.1 but 6.0, however Maria is presently a different branch of 5.1 so I don’t know how that works. Will it be in 5.1?

But what I want to seek is more news of KickFire, a Diamond Sponsor, an engine with embedded H/W, something that’s been obviously worked on in reasonable stealth. For me it’s not just interesting, it’s a competitor in our technology space, so I’ve been researching Joseph Chamdani and some of his patents.

Plenty of news in the past few weeks on Kickfire including Kickfire Update by Keith Murphy on April 3, Kickfire: stream-processing SQL queries by Baron Schwartz on April 4, Kickfire looking to push MySQL limits by Farhan Mashraqi on April 4, and Kickfire Kickfire Kickfire by Peter Zaitsev on April 4, and myself back on March 23.

So what can I make from the lack of company information and posted information to date.

  • Hardware based acceleration.
  • No Solid State Drive (SSD) Technology, at least not yet but C2App mentions SSD.
  • Data Warehousing, lending to thinking it’s not a transactional storage engine
  • A new storage engine and a new approach to data storage. I find this surprising, as it takes years to develop a feature complete storage engine, and most new 5.1 storage engines are indeed existing products, take Nitro, Solid, Infobright and Falcon. Only PBXT has been written from the ground up for MySQL 5.1, so looking to know more about it’s development
  • Expensive, it’s dedicated H/W + (assuming) MySQL Enterprise + Storage Engine

Posted in Blob Streaming, Databases, Falcon, Infobright, InnoDB, KickFire, Maria, MySQL, MySQL User Conferences, Nitro, Non Transactional, PBXT, PrimeBase Technologies, Professional, Solid, Storage Engines, Transactional, mysqluc08 | No Comments »

Come and get a t-shirt at UC2008

Sunday, April 6th, 2008

Here I am at my desk sporting the PrimeBase supporters t-shirt that will be available at the exhibitors booth at the 2008 MySQL Conference. The front is rather uneventful with the official logo, but the back will be worth the experience. So everybody interested in supporting PBXT as the transactional storage engine for MySQL developed by the community and for the community, please come and see us and mention the secret password.

We have been placed way back in the right hand side of the exhibitors hall at booth 518, in front of the Open Source and OEM providers.

Posted in Databases, MySQL, PBXT, PrimeBase Technologies, Professional, mysqluc08 | No Comments »

A day with Paul McCullagh – Architect of PBXT and BlobStreaming

Friday, March 28th, 2008

Continuing on from my lightning visits with Jan Kneschke and Michael Zinner, today I got to spend a day with Paul McCullagh at his home in Hamburg Germany.


Paul is the architect of the PBXT Pluggable Storage Engine for MySQL 5.1, and also the Blob Streaming Storage Engine. His work was acknowledged with the MySQL Community contributor for the year in 2007. The successful PrimeBase product for the publishing Industry in Europe and North America also now uses PBXT for underlying data storage which is great to see a company use it’s own products.

Like each friend and expert this week, Paul develops using Mac, but at least uses the US English version & keyboard layout, not the German layout. As Paul states, “the German version is not productive for coding”.

Paul uses NetNewsWire Lite as his RSS feeder, as I observe some PlanetMySQL links. I’m more old school, I just go to the site every few days. I really should move from the dark ages, one advantage is the RSS feeder shows you what you have read.

However, there is a particular reason why I visited Paul, other then to spend some time with a good friend. I’m doing more investigation regarding making coding changes to MySQL, and I’m very out of the development cycle. I’ve forgotten more then I remember with C++ and in particular the tools and techniques used to develop, debug and deploy. Of course I know what I need to do, I just need to know what now is the most efficient means of doing today.

Paul uses Xcode to view and edit his MySQL code. The development environment provided by Apple is good for Java and C++. As an Eclipse person from my Java days, I was using CDT for my C++ environment. XCode is used for project management (i.e. looking at files in project) and for file editing. A standard terminal is used to do make. Xcode can do a quick compile for syntax checking and you get a list of errors and hot links to lines of code.

The next key part is debugging, and you can very easily define executables and enable you to run in an integrated debugger. A good option testing is to run mysql test with is –manual-gdb. This gives you the command you can run. What Paul does it copy the parameters, and add to an executable defined within XCode, the arguments from the –manual-gdb output.

The MySQL source tree was added as a project but only partial source directories such as (storage,sql,include) are required. You do need to make sure it does compile (but necessary for a build via Xcode.)

Additional debugging help is In xcode you can do jump to variable definition. So you can add breakpoints, and then run the defined executable that was build via command line make, and copy mysqld binary to initially deployed directory.

Regarding testing, we got to talk again about the issues with the MySQL Test Suite including the issues I encountered while working with Nitro. Hard coded storage engine syntax such as such as engine type=Innodb make testing more complicated then necessary. MySQL test requires some modifications for pluggable storage engines. You need to add a row to the plugins table via mysql_system_tables_data.sql to enable –default-storage-engine=pbxt as a command line argument. Paul did state that on using the MySQL testing suite, the Perl version is a great improvement in performance.

So now I understand the environment, I get onto my problem, and we are specifically looking at the thread sort_buffer and sql/filesort.cc

So when I ask about a few lines of specific code, Paul shakes his head, and says “This looks crazy”. After we spend some time on it, his comment is upgraded, “this doesn’t look crazy, it is crazy. When you look at the effect of the code, it appears it can be done in 3 lines, and it would make sense what it is doing.” This is worth further discussion in a future post.

One last tip learned. You need to be careful with multiple versions and open projects of the same, not to make changes 1/2 in one version and 1/2 in another version.

Posted in Databases, MySQL, PBXT, PrimeBase Technologies, Professional | No Comments »

Just what are MySQL 9.x features?

Wednesday, March 26th, 2008

Top marks to Jay Pipes for getting the Forge 2.0 finally out after quite some time, as well as in the midst of the MySQL Conference he is organizing.

I am worried however about some of the content, as shown in the screenshot below, the opening page lists Worklog tasks/features for versions 6.x or 7.x , that’s ok, but features in 9.x. Where is the practicality of thinking more then 2 releases ahead, and just having a future bucket. Indeed, we have 5.1 and 6.0 already frozen and not releases, so 6.x is already 3 releases out.

Tonight we were told at the NY PHP Meeting MySQL 5.1 is not due to late Q2, so that’s at least June 2008.
The MySQL 5.1 Release Notes reveals a history that I don’t find very flattering.

  • MySQL 5.1.3 (29 November 2005)
  • MySQL 5.1.9 (12 April 2006) *First reported beta via docs
  • MySQL 5.1.22 (24 September 2007: Release Candidate)

I hope that Sun will take on board this very slow release cycle of producing GA products, the last version MySQL 5.0.15 (19 October 2005: Production) 2 years and 6 months ago.

I’m even more interested then previously in the ultimate release and success of MySQL 5.1 as this is a pre-requisite of my new employer’s key product the PBXT Storage Engine for MySQL.

Posted in Databases, MySQL, PBXT, PrimeBase Technologies, Professional | No Comments »

My passion for Open Source

Saturday, March 15th, 2008

I am a very strong proponent of Open Source (excluding my Macbook). Joining MySQL Inc was a wonderful achievement, being part of the team behind the most popular open source database. Leaving MySQL was not an easy decision due to the people, but the Sun transition and requirements did help. However it is no surprise I am joining another open source company – Primebase Technologies in Hamburg, Germany. My association with the MySQL Community will only be strengthened with my full work and support behind the PBXT and Blob Streaming pluggable storage engines for MySQL.

It is actually poetic that I am joing Primebase for I have the auspicious recognition while an active part of the MySQL community of introducing Paul McCullagh to MySQL some 2 years ago. Only weeks later, PBXT was MySQL news in the opening CEO keynote my Marten Mickos at the 2006 MySQL Users Conference.

I expect this opportunity will increase my contributions, in particular in the lacking areas of instrumentation and memory management. As well I will now be focusing my efforts on MySQL 5.1 and ensuring this is of the highest quality for the MySQL Community. While my open source code contributions have been small to date, my first some years ago adding in JMeter support for MySQL Stored procedures and Transactions, the feeling of seeding instead of leaching is a wonderful thing. I look forward to a greater opportunity with the team at Primebase Technologies.

Posted in Databases, MySQL, PBXT, PrimeBase Technologies, Professional | No Comments »

MySQL Conference – PBXT Recognized

Wednesday, April 25th, 2007

As I mentioned in MySQL Conference – Rewarding the Community, Paul McCullagh, the creator of the Community Transactional Storage Engine PBXT won the Community Code Contributor of the Year award.

A photo for Paul. Great work.

Posted in Databases, General, MySQL, PBXT, mysqluc07 | No Comments »

Using PBXT 0.9.5

Tuesday, July 4th, 2006

Paul has released Version 0.95 of his PBXT MySQL Transactional Storage Engine.

Here is what I did to get it operational under CentOS 4.3.

su -
useradd pbxt
su - pbxt
wget http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.5.tar.gz
tar xvfz mysql-4.1.16-pbxt-0.9.5.tar.gz
cd mysql-4.1.16-pbxt-0.9.5
./configure --with-debug=full --with-pbxt-storage-engine --without-query-cache --with-mysqld-user=pbxt  --prefix=/home/pbxt/mysql
make
make install
scripts/mysql_install_db
cd /home/pbxt/mysql
./bin/mysqld_safe --user=pbxt --basedir=/home/pbxt/mysql &

Now, lets test and confirm PBXT is operational.

bin/mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-pbxt-0.9.5-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show engines;
+------------+---------+------------------------------------------------------------+
| Engine     | Support | Comment                                                    |
+------------+---------+------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     |
| HEAP       | YES     | Alias for MEMORY                                           |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES     | Collection of identical MyISAM tables                      |
| MRG_MYISAM | YES     | Alias for MERGE                                            |
| ISAM       | NO      | Obsolete storage engine, now replaced by MyISAM            |
| MRG_ISAM   | NO      | Obsolete storage engine, now replaced by MERGE             |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys |
| INNOBASE   | YES     | Alias for INNODB                                           |
| BDB        | NO      | Supports transactions and page-level locking               |
| BERKELEYDB | NO      | Alias for BDB                                              |
| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables             |
| NDB        | NO      | Alias for NDBCLUSTER                                       |
| EXAMPLE    | NO      | Example storage engine                                     |
| ARCHIVE    | NO      | Archive storage engine                                     |
| CSV        | NO      | CSV storage engine                                         |
| BLACKHOLE  | NO      | Storage engine designed to act as null storage             |
| PBXT       | YES     | Super fast transactional, row-level locking engine         |
+------------+---------+------------------------------------------------------------+
18 rows in set (0.00 sec)

Now to take if for a spin with the MySQL Sakila Sample Database. At this time I need a modified version of the schema, as the sample database is designed for Version 5.

Download sakila.mysql4 script.
Download sakila.pbxt.sql script.

bin/mysql -uroot mysql
mysql>  source sakila.mysql4
mysql> source  sakila.pbxt.sql
# NOTE: Alter table of film_text fails as expected due to FULLTEXT index
mysql> show create table actor;
mysql> exit;

A few warnings in my sakila.mysql4 script I’ll need to review, but it looks ok from an installation step. Now back to my tests that I’m writing to use the Sakila Sample Database which I was working on, so I can really test the ACID state and performance of PBXT.

Posted in Databases, General, MySQL, Open Source, PBXT | No Comments »

« Older Entries