We will be holding our highly acclaimed MySQL workshops in Frankfurt, Germany the week of February 13th. Early registration is open; enroll today and secure your seat. Detail and enrollment can be found here.

Many of you have seen the announcement for our Percona Live in Washington, DC in January! But did you know we also have our highly rated MySQL Training coming to Washington, DC the week of January 16h? Full details can be found on the Percona website. If you would like to attend the MySQL Training, click here to register and receive a 20% discount.

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual:

1. Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS)
2. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed

…

There are many ways of improving response times for users. There are some people that spend a lot of time, energy and money on trying to have the application respond as fast as possible at the time when the users made the request.

Those people may miss out on an opportunity to do some or all of the processing the application needs to do at a different point in time. In other words, if you preprocess your data ahead of time, you can reduce the time it takes to complete a request.

Allow me to give you three examples of what I mean:

1)  There is a sales report that your managers would like to see on their fancy new dashboards. The query for this report takes 45 minutes to run and may disrupt other functions that the database server needs to do. You decide to run this report at 3am when there is very little happening on the database server and save the results to a separate table. When the dashboard …

Released on June 8, 2011 (Downloads are available here and from the Percona Software Repositories.

Percona Server 5.1.57-12.8 is now the current stable release in the 5.1 series. It is is based on MySQL 5.1.57.

Bug Fixes

• Fixed InnoDB I/O code so that the interrupted system calls are restarted if they are interrupted by a signal. InnoDB I/O code was not fully conforming to the standard on POSIX systems, causing a crash with an assertion failure when receiving a signal on pwrite(). Bug Fixed: LaunchPad: #764395 / MySQL bug #60788 (A. …

One of item I always look at SHOW ENGINE INNODB STATUS to see if there are any transactions spending very long time in ACTIVE state. In the perfect world if you’re running online system you should not see transactions spending more than couple of seconds in ACTIVE state. Especially ACTIVE transactions which do not currently run any query are suspicious. There are however cases when screaming fire about ACTIVE transactions alone would be misleading. There is a whole set of applications which run quite fine while having ACTIVE measured in hours. It is JAVA applications which often run in AUTOCOMMIT=0 mode and do not explicitly commit transactions unless there were any writes. If database is configured in READ-COMMITTED transaction mode it is actually fine from performance point of view as Innodb does not have to preserve row versions going back to start of transactions.

There is however a better metric, which in most cases will …

http://Flexvie.ws fully implements a method for creating materialized views for MySQL data sets. The tool is for MySQL, but the methods are database agnostic. A materialized view is an analogue of software transactional memory. You can think of this as database transactional memory, or as database state distributed over time, but in an easy way to manage.

It has been shown that combinatorial algebraics can be applied to all aggregate functions (Flexviews uses only composable operations even for deletes over “non-distributable aggregate functions”), and Flexviews includes this capability today. Because Flexviews can move database …

Since it is possible to turn any “infinite” list into a finite list using weighted sets (there is no such thing as an infinite list unless you can provide me with infinite data), it is no longer possible to consider infinite lists of input into computer programs. A program without an infinite list of instructions will halt unless there is an infinite loop. Improper infinite loops can be detected within software programs using multiple testing methods. With proper error checking and therefore with proper checks, halting state prediction should be possible. If improper state is detected in an application, rollback the data state in the database to prevent corruption.

An “indeterminate” input can be expressed as a NULL in the input. It is not possible to produce or receive an infinite input. All input is read in small chunks, or all the memory in the universe would be needed, which would itself represent universal state.

Here is the problem: http://en.wikipedia.org/wiki/Partition_problem

Any weighted set can be partitioned. A weighted set can be represented in a sparse way as a space and time optimization. Also duplicates are removed, making it easy to at least partially partition any problem by hash.

For example, you can distribute the subset sum problem over multiple machines when you reduce the set to unique values and hash on the value (weighted set). This allows you to distribute checks, reducing complexity.

You can partition the problem into even and odd items, the absolute value of the items, etc, and add many simple checks which can be tested during insertion instead of after a huge list has been created inside the database. Using distributed computation of combinatorial algebra (sum) you can aggregate the data with your load, allowing you to answer questions on the data while it is loaded. The check will fire as soon as an …

Often times, from a computing perspective, one must run a function on a large amount of input. Often times, the same function must be run on many pieces of input, and this is a very expensive process unless the work can be done in parallel.

Shard-Query introduces set based processing, which on the surface appears to be similar to other technologies on the market today. However, the scaling features of Shard-Query are just a side effect of the fact that it operates on sets in parallel. Any set can be operated on to any arbitrary degree of parallelism up to, and including, the cardinality of the set.
Given that:

1. It is often possible to arbitrarily transform one type of expression into a different, but compatible type for computational purposes as long as the conversion is bidirectional
2. An range operation over a set of integers or dates can be transformed into one or more discrete sub-ranges
3. Any …