Hi I have been following the thread and I am interested in the limits of MySql. I have a site which is growing. The biggest tables are currently about 750K but this will grow to the 3-10M record mark over the next 6 months. The databases are well designed and are currently running smoothly on 2x1GHz PIII and 512MB RAM. I am planning and specifying hardware for the next phase of growth and had assumed MySql would handle this size of table comfortably. What are the problems to look out for? The site has an abstraction layer so moving databases is not out of the question, but would involve work and expense not currently budgeted for. I am a moderately skilled DBA and we have the budget for hardware. I don't anticipate going beyond 10M records and would rather stick with MySql unless we are heading for big trouble. Thoughts and advice appreciated Peter ----------------------------------------------- Excellence in internet and open source software ----------------------------------------------- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 ----------------------------------------------- -----Original Message----- From: Maxim Maletsky [mailto:maxim@php.net] Sent: 08 November 2002 15:09 To: Steve Vernon Cc: php-db@lists.php.net Subject: Re: ROugh idea of speed "Steve Vernon" <steve@extremewattage.co.uk> wrote... : > Hiya, > Just wondering what is the rough idea of speed of a server like this is > holding a database with millions of records. I know its difficult, depends > on the data stored etc. Also rather the design and, whether do you really need to store it all in. > Its basically storing an index int and about 5 or so char field (50 > long). In total I want to store 500 million records. That makes no sense to me: 5 char field will be able to store up to: 107,820,390,375 unique char combinations but in the real life - considering only 52 characters+10number you'd get: 916,132,832 unique combinations. Now, if you would sacrifice case sensitivity and hold only upper-case characters and numbers you would end up with: 60,466,176 This is often the case for the usernames etc. So, it makes very few sense counting on 500 Millions of records storing only one 5c column. You should rethink the design first. > Accessed using PHP. Won't matter what you access it with. > a.. 2x Intel Pentium III 1260 CPU or higher enough > b.. 1 GB RAM not enough for any kind of WHERE LIKE over 500mils > c.. 60 GB hard drive might work... but might not > d.. 20 GB traffic/month oh yes > e.. RedHat LInux 7.2 YAY! > Ive read that its better to store the data in different databases on the > same server? Wrong. It is better to have one HTTP server and one optimized for the database. Where did you hear that staff? > Can someone please give me a rough idea of the speed and how many > servers needed, my client wants to know how much it will cost to host the > site. For 500 million records I would consider whether: * 1 Oracle license one one dedicated server (HTTP on the separate machine, so servers in total). Full expense will probably be some $10-$30k. * 2 or 3 PostgreSQL machines load balanced and one HTTP server. Similar expense to above. (although Open Source, you'd have maintenance expenses and 2 more machines up) * Paying a datacenter with an already optimized database and servers up and running on multiple servers. Something like $2.000 a month? > Anyone have any experience with holding a lot in MySQL? Any idea of > speed would be great. mySQL will never make it. Unless you spend bunch of money on good people that can make it work. Don't go for mySQL in this case. I always had bad experiences holding around one million of records with mySQL. PostgreSQL worked well for me on 5-10 mils and Oracle works smoothly on 3.5 Terrabytes of data. Cheers -- Maxim Maletsky maxim@php.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
