Large DB Problem.

------=_NextPart_000_006D_01C4D727.785EED80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=3D;' 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.

James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com


------=_NextPart_000_006D_01C4D727.785EED80--

Re: Large DB Problem.

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 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.
>
> James Mackie
> Appiam Ltd
> Developer
> www.appiam.com
> james@appiam.com
>
>


--
John Paul Ashenfelter
CTO/Transitionpoint

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

Thanks very much for your reply.

I'm using IIS 6 with Windows 2003 Server Standard and MySQL 5.

My main problem is one web page I have written in particular. It needs to
display anything from 1 to 200 records/rows from the MySQL database. When
you update this page (pressing submit) it then needs to update every single
record on each of the 200 lines. Also the ASP file is quite large because
the web page has about 3,000 lines of code - about 60K. It works fine with
a few hundred thousand records but when I go over the million mark it can
take 30 seconds to submit the page which is too long.

So you would recommend multiple tables rather than multiple databases?

Will using MERGE on 1,000 tables be quicker than keeping everything in one
table?

Thanks in advance.

James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com
07884 494 333

----- Original Message -----
From: "John Paul Ashenfelter"
To: "James - Developer"
Cc:
Sent: Tuesday, November 30, 2004 10:06 PM
Subject: Re: Large DB Problem.


>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
> 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.
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@appiam.com
>>
>>
>
>
> --
> John Paul Ashenfelter
> CTO/Transitionpoint


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

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.


James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com
07884 494 333

----- Original Message -----
From: "John Paul Ashenfelter"
To: "James - Developer"
Cc:
Sent: Tuesday, November 30, 2004 10:06 PM
Subject: Re: Large DB Problem.


>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
> 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.
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@appiam.com
>>
>>
>
>
> --
> John Paul Ashenfelter
> CTO/Transitionpoint
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=james@appiam.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

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.

Armando

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.
>
>
> James Mackie
> Appiam Ltd
> Developer
> www.appiam.com
> james@appiam.com
> 07884 494 333
>
> ----- Original Message ----- From: "John Paul Ashenfelter"
>
> To: "James - Developer"
> Cc:
> Sent: Tuesday, November 30, 2004 10:06 PM
> Subject: Re: Large DB Problem.
>
>
>> 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
>> 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.
>>>
>>> James Mackie
>>> Appiam Ltd
>>> Developer
>>> www.appiam.com
>>> james@appiam.com
>>>
>>>
>>
>>
>> --
>> John Paul Ashenfelter
>> CTO/Transitionpoint
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe: http://lists.mysql.com/win32?unsub=james@appiam.com
>>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

One issue I'd be concerned aboutis that you're using an alpha version
of MySQL -- I'd benchmark against 4.1.7 unless there is something
specific from 5.x you're using (eg stored procs). The alpha code,
especially on Windows, is probably not optimal.

I'd also immediately take a look at sections in the docs on

INSERT http://dev.mysql.com/doc/mysql/en/Insert_speed.html and
UPDATE http://dev.mysql.com/doc/mysql/en/Update_speed.html

There are LOTS of options to improve the speed, but they are all very
situation dependent which makes general advice hard. But the key piece
of information to take away from this thread is that the size of your
table is not in-and-of-itself a problem for MySQL. Things ranging from
changing the bulk_insert_buffer setting for the database to using
temporary tables to writing a changefile to disk and using the LOAD
DATA INFILE command.

At the risk of sounding snotty, you probably need a few hours of
consulting from a MySQL specialist -- optimization is a big can of
worms to open if you've never done it. Of course if you've got the
time, it's a lot more valuable to research and try it yourself so
you're ready for the next problem :)

MERGE tables, btw, won't help you a lot -- they're much better for
organizing partitioned tables into larger tables (sort of like a view
that can only implement the UNION operator)


On Tue, 30 Nov 2004 23:44:04 -0000, James - Developer wrote:
> Thanks very much for your reply.
>
> I'm using IIS 6 with Windows 2003 Server Standard and MySQL 5.
>
> My main problem is one web page I have written in particular. It needs to
> display anything from 1 to 200 records/rows from the MySQL database. When
> you update this page (pressing submit) it then needs to update every single
> record on each of the 200 lines. Also the ASP file is quite large because
> the web page has about 3,000 lines of code - about 60K. It works fine with
> a few hundred thousand records but when I go over the million mark it can
> take 30 seconds to submit the page which is too long.
>
> So you would recommend multiple tables rather than multiple databases?
>
> Will using MERGE on 1,000 tables be quicker than keeping everything in one
> table?
>
> Thanks in advance.
>
> James Mackie
> Appiam Ltd
> Developer
> www.appiam.com
> james@appiam.com
> 07884 494 333
>
>
>
> ----- Original Message -----
> From: "John Paul Ashenfelter"
> To: "James - Developer"
> Cc:
> Sent: Tuesday, November 30, 2004 10:06 PM
> Subject: Re: Large DB Problem.
>
> >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
> > 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.
> >>
> >> James Mackie
> >> Appiam Ltd
> >> Developer
> >> www.appiam.com
> >> james@appiam.com
> >>
> >>
> >
> >
> > --
> > John Paul Ashenfelter
> > CTO/Transitionpoint
>
>


--
John Paul Ashenfelter
CTO/Transitionpoint

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

Thank you.

My nested sql statements I have been using would not work with MySQL 3 so
thank you I will use MySQL 4.1.7.

I've been reading the links you sent me! Very useful thanks.

I've just got off the phone to the MySQL department in London and have
signed up for a training course in February.

The minimum consulting they do is one day at £1,000 so will have to think
about that one!

Thanks again.

James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com
07884 494 333

----- Original Message -----
From: "John Paul Ashenfelter"
To: "James - Developer"
Cc:
Sent: Wednesday, December 01, 2004 2:53 AM
Subject: Re: Large DB Problem.


> One issue I'd be concerned aboutis that you're using an alpha version
> of MySQL -- I'd benchmark against 4.1.7 unless there is something
> specific from 5.x you're using (eg stored procs). The alpha code,
> especially on Windows, is probably not optimal.
>
> I'd also immediately take a look at sections in the docs on
>
> INSERT http://dev.mysql.com/doc/mysql/en/Insert_speed.html and
> UPDATE http://dev.mysql.com/doc/mysql/en/Update_speed.html
>
> There are LOTS of options to improve the speed, but they are all very
> situation dependent which makes general advice hard. But the key piece
> of information to take away from this thread is that the size of your
> table is not in-and-of-itself a problem for MySQL. Things ranging from
> changing the bulk_insert_buffer setting for the database to using
> temporary tables to writing a changefile to disk and using the LOAD
> DATA INFILE command.
>
> At the risk of sounding snotty, you probably need a few hours of
> consulting from a MySQL specialist -- optimization is a big can of
> worms to open if you've never done it. Of course if you've got the
> time, it's a lot more valuable to research and try it yourself so
> you're ready for the next problem :)
>
> MERGE tables, btw, won't help you a lot -- they're much better for
> organizing partitioned tables into larger tables (sort of like a view
> that can only implement the UNION operator)
>
>
> On Tue, 30 Nov 2004 23:44:04 -0000, James - Developer
> wrote:
>> Thanks very much for your reply.
>>
>> I'm using IIS 6 with Windows 2003 Server Standard and MySQL 5.
>>
>> My main problem is one web page I have written in particular. It needs
>> to
>> display anything from 1 to 200 records/rows from the MySQL database.
>> When
>> you update this page (pressing submit) it then needs to update every
>> single
>> record on each of the 200 lines. Also the ASP file is quite large
>> because
>> the web page has about 3,000 lines of code - about 60K. It works fine
>> with
>> a few hundred thousand records but when I go over the million mark it can
>> take 30 seconds to submit the page which is too long.
>>
>> So you would recommend multiple tables rather than multiple databases?
>>
>> Will using MERGE on 1,000 tables be quicker than keeping everything in
>> one
>> table?
>>
>> Thanks in advance.
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@appiam.com
>> 07884 494 333
>>
>>
>>
>> ----- Original Message -----
>> From: "John Paul Ashenfelter"
>> To: "James - Developer"
>> Cc:
>> Sent: Tuesday, November 30, 2004 10:06 PM
>> Subject: Re: Large DB Problem.
>>
>> >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
>> >
>> > 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.
>> >>
>> >> James Mackie
>> >> Appiam Ltd
>> >> Developer
>> >> www.appiam.com
>> >> james@appiam.com
>> >>
>> >>
>> >
>> >
>> > --
>> > John Paul Ashenfelter
>> > CTO/Transitionpoint
>>
>>
>
>
> --
> John Paul Ashenfelter
> CTO/Transitionpoint


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

Thanks Armando.

10,000,000 is what I expect but I could have 200,000,000.

Field types - 13 are varchars(50) and 2 are int(10).

I only have one index on each table which is the unique auto-generated id.

Although this is not the most commonly searched field.

Example queries:

Many of them are like this:

ArrayMySRef = Split(mySRef,",")
ArrayMySur = Split(mySur,",")
ArrayMyDra = Split(myDra,",")
ArrayMyPre = Split(myPre,",")
ArrayMySor = Split(mySor,",")
ArrayMyAmo = Split(myAmo,",")
ArrayMyBlk = Split(myBlk,",")
ArrayMyLoc = Split(myLoc,",")
ArrayMyItm = Split(myItm,",")

IF myType = "2" OR myType = "3" AND myCon <> "" THEN
myMax = UBound(ArrayMySRef)
FOR i = 0 TO myMax
sql99 = "UPDATE tblMulti SET CID1 = '"&myID1&"', CID2 = '"&myID2&"', Rep =
'"&myRep&"', SRef = '"&TRIM(ArrayMySRef(I))&"', "&_
"Sur = '"&TRIM(ArrayMySur(I))&"', Dra = '"&TRIM(ArrayMyDra(I))&"', Pre =
'"&ArrayMyPre(I)&"', Sor = '"&TRIM(ArrayMySor(I))&"', "&_
"Amo = '"&TRIM(ArrayMyAmo(I))&"', Con = '"&TRIM(ArrayMyCon(I))&"', FLX =
'"&TRIM(ArrayMyFLX(I))&"' WHERE SMID = '"&ArrayMyIDX(I)&"'"
Con.Execute sql99
NEXT
END IF

The reason being is that the page has a form with many rows and many colums.
When the form is sent, I have to SPLIT the variables - I think this is what
takes the time to be honest.

Thanks again for your help.

James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com
07884 494 333

----- Original Message -----
From: "Armando"
To:
Sent: Wednesday, December 01, 2004 2:34 AM
Subject: Re: Large DB Problem.


> 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.
>
> Armando
>
> 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.
>>
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@appiam.com
>> 07884 494 333
>>
>> ----- Original Message ----- From: "John Paul Ashenfelter"
>>
>> To: "James - Developer"
>> Cc:
>> Sent: Tuesday, November 30, 2004 10:06 PM
>> Subject: Re: Large DB Problem.
>>
>>
>>> 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
>>> 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.
>>>>
>>>> James Mackie
>>>> Appiam Ltd
>>>> Developer
>>>> www.appiam.com
>>>> james@appiam.com
>>>>
>>>>
>>>
>>>
>>> --
>>> John Paul Ashenfelter
>>> CTO/Transitionpoint
>>>
>>> --
>>> MySQL Windows Mailing List
>>> For list archives: http://lists.mysql.com/win32
>>> To unsubscribe: http://lists.mysql.com/win32?unsub=james@appiam.com
>>>
>>
>>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=james@appiam.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Large DB Problem.

Just hope you never have a table crash

I've got a 9 gig table that's been restoring a week, and it's not done yet

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org