Now, I have no problem but as things get busy I might need to build the pages. I have a question: is it possible to replicate everything so no functionality is lost when building? I mean as far as navigating, lower case categories, full-name-link-id urls, submiting links, images etc.. The new comments, clicks etc I realize will be updated once the html is built.
Granted my script is modified a bit but http takes 10-20% of the cpu when I load wsn (I have tried dozens of times with the top command running so I know it's this.) Vbulletin barely 1-2% The server load does not exist but the homepage takes 1-2 seconds to draw a brand new server:
Due to foreign langauge categories I cannot use cache but I have eaccelator turned on. has anyone tried it? This is somewhat slower that I hoped it woudl be, but I love the features so I have to work something out. I know that all those features take a toll...
Apparently I have the same query for EACH cat and subcat:
Query 123: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`foreignlanguage` FROM wsnlinks_categories WHERE id=46
and this is taking the most time of all--almost 50% of the time.
Query 28: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`shqip` FROM wsnlinks_categories WHERE parent=0 AND validated=1 AND hide=0 AND isalbum=0 AND deleted=0 ORDER BY priority ASC,name ASC LIMIT 0,40
How many top level categories do you have? 40-70 queries is what I'm used to seeing in 4.0.x.
Building an HTML version is no different from using the cache, except that it's more complicated and requires messing up your base directory if you want to integrate non-HTML pages, so I don't see how you can say that you can use one and not the other. If you have multiple languages for people to select from then generating HTML is only going to generate one language.
Do you have a remote MySQL server? Those tend to need persistent connections for good performance .
Hi Paul, I have 105 total cat+subcats, and I have them all displayed on the front page for user friendly and seo purposes. I have a local db, on the same server. Page generation:
124 total queries This page was created in 1,11 seconds Memory used: 1136952 bytes Server Status: time since last reboot is 10 days, 5:56, load average: 0.02, 0.05, 0.06
this is on a server with NO traffic to the directory. The other traffic is very lite on mySQL /php so it's almost non-existent. I disabled the member feature too.
the thing is that I doubt I will ever have more than 500-1000 links, but I am not sure how the server will react to 100's of people asking for the /directory/ when it takes a while with just one person
Putting 105 categories on one page will always become problematic, and turning off other switches isn't going to make any significant difference since most of the time is being spent on parsing those categories. It's also surely the antithesis of user friendliness... what person can find what they're looking for from an unorganized list of 105 categories? I've never met anyone who's willing to even read a list of 105 items on a website, let alone use that as their navigation to find the links they want.
Actually you can have all 105 on the front page, but only if they're organized as subcategories. If you had 20 main categories and the other 85 as subcategories of those, they'd still all be shown on the front page, but in an organized way that would be possible for a human user to navigate, and with much better performance.
Paul, they are categorized and listed under the respective category as subcats. Given that main categories are broad and can be ambiguous, listing the subcats solves that problem.
The thing is that I have at least two other scripts--one custom made and one off the shelf--that call a LOT more than a cat name and still load in a flash. On the custom one, creating a few indexes solved the being too slow problem, I mean the difference was stagering especially since it takes about 10 fields for 100+ products and it sorts them in the fly too. The other one, a yellow pages script, already loads in a flash with no problems.
Looking at my db's though webmin, it appears that most of my other scripts have a lot of indexes--some have almost an equal number of indexes and tables. Would it be worth trying such a thing for me? Or is there a way to bundle the mySQL calls in fewer ones, like getting them all at once instead of calling them row by row? Of course my knowledge is from reading here and from previous probelms with scripts so if I am not making any sense feel free to say so :-). I just love the script and have a feeling that it can be fixed to load extremely fast.
Please give the answer to my previous question then: how many top level categories do you have? If you only have a few then something else must be causing the 123 queries.
Hi Paul, sorry I misunderstood the question: I have 12 main categories. With them and subcats it ends up being 105 or so. Playing with the main template I noticed that taking out the subcats, the quieries drop to 36 and exec time is 0.54 seconds. The language option plays no role, as the english alone or with <OTHERWISE IF {VISITORLANGUAGE} is xxxxxxx>{CATxxxx}</IF></a> takes the same time. ------this is taking 28 seconds out of 41 total without the subcats--- Query 28: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`foreignlan` FROM wsnlinks_categories WHERE id=8
Note for readers: the queries were mostly caused by the subcategories area in main.tpl not using {CATURL} for the link, and thus having to rewrite on the fly since category name url rewriting was on.
0/5
1
2
3
4
5
This thread is closed, so you cannot post a reply.
Comments on 123 total queries on front page--Speed
Member
Usergroup: Customer
Joined: May 13, 2007
Total Topics: 16
Total Comments: 37
Posted Aug 17, 2007 - 7:36 PM:
Now, I have no problem but as things get busy I might need to build the pages. I have a question: is it possible to replicate everything so no functionality is lost when building? I mean as far as navigating, lower case categories, full-name-link-id urls, submiting links, images etc.. The new comments, clicks etc I realize will be updated once the html is built.
Granted my script is modified a bit but http takes 10-20% of the cpu when I load wsn (I have tried dozens of times with the top command running so I know it's this.) Vbulletin barely 1-2% The server load does not exist but the homepage takes 1-2 seconds to draw a brand new server:
Dual Processor Dual Core Intel Woodcrest 5130
4 Total 2.00 GHz Cores
Dual 1333 MHz Front-side Bus (FSB)
4 MB Shared L2 Cache
2 GB 667 MHz FB-DIMM Memory (Fully Buffered)
Due to foreign langauge categories I cannot use cache but I have eaccelator turned on.
has anyone tried it? This is somewhat slower that I hoped it woudl be, but I love the features so I have to work something out. I know that all those features take a toll...
Member
Usergroup: Customer
Joined: May 13, 2007
Total Topics: 16
Total Comments: 37
Apparently I have the same query for EACH cat and subcat:
Query 123: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`foreignlanguage` FROM wsnlinks_categories WHERE id=46
and this is taking the most time of all--almost 50% of the time.
Query 28: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`shqip` FROM wsnlinks_categories WHERE parent=0 AND validated=1 AND hide=0 AND isalbum=0 AND deleted=0 ORDER BY priority ASC,name ASC LIMIT 0,40
I guess I need to disable some stuff
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
How many top level categories do you have? 40-70 queries is what I'm used to seeing in 4.0.x.
Building an HTML version is no different from using the cache, except that it's more complicated and requires messing up your base directory if you want to integrate non-HTML pages, so I don't see how you can say that you can use one and not the other. If you have multiple languages for people to select from then generating HTML is only going to generate one language.
Do you have a remote MySQL server? Those tend to need persistent connections for good performance .
What's your normal page generation time?
Make sure you've read scripts.webmastersite.net/w...nally-large-sites-189.html
4.1 should improve speed / reduce load when complete.
Member
Usergroup: Customer
Joined: May 13, 2007
Total Topics: 16
Total Comments: 37
Hi Paul,
I have 105 total cat+subcats, and I have them all displayed on the front page for user friendly and seo purposes. I have a local db, on the same server.
Page generation:
124 total queries
This page was created in 1,11 seconds
Memory used: 1136952 bytes
Server Status: time since last reboot is 10 days, 5:56, load average: 0.02, 0.05, 0.06
this is on a server with NO traffic to the directory. The other traffic is very lite on mySQL /php so it's almost non-existent. I disabled the member feature too.
the thing is that I doubt I will ever have more than 500-1000 links, but I am not sure how the server will react to 100's of people asking for the /directory/ when it takes a while with just one person
thanks,
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
Putting 105 categories on one page will always become problematic, and turning off other switches isn't going to make any significant difference since most of the time is being spent on parsing those categories. It's also surely the antithesis of user friendliness... what person can find what they're looking for from an unorganized list of 105 categories? I've never met anyone who's willing to even read a list of 105 items on a website, let alone use that as their navigation to find the links they want.
Actually you can have all 105 on the front page, but only if they're organized as subcategories. If you had 20 main categories and the other 85 as subcategories of those, they'd still all be shown on the front page, but in an organized way that would be possible for a human user to navigate, and with much better performance.
Member
Usergroup: Customer
Joined: May 13, 2007
Total Topics: 16
Total Comments: 37
Paul, they are categorized and listed under the respective category as subcats. Given that main categories are broad and can be ambiguous, listing the subcats solves that problem.
The thing is that I have at least two other scripts--one custom made and one off the shelf--that call a LOT more than a cat name and still load in a flash. On the custom one, creating a few indexes solved the being too slow problem, I mean the difference was stagering especially since it takes about 10 fields for 100+ products and it sorts them in the fly too. The other one, a yellow pages script, already loads in a flash with no problems.
Looking at my db's though webmin, it appears that most of my other scripts have a lot of indexes--some have almost an equal number of indexes and tables. Would it be worth trying such a thing for me? Or is there a way to bundle the mySQL calls in fewer ones, like getting them all at once instead of calling them row by row? Of course my knowledge is from reading here and from previous probelms with scripts so if I am not making any sense feel free to say so :-). I just love the script and have a feeling that it can be fixed to load extremely fast.
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
Please give the answer to my previous question then: how many top level categories do you have? If you only have a few then something else must be causing the 123 queries.
My http://links.webmastersite.net/?debug=5 shows 28 queries, though the 4.0 series would give it more like 40.
Queries aren't always the main cause of slowing anyway though. And without seeing your site it's impossible to guess.
Member
Usergroup: Customer
Joined: May 13, 2007
Total Topics: 16
Total Comments: 37
Hi Paul,
sorry I misunderstood the question: I have 12 main categories. With them and subcats it ends up being 105 or so. Playing with the main template I noticed that taking out the subcats, the quieries drop to 36 and exec time is 0.54 seconds. The language option plays no role, as the english alone or with <OTHERWISE IF {VISITORLANGUAGE} is xxxxxxx>{CATxxxx}</IF></a> takes the same time.
------this is taking 28 seconds out of 41 total without the subcats---
Query 28: SELECT `id`,`name`,`parent`,`validated`,`description`,`time`,`parentnames`,`parentids`,`numlinks`,`hide`,`lastlinktime`,`custom`,`lastedit`,`moderators`,`headerinfo`,`related`,`numsub`,`type`,`permissions`,`subscribers`,`subscriber`,`mixtypes`,`isalbum`,`orderlinks`,`totalcomments`,`ulsearchmax`,`ulsearchquery`,`ulsearchadult`,`alias`,`catcols`,`linkcols`,`dmozpath`,`sponsorlinks`,`views`,`customwrap`,`deleted`,`reqlinks`,`reqcoms`,`reqlinksdelay`,`priority`,`levelsdeep`,`lastposterid`,`lastpostername`,`lastcommenttime`,`lastthreadid`,`lastthreadtitle`,`linkshere`,`unvalidatedlinks`,`unvalidatedcomments`,`actaslink`,`countposts`,`subcatshtml`,`linksperpage`,`wysihtml`,`numonline`,`foreignlan` FROM wsnlinks_categories WHERE id=8
I will send the link via the support area
developer
Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California
Total Topics: 61
Total Comments: 7868
Note for readers: the queries were mostly caused by the subcategories area in main.tpl not using {CATURL} for the link, and thus having to rewrite on the fly since category name url rewriting was on.