Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 23 Dec 2010, 03:18
modyyy55 modyyy55 is offline
 
Join Date: Oct 2009
Dividing the Database

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.
Reply With Quote
  #2  
Old 23 Dec 2010, 03:32
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
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.
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.
Reply With Quote
  #3  
Old 24 Dec 2010, 14:43
modyyy55 modyyy55 is offline
 
Join Date: Oct 2009
Originally Posted by BirdOPrey5 View Post
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.
Reply With Quote
  #4  
Old 24 Dec 2010, 15:20
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
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.)
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.
Reply With Quote
  #5  
Old 26 Dec 2010, 19:37
modyyy55 modyyy55 is offline
 
Join Date: Oct 2009
Originally Posted by BirdOPrey5 View Post
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 ...
Reply With Quote
  #6  
Old 26 Dec 2010, 19:49
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
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

http://www.vbulletin.org/forum/showp...8&postcount=15

Also see dedicated forum here: http://www.vbulletin.com/forum/forum...-Configuration
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.
Reply With Quote
  #7  
Old 26 Dec 2010, 20:47
modyyy55 modyyy55 is offline
 
Join Date: Oct 2009
Originally Posted by BirdOPrey5 View Post
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

http://www.vbulletin.org/forum/showp...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.
Reply With Quote
  #8  
Old 26 Dec 2010, 21:24
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
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.
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.

Last edited by BirdOPrey5; 29 Dec 2010 at 15:27.
Reply With Quote
  #9  
Old 29 Dec 2010, 10:41
Digital Jedi's Avatar
Digital Jedi Digital Jedi is offline
 
Join Date: Oct 2006
Real name: Mark Daniel Martinez
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.
__________________
Reply With Quote
  #10  
Old 08 Jan 2011, 20:53
modyyy55 modyyy55 is offline
 
Join Date: Oct 2009
GREAT ! , 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.
Reply With Quote
  #11  
Old 08 Jan 2011, 21:23
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
Thanks for letting others know what you did! Good luck.
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.
Reply With Quote
  #12  
Old 11 Jan 2011, 23:38
Sir Jake Sir Jake is offline
 
Join Date: Oct 2009
How would one do this with just phpmyadmin and ftp?
I see the attachments are using 800+Mb's in the database.
Thanks for the help BirdOPrey!

Last edited by Sir Jake; 12 Jan 2011 at 09:56.
Reply With Quote
  #13  
Old 12 Jan 2011, 05:16
BirdOPrey5's Avatar
BirdOPrey5 BirdOPrey5 is offline
 
Join Date: Jun 2008
Real name: Joe D.
If attachments are in the database move them to the file system...

admin cp -> attachments -> attachment storage type
Move to File System...
(follow instructions, make a new folder, chmod it 777)
__________________
-Joe
Former vb.org Moderator. Retired.

@BirdOPrey5 | All Things BOP5 | Joe's Ultimate Off Topic
Note - I no longer making new VB mods, sorry.
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 11:08.

Layout Options | Width: Wide Color: