Hoe can I reduce my Avergae Disk Queue length  when using MySQL

Posted on 2008-11-20
Last Modified: 2012-06-27

I have found th eproblem of my slow server. It seems my Average Disk Queue Length peaks at 37! Apparently it should be no more than 2!
All that is on that disk is the MySql datafile.
What I have done is made a copt of a 500MB MyIsam table and converted it to Innodb, which is now about 1 Gig.
When I run my site against the innodb table, I get very high Disk I/O to the datafile.
I have plenty of physical memory left and the cpu is not overloaded, its just the disk access thats killing my server.
I have tried changing all the parameters but cant seem to get the disk accees down.
Any Ideas what I should be changing?

Im only running less than 100 connections.
Question by:jellydeal
    LVL 25

    Expert Comment

    Lost any indexes while converting or they need maintenance?  Sorry but can't help you technicaly for MySql
    LVL 1

    Accepted Solution

    How many harddrives do you have? If the perfmon reports 37 and you have 10 hard drives in your diskarray then your diskqueue lenght is only 3,7 ( 37 divided by number of harddrives )

    -Make sure you are not doing any table scans
    -Make sure you are only returning as much data you need
    -Make sure you are using as narrow columns as you can
    -Make sure you have correct indexes
    -Make sure your indexes are not fragmented
    -Make sure your indexes have high selectivity ( less then 10% )


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now