473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Erratic performance for a specific query in MySQL

I have two job queues doing complimentary work and writing their output
once a minute into a file. The files for each minute have to processed
in pairs - one from the first job queue and one from the second and the
results are merged and delivered to the final client. The filename and
other details of these files go in to a MySQL database - a different
table for each job queue.
The merging process has to read the two files for that minute from the
two tables joining on a file id. Here is MySQL schema I am using:

CREATE TABLE `Job_Queue_1` (
`FileID` varchar(64) NOT NULL default '',
`File_Path` text NOT NULL,
`File_TimeStamp` bigint(12) NOT NULL default '0',
`Process_Status` char(1) NOT NULL default '',
PRIMARY KEY (`FileID`),
KEY `Process_Status` (`Process_Status`,`File_TimeStamp`)
) ENGINE=InnoDB
CREATE TABLE `Job_Queue_2` (
`FileID` varchar(64) NOT NULL default '',
`File_Path` text NOT NULL,
`File_TimeStamp` bigint(12) NOT NULL default '0',
`Process_Status` char(1) NOT NULL default '',
PRIMARY KEY (`FileID`),
KEY `Process_Status` (`Process_Status`,`File_TimeStamp`)
) ENGINE=InnoDB

File_TimeStamp is an integer representing the time as YYYYMMDDhhmm at a
minute granularity. Process_Status is 'N' for a new file and 'F' for
that which has already been merged with its pair. FileID is the join
condition.

The query used by the application to pick up a pair of files is:

select a.FileID, a.File_Path, b.FileID, b.File_Path
from Job_Queue_1 a, Job_Queue_2 b
where a.FileID = b.FileID
and a.Process_Status = 'N' and b.Process_Status = 'N'
order by a.File_TimeStamp limit 1 ;

This runs very well for upto two million rows in each table. But I was
doing some stress testing with 4 million rows in each table, 2.5 with
Process_Status as 'F' and 1.5 with Process_Status as 'N'. The query was
hitting the database almost 50 times each second. I noticed that for 5
minutes the query would be very fast - about 20 milliseconds per query
and the explain statement on MySQL showed "Using where". And randomly
for 5 minutes the same query would drastically degrade in performance
and take about 30 seconds and the explain statement would show "Using
where, using temporary, using filesort". And then it would go back to
being fast.

Any clue of what might be happening here? The server is running on
RHEL3 with dual AMD CPUs and and the innodb_buffer_pool_size is 1.8 GB.

Thanks in advance.

Apr 23 '06 #1
0 1177

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: sandy | last post by:
Hi All, I am a newbie to MySQL and Python. At the first place, I would like to know what are the general performance issues (if any) of using MySQL with Python. By performance, I wanted to...
3
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where...
0
by: Rajesh Kapur | last post by:
I have a master slave configuration on linux machines running MySQL 4.0.21. Once every hour, a process deletes about 9000 rows and re-inserts fresh data on the master. The master process completes...
11
by: Dave [Hawk-Systems] | last post by:
have the table "numbercheck" Attribute | Type | Modifier -----------+------------+---------- svcnumber | integer | not null svcqual | varchar(9) | svcequip | char(1) | svctroub ...
57
by: Bing Wu | last post by:
Hi all, I am running a database containing large datasets: frames: 20 thousand rows, coordinates: 170 million row. The database has been implemented with: IBM DB2 v8.1
0
by: ZMan | last post by:
Scenario: This is about debugging server side scripts that make calls to middle-tier business DLLs. The server side scripts are legacy ASP 3.0 pages, and the DLLs are managed DLLs...
1
by: murray | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
0
by: OctoTools | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
0
by: jllanten | last post by:
I will appreciate any help you can provide me. In the company where i work we have a project which creates about 4-5M records daily of stats. We're currently storing this data in a db named...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.