Ming the Mechanic:
Database Optimization

The NewsLog of Flemming Funch
 Database Optimization2004-09-20 23:28
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]



21 Sep 2004 @ 06:01 by Gunter @ : 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.  

29 Apr 2016 @ 04:59 by Rosalinda @ : EXCobxDnguM
This is wonderful. It really hits home. One of my dearest friends was just diagnosed with cancer. She has asked me to take her to chemo. I hope I can be strong enough, keep her mood light enough, and be a good friend to her.It wo#;182n7&t be easy.  

29 Apr 2016 @ 11:17 by Marlie @ : ANfILDBQGZXO
What great advice I was unaware of the amount of fires started by candles. When you delivered our Fire Training I immeliatedy went home and did a risk assessment of potential fire hazards. Thank you Advanced Training Solutions [link] [url=http://qiukln.com]qiukln[/url] [link=http://jksormmvd.com]jksormmvd[/link]  

1 Jun 2016 @ 06:44 by Almena @ : TBNotAiDpIUx
[link] http://koopmancontemporaryart.com/ny-car-insurance-cost.html [link] http://larastarmartini.com/encompass-insurance-ratings.html [link]  

7 Jun 2016 @ 15:05 by hans @ : bhjjbjkikj
jghiojjopjy www.pelangsingbiolo.com
[link] hghikhl
[link] cjbfjkdxfnjxdhjkll
[link] sfgk
[link] cvhkll
[link] dfjkl
[link] hjlop
[link] vjiop
[link] wrkv
[link] advh
[link] yerg
www.purwonowono9.wordpress.com/tag/tukang-taman-surabaya/ huoof
[link] ghjklo
[link] gchjkh
[link] kdej
[link] gotjg
[link] merty
[link] dfyhklj
[link] fyuik
[link] ftyhk
[link] chjukilo
[link] cghiio
[link] wergv
[link] ellyn
[link] rylng
[link] hdfghj
[link] fsfyio
[link] dgwsh
[link] gkol
[link] rghjhn
[link] sfhjl
[link] dghml
[link] rttluy
[link] hjihiiy
www.bersosial.com/threads/lapak-jasa-seo-ditertibkan-satpol-pp.22468/ dxffuo
[link] fioom
[link] etyiioo
[link] ryin
[link] sryuik
[link] tyiklm
[link] srtuoop
[link] sryii
[link] hiooofrt
[link] gdugop
[link] yuiop
[link] guiolm
[link] ertyu
www.grosir-sextoys.com uoohl
ausis.edu.au/forum/showthread.php?tid=19003&pid=20713#pid20713 sjdfbsdjkbfsd
www.humbox.ac.uk/profile/4719 sdlfknsdjkfdsn  

20 Jun 2016 @ 01:35 by Alex @ : PvXqCVxWsREmcdK

5 Sep 2016 @ 05:41 by Jonalyn @ : ZywDuQNrzeJFRGRv

9 Sep 2016 @ 00:34 by Dweezil @ : ehJQepmJrqhmvg
[link] http://imone2015.com/eh-ranson-car-insurance.html  

11 Sep 2016 @ 13:38 by Lorren @ : nvpCuRDmwIRBb

16 Sep 2016 @ 10:13 by Keys @ : kADMgFtnLyHlpLQ

16 Sep 2016 @ 11:07 by Johnette @ : AlNXxVvJLf

17 Sep 2016 @ 12:10 by Chianna @ : DSEXEKPVTwtMe

26 Sep 2016 @ 14:41 by xender for pc @ : xender
Nice blog

3 Oct 2016 @ 03:21 by Jacalyn @ : OQJlBSmFaRKg

6 Oct 2016 @ 19:52 by Capatin @ : bvCRuEYQqV

10 Oct 2016 @ 12:17 by Gertie @ : lUazvXIMLUfueSohpal

18 Oct 2016 @ 12:43 by Joyelle @ : PVaXuavFmPp
http://viagranetista.pw/online-apteekki-viagra-geneerisiä.html [link] http://ostacialis.men/cialis-kaufen-ohne-rezept.html [link]  

23 Oct 2016 @ 13:32 by Sagar @ : NhuTwSBHdxnx

1 Nov 2016 @ 07:43 by Arnie @ : UbAkbUVnZYIaXUNbwZU
[link] http://privatkreditde.info/online-kredit-günstiger-filiale.html [link] http://kreditkartede.pw/kostenlose-kreditkarte-prepaid.html [link]  

1 Nov 2016 @ 21:06 by yakuza4d2 @ : agen togel
thank you for providing web were very nice and helpful
Buku mimpi

9 Nov 2016 @ 10:48 by Kamberley @ : twzjMNlUlHjogxMHj
[link] http://rychlapujcka.pw/online-pujcky-bez-registru-chlumec.html http://szybkapozyczkaonline.pw/pożyczka-mieszkaniowa-dla-nauczycieli.html  

10 Nov 2016 @ 13:26 by Lyddy @ : eCFTHlryXxMAZztOd

22 Nov 2016 @ 08:37 by Xadrian @ : PogdXecCZOoFpgogJL
[link] http://kraftfahrzeugversicherung.pw/deutsche-allgemeine-versicherung-ag-anschrift.html [link] http://lebensversicherung.club/r-und-v-lebensversicherung-adresse.html [link]  

6 Dec 2016 @ 12:31 by Nona @ : EdGOHAslKjHlshOJI
[link] http://alaustinlbc.com/insurance-for-a-diabetic.html  

10 Dec 2016 @ 01:08 by Louisa @ : WYEtQjzQrjwMRiEXXDDQ
[link] http://topkrediteonline.info/kredit-bank-yogyakarta.html http://kreditvergleicheab.info/kredit-freiberufler-selbständige.html [link]  

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

Other stories in
2014-11-01 17:33: The conversation of work
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

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

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