More table or more database for better performance?

Question

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside.

Which option is better for server performance?

1) 400 databases each have 1000 table?

2) 4000 databases each have 100 table?

Update: I see i need to put some more details, sorry for trying to ask simple as much as i can. Site will based on wordpress multisite with http://wordpress.org/extend/plugins/hyperdb/

1 website with 10 database table generally wont pass 6-7 mb as total database info. There will be 9-10 heavy website that will have serious database info, i will put them in a special database. ( lets say 4096 database, 96 reserve for vip,heavy sites and split all other sites in 4000 database )

Server is: http://www.wiredtree.com/managedhybrid/ After reaching some point, i am planning move to pure dedicated server which will based on raid10 15k RPM SAS mysql discs and different media discs

So 40.000 sites but all of them sharing same php files, just different databases and different upload folders. This is how wordpress.com working. Same rules but my sites wont be a blog.

Please ask what other info do you want, i will try to explain more. I am very sorry if i break any rules or something.

( Sorry for bad english too :P )

Answer 1

Performance shouldn't be a concern: security, consistency, data integrity should be

• Security: If each web site has a database this implies that the data for that web site is private and separate. So 40000 databases with 10 tables each.

• Consistency: managing 40k websites requires utter consistency. Why pick 400x1000 or 4000x100? How will you separate tables? How do you know which site uses which set of tables in which DB?

• Data integrity: If you restore one database, do you really want to reset the data for another 399 or 3999 websites too?

So: 40k databases, one per web site.

Finally, do you have 40k web sites or is this premature optimisation?

Answer 2

Depending on the hardware, the amount of data, and DB infrastructure, performance should still be a concern.

CASE IN POINT

I have a Web/DB Hosting client with the following hardware and DB Setup:

Three DB Servers (192GB RAM, 2TB RAID10 SAS Drives, Dual HexaCore (12 CPUs))
780 Databases (162 tables per database) total 480GB of Data and Indexes
MySQL 5.5.9 configured with InnoDB (file per table,io threads set to 20000)
Circular Replication amongst the three DB Servers
162GB Buffer Pool on Each DB Server

The weakest link with this client's app and infrastructure is the RAM. Here is why :

Even with 162GB forthe InnoDB Buffer Pool, wth proper load balancing at the application level, about 85% of the InnoDB Buffer Pool is full on the three servers. This works well because roughly one third of my client's customers ru on each server thus caching just that segment of customers on any given DB Server. This setup works in spite of having only 162GB of RAM per servers and about a total of 480GB of innodb data. Obviously, all the data could not fit in RAM. Scaling out accommodated the spread of the client's data for quick access.

In the past the client was using smaller hardware
9 DB Servers
MySQL 5.0.90
3 DRBD Clusters + Circular Replication Amongst DRBD Clusters
32GB RAM and DUal Quad Core (8 CPUs)
550 GB RAID 10 SAS
13GB InnoDB Buffer Pool

In this old setup, the 13GB was 99% full all day. WIth 480GB of data, 13GB would thrash in and out of the buffer pool 24/7. There would be periods of intermittent replication lag due to this thrashing and without the presence of slow queries.

Moving from this architecture into the new one mentioned above provided more speed (CPU and Disk). However, even 192GB of RAM still had to have accommodations made by adjusting other external factors.

MORAL OF THE STORY

The weakest link of any given DB infrastructure requires one of five things:

1. Scaling up (beefing up hardware [CPU, spindles, etc])
2. Scaling out (splitting read/writes,provisioning replication and high availability)
3. Tuning of DB Configuration
4. Query Optimization
5. Combination of one or more of the these