> 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/standa >>>>> lo >>>>> ne/dbtestpop.php?rev=1.31&content-type=text/vnd.viewcvs-markup >>>>> >>>>> http://gpl.e3ft.com/vcvsgpl01/viewcvs.cgi/gpl01/webprojs/fboprime/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 >>>>> >>>> >>>> >>> >>> >>> >> >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
