[PHP-DB] MySql limits - WAS [PHP-DB] ROugh idea of speed
Hi 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? Is it tables that have heavy write use that are prone to problems or are read only tables a problem too? 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 --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ROugh idea of speed
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 [EMAIL PROTECTED] On Sat, 9 Nov 2002 10:18:22 - "Peter Lovatt" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] ROugh idea of speed > > > > "Steve Vernon" <[EMAIL PROTECTED]> 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 > a
Re: [PHP-DB] ROugh idea of speed
Steve Vernon [mailto: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. > > Its basically storing an index int and about 5 or so char > field (50 > long). In total I want to store 500 million records. Accessed > using PHP. > a.. 2x Intel Pentium III 1260 CPU or higher > b.. 1 GB RAM > c.. 60 GB hard drive > d.. 20 GB traffic/month > e.. RedHat LInux 7.2 > Ive read that its better to store the data in different > databases on the > same server? > > 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. > > Anyone have any experience with holding a lot in MySQL? > Any idea of > speed would be great. > One of my clients is running a special interest portal, with roughly 200-300 parallel users in peak times, and about 1000 SQL queries/sec (of which about 90% are SELECTs). For stability and scalability reasons, we have an LVS (http://linuxvirtualserver.org/) cluster for http, and 2 mysql servers (Athlon 1800+, 512 MB, SCSI-RAID) with active/active replication, having a loadaverage of about 0.10 at peak times. Our database is about 1 Gig now, storing authentication- and user data (including user-uploadable pictures, which make 600 MB by now). We had many problems with mysql running on the same machines apache was running (freezes and deadlocks with 1 httpd consuming 400+ MB and loadaverage 100+), for yet unknown reasons (kernel <= 2.4.16 comes to mind...), which were all solved moving to the above configuration. I am really confident with the current setup, which is running for half a year now. But keep in mind that - most of the times - you get far more performance by optimizing your database (and queries) than by upgrading hardware (the --log-slow-queries and --log-long-format options to mysqld are your friend). Thomas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] ROugh idea of speed
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: [EMAIL PROTECTED] Subject: Re: [PHP-DB] ROugh idea of speed "Steve Vernon" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- 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
Re: [PHP-DB] ROugh idea of speed
I was running a site on a dual 800 CPU, 1GB ram, dual mirrored 18GB drives, doing about 16.8GB per day on average, including PHP/HTML code. The system had several tables (~20), some regularly accessed, some not. The biggest table was a log table of about 2.5+ million rows. Ran reports from it without a problem (as long as I wasn't sorting by date!). Was able to run apache + php + mysql doing 1M page views a day, all generated based on info in the DB. Probably ran at about 150+ queries per second all day long. This was on FreeBSD, not Linux. Hopefully this gives you a bit of an idea. As for 500 million records, if they are all in one table, that's something I've never tried to do before! Peter On Fri, 8 Nov 2002, Steve Vernon 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. > > Its basically storing an index int and about 5 or so char field (50 > long). In total I want to store 500 million records. Accessed using PHP. > a.. 2x Intel Pentium III 1260 CPU or higher > b.. 1 GB RAM > c.. 60 GB hard drive > d.. 20 GB traffic/month > e.. RedHat LInux 7.2 > Ive read that its better to store the data in different databases on the > same server? > > 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. > > Anyone have any experience with holding a lot in MySQL? Any idea of > speed would be great. > > Thanks, > > Steve > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ROugh idea of speed
On Friday 08 November 2002 23:08, Maxim Maletsky wrote: > "Steve Vernon" <[EMAIL PROTECTED]> 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: You've mis-read the question :) He wants around 5 fields each containing 50 chars. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* Say something you'll be sorry for, I love receiving apologies. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ROugh idea of speed
"Steve Vernon" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] ROugh idea of speed
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. Its basically storing an index int and about 5 or so char field (50 long). In total I want to store 500 million records. Accessed using PHP. a.. 2x Intel Pentium III 1260 CPU or higher b.. 1 GB RAM c.. 60 GB hard drive d.. 20 GB traffic/month e.. RedHat LInux 7.2 Ive read that its better to store the data in different databases on the same server? 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. Anyone have any experience with holding a lot in MySQL? Any idea of speed would be great. Thanks, Steve -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
