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


Excellence in internet and open source software
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
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
> 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

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:


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

>   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

>     Ive read that its better to store the data in different databases on
> same server?

Wrong. It is better to have one HTTP server and one optimized for the
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

* 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.


Maxim Maletsky

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

Reply via email to