VW Vortex - Volkswagen Forum banner
1 - 20 of 41 Posts

·
Registered
Joined
·
2,025 Posts
Discussion Starter · #1 ·
That voting thread's got too many opinions and this topic is tangential to that one anyway.
Programmers unite! Let's solve this puppy!
By the way... DAMN zeroforum is commercial. I was going to download the basic source from their site, but lo and behold... I can't.

Based on the current voting trend, people want search back up.
If the Vortex re-enables it now without the new hardware, maybe there IS a way to optimize the search function.
A band-aid till hardware rescue is to disable the My Recent Topics link as it has been identified as the No. 1 forum speed killer. Like I mentioned before, we could just comment out the appropriate HTML so no one can hit it.
Meanwhile, we could also take a look at the DB schema and see how we can optimize the SQL code, PHP code, or even create some new tables. One person mentioned using a table as a scratchpad with the my recent topics list of a user stored in there (another user is essentially doing this with his Notepad feature).
Any other thoughts on pre-new-hardware solutions?


[Modified by paultakeda, 10:24 AM 8-6-2002]
 

·
Registered
Joined
·
8,660 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

what needs to happen to allow the user's CPU do the search work? instead of bogging down the Server with our massive searches?
 

·
Registered
Joined
·
2,859 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

quote:[HR][/HR]A band-aid till hardware rescue is to disable the My Recent Topics link as it has been identified as the No. 1 forum speed killer. Like I mentioned before, we could just comment out the appropriate HTML so no one can hit it.[HR][/HR]​
That sounds reasonable. Could be a good idea.
 

·
Registered
Joined
·
2,025 Posts
Discussion Starter · #4 ·
Re: VWVortex zeroforum optimization discussion (dragonfli_x)

quote:[HR][/HR]
what needs to happen to allow the user's CPU do the search work? instead of bogging down the Server with our massive searches?
[HR][/HR]​
That's probably impossible. My understanding is that the application server is essentially dying from too much load. When 300 users activate search, they are loaded onto a queue by the server while the db retrieves the information.
Let's break it down.
My Recent Topics provides the following querystring:
http://forums.vwvortex.com/zerosearch?cmd=recenttopics&id=USER_ID
I assume a PHP search module's recent topics subroutine/function is called. It takes one paramter, the user's id. The function retrieves a dataset using a SQL query that probably looks something like this:
"SELECT
forum_topics.forum_topic_id, forum_topics.forum_topic
FROM
forum_topics, forum_posts
WHERE
forum_topics.forum_topic_id = forum_posts. forum_topic_id
AND
forum_posts.user_id = " . USER_ID . "
AND
forum_posts.user_post_date > " . ONE_DAY_AGO . ";"
I'm just guessing since I have no zeroforum source to look at.
The dataset is returned and the PHP renders a list for the user to browse in HTML.
300 hits of this is a load on the application server. I don't think we can put it on the client without creating an application that connects directly to the Vortex database... and I seriously doubt this is an option.
We could try to optimize the search, but then we'd need to know how the db stores data and how it is retrieved and processed by the PHP code.
If My Recent Topics is the big killer, then we can concetrate on optimizing that particular SQL query string. If it's pretty well optimized, then, yes, disabling that particular function will do till the new hardware goes online.
Of course, you could still bog the server down if 300 people initiate a search at the same time, but that's probably not as likely as 300 hitting My Recent Topics.


[Modified by paultakeda, 11:31 AM 8-6-2002]
 

·
Registered
Joined
·
8,660 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

If there was a way we could get zeroforums access to the database, a client side would only have to get access to the tables one time to just look at the data, not to process... right? so the loads can be higher on the existing processor since it's the clients CPU working its own search... I don't really know database management or optimizing... just wondered if it could happen. I saw a flash app do a dynamic search on a auto parts database with minimal lag because the server wasn't being worked...
i don't know how it was done though...
 

·
Registered
Joined
·
2,025 Posts
Discussion Starter · #6 ·
Re: VWVortex zeroforum optimization discussion (dragonfli_x)

quote:[HR][/HR]
If there was a way we could get zeroforums access to the database, a client side would only have to get access to the tables one time to just look at the data, not to process... right? so the loads can be higher on the existing processor since it's the clients CPU working its own search...
[HR][/HR]​
You're essentially building zeroforums on your end with just a straight up database server on the Vortex. This is pretty much how auto parts databases work. The software is on the client computer and it has a direct link to the database. This isn't how web forums work, though.
 

·
Banned
Joined
·
10,348 Posts
Re: VWVortex zeroforum optimization discussion (dragonfli_x)

quote:[HR][/HR]If there was a way we could get zeroforums access to the database, a client side would only have to get access to the tables one time to just look at the data, not to process... right? so the loads can be higher on the existing processor since it's the clients CPU working its own search... I don't really know database management or optimizing... just wondered if it could happen. I saw a flash app do a dynamic search on a auto parts database with minimal lag because the server wasn't being worked...
i don't know how it was done though...[HR][/HR]​
The amount of data in the tables is likely quite large, so the bandwidth and cpu usage associated with "getting access to the tables one time" - or caching all the database data - would be an even larger burden on the db server than a simple search query. Also, since the database contents are constantly changing, updating the caches periodically probably wouldn't work out so well.
You hint at a better solution which might involve mirroring or replicating the databases across different servers to balance the load. My understanding is that load balancing databases is very difficult to setup. Load balancing front-end webservers is much easier, but it appears that the bottleneck on the vortex is in the db server itself.
Tough problem. Probably the only real solution is to add some beefier hardware, or start kicking off users who write about street racing
 

·
Registered
Joined
·
8,660 Posts
Re: VWVortex zeroforum optimization discussion (phatvw)

oohhh!!... sniff sniff, o i c... well, that cleared it up quick!... any other methods of optimizing the server other than load balancing via mirroring, steroid push the hardware, "band-aids," i missed something... didn't i? oh yeah, beating down street racers...j/k

quote:[HR][/HR]
The amount of data in the tables is likely quite large, so the bandwidth and cpu usage associated with "getting access to the tables one time" - or caching all the database data - would be an even larger burden on the db server than a simple search query. Also, since the database contents are constantly changing, updating the caches periodically probably wouldn't work out so well.
You hint at a better solution which might involve mirroring or replicating the databases across different servers to balance the load. My understanding is that load balancing databases is very difficult to setup. Load balancing front-end webservers is much easier, but it appears that the bottleneck on the vortex is in the db server itself.
Tough problem. Probably the only real solution is to add some beefier hardware, or start kicking off users who write about street racing

[HR][/HR]​
 

·
Registered
Joined
·
472 Posts
Re: VWVortex zeroforum optimization discussion (phatvw)

quote:[HR][/HR]Load balancing front-end webservers is much easier, but it appears that the bottleneck on the vortex is in the db server itself.
Tough problem. Probably the only real solution is to add some beefier hardware, or start kicking off users who write about street racing

[HR][/HR]​
Nothing a 24-way RS6000 wouldn't be able to solve
! Let's help these guys get bigger servers !



[Modified by newbiewithGTI2002, 6:34 PM 8-6-2002]
 

·
Registered
Joined
·
593 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

I posted this to the "other" forum and it seems to have gotten lost among all the really useful BS going on there. Perhaps someone here will take it as a more serious solution for the interium.
quote:[HR][/HR]Keep the Search ON! My Recent Topics OFF.
If the My Recent Topics and Search are one and the same, then still Keep the Search ON and ASK that people NOT use the My Recent Topics (based on the honor system) and remind them what happens if they do. I think then, if the system bogs down and someone is doing My Recent Topics, a connection will be made - duh.
It'll be sort of like a "Spare the Air Day". Where if everyone doesn't pitch in to cut back, it'll be worse for all. And if they do, then

At least we'd all be cognitive of our actions and the impact to performace. I'd say most people would do the right thing.[HR][/HR]​
 

·
Registered
Joined
·
1,211 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

I made a suggestion post early on in the "Bring Search Back" poll posting, but it got lost in the fray. I also am versed in PHP & MySQL, as I see you are too, so... any input on this (quoting myself):
quote:[HR][/HR]
I have an actual suggestion to fix the My Recent Topics system, to make it not so much client side, but to remove its load on the system. Without knowing, I can only assume that the load on the server from My Recent Topics is caused by a VERY large query that essentially searches the entire DB based on the person's username. Considering the number of users alone thats an awfully ugly query. I suspect a code change to instead of querying the DB, add a table to the person's profile that much like "Track my subscriptions", adds every thread they post to to a simple list. I suppose further implementation could add clean-up so that the list doesn't exceed a certain size, but I think this would give all the people who use the feature (me ) essentially what they want, even though they won't necessarily get all the details they had before from the search. Or, a different system using what someone else suggested, and is also a possibility that utilizes USER space... a cookie.
btw, Jamie, do you and your army of code monkeys (do you even HAVE an army of code monkeys?) actually edit the ZeroForum code to make changes like this, or do you make feature requests to the ZeroForum developers? Oh, and out of professional curiousity, is ZeroForum built on PHP & MySQL?
Aaron R Herrmann
Web Developer, IBM WebAhead Internet Technology [Intern
]
[HR][/HR]​
 

·
Registered
Joined
·
1,989 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

The search functionality probably needs some good indexing. Make a inverted index for keyword searches, and a per-userid recent topics index. Since the only way things get added to the database are through the forum interface, one can make it so that it updates the indices when the message is posted so they're always in sync.
Like I mentioned in the other thread, I wrote software that was doing full text searches on hundreds of gigabytes of medical records in realtime with response times of under one second. Granted, the inverted indices were huge, but disk space is cheaper than disk bandwidth.
 

·
Registered
Joined
·
13,050 Posts
Re: VWVortex zeroforum optimization discussion (vwgtirob)

I'm looking into a way to get hold of some of the code on a development server here so that you guys can take a look at it.
Zeroforum is PHP/MySQL based.
Our longterm solution is a 12 disc array coupled to a dedicated database server. It will require some changes to the ZeroForum code and to the kernal to operate correctly.
Mean time we don't have a team of "code monkeys" here, so I'll see what I can do to get some of code where you guys can look at it.
Thanks for the help.
-jamie
 

·
Registered
Joined
·
472 Posts
Re: VWVortex zeroforum optimization discussion (vwvortex1)

quote:[HR][/HR]I'm looking into a way to get hold of some of the code on a development server here so that you guys can take a look at it.
Zeroforum is PHP/MySQL based.
Our longterm solution is a 12 disc array coupled to a dedicated database server. It will require some changes to the ZeroForum code and to the kernal to operate correctly.
Mean time we don't have a team of "code monkeys" here, so I'll see what I can do to get some of code where you guys can look at it.
Thanks for the help.
-jamie[HR][/HR]​
Don't forget that a lot of people are willing to help financially...
 

·
Registered
Joined
·
33,982 Posts
Re: VWVortex zeroforum optimization discussion (newbiewithGTI2002)

quote:[HR][/HR]Don't forget that a lot of people are willing to help financially... [HR][/HR]​
I have saved a big chuck thanks to the Vortex whether it is for mods, or to get the dealer repair stuff under warranty. There are about 40K users in the vortex. If you get 10K of them give you $5, that's a pretty amount to pay for extra hardware, software, bandwidth, etc. I bet there are a lot of us willing and able to give you more than $5.
 

·
Registered
Joined
·
2,025 Posts
Discussion Starter · #17 ·
So far I think we've got some pretty decent ideas about how to optimize the search function, from db to php to html.
I'd be really interested to see the sql and php code to see what is possible and what is not. It might also help if we had a db schema for the relevant tables.
 

·
Registered
Joined
·
603 Posts
Re: VWVortex zeroforum optimization discussion (paultakeda)

i have been out of the programming loop too long to offer a code-based solution, but cant the recent topics simply be cached in the form of a cookie on the end-users pc? the only time the actual vortex db search would occur was if the cookie was absent (i.e. the user logs into vortex from a different pc). this would eliminate the 'search' from the equation as far as 'my recent topics' goes. the only data required from the vortex server would be updating the data for each thread being tracked (i.e. last user to post, date modified). this would be considerably less work for the server cpu (and it would cut down on bandwidth big time).
i have a deeper concern:
if nothing is done soon, users will most likely resort to bookmarking all of the threads they are active in. this means they will have to manually check each thread from their end. if you take into account multi-page threads, each user could potentially request tens of pages within minutes time (much faster than the usual vortex user would). if enough people start doing this, it will cost vortex in raw bandwidth instead of merely racking up a cpu-query queue. the end result will be that vortex is slow once more, but now it will be due to practices on the users end, and not easily controlled on the server side. in the end vortex would have to have a limit on how fast you can browse (and there would still be no search).
i can just see the whole thing spiraling out of control when all thats needed is user-side cookies storing the list and a simple thread-index transmitted to vortex followed up with a simple response from vortex (the last poster/date mod).
ebay does this the same way. thats how they handle a large number of users, cant we do the same thing?
 

·
Registered
Joined
·
1,989 Posts
Re: VWVortex zeroforum optimization discussion (Allyn)

I was suggesting instead of having to do a search to find out which topics you've posted to recently, keep a table of the most recently-participated-in topics on the server. Very low impact.
I don't think a cookie would be big enough to store the data about the recent topics, and I post from multiple machines...
 

·
Registered
Joined
·
2,025 Posts
Discussion Starter · #20 ·
Re: VWVortex zeroforum optimization discussion (vwgtirob)

Nail on the head. Cookies are client-machine specific. If you log on to different machines your recent topics will all differ.
The lookup table idea vwgtirob has is a good one. But we really need to know what the php and sql code and the db scheme look like before we can move from educated guesses to solid ideas on how to optimize the lookup.
 
1 - 20 of 41 Posts
Top