Query optimisation engine MySQL and Percona

[scarybarry]

Hi everyone,

I’m having a predicament in crossing over to Percona. As a new user of Percona we encounter the following problem.

We have an OLD server which is running MySQL 5.0.67-0.dotdeb.1-log.
And we have a NEW server which is running MySQL server 5.5.30-30.1-log Percona Server (GPL), Release 30.1.

We are running the following query on both machines:
SELECT SQL_NO_CACHE ags.status_id FROM ast_stats_agentstatus AS ags force index (status_id) WHERE ags.agent_id = “10963” ORDER BY ags.id DESC LIMIT 1;

Of course, this is a test query, hence the SQL_NO_CACHE.

Performance indicator time that has taken for this query is.
OLD: 0.01 sec
NEW: 0.36 sec

After some more digging with the explain query showed us the following

OLD:
±—±------------±------±-----±--------------±-------- ±--------±------±-----±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-------- ±--------±------±-----±----------------------------+
| 1 | SIMPLE | ags | ref | uniqkey | uniqkey | 4 | const | 1583 | Using where; Using filesort |
±—±------------±------±-----±--------------±-------- ±--------±------±-----±----------------------------+

NEW:
±—±------------±------±------±--------------±------- -±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±------- -±--------±-----±-----±------------+
| 1 | SIMPLE | ags | index | uniqkey | PRIMARY | 4 | NULL | 1408 | Using where |
±—±------------±------±------±--------------±------- -±--------±-----±-----±------------+

This shows us that for the key on OLD the uniqkey is used, and on the NEW setup the PRIMARY key is used.

When forcing the query to use the uniqkey the problem is solved. So using the following query on the new machine results in a query time of 0.01 sec:
SELECT SQL_NO_CACHE ags.status_id FROM ast_stats_agentstatus AS ags force index (uniqkey) WHERE ags.agent_id = “10963” ORDER BY ags.id DESC LIMIT 1;

This leads me to presume that the query optimizer is less effecient that the OLD version.

It is not a option to change all the queries in the application code. Does someone know a different solution for this problem?

I’m also going to investigate what the outcome is going to be when I’m using the latest MySQL Server.

[gmouse]

Could you show output of SHOW CREATE TABLE?

Is the result consistent after running ANALYZE TABLE?

[scarybarry]

Hi gmouse,

mysql> show create table ast_stats_agentstatus\G
*************************** 1. row ***************************
Table: ast_stats_agentstatus
Create Table: CREATE TABLE ast_stats_agentstatus (
id int(11) NOT NULL AUTO_INCREMENT,
agent_id int( NOT NULL DEFAULT ‘0’,
status_id tinyint(4) NOT NULL DEFAULT ‘0’,
cluster_id tinyint(4) NOT NULL DEFAULT ‘0’,
createdat datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
duration int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘This field is set to amount of time in status, after going into new status’,
guid varchar(32) NOT NULL COMMENT ‘guid for this statuschange’,
PRIMARY KEY (id),
KEY uniqkey (agent_id,status_id,createdat,cluster_id),
KEY duration (duration),
KEY guid (guid)
) ENGINE=MyISAM AUTO_INCREMENT=6495282 DEFAULT CHARSET=latin1

The Analyze and optimize didn’t have any affect.

[scarybarry]

Hi Gmouse,

Thanks for your reply. Unfortunately the queries are coming from a code base of a customer of ours. These type of queries seem to be used a lot, and changing all of these in the given period of time is not an option for this customer. I did check the MySQL database engine of Oracle for comparison. There the query does use a “using filesort”. I’m not saying that this is the correct way, but it is the way for my customer for now to keep things running fast. This is sad, this will likely move our customer to stick with MySQL instead of Percona. We will be missing out on some very nice features of Percona and the migration to the fast inno db engine of Percona will be harder in the future.

My customer gave me some additional information about the query. When they changed limit 1 to limit 2 the query does uses uses the “using filesort”. Is there an explanation for this behavior. Sorry for the following suggestion, but is this a bug in the query optimizer?

[jynus]

@scarybarry;9707

Hi Gmouse, Thanks for your reply. Unfortunately the queries are coming from a code base of a customer of ours. These type of queries seem to be used a lot, and changing all of these in the given period of time is not an option for this customer. I did check the MySQL database engine of Oracle for comparison. There the query does use a “using filesort”. I’m not saying that this is the correct way, but it is the way for my customer for now to keep things running fast. This is sad, this will likely move our customer to stick with MySQL instead of Percona. We will be missing out on some very nice features of Percona and the migration to the fast inno db engine of Percona will be harder in the future.

Please, read Gmouse comment properly, he is suggesting (correctly) that you should change your indexes, not your queries. “Using filesort” is, in general, something to be avoided -although not a problem in this particular query. You can do that just creating the index (modifying the table structure).

Having said that, you must notice that your particular problem has nothing to do with query optimization, but with your query: you are using the “” characters when comparing with an integer column): that makes the index usage on the comparison sometimes impossible to use and/or confuses the optimizer.

Also please note that, unlike other forks, there are no significative differences between the same versions of Percona and MySQL at SQL/Optimizer level. If you have different query plans/execution time, you could have the same issues when reinstalling MySQL (different statistics, different data distribution, buffer pool/key cache contents, query cache, configuration changes, and specially different versions, different SQL modes, etc.). Check my results in Percona when using the right key (even without changing the query, with the quotes):

mysql> SELECT count(*) FROM ast_stats_agentstatus;
+----------+
| count(*) |
+----------+
| 13107200 |
+----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE ast_stats_agentstatus ADD INDEX (agent_id, id);
Query OK, 13108600 rows affected (1 min 29.35 sec)
Records: 13108600 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_NO_CACHE ags.status_id FROM ast_stats_agentstatus AS ags WHERE ags.agent_id = "10963" ORDER BY ags.id DESC LIMIT 1;
+-----------+
| status_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE ags.status_id FROM ast_stats_agentstatus AS ags WHERE ags.agent_id = "10963" ORDER BY ags.id DESC LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ags
type: ref
possible_keys: agent_id,uniqkey
key: agent_id
key_len: 4
ref: const
rows: 5292
Extra: Using where
1 row in set (0.00 sec)

Of course, I am not suggesting that you should migrate blindly, but the reasons may be wrong in this case.

[gmouse]

Having said that, you must notice that your particular problem has nothing to do with query optimization, but with your query: you are using the “” characters when comparing with an integer column): that makes the index usage on the comparison sometimes impossible to use and/or confuses the optimizer.

Good point, but that is only when comparing a text column with a number. See type conversions:

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If [I]str_col[/I] is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

I totally agree with the remainder of your post, a migration to vanilla MySQL will not help.

[scarybarry]

Thanks for your replies. We have made a extra index on agent_id in combination with id as suggested. This definitely helped. Is it because the index values are now in the query data and we now can sort the data using only this data because of the combined key? So it doesn’t have to run down the 6.5 milion records for the index (primairy key)?

[gmouse]

The reason is that the returned row can be found using a binary tree rather than via a linked list.

See
[url]http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html[/url]
and
[url]http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html[/url]