Well a blog post after so many months 🙂 I was bit busy finishing off some pending stuff and working side by side. As you all know I do work for some clients on an ongoing basis and that duties comes first.

Now here is what happened somedays ago.

I was messing around with one wordpress of one client and we were having huge issues. First of all whenever new pages were added the wordpress was getting really slow.

My client and me looked into it with WP-Tuner and found that it was making like 2900 queries just to generate the homepage of the site.

Now I did put it in debug mode and did all the blah blah stuff. I don’t even remember but some ideas and leads were crazy and yet I was pursuing it.

In found out that the wp_options table has a option_name called “_transient_rewrite_rules” which stores the pages and its data as an array into the table cell called option_value

It contained exact 1 MB data in it. It was too good to be true that a cell is holding exact 1024 kilobytes of data. So after pursuing that lead finally found out that the MySQL max_allowed_packet was set at 1MB by default. So I logged into SSH and changed my.cnf to make max_allowed_packet a bit larger.

It worked like a charm.

Last week was a holiday in UK. So we were shifting the sites to brand new server with 4 processors and 4 GB RAM 😀 We thought it will be a great move. The move was smooth and went without any dramas.

Now the site was fast but just as it is the habit we couldn’t stop looking into the stuff. We found out that it was again making 2744 queries to generate the homepage. Now I even forgot what I did in past to fix this issue. All I knew was I did some setting. [Infact that is the reason I am making this blog post. So I don’t keep forgetting it. And yeah I do forget things as I do 10 new things per day 🙁 life sucks sometimes.]

So told my client to dig his chat logs and see if something was mentioned in talks that can give a hint or idea.

Which he found it after some hours of digging through chat logs. Back on max_allowed_packet.

I went in to SSH and updated the value. 🙂

But the site was still not fixed. We checked MySQL docs and stuff. No joy there too.

Banged my head for 2 days and no joy as all leads were turning out to be dead ends.

Upgraded MySQL just in case this was some other issue.

Finally today restored the Old DB and found that when we moved the site and updated a page. It was 1MB limit and the DB was updated with only first 1MB data and rest of 0.28 MB data itself was missing in that array 🙁 that is why it was making all this queries to get the rewrite rules as it was not able to get it from that array.

The site is now done and live and now only makes 11 queries per page load 🙂 Only thing is 3 days old DB is restored but I hope to clear that much mess in some hours 🙂