Bug #617 Queries mysteriously seem to stop using an Index / slow performance
Submitted: 6 Jun 2003 18:27 Modified: 9 Jun 2003 5:41
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23.52, 4.0.12 OS:Linux (Linux/Redhat 8.0)
Assigned to: CPU Architecture:Any

[6 Jun 2003 18:27] [ name withheld ]
Description:
This problem has occurred (at least) 3 times over the last 6 months. It has happened twice with 3.23.52 and at least once with 4.0.12. What will happen is when querying a table, performance becomes seriously slowed down and doesn't recover. But, stopping and starting the MySQL server resolves the issue. I don't think that this is being caused by a lack of system resources, etc., and I don't reboot the server. I don't restart the httpd, either. The table that I'm querying has about 3 million rows and 46 columns. It's physical .MYD file size is about 500MB and the .MYI file size is about 220MB; including several key fields one of which ia a primary key and is set autoincrement.

I am performing queries like: "select * from my_table where my_table_primary_id in ('1', '2', '3')" ....The "in" lists include at most 15 elements and normally this query runs fast as lightning. When the problem starts occurring, it takes about 30 seconds or so to run--about the amount of time to sequentially search the table. These queries are used for accessing data to build web pages, and the site is crippled (http://rod.brunsco.net) when this occurs.

The server is a dual cpu tyan AMD2000MP box with 1GB RAM and 400GB RAID 5 drive array. I'm using the ext3 filesystem, fully patched RedHat8, and custom compiled  2.4.19 kernel. I compiled the MySQL server from source (as I always have). I'm using PHP 4.3.1 (and custom PHP extensions), apache 1.3.26, and the libmysqlclient version 10.0.0 (from mysql distrobution version 3.23.52). All compiled from source under RedHat 8.

Any Ideas? --Thanks!

How to repeat:
unknown at this time.

Suggested fix:
unknown at this time.
[9 Jun 2003 5:41] Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

What you describe looks more like case for MySQL and Server tunning and application optimization.

If you can provide repeatable test case we can treat it as a bug, otherwise we don't have much choice, but to rule it out of Bugs Database.

No matter if it is bug or not please try to repeat it with MySQL from our binary distribution - RPM as you are with RedHat.
[29 Sep 2010 2:26] Jason Garrett
I've seen this issue roughly 4 times this year, often over 4-8 weeks between occurances.  Each time, the solution is to stop and start the Windows Service.

I've upgraded the MySQL Server twice to see if it resolved the issue, but it has reoccured since, the most recent was today.

I am using 5.1.48-community-log, running on Windows Server 2k3 Advanced.

The query will run correctly, and return in 2-3 seconds typically.
At some seemingly random time which appears to be related to the uptime and use of the server, we get a Support call from a user saying that the report is failing to complete.  When we check the query plan (using EXPLAIN), it appears to not be using the index correctly.  We see that a possible key exists, but it is not used, thus making the query very slow!  An snippet example of an EXPLAIN is below.  Note the 'key' is empty, and 'rows' is very large, being the number of rows in the table.

           id: 1
  select_type: SIMPLE
        table: dockets1
         type: eq_ref
possible_keys: PRIMARY
          key: 
      key_len: 4
          ref: slim.archivedlogs1.docketID
         rows: 1626160
        Extra: Using where

Below shows the normal EXPLAIN for the query, as it is for weeks prior to the event, and immediately after the MySQL service is restarted.

Looks like this issue is more serious than just poor query writing.

explain SELECT   `voyagelots1`.`branchID`,   `voyages1`.`voyageCode`,
IF(`voyagelotting1`.`lot` IS NOT NULL, `voyagelotting1`.`lot`, `voyageloadouts1`.`lot`) AS lot,
IF(`voyagelotting1`.`lot` IS NOT NULL, `voyageloadouts1`.`lot`, NULL) AS containerNumber,
`voyagelots1`.`dischargePort`,   `voyagelots1`.`consignee`,   `voyageloadouts1`.`hatch`,
`voyageloadouts1`.`place`,   `dockets1`.`docketNumber`,   `dockets1`.`forestSource`,
`dockets1`.`forestCompartment`,   `dockets1`.`forestStand`,   `dockets1`.`loggingOrganisation`,
`voyagelots1`.`comments`,   `voyagelots1`.`commentsOperator`,   `voyagelots1`.`operatorRef1`,
`voyagelots1`.`operatorRef2`,   `specifications1`.`species`,   `specifications1`.`grade`,
`specifications1`.`_length`,   sum(`archivedlogs1`.`volume`) AS `totalVolume`,
sum(`archivedlogs1`.`weight`) AS `totalWeight`,   count(*) AS `totalCount`
FROM     (       (         (           (             (
`slim`.`archivedlogs` `archivedlogs1`
INNER JOIN `slim`.`specifications` `specifications1`
ON `archivedlogs1`.`specificationID`=`specifications1`.`specificationID`             )
INNER JOIN `slim`.`dockets` `dockets1`
ON `archivedlogs1`.`docketID`=`dockets1`.`docketID`           )
INNER JOIN `slim`.`voyageloadouts` `voyageloadouts1`
ON `archivedlogs1`.`voyageLoadOutID`=`voyageloadouts1`.`voyageLoadOutID`         )
INNER JOIN `slim`.`voyagelots` `voyagelots1`
ON (`voyageloadouts1`.`voyageID`=`voyagelots1`.`voyageID`)
AND (`voyageloadouts1`.`branchID`=`voyagelots1`.`branchID`)
AND (`voyageloadouts1`.`lot`=`voyagelots1`.`lot`)       )
INNER JOIN `slim`.`voyages` `voyages1`
ON `voyagelots1`.`voyageID`=`voyages1`.`voyageID`     )
LEFT OUTER JOIN     (       `slim`.`voyagelotting` `voyagelotting1`
INNER JOIN `slim`.`voyagelotcontainersassign` `voyagelotcontainersassign1`
ON `voyagelotting1`.`voyageLottingID`=`voyagelotcontainersassign1`.`voyageLottingID`     )
ON `voyagelots1`.`voyageLotID`=`voyagelotcontainersassign1`.`voyageLotID`
WHERE  `voyagelots1`.`branchID`=4 AND `voyages1`.`voyageCode`='10316'
AND `specifications1`.`supplierCode`='RNZ'
GROUP BY branchID, voyageCode, lot, species, grade, containerNumber, docketNumber, _length, hatch, place\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: voyages1
         type: const
possible_keys: PRIMARY,idx_voyage_voyageCode
          key: idx_voyage_voyageCode
      key_len: 53
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: voyagelots1
         type: ref
possible_keys: idx_voyageLots_loadOut
          key: idx_voyageLots_loadOut
      key_len: 8
          ref: const,const
         rows: 22
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: voyageloadouts1
         type: ref
possible_keys: PRIMARY,idx_voyageLoadOuts_loadOut
          key: idx_voyageLoadOuts_loadOut
      key_len: 40
          ref: const,const,slim.voyagelots1.lot
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: voyagelotting1
         type: index
possible_keys: PRIMARY
          key: idx_voyageLotting_lot
      key_len: 41
          ref: NULL
         rows: 373
        Extra: Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: voyagelotcontainersassign1
         type: ref
possible_keys: idx_ids
          key: idx_ids
      key_len: 10
          ref: slim.voyagelotting1.voyageLottingID,slim.voyagelots1.voyageLotID
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: archivedlogs1
         type: ref
possible_keys: idx_archivedlogs_voyageLoadOutID,idx_archivedlogs_docketID,idx_specificationid
          key: idx_archivedlogs_voyageLoadOutID
      key_len: 5
          ref: slim.voyageloadouts1.voyageLoadOutID
         rows: 663
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: dockets1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: slim.archivedlogs1.docketID
         rows: 1
        Extra: Using where
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: specifications1
         type: eq_ref
possible_keys: PRIMARY,idx_specifications_specification
          key: PRIMARY
      key_len: 4
          ref: slim.archivedlogs1.specificationID
         rows: 1
        Extra: Using where
8 rows in set (0.00 sec)