| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 3.23.52, 4.0.12 | OS: | Linux (Linux/Redhat 8.0) |
| Assigned to: | CPU Architecture: | Any | |
[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)
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.