We’ve been experimenting lately with database partitioning (in version 2.3.2-dev, make sure to update your kettle.jar to the latest snapshot). In our context, database partitioning means that we divide our data over a cluster of several databases.

A typical way of doing that is that you divide the customer_id by the number of hosts in the cluster and get the remainder. If the remainder is 0, you store the data on the first host in the cluster, 1 for the second, 2 for the third, etc.

This sort of thing is something that we’ve been implementing in Kettle for the last couple of weeks. The reasoning is simple: if one database is not up to the task, split the load over 2 or 5 or 10 databases on any amount of hosts.  ( Now imagine all the PCs at work all running an in-memory database )
Besides small changes to the Kettle transformation …

I've run into the following thread couple of days ago:

Basically someone is using sphinx to perform search simply on attributes (date, group etc) and get sorted result set and claiming it is way faster than getting it with MySQL. Honestly I can well believe it for cases when you want to know number of matching rows as well as if you can't build efficient indexes so selectivity is done by index and index used to resolve order by.

Funny enough to filter by attributes or sort sphinx does not use indexes - indexes are only used for full text search matching, but it is still extremely fast doing data crunching.

I just tested right now performing search of "the" which matched 100.000.000 of documents out of 200.000.000 collection (200GB) completed in 0.7 second. This is system we're building for one of our clients which uses cluster of 3 nodes to …

Many people have problem how to use the string data types (char/varchar) from the NDBAPI. Here I explain how you can use them.

First of all, let's create a table we can use as an example:

CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` char(64) DEFAULT NULL,
`c` varchar(255) DEFAULT NULL,
`d` varchar(256) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;


CHAR

In the MySQL server CHARs are space padded to the length of the CHAR.

Example:
insert into t1(a,b) values (1, 'mysql'); -- 'mysql' is 5 bytes long.

The MySQL server will now space-pad the string up to the length (in this case 64 bytes, given the character set used).

However, the NDBAPI does not respect this and does not care if it is space-padded, null-padded, or even 5-padded. But, if you want to be able …

SAN FRANCISCO ? Hyperic Inc., the leader in multi-platform, open-source IT management, today announced a technology license agreement with MySQL AB, the developer of the world?s most popular open source database. Under terms of the agreement, Hyperic?s SIGAR (System Information Gathering and Reporter) API is being incorporated into the new monitoring and advisory component of the recently-announced MySQL Enterprise subscription offering.

I will be a speaker at the Open Source Database Conference, which is held in Frankfurt from 6th to 8th November 2006, parallel to the International PHP conference.
I will present two sessions, one on Advanced Replication Techniques in MySQL 5 and the other on The MySQL General Purpose Stored Routines Library.
I submitted both proposals long before I started considering joining MySQL, so I will go there with the blessing of my current employer, but I will speak under my former affiliation, to avoid attributing to my current company what are my personal ideas.

There is a feature in MySQL to log all queries that take longer than long_query_time to execute, and optionally also log the queries that don't use indexes. If you are not familar with it, I recommend reading this page in our manual.

One issue to be aware of, is that some queries may take much longer than long_query_time to execute, but will not show up in your slow query log. The manual gives a very good explaination for this;

The time to acquire the initial table locks is not counted as execution time.

There are two status variables that may help in seeing if you are hitting read/write concurrency issues because of table-level locking;

mysql> show global status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value | …

Using freely available ZRM for MySQL , you can set up a solution to backup and restore your MySQL database, within minutes. This article has all the details.

If you're following MySQL or PHP landscape you should have seen announcement by MySQL to develop pure PHP driver. If not - Here is FAQ .

I'm to meet the team (Georg, Andrey etc) which will be developing this driver during my visit to Open Source Database Conference in November so I thought it would be good idea to gather some wish list for things nice to have in this new driver. Below is my list and I would appreciate to hear your ideas.

Build In Profiling I would like to see how many connections and queries page generated and how long they took - I will place this information in the log. For debugging I'd like to be able to get a table below page output listing all the queries, their exec times and number of rows they are returned. So far it has to be done in inherited class.

Auto Explain Would be very helpful for …

I have decided to withdraw definitively from the political debate on the European Patent Litigation Agreement (EPLA). The process itself may very well take several more years, but I will not do any more lobbying nor make any more public statements in this regard.

This year I made a lot of effort to inform politicians, the media and the public of the shortcomings of the present EPLA proposal, and I had the chance to make my contribution in the build-up to the European Parliament’s October 13 resolution, but I kept rather silent in recent weeks and will not speak out on this particular issue again. Until there is a new patent policy process in which I might participate, I will not comment on any patent-related issues. Last year I returned to the fray after three months of absence ? this time there definitely won’t be …

Ah well, since my first article on this, MySQL has had multiple version changes and I have gained more experience in using MySQL. (Off topic...... I really like the fact that I am stuck in everything from MySQL server config/support thru Apache thru WebObjects/Java development, deployment and administration ...... never a dull day in the job!). Here is my "new improved" process for setting up a replication master-slave configuration using MySQL 4.1.21. These instructions will definitely not work for version 4.0.XX and earlier and may not work for some earlier versions of 4.1. Earlier version incompatabilities are mostly related to the parameters used in mysqldump.

This article assumes a basic knowledge of unix (cd, ssh, scp, mkdir, chown) and a basic knowledge of mysql (mysqld, mysql, mysqldump, mysqladmin)