How do I know the database size limit?

[basa]

Hello folks, happy new year. I was wondering how I would find out if I were reaching the end of my database. It is at 374.58 MB. At what point does it become too large? I also have 7gb of attachments... outside the dababase of course.

The server is mine so disk room is not an issue. Thanks in advance.

[Floris]

There are web sites that have 15 GB .sql backup files, so nearing 500mb really isn't an issue unless you are running out of disk space on the partition the database is stored on.

[Dennis Olson]

Note that IP searches become impossible once your forum reaches about 300,000 posts. At that point, you have to create a key of the IP field in posts.

FYI

(I always find this ironic because vB is touted as "THE solution for large fora", but the IP search function effectively disables the server once the "large" point is reached....)

[Kier]

IP searches are fairly intensive on the database, but adding an index means that new posts can take a lot longer to insert into the database.

As IP searches are a relatively rare function to call, we choose to optimise in favour of post insertion rather than IP searching.

[Dennis Olson]

Well, my database is now over 1.2 million posts. There is essentially ZERO slowdown in performance. However, by choosing the development path you did, you effectively disable IP search for the very large fora that your company touts support of. If you continue along that path, you should do one of two things:

1) Add a disclaimer about the IP search not working for large fora

2) Automatically disable IP searches once the post table gets "x" posts in it.

Because your current methodology is absolutely worthless.

[AZ_Astro]

Note that IP searches become impossible once your forum reaches about 300,000 posts. At that point, you have to create a key of the IP field in posts.

I am a new user of vBulletin. I am curious what an IP search is? Are you talking about a field in a table that is an IP address?

I frequent a forum called www.rcgroups.com that restricts posts in a thread to 500. Is this related to what you guys are talking about?

Thanks.

[Dennis Olson]

IPaddress is a field in several tables, including POSTS (where all posts live). Once the database reaches a certain threshhold (I use 300,000 total posts as my arbitrary figure), the IP search becomes so resource-intensive that it effectively crashes the server. (Although the server is in fact NOT crashed, to your user community it's unavailable for a period of time while it processes the IP search.)

Every time someone posts, their IP address is logged (if you have that option set in your vB options). Suppose you have a problem user, and want to see if he/she might have any other board names on your forum. So, you attempt to search the POSTS table to see if any other users have the same IP address/block. That search spins SERIALLY through the POSTS table.

Once you get enough posts in the table, the search takes so long that your server cannot perform its other functions, the entire CPU being taken up by the search.

The vBulletin software is (in general) quite efficient at handling large boards. However, IN THIS INSTANCE, their design was incredibly poor. (Again, only for large boards.) The solution is to make the IPaddress field a "key", thus making searches very fast. However, to the folks at vB, this is a "hack" (modification) and they don't support it. Fortunately, the query is simple and quick to implement, and no code changes are required to support it.

The "cost" in terms of posting speed is minimal. I know this for a fact, so don't let them blow smoke up your, um, skirt.....

[Floris]

If you have a problem user you can hover over the IP icon or click on it to find his IP, after which you can ban it. And all the accounts he uses will render useless to him.

Hopefully in the future the search for IP can be improved to be less resource intensive.

An admin uses IP search perhaps once per week, only on a busy site where they have a lot of abusive users. A good staff setup can result in having hardly any abuse over periods of months.

[Dennis Olson]

An admin uses IP search perhaps once per week, only on a busy site where they have a lot of abusive users. A good staff setup can result in having hardly any abuse over periods of months.

Yes indeed. My forum averages 2000 posts a day. Our topics can be very controversial, and we have had many problems in times past. What Floris fails to say however, is that once my post count reached about 500,000, a single IP search would "hang" the server for about 10 minutes, necessitating a reboot. This was on a dual 800MHz Xeon server w/2Gb ram.

The method I detailed above (implementing IPaddress as a key field) now allows 20-30 second IP searches across 1.2+ million posts.

Hopefully in the future the search for IP can be improved to be less resource intensive.

Yeah, one would certainly hope so....

[Floris]

I don't see how this is relevant to the whole thread, so please go back on topic again?

[Zachery]

An admin uses IP search perhaps once per week, only on a busy site where they have a lot of abusive users. A good staff setup can result in having hardly any abuse over periods of months.

Yes indeed. My forum averages 2000 posts a day. Our topics can be very controversial, and we have had many problems in times past. What Floris fails to say however, is that once my post count reached about 500,000, a single IP search would "hang" the server for about 10 minutes, necessitating a reboot. This was on a dual 800MHz Xeon server w/2Gb ram.

The method I detailed above (implementing IPaddress as a key field) now allows 20-30 second IP searches across 1.2+ million posts.

Hopefully in the future the search for IP can be improved to be less resource intensive.

Yeah, one would certainly hope so....

Dennis is that the webserver or database server?

Have you tried on more powerfull hardware

[Dennis Olson]

Database side. I don't have $'s for more powerful hardware. My forum isn't a corporate-supported site. Floris, a user asked a valid question, and I attempted to answer it honestly. Why so touchy...?

[Floris]

IP search has nothing to do with the question 'how big can my mysql database be?'. Sorry if I sound touchy, didn't mean to sound touchy.

[AZ_Astro]

The vBulletin software is (in general) quite efficient at handling large boards. However, IN THIS INSTANCE, their design was incredibly poor. (Again, only for large boards.) The solution is to make the IPaddress field a "key", thus making searches very fast. However, to the folks at vB, this is a "hack" (modification) and they don't support it. Fortunately, the query is simple and quick to implement, and no code changes are required to support it.

Yes - I asked the question and I appreciate the lengthy and precise response that Mr. Olson provided. Thanks.

I have been looking the database over and I see the IP address fields now. I think it's a common occurrence in database management to add indices or change fields to become indexes/keys when things start to bog down - and given the size of the vBulletin database, if that's the only field that's killing search times, then the vBulletin software development team is doing great! The software evolves as problems develop, and it's the good folks who report the problems and point out where things can be improved who help the product along.

Sorry to get the thread off track.

[Dennis Olson]

Here is the thread where the issue is discussed in eye-bleeding detail....

http://www.vbulletin.com/forum/showthread.php?t=113497