hi all...

from http://dev.mysql.com/doc/refman/5.0/en/features.html:

"Handles large databases. We use MySQL Server with databases that contain
50 million records. We also know of users who use MySQL Server with 60,000
tables and about 5,000,000,000 rows."

that's cool but i assume this is distributed over a few machines...

we have a new client that needs a table with 99 000 000 rows, 2 -3 columns.
i was just wondering if i have a two dual core 2 processors in a machine
with 4 gigs of ram - is that enough to host and serve queries from a table
of this size?
a few tables on the same machine?
more than one machine?
what are the query times like?

can somebody please share some/any experience s/he has/had with managing
databases/tables with that amount of records. i'd really appreciate it...


thanks a lot....

> The answer depends upon the actual queries and/or how much data is being
> returned.

there is ALWAYS only one record found/returned per query.... it's like
looking up one unique id.... it's like checking if number 5893786 is there
in row number one... or something like number 5893786 - a unique id...



> trying to answer only based upon row count is NONSENSICAL, IMO.
>
>
> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>
>> hi all...
>>
>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>
>> "Handles large databases. We use MySQL Server with databases that
>> contain
>> 50 million records. We also know of users who use MySQL Server with
>> 60,000
>> tables and about 5,000,000,000 rows."
>>
>> that's cool but i assume this is distributed over a few machines...
>>
>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>> columns.
>> i was just wondering if i have a two dual core 2 processors in a machine
>> with 4 gigs of ram - is that enough to host and serve queries from a
>> table
>> of this size?
>> a few tables on the same machine?
>> more than one machine?
>> what are the query times like?
>>
>> can somebody please share some/any experience s/he has/had with managing
>> databases/tables with that amount of records. i'd really appreciate
>> it...
>>
>>
>> thanks a lot....
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=sol.beach@gmail.com
>>
>>
>

On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>
> hi all...
>
> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>
> "Handles large databases. We use MySQL Server with databases that contain
> 50 million records. We also know of users who use MySQL Server with 60,000
> tables and about 5,000,000,000 rows."
>
> that's cool but i assume this is distributed over a few machines...
>
> we have a new client that needs a table with 99 000 000 rows, 2 -3
> columns.
> i was just wondering if i have a two dual core 2 processors in a machine
> with 4 gigs of ram - is that enough to host and serve queries from a table
> of this size?
> a few tables on the same machine?
> more than one machine?
> what are the query times like?
>
> can somebody please share some/any experience s/he has/had with managing
> databases/tables with that amount of records. i'd really appreciate it...


99 million isn't that large of a number.

If you key the database properly, search times should be very modest. I
can't speak for insert times, though, especially when keys are involved.

This kind of thing is easy enough to do in your favorite scripting
language. I would just create a table with a few keys and just for($i=0;
$i<99000000; $i++) it with random numbers.

If you have PHP on your system, here is some PHP code (runnable from the
command line) that you should be able to hack down. It should answer your
immediate questions about which PHP statements to use (if you've never done
this from PHP before):

http://gpl.e3ft.com/vcvsgpl01/viewcv...viewcvs-markup

http://gpl.e3ft.com/vcvsgpl01/viewcv...viewcvs-markup

Near the end of it, especially if the software writes output, you should get
an intuitive feel for how long each INSERT is taking.

You can even do test queries using the barebones MySQL client ... you should
see interactively how long a query takes.

I would ALMOST do this for you, but it is just beyond the threshold of what
I'd do because I'm bored and watching TV. I'm just a little curious
myself. I've never messed with a table about 10,000 rows or so.

Dave

hi david.. thanks...

i've done this many times and yes either trough php, perl, python or on
the mysql cl client. but my question here is not about doing it and insert
times it's more about hosting it and query times. i currently have a
working table for the same purpose with about 1.5 million records in it.
and the thing runs smoot on a machine that is 4 years old with 1 gig of
ram and 2.8 ghz ghz processor. the thing is that now i'm talking about
this x 100 times. more or less. i'm not worried about the insert times -
this happens only ones and for a million entries, depending on what
technic is used, it takes no longer than a few minutes.
what i was asking basically was somebody to share experience with running
a server with that amount of records in one table.

currently the table i have has a size of 65 mgb which by 100 is about 6600
mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to
successfully use a table like that. either that or cluster 2 machines with
4 gigs each and split the table. does this sound reasonable? is my logic
flawed somehow?

i'll appreciate any comments on this subject .... thanks...



> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>
>> hi all...
>>
>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>
>> "Handles large databases. We use MySQL Server with databases that
>> contain
>> 50 million records. We also know of users who use MySQL Server with
>> 60,000
>> tables and about 5,000,000,000 rows."
>>
>> that's cool but i assume this is distributed over a few machines...
>>
>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>> columns.
>> i was just wondering if i have a two dual core 2 processors in a machine
>> with 4 gigs of ram - is that enough to host and serve queries from a
>> table
>> of this size?
>> a few tables on the same machine?
>> more than one machine?
>> what are the query times like?
>>
>> can somebody please share some/any experience s/he has/had with managing
>> databases/tables with that amount of records. i'd really appreciate
>> it...
>
>
> 99 million isn't that large of a number.
>
> If you key the database properly, search times should be very modest. I
> can't speak for insert times, though, especially when keys are involved.
>
> This kind of thing is easy enough to do in your favorite scripting
> language. I would just create a table with a few keys and just for($i=0;
> $i<99000000; $i++) it with random numbers.
>
> If you have PHP on your system, here is some PHP code (runnable from the
> command line) that you should be able to hack down. It should answer your
> immediate questions about which PHP statements to use (if you've never
> done
> this from PHP before):
>
> http://gpl.e3ft.com/vcvsgpl01/viewcv...viewcvs-markup
>
> http://gpl.e3ft.com/vcvsgpl01/viewcv...viewcvs-markup
>
> Near the end of it, especially if the software writes output, you should
> get
> an intuitive feel for how long each INSERT is taking.
>
> You can even do test queries using the barebones MySQL client ... you
> should
> see interactively how long a query takes.
>
> I would ALMOST do this for you, but it is just beyond the threshold of
> what
> I'd do because I'm bored and watching TV. I'm just a little curious
> myself. I've never messed with a table about 10,000 rows or so.
>
> Dave
>

I guess a lot of that depends what an acceptable query execution time for
you is.
Also, what else does the machine do, are there other databases or tables
that are queried at the same time, do you have to join other tables in for
your queries, etc?

Olaf


On 6/12/07 3:24 AM, "kalin mintchev" <kalin@el.net> wrote:

>
> hi david.. thanks...
>
> i've done this many times and yes either trough php, perl, python or on
> the mysql cl client. but my question here is not about doing it and insert
> times it's more about hosting it and query times. i currently have a
> working table for the same purpose with about 1.5 million records in it.
> and the thing runs smoot on a machine that is 4 years old with 1 gig of
> ram and 2.8 ghz ghz processor. the thing is that now i'm talking about
> this x 100 times. more or less. i'm not worried about the insert times -
> this happens only ones and for a million entries, depending on what
> technic is used, it takes no longer than a few minutes.
> what i was asking basically was somebody to share experience with running
> a server with that amount of records in one table.
>
> currently the table i have has a size of 65 mgb which by 100 is about 6600
> mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to
> successfully use a table like that. either that or cluster 2 machines with
> 4 gigs each and split the table. does this sound reasonable? is my logic
> flawed somehow?
>
> i'll appreciate any comments on this subject .... thanks...
>
>
>
>> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>>
>>> hi all...
>>>
>>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>>
>>> "Handles large databases. We use MySQL Server with databases that
>>> contain
>>> 50 million records. We also know of users who use MySQL Server with
>>> 60,000
>>> tables and about 5,000,000,000 rows."
>>>
>>> that's cool but i assume this is distributed over a few machines...
>>>
>>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>>> columns.
>>> i was just wondering if i have a two dual core 2 processors in a machine
>>> with 4 gigs of ram - is that enough to host and serve queries from a
>>> table
>>> of this size?
>>> a few tables on the same machine?
>>> more than one machine?
>>> what are the query times like?
>>>
>>> can somebody please share some/any experience s/he has/had with managing
>>> databases/tables with that amount of records. i'd really appreciate
>>> it...
>>
>>
>> 99 million isn't that large of a number.
>>
>> If you key the database properly, search times should be very modest. I
>> can't speak for insert times, though, especially when keys are involved.
>>
>> This kind of thing is easy enough to do in your favorite scripting
>> language. I would just create a table with a few keys and just for($i=0;
>> $i<99000000; $i++) it with random numbers.
>>
>> If you have PHP on your system, here is some PHP code (runnable from the
>> command line) that you should be able to hack down. It should answer your
>> immediate questions about which PHP statements to use (if you've never
>> done
>> this from PHP before):
>>
>> http://gpl.e3ft.com/vcvsgpl01/viewcv...me/sw/standalo
>> ne/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup
>>
>> http://gpl.e3ft.com/vcvsgpl01/viewcv...me/sw/phplib/u
>> srs.inc?rev=1.11&content-type=text/vnd.viewcvs-markup
>>
>> Near the end of it, especially if the software writes output, you should
>> get
>> an intuitive feel for how long each INSERT is taking.
>>
>> You can even do test queries using the barebones MySQL client ... you
>> should
>> see interactively how long a query takes.
>>
>> I would ALMOST do this for you, but it is just beyond the threshold of
>> what
>> I'd do because I'm bored and watching TV. I'm just a little curious
>> myself. I've never messed with a table about 10,000 rows or so.
>>
>> Dave
>>
>
>

> I guess a lot of that depends what an acceptable query execution time for
> you is.

well... i don't really know. 30 secs maximum?! i've never worked with
such huge tables. 3 - 5 million records is fine but i've never worked on a
db with a table with 100 000 000 records.


> Also, what else does the machine do, are there other databases or tables
> that are queried at the same time, do you have to join other tables in for
> your queries, etc?

that would be installed on a separate machine that might run only that
project. so yea there will be queries to other tables but only after the
result of the 99 million table returns.
there are no join requests with the 99 m table.

my calculation was mostly based on resources - like ram. like i mentioned
earlier the .MYD and .MYI files together on the current one that i have -
which has about 1.2 million records - are 90 mgb.
are the .MYI files kept in ram or both .MYD and .MYI?

multiplying 90x100 is what the size of the MYI + MYD will be, right?
is that all living in ram?

thanks....




> Olaf
>
>
> On 6/12/07 3:24 AM, "kalin mintchev" <kalin@el.net> wrote:
>
>>
>> hi david.. thanks...
>>
>> i've done this many times and yes either trough php, perl, python or on
>> the mysql cl client. but my question here is not about doing it and
>> insert
>> times it's more about hosting it and query times. i currently have a
>> working table for the same purpose with about 1.5 million records in it.
>> and the thing runs smoot on a machine that is 4 years old with 1 gig of
>> ram and 2.8 ghz ghz processor. the thing is that now i'm talking about
>> this x 100 times. more or less. i'm not worried about the insert times -
>> this happens only ones and for a million entries, depending on what
>> technic is used, it takes no longer than a few minutes.
>> what i was asking basically was somebody to share experience with
>> running
>> a server with that amount of records in one table.
>>
>> currently the table i have has a size of 65 mgb which by 100 is about
>> 6600
>> mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to
>> successfully use a table like that. either that or cluster 2 machines
>> with
>> 4 gigs each and split the table. does this sound reasonable? is my logic
>> flawed somehow?
>>
>> i'll appreciate any comments on this subject .... thanks...
>>
>>
>>
>>> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>>>
>>>> hi all...
>>>>
>>>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>>>
>>>> "Handles large databases. We use MySQL Server with databases that
>>>> contain
>>>> 50 million records. We also know of users who use MySQL Server with
>>>> 60,000
>>>> tables and about 5,000,000,000 rows."
>>>>
>>>> that's cool but i assume this is distributed over a few machines...
>>>>
>>>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>>>> columns.
>>>> i was just wondering if i have a two dual core 2 processors in a
>>>> machine
>>>> with 4 gigs of ram - is that enough to host and serve queries from a
>>>> table
>>>> of this size?
>>>> a few tables on the same machine?
>>>> more than one machine?
>>>> what are the query times like?
>>>>
>>>> can somebody please share some/any experience s/he has/had with
>>>> managing
>>>> databases/tables with that amount of records. i'd really appreciate
>>>> it...
>>>
>>>
>>> 99 million isn't that large of a number.
>>>
>>> If you key the database properly, search times should be very modest.
>>> I
>>> can't speak for insert times, though, especially when keys are
>>> involved.
>>>
>>> This kind of thing is easy enough to do in your favorite scripting
>>> language. I would just create a table with a few keys and just
>>> for($i=0;
>>> $i<99000000; $i++) it with random numbers.
>>>
>>> If you have PHP on your system, here is some PHP code (runnable from
>>> the
>>> command line) that you should be able to hack down. It should answer
>>> your
>>> immediate questions about which PHP statements to use (if you've never
>>> done
>>> this from PHP before):
>>>
>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...me/sw/standalo
>>> ne/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup
>>>
>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...me/sw/phplib/u
>>> srs.inc?rev=1.11&content-type=text/vnd.viewcvs-markup
>>>
>>> Near the end of it, especially if the software writes output, you
>>> should
>>> get
>>> an intuitive feel for how long each INSERT is taking.
>>>
>>> You can even do test queries using the barebones MySQL client ... you
>>> should
>>> see interactively how long a query takes.
>>>
>>> I would ALMOST do this for you, but it is just beyond the threshold of
>>> what
>>> I'd do because I'm bored and watching TV. I'm just a little curious
>>> myself. I've never messed with a table about 10,000 rows or so.
>>>
>>> Dave
>>>
>>
>>
>
>
>

On Tue, Jun 12, 2007 at 11:09:41AM -0400, kalin mintchev wrote:
> > I guess a lot of that depends what an acceptable query execution time for
> > you is.
>
> well... i don't really know. 30 secs maximum?! i've never worked with
> such huge tables. 3 - 5 million records is fine but i've never worked on a
> db with a table with 100 000 000 records.

I wouldn't worry about it. I have a table here with nearly a billion
rows, and it fetches based on the primary key in "0.00 sec".

> my calculation was mostly based on resources - like ram. like i mentioned
> earlier the .MYD and .MYI files together on the current one that i have -
> which has about 1.2 million records - are 90 mgb.
> are the .MYI files kept in ram or both .MYD and .MYI?

Neither of them are kept in RAM. You want a reasonable amount of
memory for buffers, query cacheing, etc, but you don't need to hold
any of the files in memory.

My main concern with large tables is disk space, especially if you're
ever going to want to do ALTER TABLE, when you will need enough free
space to hold the existing and new copies of the table on disk at the
same time.

You should be fine. 100 000 000 is not that much.
Just make sure you set the right keys (and then query by them) on the table
and even more importantly, set caches and buffers to utilize your RAM
appropriately.

Olaf


On 6/12/07 11:09 AM, "kalin mintchev" <kalin@el.net> wrote:

>> I guess a lot of that depends what an acceptable query execution time for
>> you is.
>
> well... i don't really know. 30 secs maximum?! i've never worked with
> such huge tables. 3 - 5 million records is fine but i've never worked on a
> db with a table with 100 000 000 records.
>
>
>> Also, what else does the machine do, are there other databases or tables
>> that are queried at the same time, do you have to join other tables in for
>> your queries, etc?
>
> that would be installed on a separate machine that might run only that
> project. so yea there will be queries to other tables but only after the
> result of the 99 million table returns.
> there are no join requests with the 99 m table.
>
> my calculation was mostly based on resources - like ram. like i mentioned
> earlier the .MYD and .MYI files together on the current one that i have -
> which has about 1.2 million records - are 90 mgb.
> are the .MYI files kept in ram or both .MYD and .MYI?
>
> multiplying 90x100 is what the size of the MYI + MYD will be, right?
> is that all living in ram?
>
> thanks....
>
>
>
>
>> Olaf
>>
>>
>> On 6/12/07 3:24 AM, "kalin mintchev" <kalin@el.net> wrote:
>>
>>>
>>> hi david.. thanks...
>>>
>>> i've done this many times and yes either trough php, perl, python or on
>>> the mysql cl client. but my question here is not about doing it and
>>> insert
>>> times it's more about hosting it and query times. i currently have a
>>> working table for the same purpose with about 1.5 million records in it.
>>> and the thing runs smoot on a machine that is 4 years old with 1 gig of
>>> ram and 2.8 ghz ghz processor. the thing is that now i'm talking about
>>> this x 100 times. more or less. i'm not worried about the insert times -
>>> this happens only ones and for a million entries, depending on what
>>> technic is used, it takes no longer than a few minutes.
>>> what i was asking basically was somebody to share experience with
>>> running
>>> a server with that amount of records in one table.
>>>
>>> currently the table i have has a size of 65 mgb which by 100 is about
>>> 6600
>>> mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram to
>>> successfully use a table like that. either that or cluster 2 machines
>>> with
>>> 4 gigs each and split the table. does this sound reasonable? is my logic
>>> flawed somehow?
>>>
>>> i'll appreciate any comments on this subject .... thanks...
>>>
>>>
>>>
>>>> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>>>>
>>>>> hi all...
>>>>>
>>>>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>>>>
>>>>> "Handles large databases. We use MySQL Server with databases that
>>>>> contain
>>>>> 50 million records. We also know of users who use MySQL Server with
>>>>> 60,000
>>>>> tables and about 5,000,000,000 rows."
>>>>>
>>>>> that's cool but i assume this is distributed over a few machines...
>>>>>
>>>>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>>>>> columns.
>>>>> i was just wondering if i have a two dual core 2 processors in a
>>>>> machine
>>>>> with 4 gigs of ram - is that enough to host and serve queries from a
>>>>> table
>>>>> of this size?
>>>>> a few tables on the same machine?
>>>>> more than one machine?
>>>>> what are the query times like?
>>>>>
>>>>> can somebody please share some/any experience s/he has/had with
>>>>> managing
>>>>> databases/tables with that amount of records. i'd really appreciate
>>>>> it...
>>>>
>>>>
>>>> 99 million isn't that large of a number.
>>>>
>>>> If you key the database properly, search times should be very modest.
>>>> I
>>>> can't speak for insert times, though, especially when keys are
>>>> involved.
>>>>
>>>> This kind of thing is easy enough to do in your favorite scripting
>>>> language. I would just create a table with a few keys and just
>>>> for($i=0;
>>>> $i<99000000; $i++) it with random numbers.
>>>>
>>>> If you have PHP on your system, here is some PHP code (runnable from
>>>> the
>>>> command line) that you should be able to hack down. It should answer
>>>> your
>>>> immediate questions about which PHP statements to use (if you've never
>>>> done
>>>> this from PHP before):
>>>>
>>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...rime/sw/standa
>>>> lo
>>>> ne/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup
>>>>
>>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...rime/sw/phplib
>>>> /u
>>>> srs.inc?rev=1.11&content-type=text/vnd.viewcvs-markup
>>>>
>>>> Near the end of it, especially if the software writes output, you
>>>> should
>>>> get
>>>> an intuitive feel for how long each INSERT is taking.
>>>>
>>>> You can even do test queries using the barebones MySQL client ... you
>>>> should
>>>> see interactively how long a query takes.
>>>>
>>>> I would ALMOST do this for you, but it is just beyond the threshold of
>>>> what
>>>> I'd do because I'm bored and watching TV. I'm just a little curious
>>>> myself. I've never messed with a table about 10,000 rows or so.
>>>>
>>>> Dave
>>>>
>>>
>>>
>>
>>
>>
>
>

> You should be fine. 100 000 000 is not that much.
> Just make sure you set the right keys (and then query by them) on the
> table
> and even more importantly, set caches and buffers to utilize your RAM
> appropriately.

thanks.....



> Olaf
>
>
> On 6/12/07 11:09 AM, "kalin mintchev" <kalin@el.net> wrote:
>
>>> I guess a lot of that depends what an acceptable query execution time
>>> for
>>> you is.
>>
>> well... i don't really know. 30 secs maximum?! i've never worked with
>> such huge tables. 3 - 5 million records is fine but i've never worked on
>> a
>> db with a table with 100 000 000 records.
>>
>>
>>> Also, what else does the machine do, are there other databases or
>>> tables
>>> that are queried at the same time, do you have to join other tables in
>>> for
>>> your queries, etc?
>>
>> that would be installed on a separate machine that might run only that
>> project. so yea there will be queries to other tables but only after the
>> result of the 99 million table returns.
>> there are no join requests with the 99 m table.
>>
>> my calculation was mostly based on resources - like ram. like i
>> mentioned
>> earlier the .MYD and .MYI files together on the current one that i have
>> -
>> which has about 1.2 million records - are 90 mgb.
>> are the .MYI files kept in ram or both .MYD and .MYI?
>>
>> multiplying 90x100 is what the size of the MYI + MYD will be, right?
>> is that all living in ram?
>>
>> thanks....
>>
>>
>>
>>
>>> Olaf
>>>
>>>
>>> On 6/12/07 3:24 AM, "kalin mintchev" <kalin@el.net> wrote:
>>>
>>>>
>>>> hi david.. thanks...
>>>>
>>>> i've done this many times and yes either trough php, perl, python or
>>>> on
>>>> the mysql cl client. but my question here is not about doing it and
>>>> insert
>>>> times it's more about hosting it and query times. i currently have a
>>>> working table for the same purpose with about 1.5 million records in
>>>> it.
>>>> and the thing runs smoot on a machine that is 4 years old with 1 gig
>>>> of
>>>> ram and 2.8 ghz ghz processor. the thing is that now i'm talking about
>>>> this x 100 times. more or less. i'm not worried about the insert times
>>>> -
>>>> this happens only ones and for a million entries, depending on what
>>>> technic is used, it takes no longer than a few minutes.
>>>> what i was asking basically was somebody to share experience with
>>>> running
>>>> a server with that amount of records in one table.
>>>>
>>>> currently the table i have has a size of 65 mgb which by 100 is about
>>>> 6600
>>>> mgb or 6.6 gigs. which means that i have to have about 8 gigs of ram
>>>> to
>>>> successfully use a table like that. either that or cluster 2 machines
>>>> with
>>>> 4 gigs each and split the table. does this sound reasonable? is my
>>>> logic
>>>> flawed somehow?
>>>>
>>>> i'll appreciate any comments on this subject .... thanks...
>>>>
>>>>
>>>>
>>>>> On 6/11/07, kalin mintchev <kalin@el.net> wrote:
>>>>>>
>>>>>> hi all...
>>>>>>
>>>>>> from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>>>>>>
>>>>>> "Handles large databases. We use MySQL Server with databases that
>>>>>> contain
>>>>>> 50 million records. We also know of users who use MySQL Server with
>>>>>> 60,000
>>>>>> tables and about 5,000,000,000 rows."
>>>>>>
>>>>>> that's cool but i assume this is distributed over a few machines...
>>>>>>
>>>>>> we have a new client that needs a table with 99 000 000 rows, 2 -3
>>>>>> columns.
>>>>>> i was just wondering if i have a two dual core 2 processors in a
>>>>>> machine
>>>>>> with 4 gigs of ram - is that enough to host and serve queries from a
>>>>>> table
>>>>>> of this size?
>>>>>> a few tables on the same machine?
>>>>>> more than one machine?
>>>>>> what are the query times like?
>>>>>>
>>>>>> can somebody please share some/any experience s/he has/had with
>>>>>> managing
>>>>>> databases/tables with that amount of records. i'd really appreciate
>>>>>> it...
>>>>>
>>>>>
>>>>> 99 million isn't that large of a number.
>>>>>
>>>>> If you key the database properly, search times should be very modest.
>>>>> I
>>>>> can't speak for insert times, though, especially when keys are
>>>>> involved.
>>>>>
>>>>> This kind of thing is easy enough to do in your favorite scripting
>>>>> language. I would just create a table with a few keys and just
>>>>> for($i=0;
>>>>> $i<99000000; $i++) it with random numbers.
>>>>>
>>>>> If you have PHP on your system, here is some PHP code (runnable from
>>>>> the
>>>>> command line) that you should be able to hack down. It should answer
>>>>> your
>>>>> immediate questions about which PHP statements to use (if you've
>>>>> never
>>>>> done
>>>>> this from PHP before):
>>>>>
>>>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...rime/sw/standa
>>>>> lo
>>>>> ne/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup
>>>>>
>>>>> http://gpl.e3ft.com/vcvsgpl01/viewcv...rime/sw/phplib
>>>>> /u
>>>>> srs.inc?rev=1.11&content-type=text/vnd.viewcvs-markup
>>>>>
>>>>> Near the end of it, especially if the software writes output, you
>>>>> should
>>>>> get
>>>>> an intuitive feel for how long each INSERT is taking.
>>>>>
>>>>> You can even do test queries using the barebones MySQL client ... you
>>>>> should
>>>>> see interactively how long a query takes.
>>>>>
>>>>> I would ALMOST do this for you, but it is just beyond the threshold
>>>>> of
>>>>> what
>>>>> I'd do because I'm bored and watching TV. I'm just a little curious
>>>>> myself. I've never messed with a table about 10,000 rows or so.
>>>>>
>>>>> Dave
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

At 6:24 PM -0400 6/11/07, kalin mintchev wrote:
>hi all...
>
>from http://dev.mysql.com/doc/refman/5.0/en/features.html:
>
>"Handles large databases. We use MySQL Server with databases that contain
>50 million records. We also know of users who use MySQL Server with 60,000
>tables and about 5,000,000,000 rows."
>
>that's cool but i assume this is distributed over a few machines...

No.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com