Using one mysql database or few. What's suits for me?

views:

answers:

Using one mysql database or few. What's suits for me?

Hello all,

I'm looking for guidance. Here is what I'm doing.

I'm creating an advertising/publishing script using PHP and MySQL. At this time, the current program contains 41 million rows (7.5GB).

I'm thinking about storing real-time statistics and other data (users, ads, places, daily/monthly stats) on two mysql databases. And then update data (from real-time db to the 2nd db which shows users their statistics) using a cron thrice a day.

So, will this be an 'UP' or a 'DOWN' (Good or Bad)?

Thanks, pnm123

+1 A:

I'd probably run a master-slave, and then use the slave as a source for creating the second database you talk about. That should allow you to aggregate results, etc without impacting your main application.

timmy 2010-09-10 09:13:37

Correct me If I'm wrong. You are saying to use two databases as I'm thinking. One to use on the front-end and the second to use on back-end to store all real-time records. Right?

pnm123 2010-09-10 09:19:04

well, 3 actually. the slave would mirror the master. then you'd create an extra database but use the slave as the source for your aggregate queries. this should allow you to scale comfortably and not run the risk of bringing down your application while processing your data. after the cron job runs, the slave will have spare capacity to catch up with the master. it'd be important to put the master and slave on separate servers - the database containing the processed results could be on either server or another machine, but that may not be necessary (it all depends on overall load).

timmy 2010-09-10 09:22:37

I just understood what you said. So how about this. Font-end db contains users, campaigns, ads, places. By using Replication, server make a mirror of the front-end db to another db on 2nd server. 2nd server use that db to show ads and create logs on same or a different db. Finally, cron updates daily/monthly and overall statistics to the front-end db on the 1st server.

pnm123 2010-09-10 11:30:47

ansaurus

tags:

views:

answers:

Using one mysql database or few. What's suits for me?

related questions