Mysql optimize disk I/O

Posted on 2012-08-22
Last Modified: 2012-08-26
I have a Ubuntu server with 8GB memory and it's using less than 2GB.
I have a PHP app that takes 16 seconds to load a page.
I have a large database.

I noticed that if I did a database dump the disk IO is very high.

I changed query_cache_size = 916777216

That should be 1GB of memory for MySQL.

I did a database dump again and it read it all from the disk.

There is still only less than 2GB of memory used.

I think the key to optimizing this app is to cache most queries in memory. What's the next step.
Question by:M256
    LVL 24

    Accepted Solution

    Unless most of your data is static, a very big query cache will be underutilized.  Note that when a table is updated cached queries that involve that table are rendered invalid.  I think you should tune your database so that it will cache indexes and actual tables.  I always point people to the following links:
    LVL 9

    Expert Comment

    by:Derek Jensen
    Indeed; a DB dump will, by design, read from the HDD. And besides, you don't want to store your entire DB in memory anyway.

    While increasing your SQL cache size will probably help(a little), it will not help the speed a page loads nearly as much as indexing your tables properly.

    I'd recommend you stop with the DB dumping, and instead look at how long any particular query that your page is making takes to run all by itself, and if need be, how many calls your page is making to your DB.

    I worked on a report once that, due to horrific design, was making over 50,000 queries each time it was run! No wonder it took over 10 minutes to load...

    I'm not saying that's what your site is doing, but if any one query takes longer, on average, than about 0.5 seconds to run, it can almost always be optimized, by either query structure, or indexing changes. As a rule of thumb, I try to get all my queries to run in under 0.05 seconds. This is usually quite easy, and the above links are a great starting point. :)
    LVL 5

    Author Comment

    So the page takes 16 seconds to load. How do I find out which queries are taking a long time on the MySQL side? I'm looking for a log file of sorts.
    LVL 9

    Expert Comment

    by:Derek Jensen
    I wouldn't know anything about a log file, unless you set one up yourself; just pick the first query in the page, run it on your MySQL DB IDE, see how long it takes, and if it doesn't take very long(<0.25s), move on to the next one.

    ...If you're talking about the moodle webserver taking 16s to load, then just jump straight to indexing. ;-)
    LVL 24

    Expert Comment

    You can use the slow query log see Note that by default it logs queries taking longer than 10 seconds, which is too long.  I set it to 2 seconds, then after fixing all queries that are logged, I set it to 1 for further tuning.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    10 Experts available now in Live!

    Get 1:1 Help Now