I started using Oracle, a MVCC database, to develop reporting (data warehousing, BI, take your pick) systems years ago.  I’ve come to appreciate the scalability improvements that MVCC provides, particularly for pseudo real-time reporting applications, the ones where loads are occurring at the same time as report generation.  So when people say InnoDB, partly due to MVCC, isn’t as good as MyISAM for reporting I had to look into this in more detail.

What I found is InnoDB is a good engine for reporting.  In some ways, such as performance, it is at times better than MyISAM, and one of the downsides, such as a larger disk requirement, can be mitigated.  The trick is to for the primary key to be the one predominant access path.  In this example, the InnoDB clustered index, is purchaseDate and another column, such as orderId is added to make it unique.  This has a number of advantages.  In my experience, …

While Andrey made good progress with the Connector/OpenOffice.org I wasted my time trying to use OpenSolaris 2008.05 as an OpenOffice.org development platform. OpenSolaris is not on the list of platforms used my the maintainers and developers of OpenOffice.org. Another open technology - JDK 1.5 - has lowered my openness towards new development platforms.

If you are an OpenOffice.org end-user, skip this blog posting: grab a binary package and be happy! That’s better for the world-wide climate because your power consumption will be lower. An Intel Core 2 Duo E6750 …

While Andrey made good progress with the Connector/OpenOffice.org I wasted my time trying to use OpenSolaris 2008.05 as an OpenOffice.org development platform. OpenSolaris is not on the list of platforms used my the maintainers and developers of OpenOffice.org. Another open technology - JDK 1.5 - has lowered my openness towards new development platforms.

If you are an OpenOffice.org end-user, skip this blog posting: grab a binary package and be happy! That’s better for the world-wide climate because your power consumption will be lower. An Intel Core 2 Duo E6750 …

I have recently decided I would like to take advantage of the benefits of InnoDB file-per-table. Namely, that OPTIMIZE TABLE benefits-- defragmentation (Grazr does a lot of writes, both INSERT and DELETE), .ibd files being able to shrink if optimized, etc.

So, the next question is how to convert a ton of data to this setup. What I have tried is this:

1. Add innodb_file_per_table to my.cnf
2. Restart MySQL
3. Alter all InnoDB tables engine=InnoDB

This works great... except, my two 10GB tablespace files remain. I would like to make them smaller, but there is no way of doing this.

Also, there is no trick where you back up the newly created .ibd tables after the alter, move the large table space files to a backup dir, restart and pray that the new smaller tablespace files magically work with your .ibd files. That does *not* work ;)

So, dump all data and restore …

So you need to design highly available MySQL powered system... how do you approach that ?
Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience - with quality commodity hardware (Dell,HP,IBM etc) you would see box failing once per couple of years of uptime which is enough to maintain level of availability needed by many small systems. In fact they typically would have order of magnitude more availability issues caused by their own software bugs, DOS attacks and other issues.

However as your system growths the reliability goes down. If you have 100 servers with each failing every 2 years this is about a server a week which is bad and if you're into thousands and tens of thousands of servers server failures are becoming common place so it is important to make sure failing server does not affect …

I've been struggling to come up with a decent prototype for the self-verifying random testcases I wrote about earlier. In the mean time, I wrote a set of triggers and a view which should yield the same information, no matter what you do to the underlying table. If they don't, there's a bug in the server.

The test table, t1 is simple. It has an int and a varchar column. There are triggers after insert/update/delete. The triggers update an evolving summary table.

The evolving summary table maintains the average+sum value of the int, and the average+sum length of the varchar field. The view selects these values from the table too.

To test it, I ran 50 threads of random insert/update/delete queries to table t1 for a few minutes. Then after the test had stopped I confirmed the results matched. (i.e. the running totals had been correctly maintained - if they match the view).

…

In search of better qa tools, i have resumed work on my gypsy. Within hours i verified a bug i thought was not possible any time soon...

crash on prepared statement + cursor + geometry + too many open files !

The code is also in launchpad (bzr branch lp:gypsy) if anybody cares.

While Andrey made good progress with the Connector/OpenOffice.org I wasted my time trying to use OpenSolaris 2008.05 as an OpenOffice.org development platform. OpenSolaris is not on the list of platforms used my the maintainers and developers of OpenOffice.org. Another open technology - JDK 1.5 - has lowered my openness towards new development platforms.

If you are an OpenOffice.org end-user, skip this blog posting: grab a binary package and be happy! That’s better for the world-wide climate because your power consumption will be lower. An Intel Core 2 Duo E6750 …

MySQL will be well represented at FrOSCon 2008. August 23-24 2008.
This will be my third presence there as a speaker. I fondly remember the pleasant experience of the 2006 and 2007 editions.

This time, MySQL is also a sponsor. In addition to having 5 speakers from the database group, we will also have a table and a BoF (MySQL 5.1 and beyond).
The list of speakers and talks makes you want to be there. (I hope)

In April this year, Kickfire announced the first high-performance appliance for MySQL. As part of the announcement, the company released data warehouse benchmark results that broke prior records in terms of price/performance and performance in a non-clustered environment. While the creation of a new appliance built exclusively for MySQL along with the benchmark records was noteworthy, perhaps the bigger story lies in what we believe to be the beginning of a paradigm shift in the database world - one marked by the advent of the first SQL chip.

To give some context to this story I have included a graph below which depicts the evolution of VLSI (Very-Large-Scale Integration) semiconductor technology and its growing impact on a broadening range of industries.

…