| Database Optimization | 2004-09-20 23:28 3 comments |
 by Flemming FunchDon'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. |