Database Overload Question

[raxx0r]

I am an intermediate php scripter. Anyways what I am trying to do is write a simple ad and ad tracking programming to tie into a directory/rating software (which I am also writing). What I am wondering is how to go about the ad tracking process? I installed phpads and reviewed how they did it, but it is pretty complex.

I guess my main question would be is, if I updated a counter in the database everytime a particular ad was viewed on a site of 10,000 hits a day would this overload the server?

If yes, which I am pretty sure it will be. How do you go about tracking ad views?

Thanks for your help.

Jordan

[liquorvicar]

I guess my main question would be is, if I updated a counter in the database everytime a particular ad was viewed on a site of 10,000 hits a day would this overload the server?

that depends on the spec of your server but i'd guess if your server could handle 10,000 hits a day with dynamic ad generation then it could probably handle the minimal extra load of an SQL increment statement.

[raxx0r]

Well what if I got say in excess of 10,000 hits a day. How would anyone track these statistics?

If I am going to write it, I want to write it so it's expandable, fast and reliable.

What is the common method in this situation?

Can you like store views/clicks in a log file then have a cron to update the sql database daily or something?

Thanks for your help.

[Sxooter]

10,000 hits a day is nothing, really. Assuming ALL your load occured during an 8 hour window, that would be 28,800 seconds. So, that means an average of one request every 2.8 seconds.

When you're getting 5 or 10 requests a second, you'll start to notice the load. But for that to happen, you'd need to have well over 100,000 hits a day.

The biggest issue is updates. MySQL with MyISAM is a poor choice for a heavily updated table. If you can use innodb instead, or switch to postgresql you're likely to handle higher write loads better.

The nice thing about mysql is you can put your heavily read data (i.e. the ads themselves) in myisam, and put your heavily updated data into innodb tables.

[raxx0r]

O.K. lets say worst case scenerio i get 20,000 hits. Each page has 5 ads, plus the page's content will be pulled from the database. So you can see how it would turn from 20k to 120k requests on the database.

So really the answer is to use different database technology rather then change how/when you access the database?

• Jordan

[Sxooter]

Different database technology can only do so much. If you've got lots and lots of reading going on, then look at using a larger number of hard drives, if you've got lots of writes, look at using a battery backed, caching raid controller.

Basically, the database technology is one of the limiting factors, your hardware is another, and your system design is another.

The first step is simple optimization of your system and database software, then separating them to two different machines, web and db, then on from there, depending on where your bottlenecks are.

[raxx0r]

Thanks for your help Sxooter,

I will probably design the site to access and write to mysql everytime. Once I start getting generating income and traffic off this web site. I will hire a professional to come in and optimize the software and hardware better (depending on my technology level at that time.)

• Jordan