mySQL and Traffic

Single db or multiple dbs better?

[JimThomason]

I'm building a database with several projects and planning a move to php and mysql. Each of these projects get different amounts of traffic each day. Currently,

ProjectA - 170,000 visitors/day
ProjectB - 120,000 visitors/day
ProjectC - 30,000 visitors/day
ProjectD - 10,000
ProjectE - 10,000

I have all these projects data in one database. With this amount of traffic should I break these into separate databases or is this nothing for php and mysql? Pretty much each page view will need interaction with the database. There will be very few static pages.

Thanks,

Jim

[jatar_k]

By different projects does it mean different sites? Is any of the data shared between projects?

If data is shared the one db makes more sense. If the data is unique to each individual project then seperate dbs makes sense. It also depends on how much data there is.

Since you say nearly every page view accesses the db I would imagine there is a fair bit of data. If there are multiple tables to each project then multple dbs makes sense but a single table per project may make more sense.

I think you get my drift, it is really difficult to answer with out knowing how much data, structure etc.

The choice should be based on ease of access to data and speed of delivery. I don't think the number of daily visits will be a problem so long as the programming is sound and you don't have too many db calls per page.

[ggrot]

Fast mysql backups are done with mySQLhotcopy. This locks the entire database, copies the files, then unlocks the database. No writes can occur during that time. If you break up your data into multiple databases, you can keep particular writes moving much faster overall. Of course, this may not matter if your queries are almost entirely reads or the database sizes are small (<100M).

[xunker]

If they are indeed separate and don't require each other to operate, you may well want to brak them out into distinct databases for speed concerns;

As you probably know mySQL "databases" are, on the file system, just directories filled with files; if you break them projects apart into seperate databases (which makes into them separate directories) you can then split those databases among multiple disks, too, to speed up concurrent disk access considerably.

That is, if you think speed will ever become an issue.

[sun818]

Database reads and writes may be a better statistic for separating databases than visitors per day. Also, one should consider optimizing SQL and creating indexes if necessary.

[JimThomason]

We are working on a domain parking solution

ProjectA - 2500 domains
ProjectB - 3000 domains
etc.

Sorry. Should have given more detail.

Currently we use perl to rewrite the html files in each directory every 10 minutes to display a set number of random sponsor links.

Another project just redirects to sponsors page. A list of domains all point to a single index.cgi that based on http_host redirects it to the proper site.

We thought we would change to php and a database so title and sponsor info for each page is read from the database.

Each project has it's own table
domain, title, domain_words, search_term, projectid, nicheid

Another table has all the projects
Another table for all the niches
Another table has all the sponsors information

We have so many scripts and domains lists that administration has become overwhelming.

Thanks,

Jim

[jatar_k]

Sounds like you would be doing reads only for the most part and the data is related. There aren't very many tables (8? total) so one db should be ok.

Sun818 has good advice here

consider optimizing SQL and creating indexes