Gossamer Forum
Home : Products : Gossamer Links : Pre Sales :

Links SQL Building

Quote Reply
Links SQL Building
I currently have the non-SQL version installed on a dedicated box (Pentium III-850, 256mb sdram). Nearly 11,000 sites and the links.db file is around 2.5 MB (no extra fields). Still purrs like a new born kitten (watch those claws) - kudos to GT. Searches continue to be blazing fast (albeit via specially modified search script), and no data corruption to date. But the one problem (the obvious one) is building (done via cron). Building is scheduled at midnight, daily. I just arose, and found that the server is still building. Ouch. Mind you, building occurs during non-peak traffic period - no obvious impact on server: pages serve fast and scripts (including search.cgi) execute relatively fast (during build, search for more common [max hits] keyword takes 4-7 seconds). But 7+ hours to build is getting silly. I have now disabled new submissions (and building the database) for the moment as I plan to upgrade to the Links SQL within the month.

For the database (and server) I describe, what sort of time frame would be typical (as a range for example) to cron build via Links SQL? And can you perform [Selective Build] if you use ratings (sites are sorted by rating alone)?


Cheers - Dan Cool


Quote Reply
Re: Links SQL Building In reply to
I built HTML pages for a database with 500,000 links and 120,000 categories on a server with 256MB RAM and a PIII 450mhz processor and it took about 30 minutes. You can also choose to build changed pages only which would cut down the time even more.

Installations:http://www.wiredon.net/gt/

Quote Reply
Re: Links SQL Building In reply to
Hey Paul:

Did you build manually via SSH (or Telnet), or automatically via cron? Also, regarding selective build where you only build changed pages, what if you rank on the basis of ratings? As I understand it, selective build is on the basis that only pages are built if there are new links - and ratings don't figure into the equation. However, if the database can be built under a few hours, then it really does not matter.


Cheers - Dan Cool


Quote Reply
Re: Links SQL Building In reply to
In Reply To:
Did you build manually via SSH (or Telnet), or automatically via cron?
There is no difference in performance via building via cron or building from telnet/ssh.

In Reply To:
As I understand it, selective build is on the basis that only pages are built if there are new links - and ratings don't figure into the equation
No, any modification to a Link will require that category page to get rebuilt. An addition/deletion of a link will cause the category page and all parent categories to get rebuilt (because of new link counts).

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Links SQL Building In reply to
Hi Alex:

Thanks for the feedback! Of course that is right, performance would be equatable whether you build via cron or SSH (Telnet). Two and a bit hours sleep, and my mind is jello.

Good to hear that selective build will suit my needs. I will definitely be purchasing Links SQL in the very near future - committing the server to nearly a third of each day building is a tad much Wink


Cheers - Dan Cool


Quote Reply
Re: Links SQL Building In reply to
Hi Paul

With a link db that large, how fast are searches?

What I'm going to put together might have around 250,000 links, but perhaps a million hits or more a day. I want to build static category pages, but I might use page.cgi.

I haven't gotten the script, yet. That's why I'm asking.

Many thanks Smile

DT

Quote Reply
Re: Links SQL Building In reply to
Searches were pretty quick and it wasn't under mod_perl either. I can't remember exactly as I don't have the database anymore but I think about 20,000 links took a few seconds.

Mods:http://wiredon.net/gt/download.shtml
Installations:http://wiredon.net/gt/
Quote Reply
Re: Links SQL Building In reply to
Hi Paul

I wuz a dummy. I should have been clearer. I meant your db of 500,000 links.

Do you think Links SQL could handle a million or more hits per day with that size db, tons of searches, and using page.cgi for lots of hits to the detail pages?

BTW, does each category get its own table, or are all cats in the same table? I'm just wondering how easy it would be to edit stuff off line - whether I would have to pull in a monster file to Excel, or whether each cat could be pulled in separately to make the job easier on my local computer.

Thanks, again, so much Smile

DT

Quote Reply
Re: Links SQL Building In reply to
In Reply To:
Do you think Links SQL could handle a million or more hits per day with that size db, tons of searches, and using page.cgi for lots of hits to the detail pages?
If you are on a dedicated server using mod_perl, it shouldn't be a problem...it would cause CPU problems if you are on a virtual server or semi-dedicated server.

In Reply To:
BTW, does each category get its own table, or are all cats in the same table? I'm just wondering how easy it would be to edit stuff off line - whether I would have to pull in a monster file to Excel, or whether each cat could be pulled in separately to make the job easier on my local computer.
All categories are located in ONE table, however, there is another table called CatLinks, which contains the LINKID from the LINKS table and CATEGORYID from CATEGORY table...have to get used to relational database, dogtags. Wink

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: Links SQL Building In reply to
Many, many thanks, AnthroRules!

I'm heading toward a dedicated arrangement. I know that this is the only way to really make my stuff work. Gotta get there step by step, though. I'm glad to hear the SQL will be up to the job.

Okay, it looks like it's time to dive in...

Thanks, again, to all SmileSmile

DT

Quote Reply
Re: Links SQL Building In reply to
In Reply To:
I wuz a dummy. I should have been clearer. I meant your db of 500,000 links.
Yeah thats what I meant too....I meant that a search producing 20,000 links out of the 500,000 took a few sec.

Mods:http://wiredon.net/gt/download.shtml
Installations:http://wiredon.net/gt/
Quote Reply
Re: Links SQL Building In reply to
Whoa! 20,000 results out of 500,000 in a few secs. That's pretty amazing. Thanks muchly, Paul Smile

DT

Quote Reply
Re: Links SQL Building In reply to
DogTags,

That stat should be taken with a grain of salt, since Paul is probably running his site where these links are located on a dedicated server. On a virtual server, depending on the activity of other users on the box, you would probably run into timeout and excessive CPU usage problems.

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: Links SQL Building In reply to
DogTags said:

In Reply To:
I'm heading toward a dedicated arrangement.
But yeah, the server was co-located.

Mods:http://wiredon.net/gt/download.shtml
Installations:http://wiredon.net/gt/
Quote Reply
Re: Links SQL Building In reply to
Hi,

In Reply To:
On a virtual server, depending on the activity of other users on the box, you would probably run into timeout and excessive CPU usage problems
Eliot is right, please keep your expectations realistic. You won't be able to run a site with 200,000+ links on a virtual server. It does take a dedicated server to handle searching through that much information quickly.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Links SQL Building In reply to
Uh, PAUL...I was simply REMINDING DogTags that your stats are not FROM a VIRTUAL server! YES, I already KNOW that DogTags is looking at a DEDICATED SERVER solution.

AND I was reminding OTHER users that your stats should be taken with a G R A I N OF S A L T!!!

Got it?????? Mad

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: Links SQL Building In reply to
All points well taken and appreciated!

Thanks a ton, Everyone SmileSmile

DT