Large forum db question

[ørret]

Hello! I have some questions regarding large forum databases i hope you guys can help me with. Just out of interest, how large can a database be before it's literally useless. I'm looking for the limit where the database is so large that it's causing slow loading or other issues in general for visitors. What do you do when your database is too large? What is the preferred database for so much content? How do you run such a database? Who's willing to host such a database? What do you need to maintain it (hardware/software/bw etc.)? These questions might be pretty bad as i have no clue whatsoever I'm talking about. I would like if someone could tell me how all this work, and in detail if possible, and maybe give me a sample of a large site and how they run it. For example: Gaia Online with 1724899171 posts, their db must be like 50 terrabyte and in 1 sql file? Or is there a system that split databases and then merge them, so you don't get 1 large file?

Thanks in advance!

[Robulosity2]

I'm fairly sure MySQL will only support 2.0 GB Tables? (http://dev.mysql.com/doc/refman/5.0/en/full-table.html may be helpful)


MsSQL or PostreSQL will be slightly diferent, anyway your database size and functionality is essentially defined by the scalability of the server/network that it's being run on. In the example of Gaia Online they may have had that many posts, but they also may not keep that many (e.g it's counting totals but not reducing based on deleted posts or content)

Your host requirements for such a site, depend on
- Kept Post Content
- Modifications (Scripting changes, additions etc)
- Size of your database
- Active Sustained User Base (How many users actively using, or posting content)


On Database intensive actions where there are going to be large amounts of read/writes at the same time, Disk IO becomes as critical as available System Memory (Cached and Pyshical as you want to minimize Swap use on any server where you can) and CPU (Processing Power)

With that said, a dedicated server (or servers for load balancing) would be needed on a very large Forum.. Im sure if you Submit a ticket (If you are an Active Customer) or Post in the Pre-sales area if you are not, someone should be able to give you a spec requirement that should meet your needs

[Bono]

MySQL is not limited by itself, it is limited by FS, in case of EXT3 that's 4TB and in EXT4 it's 16TB. With big databases as stated before you need a lot of IO and plenty of ram, so you need to run 64bit OS so you can allocate more than 2GB of ram.

http://forums.nasioc...orums/index.php

This site has less than 6 servers and if I recall correctly only two database servers. They could even cut on their servers if they switched away from apache.

[ørret]

Thanks for info. What happen when your db is 2gb (as large as it can be)? I'm sure big sites go over this limit. Do they have like 10 2GB dbs, and then a program call the different dbs, and merge them, so the forum is "complete" (using all dbs)? I guess you need like 10 dedicated servers with latest hardware to run Gaia Online, and then a hosting cost of 3000$+/month on top of it. Even this forum is large, would be nice if someone from IPB team could tell what's needed or how they run this forum.

[Bono]

Thanks for info. What happen when your db is 2gb (as large as it can be)? I'm sure big sites go over this limit. Do they have like 10 2GB dbs, and then a program call the different dbs, and merge them, so the forum is "complete" (using all dbs)? I guess you need like 10 dedicated servers with latest hardware to run Gaia Online, and then a hosting cost of 3000/month on top of it. Even this forum is large, would be nice if someone from IPB team could tell what's needed or how they run this forum.

Like I said if your board database is close to 2GB, you will not run 32bit Linux and 2.4 kernel. In 2.6 kernel and latest EXT4 database file can be 16TB big. And when you are so big you do not rent you buy your equipment and host it in your firm or collocate it. I don't know what do you consider for large forum but few millions posts and few k simultaneous visitors you can easily handle with dedicated server quad core, 4 or 8GB of ram and SAS or even SSD drives. I can guess Gaia Online database has less than 2TB database and that's really one of the biggest forums I have seen.

[ørret]

Understood now, thank you very much!

[Guest]

I've posted this a few times over the last year, but it fits here too. We've got one database server with three huge databases on it, sizes are as follows:

Database 1: 1,481,163,341 Rows, 690GB Data, 300GB Indexes
Database 2: 702,376,617 Rows, 91GB Data, 52GB Indexes
Database 3: 2,784,634,883 Rows, 170GB Data, 104GB Indexes
Total: 4,968,174,841 Rows, 951GB Data, 456GB Indexes

As a bit of image evidence:



Now, this isn't running a forum, but it is running on one machine using MySQL. You'll likely never find that MySQL becomes the bottleneck, unless it's incredibly badly configured or doesn't have enough resources available to it.

[Bono]

I've posted this a few times over the last year, but it fits here too. We've got one database server with three huge databases on it, sizes are as follows:

Database 1: 1,481,163,341 Rows, 690GB Data, 300GB Indexes

Database 2: 702,376,617 Rows, 91GB Data, 52GB Indexes

Database 3: 2,784,634,883 Rows, 170GB Data, 104GB Indexes

Total: 4,968,174,841 Rows, 951GB Data, 456GB Indexes

As a bit of image evidence:

Now, this isn't running a forum, but it is running on one machine using MySQL. You'll likely never find that MySQL becomes the bottleneck, unless it's incredibly badly configured or doesn't have enough resources available to it.

Nice one Dan, how many QPS do you have on that mysql box?

[Guest]

Nice one Dan, how many QPS do you have on that mysql box?

It varies, can be anywhere between 250 and 5,000 queries a second, primarily inserts. It has around a thousand open connections at any given time.

[MarcusInMd]

I've posted this a few times over the last year, but it fits here too. We've got one database server with three huge databases on it, sizes are as follows:

Database 1: 1,481,163,341 Rows, 690GB Data, 300GB Indexes

Database 2: 702,376,617 Rows, 91GB Data, 52GB Indexes

Database 3: 2,784,634,883 Rows, 170GB Data, 104GB Indexes

Total: 4,968,174,841 Rows, 951GB Data, 456GB Indexes

As a bit of image evidence:

Now, this isn't running a forum, but it is running on one machine using MySQL. You'll likely never find that MySQL becomes the bottleneck, unless it's incredibly badly configured or doesn't have enough resources available to it.

I wish this was true. Even on a properly configured Mysql database server table locks with Myisam become troublesome under heavy traffic (at least with IPB 3)

[Bono]

I wish this was true. Even on a properly configured Mysql database server table locks with Myisam become troublesome under heavy traffic (at least with IPB 3)

It all comes down to optimization, if server is properly optimized mysql will never become bottleneck. With my server I really push amazing numbers most people think I have 3 servers instead of one quad core, and CMS is much slower than IPB so everything is possible.

[Guest]

I wish this was true. Even on a properly configured Mysql database server table locks with Myisam become troublesome under heavy traffic (at least with IPB 3)

Well the problem there isn't MySQL, it's that you're using MyISAM where InnoDB would be more sensible. :)

[MarcusInMd]

Well the problem there isn't MySQL, it's that you're using MyISAM where InnoDB would be more sensible. :)

Yes, of course. I was speaking with regards to Myisam tables.

I managed to get around 1700 online in 15 minutes on our forum this winter with heavy posting/reading...However tables locks started crushing us. This is on one server with dual quad core CPUs and 16GB of ram (RAID 10 SAS drives). Highly optimized and running Litespeed web server.

I am in the process of trying to merge two of my topics and posts tables because of an issue I had when moving off our DB server (which was running mysql 4.1) If I can successfully do this, I may try once again converting the posts table to innodb. I didn't have any success last year, I could never get it to convert because the file is so large.

[MarcusInMd]

I am experimenting with our test forum with using INNODB again. Now that winter is over I have to do something for next year. Our members will have my head on a platter if these severe slowdowns are not addressed. :)

[MarcusInMd]

Do you guys just run the sessions table and the posts table as innodb? I was thinking of also running the topics table as innodb as well. But i'm not sure it's needed.

[MarcusInMd]

Did the import/conversion and now there are no posts inside the topics. :( Any idea why? Innodb file increased in size to double what the topics myisam file was and it took about 10 hours to complete the import process

[MarcusInMd]

I can't even select the browse table button from within phpadmin on this table. Hmm... Any ideas why?

[MarcusInMd]

OK, I dumped the posts table again and used a backup myisam table of posts. Then I went in via CLI and mysql and did a ALTER TABLE posts ENGINE=INNODB and it converted over perfectly. It took only two hours this time too.

Once I get our production database converted over I sure hope that insane server load is a thing of the past when we see 1500 to 2000+ members online and hitting the posts table.

I guess I will convert over topics and perhaps members tables as well based on some of the stuff I have dug up from past threads on this forum.

[grinler]

I was wondering if you ever moved it over to innodb and if you found any improvements?

[MarcusInMd]

I have not. I pulled out DB server in December and have not re-deployed it yet. I have not had the time to do what I need to it before I do this. I hope to have it done in the next month or so (convert to MySql 5) and then I will redeploy the database server and then convert over to innodb. I was also kinda waiting for 3.1 to be released but it's taking so long I will probably have already converted over by the time that is released.

[wiszmaster]

Hi Marcus, were you able to convert yet?