haw many tables/ table size before system crash?

narked

I have a website which generates a lot of tables (it has now like 1,600 and growing) and I one table wich has like 1,600 entries (and growing).

my hosting company reported a crash in the systems and they say its my fault.
I had 50 simultaneous users in my page at that time.

my question is:
is it ok to have that many tables? if I reach like 10,000 whats going to happen? and about my other table (the one with a lot of entries) if it reaches 10,000 rows with 7 columns and then I perform a php script to show only the entries with certain information. and lets say 100 persons areusing that table to request rows of data, is it going to take like FOREVER to load and the system is going to crash?

Im curious about this because I guess sites like hotmail.com forexample must have like billions of rows in their database tables.
so I need to know if my webpage is going tohandle so much processing.

Im thinking in changing to a dedicated server instead of a shared one, maybe that way the system is going to handle it..?

plase help
thanks

edwardsbc

Not sure where to start here.

All computers have resource limits (CPU, Memory, Harddisk space) and using them all will result in a crash (or at least an error condition). This could have happened in your case.

You didn't say what RDBMS you are using, but they have limits too. The number of databases, tables, indexes, and rows (among other things) all have limits. Again, depending on the database system, this could cause a crash or error condition.

If your database server is not severly memory constrained, then 10,000 rows in a table is not an issue. Probably 10,000,000 rows can be handled on modern hardware with out much sweating (RAM being the primary resource required). But it really depends on the complixity of the data manipulations you are performing on those tables. Simple (or even fairly complex) lookups on a single table will be very fast, even with many rows. JOINs and sub-queries will slow things down, sometimes drastically.

I have web server being fed data from a dual Athlon 2600+ with 2GB ram which routinely returns results from a six table join in about 5-15 seconds. The tables have between 10,000 and 100,000 rows. I can't even measure the time it takes to return a single table query (it is under one second) even from my largest table.

Personnaly, I would rather manage rows than tables. Without knowing more about the data you are manipulating I can't be much more help. I would guess having so many tables is less efficient than having more rows, but you will have to get the RDBMS designers to answer that question.

narked

thanks for your reply edwardsbc, Im using mysql and php. I dont know really whats my limit for my rows, indexes etc.. Is there a way I could find that out? my service provider said "unlimited databases".
My webpage generates some charts (using php graph) and maybe all the programing I have is to intensive for the computers they are using. Im thinking in getting a dedicated server with athlon 2000 256mb sdram 40 GB hardrive. do you think that with this configuration I could make an intensive sql usage lets say 5 webpages each with 50,000 tables and 100 simultaneous users for each site? maybe 256 of ram is too little but the next plan is so way higher in price and it has 512.

and another question, I read a posting saying that is very important to index tables. I use unique ids but just by puting a unique id column on each table it dont means is indexed is it? do you know where can I read about indexing tables etc etc..

thanks a lot again

ultraslacker

A website generating tables? 1600 tables and growing? Why is it growing? What is your database design?

I use unique ids but just by puting a unique id column on each table it dont means is indexed is it?

Yes, a unique index is generated for that column. Useful in joins, but something tells me you are not using them...

narked

my page is about interactive exams. teachers need to see what questions each person answered. so for each exam someone answers I create a table with the question id, boolean if it was answered, the anwer the person gave, and finally a yes or no if the answer was correct.
my tables are named this way:
exam$examid_$personid
so thats how I can acces each table individually.

by the index question I mean I manually create a column named xxx_id and put autoincremend and unique in it. but I noticed a button in phpMyAdmin that says create an index on "x" columns.

a join? I have never heared about that

narked

Ok, now I have the server specifications of the machine that was running my page when it crashed. those idiots want to end my service!

Dell Quad Xeon 2.0 Ghz, 1.5GB RAM

they use one of this servers for 300 shared hosting accounts.
is that ok? or they are overpopulating the servers?