Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
I would like to only leave a few thousand posts on the forum, all others could go to another "archive" forum. I suspect this reduces server load. Most people only read yesterday's posts anyway, so why carry 200 000 posts?

It does not look difficult, it just is not quite trivial.

I thought about doing the following

copy the database, running a copy of the forum with the copy. disable posting for all people on the archive board.

then deleting most posts on the original databasef

A few problems:
a) what happens to new users, they are not in the archive database, so they don't have read permission in the archive

b)how can I update the archive 2 weeks later at the next archiving opportunity?? It seems easier to do the original 1st archive than to update later.


Maybe we can even automatically always update every day the archive to keep it current.

I think this should be a feature in the official threads distribution. Instead of deleting old messages, move them to archive!!


This is urgent because the other option is to buy a new bigger server!!!!! I need to greatly reduce load!!!!!

Sponsored Links
Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
I know people thought that in mysql this was not necessary. But it seems necessary. I believe that a board with 3000 posts is much faster than with 150 000 posts. Or is this not so? maybe I am mistaken!!!!

Anyone can do a theoretical calculation?

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
It all depend on how MySQL is tuned.

I just moved/upgraded a board with half a million posts and 20000 users - once MySQL got tuned up correctly... all was fine.

MySQL is built for it.

Joined: Jun 2001
Posts: 3,273
That 70's Guy
That 70's Guy
Offline
Joined: Jun 2001
Posts: 3,273
The size of the w3t_Posts table makes worlds of difference in speed. I have discussed the idea of an auto archival tool here before but it's something I can't warrant the time for.

What needs to be done is that when a thread expires it is moved to an archive table that has the same layout as the posts table. We only want to allow viewing of the archives so this table should only be accessed with ubbthreads.php, postlist.php, showflat.php and showthreaded.php. These scripts need to be modified to allow for a toggle that instructs them to use the archive table. We also need to mirror actions taken on the boards table such as the deletion of a board should delete related posts in both the posts/archive tables. The deletion of a user should also be mirrored on both tables.

Then a cron script needs to be made that moves the expired threads and replies to the archive table.

On very large sites this will cut the page generation times of IIP by more than half. This is another reason I would love to see this tool materialize.


Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
maybe we could just move old threads over to another archive forum. On the same database. But all the new stuff will run on the main forum. Which seems to be 3 times as fast.

So in other words

active main forum 1000 posts

archive forum 200 000 posts.


Question: I have some inkling that this is due to bad inefficient programming. The same purpose could be reached by reprogramming some database requests! or reorganizing the database??



This is postlist in a small forum with 1000 posts
Generated in 0.439 seconds in which 0.281 seconds were spent on a total of 29 queries. Zlib compression enabled.


The following is postlist in a large forum with 150 000 posts.
Generated in 3.513 seconds in which 3.417 seconds were spent on a total of 29 queries. Zlib compression enabled.

1100 posts
Generated in 0.245 seconds in which 0.178 seconds were spent on a total of 30 queries. Zlib compression enabled.


All this makes a little sense. Postlist is slower for large forums. Still, it does not look like this is necessary! After all, the first page of postlist.php is the same size, in the small 1000 post forum or in the 150 000 post forum.



Now it gets interesting!!

Showflat in the small 500 post forum
Generated in 0.102 seconds in which 0.049 seconds were spent on a total of 13 queries. Zlib compression enabled.


showflat in the big 150 000 forum
Generated in 1.168 seconds in which 1.114 seconds were spent on a total of 13 queries. Zlib compression enabled.


I did all this repeatedly, this is not a quirk or accident. Please someone find out why this is so, then maybe we can reorganize the database in some way.

I see no logical need why showflat in a large forum takes 10 times longer than in a small forum!!! What is worse, most action happens in the large forum.

If there is no simple database reorganization trick, then let us just do automatic bulk moving to archive boards on the same database, on the same board.


Note: I already put the default on seeing only 2 days worth of posts, I think this speeds up things considerably!!

There should be some indexing trick, like indexing the day and week of the first and/or last post. Most posts refer to the last 2 day's or last week's posts. So if that were indexed, only last 2 days were searched initially, that would do the trick!!!!!! If we only chose to display the last 2 days, then only that part of the database should be searched!!

Until the settings get changed (display more than 2 days worth of posts!!)

Sponsored Links
Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
I kind of understand that reading and posting in large forums takes longer.

I think the main point is, that most of this happens in the most recent posts. So a speedup can be applied to the first few hundred posts.

Users can be motivated to set the forum to 1 day only, because the get rewarded with extra speed.

Or we really do the archive forum, then we as the admins have full control, and don't depend on the users.

I am just simply thinking of bulk moving all posts from the main forum to the backup forum every day. I think this is a almost trivial program to write.

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
As JustDave said, moving the posts from one subforum to another won't speed up the forum that much. To get a real speed improvement, you'll have to archivate the posts into a different table.

Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
[]Astaran said:
As JustDave said, moving the posts from one subforum to another won't speed up the forum that much. To get a real speed improvement, you'll have to archivate the posts into a different table. [/]

well this is the theory. See the numbers of my test, you can try that on other people's board.

My speed test is very clear. Unless you can show that this is due to some fluke in my setup or non-optimal mslq setup.

Until then it seems proven to me that the forum size is of utmost importance. And I still strongly suspect that indexing based on an integer number for day or week will give maior improvement on recent todays or this week's posts.

Database specialists, please speak up!

Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
of course, there should also be restrictions on searches. Like giving search permissions depending on user status, like we do read and write permissions on forums.

So one might want to choose

unregistered >> no search privileges
junior member>> search only last 2 weeks
registered >> search only one forum at a time
special paying member >> unrestricted searches

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
I didn't say that it has now effect, i only said that the effect is not as big as it using a seperate table for the archiv.

Your method improves the speed a bit, cause you might avoid a full table scan. That depends on the query and the indices you have set.

A proper mysql setup and configuration is important also, but i can't say how this effects your results without knowing your settings and machine.

My board has nearly one million posts in the database. Restricting the displayed post per forum affects speed. But to get a significant improvment, i'll have to remove the old posts from the posts table.

Indexing has a positve effect on similar queries, but a large table produces more load as a smaller one. Especially on searches, which are the bottleneck of threads. Submitting a several searches in a short amount of time can hook up every db server with a large posting table.

Sponsored Links
Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
If you use mysql 4.x, you can use the UNION statement to improve the search significantly. Using a fulltext search can also be a good idea.

Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
I think I have few searches, at least I never notice people in the 'who is online' being anywhere searching.

Can that be found somewhere? maybe in statistics? would be interesting how many searches happen.

I strongly suspect that a bunch of repeated searches could kill the server. So one more reason for doing more control on allowed searches! Should be more options for the webmaster to partially allow searches, or only for certain paying members.

Well my test result showed a 10 fold speedup for a 1000 post forum compared to a 150 000 post forum. This is VERY VERY DRAMATIC? Because this was on the showflat and postlist, which most people use all the time.

That means we could get by with 5% cpu load instead of 50%. This is very very very dramatic and relevant. Please read again, don't just talk that away!!

PS: I wonder if the bulk move plugin can help me archiving??

Can some mysql whiz help me to find the command for

move all posts with less than 3 days age from forum xxx to forum xxxarchive?
instead of age, post number higher than nnn would also do.




Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
[]mario2 said:

Can that be found somewhere? maybe in statistics? would be interesting how many searches happen.[/]
No, but there is a hack arround here that tracks the search values. You could alter your dosearch.php to log all submitted queries, also.

[]
I strongly suspect that a bunch of repeated searches could kill the server.
[/]
Not kill, but cause swapping so that mysql reaches the max connection limit. That makes the board unusable for a certain amount of time.
[]
So one more reason for doing more control on allowed searches! Should be more options for the webmaster to partially allow searches, or only for certain paying members.[/]
I agree, but this can easily be hacked.

[]
Well my test result showed a 10 fold speedup for a 1000 post forum compared to a 150 000 post forum. This is VERY VERY DRAMATIC? Because this was on the showflat and postlist, which most people use all the time. [/]
Thats quiet normal, mysql needs to scan more rows until it can process the result.
[]
That means we could get by with 5% cpu load instead of 50%. This is very very very dramatic and relevant. Please read again, don't just talk that away!![/]
Don't mix the query time with cpu load. A 10 times slower query time doesn't mean that the cpu load is 10 times higher!

[]
PS: I wonder if the bulk move plugin can help me archiving??
[/]
Sure, if you want to archive the threads by hand
[]
Can some mysql whiz help me to find the command for

move all posts with less than 3 days age from forum xxx to forum xxxarchive?
instead of age, post number higher than nnn would also do.
[/]
[:"red"]Note: this queries should not be used to backup old threads! [/]
Code
 <br />UPDATE w3t_Posts <br />SET B_Board ="ARCHIVEBOARDKEYWORD" <br />WHERE B_Number < POSTID; <br /> <br />UPDATE w3t_Posts <br />SET B_Board ="ARCHIVEBOARDKEYWORD" <br />WHERE B_POSTED < TIMESTAMP OF DATE; <br />

Last edited by Astaran; 06/18/2003 9:03 AM.
Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768
Hmmmm ... couldn't those UPDATE queries cause threads to be split across forums? I.e., some of the posts within a thread would be in one board, and some in a different board.

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
That's right - it can split threads up. I should have noted that.

The query just doees, want mario2 asked for.

An automated archivation would require some additional queries to determine all affected threads and replies.
The existing doexpire cron script could be used as a base for that.

Joined: Jul 1999
Posts: 118
Enthusiast
Enthusiast
Offline
Joined: Jul 1999
Posts: 118
I did a simple archive forum. Transferred 150 000 posts there. Left only 2000 posts on the main board.

I used the bulk move plugin, with some hack (displaying 2000 threads at once instead of 30). I suggest this routine should not have "hard wired" number of posts but a variable that can be changed easily. After the big 20 000 thread move I reduced the bulk move display to 200 threads

Results were amazing. CPU load went down a lot. Data base query time for a page went down from 2 seconds to 0.2 seconds.

This might be a bit board specific. This board is mainly a gossip board. So people don't dig into the past, they mainly comment on last 1-5 day's posts.

Thank god, not much searching is going on there either.

I still would like my prior suggestions implemented:

a) restrictable search rights. Search is actually scary if ever it gets abused.

b) indexing by posting day or posting week, thus allowing to just blank out all posts older than 2 days, increasing database speed without the database artifact




Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)