The Arcive of Official vBulletin Modifications Site.

modyyy55 · #1 12-23-2010, 01:18 AM

Hello,

I have a problem with my database it is about 800 MBs now with a half million of posts.

The problem is it's getting larger and larger everyday and that's makes the server very slow and have to upgrade my server every while.

Is there a solution for that ? like dividing the post table into number of tables so that the MySQL engine doesn't have to mine all the 500,000 posts to get a one post or something ?

Is that possible ?

Thanks in advance.

BirdOPrey5 · #2 12-23-2010, 01:32 AM

800 MB is a little big for only 500,000 posts. Do you have attachments and avatars stored in the database (this is the default setting). If so you can follow instructions in the Admin CP to move attachments and avatars to the file system that should bring down your database size...

But 800 MB isn't all that big- if you're running into problems I'd really consider a new server or web host, you shouldn't notice any issues at 800 MB.

modyyy55 · #3 12-24-2010, 12:43 PM

Quote:

Originally Posted by BirdOPrey5

800 MB is a little big for only 500,000 posts. Do you have attachments and avatars stored in the database (this is the default setting). If so you can follow instructions in the Admin CP to move attachments and avatars to the file system that should bring down your database size...

But 800 MB isn't all that big- if you're running into problems I'd really consider a new server or web host, you shouldn't notice any issues at 800 MB.

Thanks Joe for your reply.

I'm already going to move to a new server in a new company, actually I'm transfering the data right now :D.

But I'm afraid that this database will expand and will need me to upgrade the server every while even if the active members on the forum are the same, that what I'm afraid of.

So I thought that dividing the database could help, is there a way for that ?

I have already moved the attachments outside of the database, will try to do that for avatar too, but the problem is that the posts table is 400 MB it self, it's the largest table in the database.

BirdOPrey5 · #4 12-24-2010, 01:20 PM

I don't know of any way to "split" the database but a decent host should allow a database size of 3 to 4GB without any issues at all- I've seen 3GB vBulletin Database (of which 2.7 GB were posts) work just as good as any other smaller database. On a dedicated host you could probably go much larger than that even.

In a case where people had very limited database sizes and were approaching limits you'd use the "Mass Prune" tools.

If you really got to the point you had to "split" anything you'd split the web server off the database server. The database would always be on one server though. Instructions for this are here: http://www.vbulletin.com/forum/showt...parate-servers
(It's old but still basically valid.)

modyyy55 · #5 12-26-2010, 05:37 PM

Quote:

Originally Posted by BirdOPrey5

I don't know of any way to "split" the database but a decent host should allow a database size of 3 to 4GB without any issues at all- I've seen 3GB vBulletin Database (of which 2.7 GB were posts) work just as good as any other smaller database. On a dedicated host you could probably go much larger than that even.

In a case where people had very limited database sizes and were approaching limits you'd use the "Mass Prune" tools.

If you really got to the point you had to "split" anything you'd split the web server off the database server. The database would always be on one server though. Instructions for this are here: http://www.vbulletin.com/forum/showt...parate-servers
(It's old but still basically valid.)

Thanks Joe.

I moved now to a Dual Xenon Quad Core server, But the server still overloaded !!

I don't know what to do now, really don't know ...

BirdOPrey5 · #6 12-26-2010, 05:49 PM

Do you have a host or are you hosting yourself? If you're getting overloaded on an 800 mb database it might be a poorly coded mod or bad settings- have you looked up optimizing your server for vbulletin?

http://www.vbulletin-faq.com/optimiz...tin-server.htm

https://vborg.vbsupport.ru/showpost....8&postcount=15

Also see dedicated forum here: http://www.vbulletin.com/forum/forum...-Configuration

modyyy55 · #7 12-26-2010, 06:47 PM

Quote:

Originally Posted by BirdOPrey5

Do you have a host or are you hosting yourself? If you're getting overloaded on an 800 mb database it might be a poorly coded mod or bad settings- have you looked up optimizing your server for vbulletin?

http://www.vbulletin-faq.com/optimiz...tin-server.htm

https://vborg.vbsupport.ru/showpost....8&postcount=15

Also see dedicated forum here: http://www.vbulletin.com/forum/forum...-Configuration

Actually the new server is managed, I moved to a managed server to take the load of tuning MySQL and so on from over my head.

But the problem still in, so I'm pretty sure it's bad a plug-in or something.

I'll start debugging, wish me luck, and please if you have some tips that could help me in debugging please send it to me.

Thanks Joe so much for your help.

BirdOPrey5 · #8 12-26-2010, 07:24 PM

Well one way is to disable every plugin and enable them 1 at a time and taking measurements of the load each time, this way you can see if one of them really increases load beyond expected levels. This could take days or longer tough if you have many mods and want to give several hours at least of use before determining average load.

A quicker way would be to disable half the mods and see if the performance issues improve greatly or not... if they do improve then re-enable half of what was off and test again, if they don't renable the first half and disable the second half.... then keep re-enabling one half or the other of the remaining until you figure out which mod is the problem- only problem with this is if you have 2 poorly performing mods you might miss one.

Good Luck. :up:

Digital Jedi · #9 12-29-2010, 08:41 AM

Did you try moving Avatars and Attachments to the file system as suggested? Images take up massive amounts of space compared to the data in posts. But yeah, you really shouldn't be having problems with a database that small. Of course it's always going to get larger. That's the point. But you shouldn't be anywhere near troublesome territory yet.

modyyy55 · #10 01-08-2011, 06:53 PM

GREAT ! :up:, I guess I'm done now with my problem now ... thanks GOD !

After lots of analysis and monitoring I found that at the load peaks the online members are very high and the server runs out of memory at that time, so the server starts to use the hard disk (swap) instead of RAM, and that what causes the load to raaaaaise that much.

So I raised the RAM, used Memcache as a vB datastore and installed xCache .... and it worked just perfect !, no overloads or any thing right now.

Also the load average is now constantly under 1, so I guess I'll downgrade the server processor also !

I guess I'll downgrade it to a Single QuadCore or even a high speed DualCore !

As the load average is very very low right now compared to a Dual Xenon QuadCore Processor which should carry a load of 8 or something !

Really so happy right now, thanks GOD.

Thank you Joy and Jedi for your help and your time.

Regards.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04691 seconds Memory Usage 2,265KB Queries Executed 13 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (3)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!