MySQL has a problem: the project has become so important that many have forgotten the company at its heart.

I have created a new tool, called xtstat, for analyzing the performance of the PBXT storage engine.

The way it works is simple. PBXT now counts all kinds of things: transactions committed and rolled back, statements executed, records read and written, tables and indexes scanned, bytes read, written and flushed to various types of files: record, index, data logs, transaction logs, and so on.

A SELECT on the system table PBXT.STATISTICS (or INFORMATION_SCHEMA.PBXT_STATISTICS if PBXT was built inside the MySQL tree) returns the current totals of all these counters. xtstat does a SELECT every second on this table and prints the difference. In this way, you can see how much work PBXT is doing in each area.

There are currently 48 different statistics:


To ensure all this counting does not itself cost any performance, each thread counts for itself, so no locking is required. The SELECT on …

One thing we were guessing at with Massimo yesterday is, if you store
large blobs as disk data, how much will they consume DataMemory and
IndexMemory (primary key, each "chunk" has a hidden primary key, first 25x bytes
of blob stored in memory...)?

My empirical test showed that about 2% of the total size of blobs is needed for RAM
(25% of that is IndexMemory).

IMHO this is close to negligible, but in many situations not negligible
at all (may have close to TB of disk data -> 20GB of RAM needed for
disk data).

Also note that this is a minimum figure. If you actually have something
else than the blob (like other indexes) you of course use much more RAM.

The test was:
CREATE TABLE `jpgtest` (
`id` int(11) NOT NULL,
`jpg` blob,
PRIMARY KEY (`id`)
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster;

Late last week Open Query quietly launched its new subscription offering for MySQL support. Essentially, you commit to a number of hours per month for a number of months, and Open Query delivers appropriate resourcing. You can sign up for a minimum of 2 hours per month, and the cost per hour is quite low because a) we prefer to build a long-term relationship with our clients and b) the cost structure of Open Query enables this and we're proud of it!

We've designed this offering to provide an equal arrangement between Open Query and its clients, and to be in line with our vision of education and "optimisation by design" rather than simply trouble-shooting. Years ago, Monty used to describe MySQL Support in terms of insurance, and that seemed reasonable at the time. It's useful to have insurance, but when building a new house or renovating an existing one you'd be …

One thing we were guessing at with Massimo yesterday is, if you store
large blobs as disk data, how much will they consume DataMemory and
IndexMemory (primary key, each "chunk" has a hidden primary key, first 25x bytes
of blob stored in memory...)?

My empirical test showed that about 2% of the total size of blobs is needed for RAM
(25% of that is IndexMemory).

IMHO this is close to negligible, but in many situations not negligible
at all (may have close to TB of disk data -> 20GB of RAM needed for
disk data).

Also note that this is a minimum figure. If you actually have something
else than the blob (like other indexes) you of course use much more RAM.

The test was:
CREATE TABLE `jpgtest` (
`id` int(11) NOT NULL,
`jpg` blob,
PRIMARY KEY (`id`)
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster;

One thing we were guessing at with Massimo yesterday is, if you store
large blobs as disk data, how much will they consume DataMemory and
IndexMemory (primary key, each "chunk" has a hidden primary key, first 25x bytes
of blob stored in memory...)?

My empirical test showed that about 2% of the total size of blobs is needed for RAM
(25% of that is IndexMemory).

IMHO this is close to negligible, but in many situations not negligible
at all (may have close to TB of disk data -> 20GB of RAM needed for
disk data).

Also note that this is a minimum figure. If you actually have something
else than the blob (like other indexes) you of course use much more RAM.

The test was:
CREATE TABLE `jpgtest` (
`id` int(11) NOT NULL,
`jpg` blob,
PRIMARY KEY (`id`)
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster;

Sun Microsystems today announced that Aftonbladet is now running the majority of its business on MySQL Enterprise™.

Aftonbladet is the largest daily newspaper in the Nordic region, with about 1,209,000 readers a day, and Aftonbladet.se is the largest online news site in the region with approximately 4.2 million unique visitors per week.

One command, which few people realize exists is SHOW OPEN TABLES - it allows you to examine what tables do you have open right now:

PLAIN TEXT SQL:

1. mysql> SHOW open TABLES FROM test;
2. +----------+-------+--------+-------------+
3. | DATABASE | TABLE | In_use | Name_locked |
4. +----------+-------+--------+-------------+
5. | test     | a     |      3 |           0 |
6. +----------+-------+--------+-------------+
7. 1 row IN SET (0.00 sec)

This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table is opened more than ones)

In_use show how many threads are …

As Giuseppe just reminded MySQL 4.0 is reaching its end of life in about 2 weeks from now. When it becomes unsupported by MySQL via normal support agreements together with 3.23 version.

At Percona we do not have such restriction and we will continue to support your environment even if you're still on MySQL 4 or 3.23 (we indeed do have customers which are still using 3.23 now)

What does it mean for you in practice ? This means we will continue helping you with issues you're having with MySQL 3.23 or 4.0 and will be happy to backport bug fixes to MySQL 3.23 and 4.0

Supporting MySQL 3.23 and MySQL 4.0 environment is indeed more expensive than current MySQL versions because we can't relay on Sun/MySQL doing any work with bug fixes plus more work is often needed because these versions have lower …