Today I took a break from software engineering. Besides a few meetings and a few small code tweaks I spent the bulk of the day looking at MySQL Cluster in 5.1.7.

I've set up a cluster a handful of times now so it's fairly familiar. From scratch to having three boxes (one Fedora Core and two Red Hat ES 4) and MySQL 5.1.7 installed took until ~10:30. At this point I was walking from the server room back to my desk when I noticed a conference room full of engineers. A 10am meeting that I thought was pushed back to 2 wasn't. Oops.

A few hours later I was loading sets of data from our production database to see just how well the data moves from 4.0 to 5.1. I haven't completed any comprehensive tests, but I wasn't impressed with performance when restoring from a dump file. Perhaps I need to do some cluster tuning, or the cluster is just slower at DDL. Next I'd like to grab some sample sets of queries from production and run them against …

Ok, we didn't acquire anyone.  But I thought it was funny the way our partnerships with IBM and EMC were reported on Heise.de, the huge german news web site.  The text below popped up on my Google News search.  We may have big balls, but not that big.  But it makes for interesting discussion.  :-)

As reported on Google via Heise.de:

MySQL acquires IBM and EMC as partnership program members
Heise Online, Germany - 8 hours ago
IBM and EMC have joined the partnership program of MySQL AB. The Network Certified Partner Program attests the smooth interaction ...

Actual MySQL Press Release:

…

I just updated my SQL reserved words checker to include a few new words from MySQL 5 that it wasn't picking up.

I use the tool when I'm designing a database table, or column. It can tell you if a value is a reserved word in MySQL, PostgreSQL, Oracle, or SQL Server.

This is a follow-up to my earlier article on the MaxDB Synchronization Manager. In the months since my prior article, the Synchronization Manager has proved itself production ready and capable of replicating real-world databases in demanding scenarios.

Take the following as theoretical and emperical examples of such scenarios (not necessarily in that order):

i) For its yearly trade show, a company needs to process user registration. Because of the high volume of attendees, there must be more than one system through which registration takes place. Therefore, the registration software is to run on multiple separate laptops. At the end of each day, the laptops synchronize their registration information with each other and a master registration database.

ii) In order to reduce human error and increase efficiency, a grocery store …

Dear Sheeri,

Congratulations! You have been accepted as a presenter for the O’Reilly Open Source Convention 2006 at the Oregon Convention Center July 24, 2006 - July 28, 2006.

The following has been accepted as a 45 minute session for the event:

“So you’ve inherited a MySQL Instance on Unix”

…..

——————-

I’d totally forgotten I’d even submitted that!

Guess I’m going to Oregon in July…..

Had evaluated mysql 5.1 and found it to be very good.
Check this out

[root@localhost mysql5]# ./bin/mysqladmin -u root -pjayant status
Uptime: 38621 Threads: 2 Questions: 98346998 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 119 Queries per second avg: 2546.464

The no of queries/second is very high. And the load on the machine is around 2.

Have tried out mysql partitioning on one of the tables. The table has large no of rows - around 90,000,000. And the results were very good. I was able to get large no of queries serverd at the same time from the table.

Few more improvements in the partitioning algorithm can make it rock.

1. Parallel execution of queries on all partitions
2. More efficient administrative commands like alter, add, COALESCE partitions.
3. Decrease locking time of partitions not required in a query.
4. Provision …

I have been noticing some forum posts / questions about InnoDB datafiles. The question is can they be copied to another server while a MySQL server is online? With a little testing, the answer is Yes!!

Provided that there is no write activity on the server, the files can be copied to another server and the MySQL server started up with no problems. This gives me lots of hope in trying to build new slaves quickly, while minimizing downtime. Both the innodb data file(s) and the log file(s) must be copied to the new server (while its down) in order for this to work. InnoDB goes through any recovery that's needed and the server comes up.

This gives me some hope in building new slaves going forward.

I don’t think I’ve ever installed MySQL via .rpm I have always installed via .tar.gz primarilarly because I’m an /opt system administrator from my old UNIX days. so my first experience installing on my CentOS 4.2 (aka RHEL 4).


$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
perl(DBI) is needed by MySQL-server-standard-5.0.19-0.rhel4.i386
Suggested resolutions:
perl-DBI-1.40-8.i386.rpm
$ rpm --import /usr/share/doc/centos-release-4/RPM-GPG-KEY
$ yum install perl-DBI-1.40-8
$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-server-standard …

As mentioned in my earlier post Emulating Oracle Output Functionality, I’ll be speaking at the MySQL Users Conference on the topic of MySQL for Oracle Developers. Here is the second in a series of points regarding current MySQL Stored Procedures and Functions functionality.

• 3. Named Parameters
• 4. Procedure Overloading

3. Named Parameters

Parameters passed to Procedures under normal operations can be considered positional parameters. For each parameter, a calling statement is required to pass the same number of parameters and in the same order. Named parameters allows the user to pass parmeters in the order of their choosing, and also not pass all parameters. …

The advantage of having ExtractValue in the database, is that I can now just pull all of the titles out of the RSS without having to deal with it in the client :)

mysql> select ExtractValue(raw_xml, "/*/channel/title") from sites_log LIMIT 7;
+----------------------------------------------------------------------- --------+
| ExtractValue(raw_xml, "/*/channel/ title") |
+----------------------------------------------------------------------- --------+
| The Motley Fool |
| Nanodot: Nanodot: Nanotechnology News and Discussion of Emerging Technologies |
| Slashdot: Science |
| Slashdot |
| Slashdot: Book Reviews |
| Slashdot: Features |
| Slashdot: Interviews |
+----------------------------------------------------------------------- --------+
7 rows in set (0.00 sec)

mysql>

Now I just do a quick:

…