Ming the Mechanic:
Database Optimization

The NewsLog of Flemming Funch
 Database Optimization2004-09-20 23:28
2 comments
picture by Flemming Funch

Don't know why it was exactly today, other than that it is Monday, but my MySQL server suddenly decided that there was way too much to do. Oh, of course it didn't, but sometimes things reach a certain threshold. I had been wondering why it took my blog so long to load recently, and the server started being really busy all the time. And mysql has this optional log of queries that take too long, which provided the answers I needed. On a server that is doing many things at the same time, anything that takes longer than a second is taking way, way too long, if we're talking about database queries. And now I realized that the queries used to produce the lists of recent referrers and search engine questions which show in the sidebar of some of the blogs took, like, five or ten seconds, which is horrible. Even if just one were running at a time, but there are only a few seconds between each time somebody views a blog page, so that can quickly become bad. So I had to quickly rewrite it so that it figures this out every hour, rather than in real time, and I optimized the indexes a bit.

From past experience, things are much more likely to bog down in MySQL once there's 3-4 million records in a table. Not just gradually worse, but like that things suddenly are taking orders of magnitude longer. And, well, the table that keeps track of blog pageviews has about 4 million entries, for the last four months or so. Anyway, all seems better now, and the server is humming along normally.


[< Back] [Ming the Mechanic]

Category:  

2 comments

21 Sep 2004 @ 06:01 by Gunter @24.126.121.37 : indeed
Yes, Flemming, indeed a noticable differnce. I actually had planned to bitch that it takes - especially - your page very long to load, but it was never annoying enough to actually do something about it - it's probably the story about how to boil a frog - right - kreeps up on you slowly - or - very nice example in motion - the US income tax - kreeps up on you slowly, until finally you look and say - helloho - why does half of my money not reach me?

Interesting - - - never thought that I would ever mention the IRS and MySQL in the same sentence ;-)  



21 Sep 2004 @ 10:06 by ming : Delays
I think I need to benchmark all the other different pieces that show in the blog, to see what really takes the time. It still takes a bit longer than I'd like it to. One factor is the various outside pieces that at least I have in my left sidebar. I notice when it loads that there's a significant wait for them, and that is a bit outside my control, which is annoying.

The plan is also that I'll convert it all to be formatted by CSS, but that's a significant change. Like, the middle column should really be loading first, rather than the left sidebar, which doesn't have the actual content. That's impossible when it is in tables, but with CSS it is no problem.  



Your Name:
Your URL: (or email)
Subject:       
Comment:
For verification, please type the word you see on the left:


Other stories in
2007-02-24 14:20: Writing books in HTML/CSS
2007-02-05 15:21: Software is hard
2006-11-19 21:30: Thingamy
2005-12-14 15:15: Ruby on Rails
2005-03-19 16:04: Comment and Refererrer Spam
2005-02-23 21:34: Wikipedia
2005-02-22 17:32: Mail
2005-02-10 16:00: More Google wizardry
2005-02-04 15:14: The Six Laws of the New Software
2005-02-02 18:37: Blog, Ping and Spam



[< Back] [Ming the Mechanic] [PermaLink]? 


Link to this article as: http://ming.tv/flemming2.php/__show_article/_a000010-001368.htm
Main Page: ming.tv