Mysql optimize disk I/O

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.
Who is Participating?
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:
Derek JensenCommented:
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. :)
M256Author Commented:
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.
Derek JensenCommented:
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. ;-)
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.