MySQL Bugs: #38695: Index usage with Maria

Bug #38695	Index usage with Maria
Submitted:	9 Aug 2008 22:32	Modified:	10 Aug 2008 6:10
Reporter:	Ricardo Sal	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Maria storage engine	Severity:	S5 (Performance)
Version:	latest	OS:	Windows (xp)
Assigned to:		CPU Architecture:	Any
Tags:	Maria index performance

Description:
In a table with 2,8M rows, the usage of more indexes that what we need seems to add performance penalty.

I use maria engine because the add and drop of indexes is very fast, also the disk space usage is lower then for other engines.

But when using i big number of indexes in a table, the performance in the 1º query is very poor (24 times slower).
After that query, the rest is fast (possibly because it gets cached).

How to repeat:
DROP TABLE IF EXISTS `pa_dumps`.`p_dumps`;
CREATE TABLE  `pa_dumps`.`p_dumps` (
  `x` int(3) NOT NULL DEFAULT '0',
  `y` int(3) NOT NULL DEFAULT '0',
  `z` int(3) NOT NULL DEFAULT '0',
  `name_id` int(5) NOT NULL DEFAULT '0',
  `size` int(6) NOT NULL DEFAULT '0',
  `score` int(10) NOT NULL DEFAULT '0',
  `value` int(10) NOT NULL DEFAULT '0',
  `xp` int(10) NOT NULL DEFAULT '0',
  `size_increase` int(5) NOT NULL DEFAULT '0',
  `value_increase` int(7) NOT NULL DEFAULT '0',
  `xp_increase` int(6) NOT NULL DEFAULT '0',
  `score_increase` int(7) NOT NULL DEFAULT '0',
  `tick` int(5) NOT NULL DEFAULT '0',
  KEY `tick` (`tick`),
  KEY `x` (`x`),
  KEY `y` (`y`),
  KEY `z` (`z`),
  KEY `name_id` (`name_id`)
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 ROW_FORMAT=FIXED;

In this table add indexes to xp, value and score and you will see a tremendous performance decrease on the 1º query made randomly filtered by x, y, z, and tick.

Suggested fix:
Make it work with same performance as the other engines

The problem is that as you add more index, the b-tree will be more spread out on disk and there will be more seeks when reading the index to the page cache.
This is the nature of b-trees and all engines that uses b-trees should have similar problems.

Possible solutions:

- Run OPTIMIZE TABLE once in a while. This will sort the index file in access order
- Set up bigger page cache so that the needed pages fits into memory
  (This may not your problem as the second query is faster, but I thought I
would mention it anyway)
- Use LOAD INDEX command to load the index into the page cache. This uses sequentail reading of the index file and should thus be fast.

It's of course also possible that your problem is a bug, but in that case we would need a test case to be able to repeat your problem.

When we are ready with Maria 2.0 (December?) we will start doing extensive benchmarks to see what things we need to improve.  I will ensure that we then will also do test with many index to test index degradation

As a side note, we will soon fix in Maria (probably in 2.0) that add index and drop index only generate/drop the new index. This will make creating/dropping index significantly faster..