MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David T. Ashley Date:June 12 2007 1:12am
Subject:Re: maximum number of records in a table
View as plain text  
On 6/11/07, kalin mintchev <kalin@stripped> 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/viewcvs.cgi/gpl01/webprojs/fboprime/sw/standalone/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup

http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/sw/phplib/usrs.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

Thread
maximum number of records in a tablekalin mintchev11 Jun
  • Re: maximum number of records in a tableDavid T. Ashley12 Jun
    • Re: maximum number of records in a tablekalin mintchev12 Jun
      • Re: maximum number of records in a tableOlaf Stein12 Jun
        • Re: maximum number of records in a tablekalin mintchev12 Jun
          • Re: maximum number of records in a tableJon Ribbens12 Jun
          • Re: maximum number of records in a tableOlaf Stein12 Jun
            • Re: maximum number of records in a tablekalin mintchev12 Jun
  • Re: maximum number of records in a tablePaul DuBois19 Jun
    • RE: maximum number of records in a tableJohn Mancuso20 Jun
Re: maximum number of records in a tablekalin mintchev11 Jun
Re: maximum number of records in a tableJ.R. Bullington12 Jun
  • Re: maximum number of records in a tablekalin mintchev12 Jun
  • Re: maximum number of records in a tablekalin mintchev12 Jun
Re: maximum number of records in a tableJ.R. Bullington12 Jun