GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Query optimisation engine MySQL and Percona

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query optimisation engine MySQL and Percona

    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.

  • #2
    Could you show output of SHOW CREATE TABLE?

    Is the result consistent after running ANALYZE TABLE?

    Comment


    • #3
      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.

      Comment


      • #4
        I can't comment on the changes in query optimizer, except that both index choices can be good depending on the data in the table.

        Is there a good reason for the columns and their order in the index uniqkey? Your query will run better if it is an index on (agent_id,id). For changing the index without downtime, see pt-online-schema-change.

        Comment


        • #5
          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?
          Last edited by scarybarry; 04-15-2013, 06:00 AM.

          Comment


          • #6
            Originally posted by scarybarry View Post
            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):

            Code:
            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.
            Last edited by jynus; 04-15-2013, 07:25 AM. Reason: Fixing code block

            Comment


            • #7
              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 str_col 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.

              Comment


              • #8
                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)?

                Comment


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

                  See
                  http://dev.mysql.com/doc/refman/5.0/...n-indexes.html
                  and
                  http://dev.mysql.com/doc/refman/5.0/...l-indexes.html

                  Comment

                  Working...
                  X