I upgraded to 4.21 from 4.1 in the past day. I have tried to republish my weblog with a new style, but the republish times are horrific (1400 entries across 4 years):
- ??? seconds for entry (ran for about 80 minutes before stalling on the completion screen)
- 2353 seconds for author monthly
- 1335 seconds for category monthly
- 843 seconds for monthly
- 161 seconds for category archives
- 73 seconds for index templates
- 16 seconds for page
That's 80 minutes without entries. For comparison, it used to take only 15 to 20 minutes to republish the entire site before.
I've looked at the phpmyadmin system variables for MySQL and they are consistent with large numbers of slow queries, probably caused by table scans due to missing indexes in searches:
- Slow_queries has crept up from 526k to 574k slow queries.
- Handler_read_rnd from 4641k to 5341k
- Handler_read_rnd_next from 955M to 1116M
- Select_full_join from 9373 to 10k
However, my weblog is hosted (on Windows under IIS6, no less) and I don't have access to any raw MySQL logs. I can't run MySQL from a command prompt. I have no way of telling which queries are causing the slow down. Also, I cannot upgrade the perl version.
Ideas for discovering the problem:
- instrument the perl code to record SQL queries & time to execute
...except I don't know what perl code executes the SQL queries. Plus, the code goes directly to the DBI/DBD perl module to execute SQL. It would mean patching the code in a zillion places. - add indexes to MT tables in MySQL
But indexes on which columns of which tables? - add foreign keys to MT tables in MySQL
Again: where?
Anyone with an idea is welcome to leave a comment.
Leave a comment