[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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.
0
M256
Asked:
M256
  • 2
  • 2
1 Solution
 
johanntagleCommented:
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:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
http://blog.codesherpas.com/on_the_path/2011/03/tuning-mysql.html
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
0
 
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. :)
0
 
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.
0
 
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. ;-)
0
 
johanntagleCommented:
You can use the slow query log see http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html. 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.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now