Subject: Large PHP/Database driven site & scalability

[Eric Peters]

Basic problem:
distributed database system

A little background info:

The site is a community driven website that has over a quarter million
accounts, an average of 400 simultaneous users and well over  200 million
rows of data in various tables with logged information on points earned
during games, profile data, editorials, message postings and other misc
content routes

We are running php4, mod_backhand, and MySQL 3.23.x

We have so far been half addressing scalability with several hacks but
are seriously not having to sit down and readdress the issue
properly.  

We don't have the VC capital necessary to spend a couple hundred thousand
to a million dollars on an Oracle enterprise system - and need to address
the situation with more brains than balls

With several senarios we have drawn for linking web servers to database
servers, with a myriad of "gateway" database servers setup for
distributing user data and linking webservers to the appropriate db server
that stores profile information there are a couple key issues I would love
to have people comment on

1) how can you get a mysql_pconnect to use an apache server connection
pool (rather than a child persistant connection) - yes I know its not
implemented - but how hard would it be - and how much would it cost to get
someone to do it : ) - and from usage standpoint of a database driven
website is it practical to have a pool of 50 on a server with 255 httpd
children ? assuming every page had at *least* one database query..

2) looking at an authentication server that would have a
username/password/database assignment - would it be practical to stepup to
having a "datapipe" that would automatically query an appropriate sql
server and retrieve the information - and how hard would a fd limit be
reached etc

3) in a user->database server assignment senario - what about information
that needs to be shared - or searched across the database - say for some
"find a friend" where it matches people with people - or game ranking
system where you want to list all of the top 20 players or a myrad of
other options that would require a plethora of queries across each server
- is there a particular approach people find themselves using - whether
there is a single server that holds "current" data and it then would be
archived to an older server - how reliable is it and etc


in general i'm looking for applied knowledge that people have tried,
rather than theoretical knowledge - i am definately interested in finding
a niche of peers that have to deel with the same issues and create a
support network

If i'm way off base let me know,

I appreciate your time and thank you,

Eric