Re: Solving table locking issues
Posted by:
Rick James
Date: February 14, 2009 02:08AM
Sorry, I am going to harp on summary tables. But first some other notes...
InnoDB does not do table locking, so it _might_ perform better.
Have you tuned key_buffer_size? Is the total size of your indexes less than that? How much RAM do you have?
BDB -- not likely. It's mostly key-value; your schema is more complicated.
The JOINs and subqueries may (or may not) be aggravating the situation. Can we see them?
Summary table do not have to be a burden. They can be augmented on demand. They can contain only data up to the last, say, hour; then you reach into the raw data to get the current info. Etc. Need more details before I can recommend specifics.
Usually a few summary tables can handle many reports. What are your "dimensions"? Usually time is one dimension, and it is usually the first part of the primary key of a summary table, but it is truncated to the hour (or day). That key also contains perhaps a couple more dimensions.
Often I have one summary table by hour, and a _different_ one by day. (There is no advantage in doing both hour and day for the same set of dimensions -- you can always get daily (or weekly or monthly) totals from an hourly table -- it will be much faster than hitting the raw data, and it won't be fighting with the UPDATE of the raw table.
Remember that an average of averages is NOT mathematically correct. So, summary tables often have COUNT(*) AS ct, SUM(foo) AS sum_foo. Then the average is SUM(sum_foo) / SUM(ct).
Sure, duplicating data is a no-no. But only until you hit performance problems. Your summary tables are totally duplicate data. Furthermore, they probably have reached into the JOIN tables and pre-evaluated subqueries to get the fields.
In one project, the "fact" table has about 500M rows. There are several web-based reports (PHP), each with several selects. Two summary tables are the main source for the reports. Rarely does a page (with multiple selects) take more than 3 _seconds_. The fact table is mostly "normalized", but the summary tables are mostly not. A couple of times I have had to rebuild a summary table (due to screwup or design change) -- costly, but possible.
Summary tables tend to be narrower, have fewer rows, and are better indexed than the fact table.
Pushing reports off to the summary tables can, in the extreme case, leave no index on the fact table other than an auto_increment primary key. Guess what? This makes INSERT run faster -- no secondary indexes to fiddle with.