The latest from the MySQL community

I'm thinking about ways to store archival data for the long term and wanted to solicit anyone who's been down this road for some input, advice, warnings, etc.

Background

Essentially I'm dealing with a system where "live" and "recently live" data is stored in a set of replicated MySQL servers and queried in real-time. As time goes on, however, older "expired" data is moved to a smaller set of replicated "archive" servers that also happen to be MySQL.

This is problematic for a few reasons, but rather than be all negative, I'll express what I'm trying to do in the form of some goals.

Goals

There are a few high-level things I'd like this archive to handle based on current and future needs:

1. Be able to store data for the foreseeable future. That means hundreds of millions of records and, ultimately, billions.
2. Fast access to a small …

We gathered together our ideas of MySQL improvements on this page http://www.percona.com/percona-lab/dev-plan.html
and we are going to implement some of them.
My favorite one is - make InnoDB files .ibd (one created with --innodb-file-per-table=1) movable from one server to another, however it is sort of challenging.
Probably next one patch we want to integrate is Google's smp-fix or Yasufumi's rw-locks (we are going to test both before)

On this page http://www.percona.com/percona-lab.html you can find links to our current binaries and patches.

Entry posted by Vadim | …

To achieve linear scalability of MySQL Cluster using the DBT2
benchmark has been a goal of mine for a long time now. Last
week I finally found the last issue that limited the scalability.
As usual when you discovered the issue it was trivial (in this
case it was fixed by inserting 3 0's in the NDB handler code).

We can now achieve ~41k TPM on a 2-node cluster, ~81k on a
4-node cluster and ~159k TPM on a 8-node cluster giving roughly
97% improved performance by doubling number of nodes. So there
is nothing limiting us now from achieving all the way up to
1M TPM except lack of hardware :)

I've learned a lot about what affects scalability and what
affects performance of MySQL Cluster by performing those
experiments and I'll continue writing up those experiences on
my blog here. I have also uploaded a new DBT2 version where I
added a lot of …

Working on query optimizer bugs can be a rather frustrating experience. First, as soon as some query doesn't run as fast it theoretically could people will consider it a bug. On one hand that's great, you get a constant stream of user input, but on the other hand you end up with a whole pile of "bugs" which you can't hope to finish.

What's more frustrating is that even if you manage to create a fix for an optimizer bug, there are chances it won't be allowed into next GA (currently 5.0.70) or approaching-GA (currently 5.1.30) release (GA is our term for "stable" or "release").

The reason behind this is that most optimizer bugfixes cause the optimizer to pick different query plans, and there's no way to guarantee that the fix will be a change for the better for absolutely everyone. Experience shows that it is possible to have a query that hits two optimizer bugs/deficiencies at once in such a way that they cancel each other out, and …

I was wondering for quite a while if MySQLs C-API (libmysqlclient) can really do async queries and where it is lacking support.

Scanning through the source (sql/client.c) you see:

int mysql_real_query(MYSQL *mysql, const char *query, ulong length)
{

  if (mysql_send_query(mysql,query,length))
    return 1;

  return((int) (*mysql->methods->read_query_result)(mysql));
}

Digging a bit deeper you discover that mysql_send_query() and mysql_read_query_result() are public, but undocumented. Well, but that doesn't stop us.

When I talk about async queries I mean:

• being able to issue several queries in parallel without using threads

That is usually what you need in event-driven applications and allows you to create parallel application without having to use too many system resources (memory, threads, ...).

As long as you use …

Google Summer of Code 2008 officially ended on August 18th. Since that deadline, I have been working to solve a few non-functional problems I discovered during testing stage.

• By week 11, Skoll Client was collecting runtime information for each of the MySQL tests. During the development stage, I only tested this feature with a few MySQL tests. However, during testing stage I discovered this feature would take way too much time and resources for majority of the users. To completely test one configuration of MySQL, it took Skoll Client over 10 hours because MySQL server must be restarted after each test. Also, the runtime information collected was over 3 GB (zipped!). If the user is only interested in the coverage of the MySQL test suite, the per-test runtime information is a major overkill. The solution was to have two runtime information collection flags in the Skoll Client, --gcov and --detailed_gcov, to collect runtime information for …

After running symfony propel-build-all on a new symfony installation got this error. The solution is to install the php-xml rpm (deb).

[root@devel2 cms]# symfony propel-build-all
>> schema converting "/home/sfprojects/cm...lugin/config/schema.yml" to XML
>> schema putting /home/sfprojects/cms/pl...erated-sfGuardPlugin-schema.xml
>> file+ config/generated-sfGuardPlugin-schema.xml
>> file- /home/sfprojects/cms/plugins/sf...erated-sfGuardPlugin-schema.xml
Buildfile: /usr/share/pear/symfony/vendor/propel-generator/build.xml
[resolvepath] Resolved /home/sfprojects/cms/config to /home/sfprojects/cms/config

propel-project-builder > check-project-or-dir-set:

propel-project-builder > check-project-set:

propel-project-builder > set-project-dir:

propel-project-builder > check-buildprops-exists:

…

We’re getting some high quality Use Cases from our user base, related to the MySQL 5.1 Use Case Competition.

The first one is by Greg Haase of Lotame, based in Elkridge, Maryland (USA), a company dedicated to providing solutions within social media. His article is about an innovative use of MySQL 5.1 partitioning and Event Scheduler to prune ARCHIVE tables.

There are more articles in the pipeline. And there is still time for you to submit your story by 30 September 2008. We’re all excited to read them, and with your permission, we’ll share them with …

Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the

SetFileValidData Function (Windows)

There seems to be two options on Win32 for preallocating disk space to files.

Basically, I want a equivilent to posix_fallocate or the ever wonderful xfsctl XFS_IOC_RESVSP64 call.

The idea being to (quickly) create a large file on disk that is stored efficiently (i.e. isn’t fragmented).

From SQL, you’d do something like “CREATE LOGFILE GROUP lg1 ADD UNDOFILE ‘uf1′ INITIAL_SIZE 1G;” and expect a 1GB file on disk. One way of getting this is calling write() (or WriteFile() on Win32) repeatedly until you’ve written a 1GB file full of zeros. This means you’re generating approximately 1GB of IO.

Except it’s worse than that: every time you extend the file, you’re going to be changing the metadata (file and free space information). If you’re lucky, you won’t be …