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).
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).
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.
Accumulating book industry sales information presents a difficult
technical challenge: database tables with billions of rows of
data, that are added added to many times per day, and which is
typically searched for time sequenced data such as a lie-cycle
analysis of a set of books.
"ColdStore" is software written by ColdLogic to specifically
address this challenge - although it is applicable to a wide
range of similar business data handling problems.
For the operations that matter to ColdLogic's
analysis, ColdStore performs orders of magnitude
faster than the best available general purpose solution.