This is a continuation of my earlier post with the same title. Recently while solving a problem where values of an auto_increment fields were getting repeated, a well documented and often neglected feature of InnoDB came forward. Lets first have a glimpse of the system and the problem.

We are maintaining a job queue in an InnoDB table for a distributed resource environment. This job queue is populated by distributed schedulers from the system, the job is assigned to a resource and once the job is done, it is removed from the job queue. The system runs on an assumption that the job id, which is an auto_increment field, always remain unique throughout the life of the system. I acknowledge that the usage is debatable, but lets keep it separate from this.

The problem: The operations team restarted the MySQL server in between. After that restart, some job id's got repeated. This broke the basic assumption of unique job …

In the ongoing effort to convert more users into paying customers, MySQL announced today that they are no longer making the source code tarball for their Enterprise server publicly available. You could see this coming from a million miles away.

Back in December 2006 I pondered on the changes with the MySQL database splitting into two offerings, the enterprise and community editions:

The source for the enterprise edition will still be available: we will continue to make all releases available over our BitKeeper tree and as source code tarballs So it appears that those willing to compile from source will still have access to the enterprise edition. This is very important (in my eyes) to …

Back in October 2006, we introduced MySQL Community Server. Since then, we’ve learnt a thing or two, spent many man hours discussing how to improve our processes, and are now refining the concept. We feel that we’ve come up with some good middle-ground that fulfils not only our company interests but fosters community use and growth as well.

The changes are in the areas of release policy and stability of MySQL Community Server and in the availability of MySQL Enterprise Server.

The changes start from the question: “How can we better target MySQL Community Server to the community and MySQL Enterprise Server to the paying customers?“. Many of them originate from our ongoing discussions with the Linux Distributions, some of whom have been distributing MySQL …

I think anyone into databases, Linux and so on (i.e. the LAMP stack), will benefit from the High Scalability website. Heck, any web developer will. Its well tagged (say, you’re into mysql or memcached), has a lot of content, and links to other sites that it gets its summaries/information from. Useful resource for all those into scalability (not necessarily high performance).

Incidentally, I see information about bdb (in the forums), but where are all the reports about highly scaling sites running postgresql? It’d be interesting to read, and catalogue, I think

Technorati Tags: …

Sun opens new microprocessor design. IBM launches eco-efficient Linux initiative. EnterpriseDB announces professional-grade PostgreSQL distribution. (and more)

Sun Microsystems Enters Commercial Silicon Market With World’s Fastest Commodity Microprocessor, Sun Microsystems (Press Release)

IBM Launches “Big Green Linux” Initiative, IBM (Press Release)

EnterpriseDB Announces First-Ever Professional-Grade PostgreSQL Distribution for Linux, EnterpriseDB (Press Release)

EnterpriseDB Introduces Powerful Business Intelligence and Data …

It's nice to see Red Hat investing in JBoss. It's good for the company and, I believe, good for JBoss.

In the first formal offering of the JBoss Application Platform with an OEM, Red Hat and Dell today announced that Dell will be shipping its PowerEdge servers with the JBoss Enterprise Application Platform or the Red Hat Application Stack, which includes Red Hat Enterprise Linux, JBoss Enterprise Application Platform and MySQL.

Most interestingly (though not surprisingly), you can get JBoss Enteprise Application Platform preintegrated and bundled with Windows, too.

The Dell/Red Hat offering features three solutions:

...

I often read articles saying to combine statements and send less queries. But you seldom see advise about splitting queries to improve performance. Here is what I came across just the other day:

SELECT IF(`uid_to`=@user, `uid_from`, `uid_to`) AS `uid`, `message`, `date`, `uid_to`=@user AS received
  FROM `beepme_msg`
  WHERE (`uid_from`=@user AND `uid_to`!=@user) OR (`uid_to`=@user AND `uid_from`!=@user)
  ORDER BY `uid_to`, `date`;

The query gets all messages send and received by the user, filtering our message the user send to himself. This looks like a good query since you’ll get all you data in one call. In reality it will mess up your performance, since it can’t use any indexes and will therefor use a table scan. And as we know, tables scans are slow.

The ‘OR’ statement basically messes this up, since only one index can be used per table. We can see what happens a bit better if …

Event: Learn How to Increase Recoverability of MySQL Databases - presented by BakBone on Tuesday, August 21, 2007

we were able to get out this month?s connector/odbc release a little earlier in the month than usual. one reason we made the release earlier was to get a replacement for last month?s 3.51.17 out there, because that release had an unfortunate bug that caused problems when working with many odbc applications, like microsoft access.

we were also able to get under 90 bugs by fixing a number of other bugs, and working through more of the old bugs and figuring out that they were either already solved or otherwise no longer relevant.

the other reason to get this out earlier in the month has to do with a project that should see some more daylight by the end of the month. more on that when the time …

The idea is as old as Replication is:

How do you know which is the most current slave.

You can use the Master_Binlog_Pos to guess what is most up to date, but which transaction does this binlog position match ?

With the proxy you can add a global transaction ID to your setup, if you let the inject some information into the stream.

The idea is simple and is documented in various places.

Create a MEMORY table which is replicated with a single UNSIGNED BIGINT and increment it at the end of each transaction.

CREATE TABLE trx (
  trx_id BIGINT UNSIGNED NOT NULL
) ENGINE=memory;

INSERT INTO trx VALUES ( 0 );

When ever you commit a transaction UPDATE the trx\_id field:

UPDATE trx SET trx_id = trx_id + 1

Usecases:

• identify which slave is most current and switch to it in case of master failure …