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/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
