I think that if your mySQL DB is planned to be 10M records, you might as well move it now to pgSQL and wait for that size - pgSQL will make it better than mySQL on these sizes. I personally don't understand, how it comes into some minds using mySQL for database larger than n millions of records? You get so much of trouble then. Oh well, maybe new mySQL versions have improved, but as far I experienced in past - when mySQL DBs get over 1M for - things were dramatic and troubleshooting I did were making me wish use anything but not mySQL. As of hardware, it will not really matter what you set on it - mySQL or pgSQL - as long as you can keep both HTTP and DB separate. Especially because you're using DB layers in PHP etc. -- Maxim Maletsky maxim@php.net On Sat, 9 Nov 2002 10:18:22 -0000 "Peter Lovatt" <pjln1@sunmaia.net> wrote: > 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 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
