Production

Hi,

Are you running both servers on the identical data?
Which MySQL version are you comparing with?
Did you run ANALYZE TABLE on both servers?
Could you please attach the output of SHOW VARIABLES, please?

I am not getting the same plans, but of course it heavily depends on the actual data. Would you be able to upload the data dump to our ftp (ftp.askmonty.org/private)?
If not, please paste the output of SHOW INDEX IN c and SHOW TABLE STATUS LIKE 'c'.

Thanks.

Yes, almost identical as both servers are slaves of the same master.
Percona 5.5 and 5.6
Not sure about ANALYZE TABLE - shall try out soon.

shall attach or ftp "show variables" result soon.

Can not upload data - it is a few hundred GB. also it is not allowed by the company.

show table status like 'c' (same in both in MariaDB 10.0.11 and Percona 5.6):
Name: c
Engine: InnoDB
Version: 10
Row_format: Compressed
Rows: 834114705
Avg_row_length: 80
Data_length: 67365765120
Max_data_length: 0
Index_length: 157280591872
Data_free: 2621440
Auto_increment: 810232004
Create_time: 2014-04-24 19:04:00
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=COMPRESSED key_block_size=8
Comment:

MariaDB 10.0.11 variables

Pecona 5.6 variables

there are 24 indexes in the table and index_type all BTREE

"ANALYZE TABLE" is not allowed as they are production servers

Very true about: query plan depends on data - has MariaDB any learning capability (heuristic) please? E.g. when it tried index and see it is too big, try range then. Percona took less than 1 seconds to execute this query. MariaDB took 2 hours and the query was timed out (no result).

has MariaDB any learning capability (heuristic) please?

No, the current versions don't have it. We have the task MDEV-350 for self-tuning optimizer, I suppose it's close to what you are looking for. It is currently targeted for the next GA (10.1).

there are 24 indexes in the table

How can there be 24 indexes? You quoted SHOW CREATE TABLE before, there are 3 indexes with 4 columns total participating in them. Is your production table different from the description above?

It's rather unfortunate that you never run ANALYZE TABLE... It could happen that optimizer on MariaDB instance was given very bad estimates for the InnoDB table, and thus chooses a bad plan.

Yes, I simplied the table structure for you guys to replicate the issue.

It actually has 24 indexes.

But did you actually see the same problem on the simplified table – both different plans and essentially different execution time?
If you did, please provide the data above (SHOW INDEX, SHOW TABLE STATUS) from this simplified table.
If you ddin't, please provide SHOW CREATE TABLE, EXPLAIN, SHOW INDEX and SHOW TABLE STATUS from the original table.

In other words, we need consistent data, everything from the same table, without assumptions that it will be the same on the simplified one:

• information about execution time;
• EXPLAIN
• SHOW CREATE TABLE
• SHOW TABLE STATUS
• SHOW INDEX
  for both servers in question.

Thanks!

Emailed you details.

Thanks

Any update please?

1). if no much data in table c
2). if you narrow down datetime say for 2 days (04-07 to 04-09)
3). if you give hint to use index (qid_2)

MariaDB has the same query type as Percona 5.6

Hope this assists a little bit more.

BTW, does the query optimizer have some sort of heuristic and can learn?

Currently not, neither MariaDB's, nor MySQL's.

Analysis:

So, the query plans are:

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

MariaDB:

1       SIMPLE         c     index   qid_2           PRIMARY      4       NULL  1488   Using where

MySQL plan:                                                      

1       SIMPLE         c     range   qid             PRIMARY      12      NULL  1488   Using where

Both are using PRIMARY key. It is defined as

  PRIMARY KEY (`cid`)

so using this key allows to satisfy the "ORDER BY cid LIMIT 300" by reading the first 300 rows. I'm wondering why does MariaDB use "index" when "range" access is possible. I'll investigate now.

On a second thought, I don't understand how MySQL could use "range" access over the PRIMARY key. The WHERE clause has no conditions on the `cid` column, it is not possible to construct a range access on PRIMARY key.

1). if no much data in table c
2). if you narrow down datetime say for 2 days (04-07 to 04-09)
3). if you give hint to use index (qid_2)

MariaDB has the same query type as Percona 5.6

This seems like something with cost calculations in test_if_skip_sort_order(). I am not sure what exactly this is (I can't repeat on a dataset I've generated). It would be nice to have a dataset taht demonstrates the problem.

Experiences from working with the dataset:

Right after loading the data, I was getting the same query plan on both MySQL 5.6 and MariaDB:

MariaDB [j1]> EXPLAIN

    -> SELECT

    ->    c.`cid` AS `cid`

    -> FROM c

    -> WHERE

    ->    c.result IN ('Answered','TPT') AND

    ->    c.`datetime` BETWEEN '2014-05-01 00:00:00' AND '2014-05-29 23:59:59'

    ->    AND c.qid = 42685

    -> ORDER BY cid LIMIT 300;

+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

|    1 | SIMPLE      | c     | index | qid_2         | PRIMARY | 4       | NULL | 26488 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

MySQL [j1]> EXPLAIN SELECT    c.`cid` AS `cid` FROM c WHERE    c.result IN ('Answered','TPT') AND    c.`datetime` BETWEEN '2014-05-01 00:00:00' AND '2014-05-29 23:59:59'    AND c.qid = 42685 ORDER BY cid LIMIT 300;

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

|  1 | SIMPLE      | c     | index | qid_2         | PRIMARY | 4       | NULL | 27690 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

Execution time depends a lot on whether it runs on "hot" or "cold" buffer pool. It is around 0.03 sec on my laptop. Increments for relevant Handler_xxx counters:

MariaDB [j1]> show status like 'Handler%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| Handler_read_first         | 1      |

| Handler_read_next          | 100039 |

+----------------------------+--------+

MySQL [j1]> show status like 'Handler%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| Handler_read_first         | 1      |

| Handler_read_key           | 1      |

| Handler_read_next          | 100039 |

+----------------------------+--------+

Trying to see what other plans are possible. Adding IGNORE INDEX(PRIMARY) gets this:

MariaDB [j1]> explain SELECT    c.`cid` AS `cid` FROM c ignore index (PRIMARY) WHERE    c.result IN ('Answered','TPT') AND    c.`datetime` BETWEEN '2014-05-01 00:00:00' AND '2014-05-29 23:59:59'    AND c.qid = 42685 ORDER BY cid LIMIT 300;

+------+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra                                              |

+------+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------+

|    1 | SIMPLE      | c     | range | qid_2         | qid_2 | 12      | NULL | 109746 | Using index condition; Using where; Using filesort |

+------+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------+

The query produces these increments:

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Handler_icp_attempts       | 58308 |

| Handler_icp_match          | 58308 |

| Handler_read_key           | 1     |

| Handler_read_next          | 58308 |

+----------------------------+-------+

and runs in around 0.09 sec (Hot buffer pool) on my laptop.

On MySQL 5.6, IGNORE INDEX(PRIMARY) has a similar effect:

MySQL [j1]> explain  SELECT    c.`cid` AS `cid` FROM c ignore index(PRIMARY) WHERE    c.result IN ('Answered','TPT') AND    c.`datetime` BETWEEN '2014-05-01 00:00:00' AND '2014-05-29 23:59:59'    AND c.qid = 42685 ORDER BY cid LIMIT 300;

+----+-------------+-------+-------+---------------+-------+---------+------+--------+---------------------------------------------------------------+

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra                                                         |

+----+-------------+-------+-------+---------------+-------+---------+------+--------+---------------------------------------------------------------+

|  1 | SIMPLE      | c     | range | qid_2         | qid_2 | 9       | NULL | 111460 | Using index condition; Using where; Using MRR; Using filesort |

+----+-------------+-------+-------+---------------+-------+---------+------+--------+---------------------------------------------------------------+

| Handler_read_key           | 58309 |

| Handler_read_next          | 58308 |

| Handler_read_rnd           | 58308 |

Debugging how it is optimized.
1. join optimizer picks a range access on key#1.
2. test_if_skip_sort_order() compares #rows from range access with index scan over PRIMARY key. It finds primary key to be cheaper and chooses that.

index statistics is not involved in the cost calculations (that is, ANALYZE TABLE, innodb_stats_persistent, etc - all these won't affect this).

I have to conclude I'm unable to repeat.

James, could you try with the test dataset - is it possible to repeatably get the query to run slow (i.e. it runs slow every time, not just the first time you run it)? If yes, could you please provide

1. Ouput of EXPLAIN that is run when the query ran slow.
2. Run FLUSH STATUS; <query> /this should run slow/ ; SHOW STATUS LIKE 'Handler%'; and then provide the output?
3. EXPLAIN for the query with "c ignore index (PRIMARY)"

?

This issue is indeed hard to replicate.

If I choose less days in datetime, I had
EXPLAIN SELECT c.`cid` AS `cid` FROM c WHERE c.result IN ('Answered','TPT') AND c.`datetime` BETWEEN '2014-04-07 00:00:00' AND '2014-04-22 23:59:59' AND c.qid = 42685 ORDER BY c.cid LIMIT 300;
----------------------------------------------------------------------------------------------------------------------------+

----------------------------------------------------------------------------------------------------------------------------+

----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As far as why Percona also had index query type, it was perhaps because that the data set is not large enough. If there are 800M records in the table, Percona query type is always range (while MariaDB uses index)

deleted in-correct comments - 10.0.12 does not fix my issue yet.

I have still not managed to replicated this issue yet either.

It only happens when the index file becomes huge.

So far, I tried upto 30 millions records in the table but failed to replicate the issue. The live databases has 800 million records

Hi Sergei,

I have just updated the table structure.

Is it possible for you for generate some pseudo data (please use c/c++ - quickest way in looping about 3 times quicker than java and 20 times quicker than php) and fill the table with hundred millions of records so as to replicate this issue please?

I can not send you the table as it contain company real commercial data. Also, the table is a few GB and I have no means to transfer the file to you.

Thanks a lot in advance
James

Hi Sergei,

We downloaded the 10.0.12 from yum.mariadb.org last Friday. Our issue seams gone. Great.

Shall keep you informed.

Thanks

Please close this ticket