Announcement

Announcement Module
Collapse
No announcement yet.

Performance Tuning for InnoDB?

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

  • Performance Tuning for InnoDB?

    Dear Forum-Users,

    first: Sorry for my poor english I'll give my best that you all understand my questions

    I have a debian 5.0 System with percona 5.5 as deb-package.
    Before i had mysql 5.5. i have switched to the percona server, because i have a big database. this contains a table with 5 mio. rows in innoDB. When i do a select with sort and while on this table, it take about 5-10 seconds to show me the result.

    For the innoDB buffer pool size i have granted 17.000 MB.
    The data-files of the sqlserver are on a fast SSD-Drive.

    What can i do to get a faster query? The indexes are correct.

    Thank you for help

    Angelo

  • #2
    You will need to be more specific. Include the exact query, the time it takes to run, SHOW CREATE TABLE, and EXPLAIN.

    Comment


    • #3
      An example Query is:
      SELECT id, von, an, betreff, datum, flags, priority, size, color FROM bm60_mails WHERE userid = '3' AND folder='0';

      It takes in phpMyAdmin 1,21 seconds.

      But it can take sometimes up to 10 seconds.

      An Explain gives:
      1 SIMPLE bm60_mails index_merge oquery,mailUser,mailFolder mailUser,oquery 4,4 NULL 4487 Using intersect(mailUser,oquery); Using where

      What do you mean with SHOW CREATE TABLE?

      Comment


      • #4
        Paste the output of this query:

        SHOW CREATE TABLE bm60_mails;

        I can already tell you that there is an indexing problem, though.

        Comment


        • #5
          thanks for your reply.

          Here you are:

          bm60_mails CREATE TABLE `bm60_mails` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `betreff` varchar(255) NOT NULL,
          `von` varchar(255) NOT NULL,
          `an` varchar(255) NOT NULL,
          `cc` varchar(255) NOT NULL,
          `body` longtext NOT NULL,
          `folder` int(11) NOT NULL,
          `datum` int(11) NOT NULL DEFAULT '0',
          `trashstamp` int(11) NOT NULL,
          `priority` enum('low','normal','high') NOT NULL,
          `fetched` int(11) NOT NULL DEFAULT '0',
          `msg_id` varchar(128) NOT NULL,
          `virnam` varchar(128) NOT NULL,
          `trained` tinyint(4) NOT NULL DEFAULT '0',
          `refs` text NOT NULL,
          `flags` int(11) NOT NULL DEFAULT '-1',
          `userid` int(11) NOT NULL DEFAULT '0',
          `size` int(11) NOT NULL,
          `color` tinyint(4) NOT NULL DEFAULT '0',
          PRIMARY KEY (`id`),
          KEY `oquery` (`folder`),
          KEY `mailUser` (`userid`),
          KEY `mailFlags` (`flags`),
          KEY `mailFolder` (`folder`)
          ) ENGINE=InnoDB AUTO_INCREMENT=12832593 DEFAULT CHARSET=latin1

          Comment


          • #6
            1.
            When you have several columns in the WHERE clause that are bound together by AND the optimal index is to create a composite index with these columns part of the index.
            So try this index:

            ALTER TABLE bm60_mails ADD INDEX bm_ix_userid_folder(userid, folder);


            Which (if you think it works well) also means that you can drop the current mailuser(userid) index since this new composite supersedes it since the userid column is the first column in this new index.

            2.
            BTW, the userid and the folder columns are both INT's which means that you should not have quotes around the numeric values 3 and 0 in the WHERE clause:

            SELECT id, von, an, betreff, datum, flags, priority, size, color FROM bm60_mails WHERE userid = '3' AND folder='0';

            In this case it wouldn't directly matter. But in other queries when you don't keep track of if the column is of numeric or string type the implicit conversion will result in a bad execution plan and poor performance even though you have the correct index in place.

            Comment


            • #7
              Are you the Godfather of MySQL?

              You have reduzed the complete Processing of Loading the mentioned website from 3 to 10 seconds to 0,1360 Seconds

              I'm searching such a year for this! thank You very much! Can i buy you a beer?

              Comment


              • #8
                Using intersect(mailUser,oquery) is a pretty clear indication of the need for a compound index, as sterin wrote. There might be other opportunities for (possibly significant) further improvement too: a covering index, and examining whether you are using the right data types, for example.

                Comment

                Working...
                X