Topic awaiting preservation: Hit Counter Database

Hi all, I'm building a hit counter that I would like to be fairly detailed showing hits for each page, total and unique hits.

I want to be able to do things like
-view detailed stats on each page
-select a time period from/to and show the counter results for that time.

The way i am doing it currently is logging the results to a mysql database every time someone visits the page, this was i can track the date/page and i.p to determine unique users.

The problem is, after about 4 days there are now 1500 database entries, so you can imagine after a long period of time such as 1 month there could be around 11,000 entires, and after a year 135,000 entries.

So my questions are
: Will this start to slow down after a short time?
: What sort of limit does a mysql database have for size?
: Is there a better way to do this?

Because of the information i want to display I can't really reduce the data im collecting, so im not sure if there is alot i can do. Perhaps there is a better way to store the data however.

Thanks in advance

depending on wether you have indices or not, this will slow down eventually.

Usually, with out any special consideration, I'd expect mysql to handle 2 gigs per table (one table = one file, and that was the usual file size limit on older linuxes or fat32).

Honestly, your web server should be logging all requests anyhow into a log file. Your provider should provide you with that for download in one of your folders.
From that, you could can easily use software like 'analog' or your own processing scheme to extrac the information you want.

Hmm is that how its normally done? via log files?

I guess its not really that different, i mean every year you could automatically create a new table to store the hits in, or every month if thats how the log files do it.

Having it all in a database just feels more in control, with one less step than reading it from log files.

but your server is keeping the logs anyhow. Why should you slow it down by letting it do double work?

True, i would have to check what information they actually log i guess.

On that note tho, wouldnt writing a new record to a table - even if that table is 1gig or so - take the same time as writing the record to a new table. All you are doing is adding the information, so i would expect minimal work which wouldnt slow the server down at all.

Doesn't the load come when you are reading from the database, performing complex queries etc? In which case you would experience the same load if you were reading from log files, if not more.

- I'm not trying to disagree or anything with you TP by the way, just trying to get to the bottom of the facts. I would have thought the above about server load be the case but I dont really know so i could easily be wrong -

Thanks for the help

No!

A typical static-pages-webserver would server most pages out of it's ram, and spend most of it's time actually writing the logs.

And the complexity of adding rows to a table depends on what and how many indices you've got. It needs to find the correct spot to add in each index - and that's usually at best O(log(N)). Without an index, it could probably be in the same magnitude.

so long,

->Tyberius Prime

Hmm well im not sure then, if it becomes such a delay to add an item to the database then that could cause problems.

I did try and check some logs out, but the subdirectories in my logs directory just give me access denied for most of them I wonder if it makes a difference that these are running on a windows server?

Anyhow i did some checks on the database i have, now im not sure if the size is linear each time you add a row or not.

Currently the table that stores all of the hits is 390,000 bytes (390kb) and it has 2445 rows. So this is 0.16kb per row.

If i assumed 500 new hits per day, thats 80kb per day, 29,200kb (29MB) per year.

So it would take around 50+ years to get to the 2gig size if this is when it starts slowing down.

Obviously the hits could increase etc, but not enough to make THAT much difference in size *i dont think*. And this depends again if the size would be linear like that, i have no idead of that. I would guess there is also a starting size for just having the table which would be quite small tho.

The other information currently is this:
Data 366,572 Bytes
Index 22,528 Bytes
Total 389,100 Bytes

Currently i only have the auto_id column indexing, so i probably need to create more indexes so i can query faster which will increase the size even more.

Anyhow, i would be happy if database was efficient for 10 years (with the above theory this would allow me to have 24,000 hits per day), so i guess i need to know if my above theory regarding the size is correct?

Another question i have if anyone knows also, i notice some hit counters say they can tell how long someone has been looking at each page. I can't figure out how to do this? I mean you could run some javascript or something but what happens when they close the page etc.. This bit has me confused... thanks.

its incredible how much information can be get just out of apache log files.

i use this package and am very pleased with it:
http://awstats.sourceforge.net/

--
www.embege.com

Hmm that does look very nice, i will have to try and get my host to give me access to the log files.

Cool looks like they fixed why i couldnt access the logs, so ill give this a go. I dont think there is information i can get that isnt already in the logs by the looks of it anyhow.


Just on that second question, anyone have any idea how people record time spent on a page etc? Is it using javascript or something?

Thanks

nah, usually it's clever log reading. If you get an access from ip x on page Y, and another access on page T 20 seconds later, you could assume that the user with ip x has spent 20 seconds on Y

ahh ok, i thought something like that but that it wouldnt be very accurate. Oh well - see what i can do with these logs! Thanks for all the help