MySQL QA

In the 5.1 release MySQL Cluster will support replication to another MySQL installation. The replication is handled using Row Bases Replication instead of Statement Based Replication.

I have been testing it for about a year now and it has gotten really hard for me to break at this point, but I am still trying.

So what is so cool about Cluster Replication? Good question!

One answer:

Cluster Replication really gives a company five 9's (99.999%) in up time. You can have a Master Cluster in the main site supporting the business and have another Slave Cluster in a total different site that can be used as a backup for times there are issues with main site. In addition, the Slave Cluster can also be used for reporting or data mining to take that traffic off of the main Cluster.

How hard is it to …

In about 30 hours I'm hitting the road again and heading first to Philadelphia, then New York, Seattle, and finally to Santa Clara, where the next MySQL Users Conference is held. I'm a speaker, and my talk is about Dynamic SQL in stored procedures. I'll make the examples available online at MySQL Forge. Right now the state of my slides is pretty much the same as of everything else, that is, disastrous.

Well I fear it is not that simple. As avid readers of my blog may have noticed I have some beef with the Zend PHP IDE proposal that was recently accepted by the Eclipse Foundation. After studying the Eclipse Foundation governance rules, looking at the facts and talking to people from Zend, I am reminded once again that it is important that people realize that open source is not about licenses.

Of course the OSI might intentionally or unintentionally make you think that licenses is all that matters. I thought it was very telling that one of the comments in my "What open source is not" post noted that the OSI in fact makes it clear that the term "open source" is a marketing term. A way to supersede the "free software" term that is so closely associated with …

I believe it was 1992, UNIX on PC's was less then stellar. 386BSD
required a math co-processor and everything else cost an arm and a leg.

Enter IBM with OS2. The install was clean, and other then having to
pay for a driver to run my CDROM drive it was great. I was able to
hack out a chunk of the GUI so that I could just have terminal
screen. Someone had ported bash to the OS so it was just like having
a cheap UNIX. I even rigged a character based terminal up to one of
my serial ports so that I can a real terminal to use!

Its fourteen years later and MySQL is working on 5.1. The OS2 code is
bit rot at this point. It complicates our include files, and it makes
a chunk of code in our portability layer a rat's nest to understand.
Someone even made the mistake of using two different flags to
represent the OS2 in the include files. Its time for it to …

We're starting on a new project using MySQL that will bulk load CSV data once a day and then users can report on it whenever they want. In the days of old (ie. Oracle), we'd simply setup a load job using SQL*Loader or use external tables. In MySQL, loading data is just an extension of SQL using the LOAD DATA command. Since my data was going to be distributed, I wanted
to explore Mike Hillyer's suggestion of using the LOCAL option of LOAD DATA.

First, I created a small table called ?testload? :

mysql> create table testload (empid integer, name varchar(20),
bonus integer);
Query OK, 0 rows affected (0.02 sec)

I then decided to try it from the server to verify everything worked as expected:

mysql> -u …

Every so often I need to reply the question, what is the best way to use 10.000 connections with MySQL (you can select your favorite big number here)
Well. The good answer is your better not.

First let see where this requirement typically comes from ? Most commonly it is something like we have 40 apache boxes each having 200 children with persistent connections which gives us 8000. Well... This brings me to two questions - do you really need persistent connections and do you really need 200 apache children ?

Persistent connections will give you decent performance benefit only in one case - if there are few database queries to create the page and if database load is large response time contributor. In modern rather complex applications it is rarely the case. This is one of examples why persistent connections were disabled in "MySQLi" for PHP. Java applications are better in this sense as they typically use …

One nice thing about speaking at the User’s Conference is that you get admission not only to the conference, but also to the Monday tutorial sessions.

This year I plan to attend two tutorial sessions:

Secure Your PHP and MySQL Web Applications

This one is delivered by Laura Thomson, whose writing and sessions I always love, can’t miss this one.

Data Warehousing with MySQL

John Paul Ashenfelter is a cool guy who really knows his stuff, I missed this session last year and don’t plan to miss it again.

As usual, you can expect a flurry of notes come conference time.

MySQL QA

Now that we have Disk Data for MySQL Cluster you can have the best of both worlds. You can use memory tables for those needing quick access and updates and Disk Data Files for those that will be too large to keep in memory all the time.

One issue with this first release is not having the ability to spread the data and undo files accross the disk sub system using the config.ini file. This can be a important issue for performance. You really don't want to have everything on one disk. Here I list a work around for moving undo and/or data files off to different drives to help in disk performance that I used during performance testing.

1) Bring up the cluster to include all data nodes
2) Each data node will create its own File System ((e.g. ndb_#_fs) where # == the DN ID)).
3) Under the data node FS create the symbolic links …

Jay Pipes gave an amazing tutorial on optimizing performance in MySQL by choosing your indexes and joins well. There were plenty of questions from the audience which he handled well, and in style. Over 50 people attended! And I really feel as though everyone learned a lot. The tutorial ran for 3 hours, but we were engaged until the last, and there were 15-20 people even at the bitter end.

Thanx, Jay! I can’t wait to see how the tutorial goes over at the MySQL Users Conference.

Pictures, video, slides and presentations will be up when we get to it.

MySQL implements the BIT data type differently in different versions, and the behavior is not what one might expect. In this article I’ll explain how MySQL’s behavior has changed over time, what strange things can happen as a result, and how to understand and work around display issues. I’ll tell you about a serious bug I’ve found, and discuss differences in the BIT data type between MySQL and Microsoft SQL Server.