Environment:
- Windows IIS6
- MySQL 5.0.22-community-max-nt
- perl 5.6 (I think)
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):
- 22 minutes for category monthly
- at least 45 minutes for entries (and it's only half done)
Builds that have been tolerable:
- 161 seconds for category archives
- 73 seconds for index templates
- 16 seconds for page archives (only a handful of pages -- under 10)
I've looked at the phpmyadmin system variables for MySQL and they are consistent with large numbers of table scans due to missing keys 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, I don't have access to any raw MySQL logs. 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 PHP code to record SQL queries & time to execute
...except I don't know what perl code executes the SQL queries
- add indexes to MT tables in MySQL
- add foreign keys to MT tables in MySQL
Any suggestions for how I might proceed here? Or implementation details for the solutions I have described?
Thanks in advance,
Hugh
Full Version: Upgrade To Mt 4.21 Causes Long Times For Refreshes
I'm pretty sure support for Perl 5.6 is deprecated. I'm not aware that any specific problems are caused with 5.6, but still.
You say you tried to republish with a new style--is this a CSS only change, or were the templates actually changed? If the templates were changed, try reverting to the previous version to compare build times. If it builds ok, your new templates have some inefficiencies; if not, we need to look elsewhere.
You say you tried to republish with a new style--is this a CSS only change, or were the templates actually changed? If the templates were changed, try reverting to the previous version to compare build times. If it builds ok, your new templates have some inefficiencies; if not, we need to look elsewhere.
QUOTE (danwolfgang @ Oct 30 2008, 08:19 AM) 
I'm pretty sure support for Perl 5.6 is deprecated. I'm not aware that any specific problems are caused with 5.6, but still.
You say you tried to republish with a new style--is this a CSS only change, or were the templates actually changed? If the templates were changed, try reverting to the previous version to compare build times. If it builds ok, your new templates have some inefficiencies; if not, we need to look elsewhere.
You say you tried to republish with a new style--is this a CSS only change, or were the templates actually changed? If the templates were changed, try reverting to the previous version to compare build times. If it builds ok, your new templates have some inefficiencies; if not, we need to look elsewhere.
I was changing from Minimalist Red to Cityscape NYC. I don't know whether that is just CSS or more extensive. I'm a bit of an amateur on that point.
What do you make of the evidence that the database is running queries with essentially no indexes used? I assume that all style templates use the database in the same way with the same queries, so a lack of joins on indexes would be the result of moving to a new version of MT, with the queries embedded in the Perl.
See http://www.iwebthereforeiam.com/2008/10/mo...cation-t-1.html for a complete statement on this.
QUOTE (hughdbrown @ Oct 30 2008, 09:12 AM)
I was changing from Minimalist Red to Cityscape NYC. I don't know whether that is just CSS or more extensive. I'm a bit of an amateur on that point.
That should only be changing the CSS file. So, actually, I don't think there's any reason you should need to rebuild the whole site. (Yes, I realize that doesn't solve the problem.)
QUOTE
What do you make of the evidence that the database is running queries with essentially no indexes used? I assume that all style templates use the database in the same way with the same queries, so a lack of joins on indexes would be the result of moving to a new version of MT, with the queries embedded in the Perl.
See http://www.iwebthereforeiam.com/2008/10/mo...cation-t-1.html for a complete statement on this.
See http://www.iwebthereforeiam.com/2008/10/mo...cation-t-1.html for a complete statement on this.
If there are no indexes on your DB, something is definitely wrong. There were some indexes with MT3, many more with MT4. Botched upgrade? Also, on that link you supplied, PHP isn't being executed to render a complete page.
QUOTE (danwolfgang @ Oct 30 2008, 10:53 AM)
QUOTE (hughdbrown @ Oct 30 2008, 09:12 AM)
I was changing from Minimalist Red to Cityscape NYC. I don't know whether that is just CSS or more extensive. I'm a bit of an amateur on that point.
That should only be changing the CSS file. So, actually, I don't think there's any reason you should need to rebuild the whole site. (Yes, I realize that doesn't solve the problem.)
QUOTE
What do you make of the evidence that the database is running queries with essentially no indexes used? I assume that all style templates use the database in the same way with the same queries, so a lack of joins on indexes would be the result of moving to a new version of MT, with the queries embedded in the Perl.
See http://www.iwebthereforeiam.com/2008/10/mo...cation-t-1.html for a complete statement on this.
See http://www.iwebthereforeiam.com/2008/10/mo...cation-t-1.html for a complete statement on this.
If there are no indexes on your DB, something is definitely wrong. There were some indexes with MT3, many more with MT4. Botched upgrade? Also, on that link you supplied, PHP isn't being executed to render a complete page.
Okay, changing the styling does not require a rebuild; it's done dynamically. Still, I've broken the page generations. I went through these steps to fix the page generation:
// Check that theme is set
1. Confirm contents of /styles.css
/* This is the StyleCatcher theme addition. Do not remove this block. */
@import url(/mt-static/themes-base/blog.css);
@import url(/mt-static/support/themes/minimalist-red/minimalist-red.css);
/* end StyleCatcher imports */
@import url(/mt-static/css/prettify.css);
2. Confirm that http://iwebthereforeiam.com/mt-static/themes-base/blog.css is not 404.
3. Confirm that http://iwebthereforeiam.com/mt-static/supp...imalist-red.css is not 404.
// Small test of page generation
1. Manage|Entries page
- select first 10 entries
- press Publish
2. View Site button
- result: no styling
- page starts without <html>, <head>, <body>, any css includes
// Page generation is broken. Roll back template caching options.
1. Design|Templates
- caching is on Header, Footer, Entry Detail, Page Detail, Categories, Sidebar - 2 Column Layout, Sidebar - 3 Column Layout (Process as PHP Include, Expire after 30 minutes)
- turn off PHP Include on all; Save
2. Manage|Entries page
- select first 10 entries
- press Publish
3. View Site button
- result: has styling
- page has <html>, <head>, <body>, css includes
// Page generation is fixed again. Try large scale regeneration.
Publish Site button
- Publish all files (start at 12:30 EST)
Your botched upgrade comment is mostly apt: I tried to turn on varieties of caching to speed up page regeneration without fully understanding the implications.
So, while full site regeneration continues for the next month, I'll comment on this:
QUOTE
If there are no indexes on your DB, something is definitely wrong. There were some indexes with MT3, many more with MT4.
It's not that there are no indexes on the DB; it's that the (some) queries do not appear to use the indexes. Judging by the MySQL variables, a large number of queries are table scans.
The problem is the PHP include, not the caching options, because your HTML pages aren't parsing the PHP. If you did a View|Source on a rendered page you would see the unexecuted PHP include. So, MT was working the way it is supposed to. It's your server configuration that isn't prepped for the option you chose.
You can still use template module caching and see benefit to it. Some detail: http://www.movabletype.org/documentation/a...le-caching.html
This doesn't really deal with the core problem of publishing speed, though. I would be really surprised if 4.1 published faster for you, but I gather that's the experience you've had? I don't have enough SQL knowledge to explain the queries. What host are you using?
You can still use template module caching and see benefit to it. Some detail: http://www.movabletype.org/documentation/a...le-caching.html
This doesn't really deal with the core problem of publishing speed, though. I would be really surprised if 4.1 published faster for you, but I gather that's the experience you've had? I don't have enough SQL knowledge to explain the queries. What host are you using?
QUOTE (danwolfgang @ Oct 30 2008, 02:09 PM)
The problem is the PHP include, not the caching options, because your HTML pages aren't parsing the PHP. If you did a View|Source on a rendered page you would see the unexecuted PHP include. So, MT was working the way it is supposed to. It's your server configuration that isn't prepped for the option you chose.
You can still use template module caching and see benefit to it. Some detail: http://www.movabletype.org/documentation/a...le-caching.html
This doesn't really deal with the core problem of publishing speed, though. I would be really surprised if 4.1 published faster for you, but I gather that's the experience you've had? I don't have enough SQL knowledge to explain the queries. What host are you using?
You can still use template module caching and see benefit to it. Some detail: http://www.movabletype.org/documentation/a...le-caching.html
This doesn't really deal with the core problem of publishing speed, though. I would be really surprised if 4.1 published faster for you, but I gather that's the experience you've had? I don't have enough SQL knowledge to explain the queries. What host are you using?
Yes, I turned off all the PHP include optimizations when I realized that this was being done on static HTML pages. :-0
I've also turned off the caching because it seems that it calculates the text for the template once and substitutes from then on. Any variables in the template are calculated only once. This is practically useless. I ended up with lots of identical pages. I thought it was an optimization to keep the templates in memory instead of going to the database each time.
Anyway, I've had it run to completion. It's not pretty.
[img]
http://www.iwebthereforeiam.com/images/Mov...-2008-10-30.png
[/img]
My hosting site is webhosting4life.com. They want me to send them my user name and password for my website so they can poke at it at will. I want them to look at the database statistics and to contact me if they want me to load it with a website refresh. The MySQL server is actually shared so it may well not even be my website that is the problem. I think they should check the log of slow queries. I think that would indicate the problem quite quickly.
QUOTE (hughdbrown @ Oct 30 2008, 09:22 PM)
I've also turned off the caching because it seems that it calculates the text for the template once and substitutes from then on. Any variables in the template are calculated only once. This is practically useless. I ended up with lots of identical pages. I thought it was an optimization to keep the templates in memory instead of going to the database each time.
Yes and no: the trick then just becomes figuring out which template modules would benefit from caching. The Entry Summary or Comments aren't good choices because--as you said--identical cached content would be published on every page. On the other hand, the HTML Head or Banner Footer are probably good candidates for caching because they do not change with every page.
QUOTE (danwolfgang @ Oct 31 2008, 08:48 AM)
QUOTE (hughdbrown @ Oct 30 2008, 09:22 PM)
I've also turned off the caching because it seems that it calculates the text for the template once and substitutes from then on. Any variables in the template are calculated only once. This is practically useless. I ended up with lots of identical pages. I thought it was an optimization to keep the templates in memory instead of going to the database each time.
Yes and no: the trick then just becomes figuring out which template modules would benefit from caching. The Entry Summary or Comments aren't good choices because--as you said--identical cached content would be published on every page. On the other hand, the HTML Head or Banner Footer are probably good candidates for caching because they do not change with every page.
Okay, I've done the song and dance with my internet hosting provider to no avail.
I'd like a reality check: can someone tell me how long it takes to publish their complete website and how many entries they have? Mine is 1400 entries and 251 minutes. I can't help thinking this is pretty slow.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.