We have just released the first binary version of Apache Olio for both the PHP and Rails implementation. Both implementations have been tested quite thoroughly now and we think they are robust enough for serious use - especially for performance testing the workloads.

I introduced Olio in a previous post. It is a toolkit that includes a sample web2.0 application implemented in both PHP and Rails that includes a load generator to drive load against the application.

Please visit the Olio site and download the kits. If you find it interesting, I invite you to come join the project.

I was a little worried last week with regards to our Google Summer of Code status, but I’m glad to see that MySQL has been accepted, for a third year running, for the Google Summer of Code 2009.

Mentors & students alike, check out the ideas page. You as a student can even submit ideas, and we’ll look for a mentor for you, naturally.

Things we’re looking for:

• Simple bug fixes
• Improvements in documentation of code
• Test suite improvements
• New features, but simple enough to implement in timeframe
…

I feel like I've been seeing this a lot lately.

occasionally, seemingly innocuous selects take unacceptably long.

Or

Over the past few weeks, we've been having bizarre outages during which everything seems to grind to a halt... and then fixes itself within 5 minutes. We've got plenty of memory, we're not running into swap, and we can't find any queries that would seem to trigger outages: just tons of simple session requests all hung up for no obvious reason.

Problems like this are always hard to debug. If it happens twice a week for 5 minutes at a time, your chance of getting someone logged onto the machine to watch it in action are pretty slim. And of course, when they do look at it, they see nothing wrong on the surface; it takes some very clever, very fast work with OS-level debugging and tracing utilities to really prove what's happening.

The two cases mentioned above were caused by …

For a test database of 182M rows(about 3BG of data file), the optimized MyISAM index was about 1.5GB. The unoptimized index file was about  2.5GB. The ColdStore index was a little over half a megabyte, which is about 3000 times smaller than the optimized MyISAM index.

You don't get this kind of size reduction by packing the data more carefully. It is due to a completely different index design conception.

Read more...

The application for which the ColdStore Storage Engine was developed has some special requirements, but requirements that are by no means unique.

The application is essentially trivial. It stores data (such as Sales or Stock levels) for a number of Products on a number of Days, and it retrieves these data for analysis. The data arrives in real time (eg daily). The analysis is typically by product. The appication could be as simple as:

INSERT INTO BIGTABLE (product,date,sales) .....

SELECT product,date,sales FROM BIGTABLE where product in (X) ORDER BY DATE

Obviously BIGTABLE is indexed on PRODUCT in order to make the SELECT run fast.

What makes the application complicated is this:

1. There are millions of products.

2. The data arrives daily, including sales for hundreds of thousands of products, for that date.

3.The analysis (and …

The application for which the ColdStore Storage Engine was developed has some special requirements, but requirements that are by no means unique.

The application is essentially trivial. It stores data (such as Sales or Stock levels) for a number of Products on a number of Days, and it retrieves these data for analysis. The data arrives in real time (eg daily). The analysis is typically by product. The appication could be as simple as:

INSERT INTO BIGTABLE (product,date,sales) .....

SELECT product,date,sales FROM BIGTABLE where product in (X) ORDER BY DATE

Obviously BIGTABLE is indexed on PRODUCT in order to make the SELECT run fast.

What makes the application complicated is this:

1. There are millions of products.

2. The data arrives daily, including sales for hundreds of thousands of products, for that date.

3.The analysis (and …

One purpose of MySQL OPTIMIZE operation is to remove dead space from a Table. ColdStore's design causes a large amount of dead space to be left in the table in order to main INSERT performance. Therefore is may be necessary to OPTIMIZE the ColdStore table to recover this disk space. ColdStore's index build performance makes this a much faster operation. (About 45 times faster).

Read more...

One purpose of MySQL OPTIMIZE operation is to remove dead space from a Table. ColdStore's design causes a large amount of dead space to be left in the table in order to main INSERT performance. Therefore is may be necessary to OPTIMIZE the ColdStore table to recover this disk space. ColdStore's index build performance makes this a much faster operation. (About 45 times faster).

Read more...

The simple use of MyISAM is simply an INSERT: The INSERT adds to the end of the table and also inserts keys into the index. Appending to the data file should be a linear operation. Inserting into the index typically declines in speed logarithmically. The INSERT for the first few tens of thousands of rows ran at about 200 thousands rows per second, but the rate rapidly dropped to 30,000 rows per second by the time the table was 100K rows, and to 20K rows/sec at 4M rows, declining logarithmically thereafter to about 15K at 150M rows. It should have taken approximately 1 day to build a billion row database at this rate. However MySQL seemed to hang at about 150M rows, for some reason we have yet to determine.

The optimized MyISAM table can be created by ALTER TABLE ... ORDER BY. This is a relative quick operation for small tables, but for a 150M row table takes a couple of weeks for an unsorted table (that is to say the …

SELECT runs approximately 400 times faster using the ColdStore engine than the straightforward use of MyISAM, and about 20 to 30  times faster than an optimized MyISAM table.

Read more...