Re: Large DB Problem. - Armando - com.mysql.lists.win32

9 messages in com.mysql.lists.win32Re: Large DB Problem.

From	Sent On	Attachments
James - Developer	30 Nov 2004 13:56
John Paul Ashenfelter	30 Nov 2004 14:06
James - Developer	30 Nov 2004 15:43
James - Developer	30 Nov 2004 15:46
Armando	30 Nov 2004 18:34
John Paul Ashenfelter	30 Nov 2004 18:52
James - Developer	01 Dec 2004 12:51
James - Developer	01 Dec 2004 13:03
matt_lists	02 Dec 2004 04:24

Subject:	Re: Large DB Problem.
From:	Armando (diji...@shaw.ca)
Date:	11/30/2004 06:34:03 PM
List:	com.mysql.lists.win32

I'll have to agree with John on this one.. Even with 10 million records, your database will not bad _that_ big if you aren't using columns that are text type and storing large amounts of text within them. I have a few friends who are developing a mysql solution for one of their customers and are performing tests based on databases with 50 million records in the busiest table.

John is right though.. the key definitely is optimization. Your database and your queries as well. If you have access to a development box, perform your testing on there and optimize at will without having to mess with the production server. If your customer is that serious about the integrity of their data and server performance, they should be willing to assist you in this. You just have to make sure they understand the impact of not being proactive. Remember.. many non-technical people have misconceptions that just because you are a technical person, you automatically have all the answers, which we all know is not the case at all for any of us :-)

As John suggests, please provide some more information regarding your current database structure, particularly the busy table you're talking about, and also provide some examples of the queries you are doing. Here's some things you can provide to help us help you:

1) What kind of field types are in your table? 2) What indexes do you have in place currently? 3) What are some examples of your queries?

Also, one thing I's recommend, just to note it, is try to stay away from blobs if you can help it. They are generally more hassle than they are worth, and will significantly increase your database size and definitely slow performance. Cheers.

James - Developer wrote:

I am just testing it over an ADSL connection. My client has between 2MB-5MB leased lines in each branch and we have 10MBps guaranteed at our data centre.

The reason I'm testing with ADSL is because most of the web users will be using ADSL because it will not only be internal staff.

I think it's *far* more likely you need to spend some time on database optimzation. 10m rows is a lot, but not that big -- there are folks using MySQL for with databases over a *terabyte*. You are going to run into issues as individual tables get above 2 terabytes, but I'm guessing you're probably only in the 10-100GB range for the whole database.

That said, you might look at MERGE tables -- that way you can partiotion the table by company, for example, but still have access to the the whole thing through a merge table.

Of course all this is speculation since the email is pretty vague :) So are there particularly slow queries? And which version of everything? And how are you connecting? etc, etc.

On Tue, 30 Nov 2004 21:56:39 -0000, James - Developer <jam...@appiam.com> wrote:

Hi, wondering if anyone could please help.

I have been writing an ASP/MySQL application over the last 12 months. In the last month the company who have paid me have brought 8 other companies which do the same as what they do. So, as you can imagine I am concerned. I have a main database with around 20 tables. The busiest table / the one with potential to become the largest contains around 15 columns. I have calculated that within 6 months, this table could quite easily have 10,000,000 (10m) records or worse case 200,000,000 (200m) records. So I have run a test and carried out a bulk insert of 5,000,000 (5m) records and as you can imagine, accessing this over the web is very/too slow to be practical.

So, I need to split the database. My initial thought was to split the database by adding an extra table for every new client who joins and it is predicted to be 1,000 clients per year which is the same as the previous years. I then thought this would be the same as my initial method because MySQL has to first access one of the 1,000 tables and then one of the thousands of records which would not make any difference.

My final thought and this is my question: I have decided to add a completely new database for every client. This is not a problem because the data does not need to be relational between clients. This means having 1,000 databases/folders and I will use a DSN string with the 'database=;' value to equal the relevant client/database name. My conception is that the OS (Windows) will access any one of the 1,000 databases quicker than MySQL can and then MySQL will find the details from the relevant database which will only be 1,000th the size of my initial idea.....

Does anybody know if this is correct?

I certainly can't have one large table because the database will be too slow.

Thanks very much in advance.