FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Mysql database size?





barremob
I'm building a site with php and a mysql database.

I already know that the site after a while wil grow to a loth of information.

Now how big may the database be, so it will not lose any speed and reliability.

Is the also a posibility that I lose information on any way?

In case this is posibel what do I use than bether?

Greetz
DX-Blog
How large a table can be depends on the OS being used:
Linux 2.2-Intel 32-bit - 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) - 4TB
Linux 2.4+ 64-bit (using XFS filesystem) - approx 8m TB
Solaris 9/10 - 16TB
NetWare w/NSS filesystem - 8TB
Win32 w/ FAT/FAT32 - 2GB/4GB
Win32 w/ NTFS - 2TB (possibly larger)
MacOS X w/ HFS+ - 2TB


As you can see, size is something you shouldn't worry about. This is just per table, so it can grow even larger. Only if you are running a major forum you something might exceed the limit if the server is running on an old linux version or windows with fat(32).


As for speed, mwuah. Site contents itself in general won't reach a stage in which optimization will truly be needed. If your database would manage to become 100mb by just the content of the site it still won't have a drastic effect on speed.

Once you hit 2GB or so you will start noting the difference between optimized and non-optimized. Especially once databases become over 10-20GB you will see a drastic change in speed, you'll also want to prefer just archiving old stuff then instead of keeping it along with the active database. This is also something you can see on major forums and all, where old posts are just being archived instead.

You can lose information yes, therefoer you should always remember to make backups from time to time. You could even set up an automated system for this so a backup would get mailed to you daily in example, kinda depending on how big it is. Mails of 200MB will no longer be very effective.

There are alternatives, but all have their pro's and their con's. In general MySQL will be good enough.
Rhysige
The hardest hit in speed is the number of queries you do. For example the site im working with at the moment had one page with 4,000 queries on one particular page. And a standard pageload of 60 ish minimum, with this number of queries hitting the database every page load from 120 minimum active members at any particular time it starts to lag a bit.
DX-Blog
Rhysige wrote:
The hardest hit in speed is the number of queries you do. For example the site im working with at the moment had one page with 4,000 queries on one particular page. And a standard pageload of 60 ish minimum, with this number of queries hitting the database every page load from 120 minimum active members at any particular time it starts to lag a bit.

4000 queries on one particular page? Not really efficient if you'ld ask me.... I'm pretty sure that that can be reduced by a lot.

4000 queries is technically what one could use to display the contents of 4000 seperate tables. Seriously makes me wonder what the hell you are doing. Personally I think that anything over 10 queries per page is already a killer on speed once you get more active people at once.

If you would get 1000 people on your site you would get 4 million queries to be requested, per page switch? No way that will work out nicely, no matter what optimization you would try to use on that.
Rhysige
Does make you wonder but it cant be optimised its all needed Razz although I did recently break down a 3k query page into smaller chunks Smile

But if you take into account querying the DB for each row with specific details and looping them into a display... it adds up
DX-Blog
Rhysige wrote:
Does make you wonder but it cant be optimised its all needed Razz although I did recently break down a 3k query page into smaller chunks Smile

But if you take into account querying the DB for each row with specific details and looping them into a display... it adds up

Why would you want to query the db for each row with specific details? Continiously connecting and disconnecting a session is hell for speed, you could better just select everything out of the table instead and then further on with whatever you're using this for to do further selections. Perhaps you'll download more data when it comes to size, but still it will be much faster since you'll have to perform much less queries :S.
Rhysige
We are talking downloading 100 rows from a few hundred thousand row table Razz I think not downloading the whole table is a smart idea. For small table sure but when youve got that many...
BearClaw
ok, this has peaked my curiosity

first off...4,000 queries? seriously? what exactly are you up too....

secondly...i'd like to see that source code.

thirdly...depending on the use, it may be more efficient to download the whole table. that way php can parse through the data and eliminate the need for php to communicate with the database.
Grimboy
BearClaw: I don't think so, it's nearly always more effecient to make the database to it. If the whole table is requested it all has to be moved into ram and then php has to grind through it. The database often uses tricks to be faster, that's one of the points of the database. It makes stuff optimised and does this transparently.

Anyway, it think the time a query on table takes grows logrythmically to the size... or something.
BearClaw
Grimboy, normally I would agree. But the gross amount of queries made, that means that php has to query the database 4k times and the database has to return the data 4k times essentially doubling the number of processes. when it comes to something that large you'd prolly have to try it both ways and see what the different processing times are.
Related topics
Cron Job to Backup MySQL Database
Mysql database problem
PHPBB MYSQL Database Question.
MySQL database for a forum
Database size?
Connect to MySql database
[MySQL database] Password and username
insert data to mySQL database problem
MySQL Database!?
Delete data from MySQL database problem!!
can we increase mysql field size more than 255 cahrs
Connecting to 2 different Mysql database and tables?
Maximum MYSQL database size
mysql file size increase
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.