Page 1 of 1
Visits log
Posted: Wed Jan 21, 2004 7:16 am
by Nay
I am onto a new project again. This is for a friend of mine (yes, again). Anyhow, I was crumbling paper and drinking a cup of coffee with thinking of the a visits log. I first planned to have a MySQL table as usual and log the IP. http agent most of the things you get out of $_SERVER.
Anyhow, his site is quite popular and (esimated) gets about 800000 hits per month. That's hits, not unique visits. Anyhow, I got worried about the poor table being overloaded and if I were to have an inflexible file to store the logs in, it'd probably be quite big as well.
I then thought of having a /logs folder and let PHP automate a new log file every week or so. But after a while, it's going to get messy as expected.
Then so I go back to the MySQL theory and empty the table everytime it reaches an insane number of hits and use the auto increment to keep the counts of the site.
Another thing I thought of was accessing the Apache's log. But that I have 0% knowledge on...
I was wondering if anyone have any thoughts on this.
Thanks,
-Nay
Posted: Wed Jan 21, 2004 8:08 am
by Roja
800k hits is definitely a high number.
The first thing I would do is try it. Make a minor version of the mysql table you want to do (Lets say just IP for now), and do a small include that simply updates the table with that information.
Include that file for a day, and see what happens. Benchmark the speed of the page for a day before, and during the day, and compare the results.. did it slow down the page? did the cpu load on the server increase much? Did people have problems loading that page?
If it wasnt too bad, then expand it a bit. Try it on more pages, and perhaps log more information. Again, watch the results.
You may need to tweak mysql to handle it better, or you may need to increase the amount of memory allocated to mysql.
I have a game that logs every page access, with around 4k hits per day (120k hits per month). We log a TON of infomation to a mysql table, and have no problems keeping up. While you have considerably more hits, you may be able to do the same by logging less information.
When in doubt, try it out.
As to the flat-file idea, I highly suggest against it. The perfomance improvement isnt that substantial, and the inability to search, trim based on date, and so on makes it much less attractice.
And finally, as to apache logging, you can find some REALLY nice apache log analyzers on the net today, like
Analog. But thats a seperate solution, outside of your project.

Posted: Wed Jan 21, 2004 8:22 am
by Nay
Thanks for the tip. I was getting into the Apache part. Since I'm sure it has a good logger so instead of creating a new one, just utilize it.
The other problem i'd say is that the site is limited to one database, it's a mess I tell ya. IPB praticly floods it. heh, anyhow, they 'plan' to move to a dedicaed server 'soon'.........so I'm also considering only writing it then to adjust to a database just for logs?
I'll look into your theory plus some Apahce-ness.
Thanks again,
-Nay
Posted: Wed Jan 21, 2004 9:42 am
by jaxn
As long as you can use Innodb table types and use minimal indexing, then it should be fine.
What you don't want to have happen is table locking (non-innodb table types).
Also, since indexes are created every time a record is inserted, you want to use minimal indexing (since you will rarely be selecting from the table).
Since there is minimal indexing, queries will take longer, so you want to generate reports on off-peak times and not on-demand.
Also, it is to your advantage that you don't have to ever UPDATE these records.
I think you will be fine to store the records in the database.
-Jackson
Posted: Wed Jan 21, 2004 10:02 am
by Nay
Innodb table types? hmm.........so if I created a table as usual with a script it's 'not' an innodb table? I'm lost

.
-Nay
Posted: Wed Jan 21, 2004 10:09 am
by Roja
Nay wrote:Innodb table types? hmm.........so if I created a table as usual with a script it's 'not' an innodb table? I'm lost

.
-Nay
Depends on your mysql version. Could be Innodb, could be MyISAM.
Create a table and find out.
If I remember correctly, mysql-3.23 and below default to MyISAM, while 4+ defaults to Innodb.
I could be wrong on the defaults, but generally, you have to explicitly set it to make an innodb table.
Posted: Wed Jan 21, 2004 10:35 am
by Nay
Oh, that........I see those options though. I'll fool around with them and crash my server

.........ahem......localhost

.
Thanks for the help.
psst: Although I know I should search on google but I'm wondering what's the main difference. Just speed?
Oh and is banning IP's as simple as if($ip = $theBadIp) { header("Location: somewhereelse.php"); } ?
-Nay
Posted: Wed Jan 21, 2004 10:43 am
by Roja
Nay wrote:Oh, that........I see those options though. I'll fool around with them and crash my server

.........ahem......localhost

.
Thanks for the help.
psst: Although I know I should search on google but I'm wondering what's the main difference. Just speed?
Yup, you should.
Innodb is the best thing since sliced bread. Its easiest to explain what they AREN'T.
MyISAM tables only do full-table locking - meaning anytime you do an insert/update or lock for a change, the whole table is locked.. which delays all other transactions until that one finishes.
Innodb arent limited to full-table locking. They can do row-locking instead, which is much faster, improves the responsiveness of the db, and also prevents dead-locking.
They handle indexes better, searches better, vote Bush out of office faster, heck, they even make coffee three times faster!
Nay wrote:
Oh and is banning IP's as simple as if($ip = $theBadIp) { header("Location: somewhereelse.php"); } ?
-Nay
Could be, depending on your setup, how you define thebadip, and how you get $ip.
Posted: Wed Jan 21, 2004 11:07 am
by jaxn
Innodb tables also have better memory management. But to get the full benefit you need to have full control over the MySQL server settings.
You don't need the full benefit for what you are describing though. Just getting rid of table locks will allow you to write multiple rows to the logs table at the same time (as opposed to a one-at-a-time bottleneck that would be caused with MyISAM).
-Jackson
Posted: Wed Jan 21, 2004 11:18 am
by Nay
ould be, depending on your setup, how you define thebadip, and how you get $ip.
From an table of 'bad ip'(s).
-Nay