Do you count the traffic you send somewhere, and how? - GoFuckYourself.com

http · 03-29-2007, 05:38 PM

One of these days I want to start recording the amount of hits I send to every single sponsor site.

Who's doing this and how? I figure writing it to a mysql table uses a lot of CPU / RAM?

If I sent 100K hits a day to 200 sites, would I run 100K mysql update queries on a table with the 200 sites?

Won't that tear down my server? Is there a more elegant way to do this, i.e. write to a text file first or whatever?

nestle · 03-29-2007, 05:49 PM

Quote:

Originally Posted by http

One of these days I want to start recording the amount of hits I send to every single sponsor site.

Who's doing this and how? I figure writing it to a mysql table uses a lot of CPU / RAM?

You would need to pass all links through your own script which will "redirect" the user to the other site.

Quote:

Originally Posted by http

If I sent 100K hits a day to 200 sites, would I run 100K mysql update queries on a table with the 200 sites?

Yes, but 100K queries every 24 hours is only 1.157 per second, regardless of how many sites these 100k hits are being sent to. 100k is a big number for people, but very miniscule to a server... unless you're running like a 8086 with 1mb of ram.

Quote:

Originally Posted by http

Won't that tear down my server? Is there a more elegant way to do this, i.e. write to a text file first or whatever?

Using a flat file as a database for this would actually be a step backwards in "elegance".

http · 03-29-2007, 05:55 PM

So a plain "update site_table WHERE site_name='site-name' ..." etc will do?

I haven't done much update queries yet, I am mainly doing selects and thought updates would be much more expensive. Server in question is a Seprom w/ 512MB (and quite busy with just the selects) but getting a Core 2 Duo 2.4 w/ 2GB Ram & Raptor drive soon.

Splum · 03-29-2007, 05:59 PM

Quote:

Originally Posted by http

So a plain "update site_table WHERE site_name='site-name' ..." etc will do?

I haven't done much update queries yet, I am mainly doing selects and thought updates would be much more expensive. Server in question is a Seprom w/ 512MB (and quite busy with just the selects) but getting a Core 2 Duo 2.4 w/ 2GB Ram & Raptor drive soon.

Mysql needs alot of ram so I would wait until you upgraded, but certainly you NEED to track all hits in and out and analyze that data or you are throwing money away.

nestle · 03-29-2007, 06:02 PM

Quote:

Originally Posted by http

So a plain "update site_table WHERE site_name='site-name' ..." etc will do?

Something like:

Code:

UPDATE site_table SET columnCounter = columnCounter + 1 WHERE siteID = 2134;

Quote:

Originally Posted by http

I haven't done much update queries yet, I am mainly doing selects and thought updates would be much more expensive. Server in question is a Seprom w/ 512MB (and quite busy with just the selects) but getting a Core 2 Duo 2.4 w/ 2GB Ram & Raptor drive soon.

What else is that Sempron box doing (or approx how many selects are being done?) and what's the average load on high-traffic periods? You sure it's mysql that is hitting the bottleneck? If a web server is being hosted on the same server, that could be it as well.

http · 03-29-2007, 06:08 PM

Thanks nestle & Splum

Would 4GB RAM make sense instead of 2GB? I serve everything dynamic w/mod_rewrite etc. and can barely answer the SE robot queries because the server is at it's limit.

I am afraid part of the RAM would be idle all the time if I get 4GB. Would a mysql_query_cache of 2GB make sense and work well (and the remaining 2 GB for Apache etc..) for dynamic page serving (select) and hit tracking (update) w/ mysql?

camgirlshide · 03-29-2007, 06:09 PM

I use google analytics javascript tags

http · 03-29-2007, 06:12 PM

Quote:

Originally Posted by nestle

Something like:

Code:

UPDATE site_table SET columnCounter = columnCounter + 1 WHERE siteID = 2134;

What else is that Sempron box doing (or approx how many selects are being done?) and what's the average load on high-traffic periods? You sure it's mysql that is hitting the bottleneck? If a web server is being hosted on the same server, that could be it as well.

It's serving up to 250K pages/day, all dynamic (php & mysql select) w/ mod_rewrite plus a C app runs about 10 times for each page. Serving a page to a SE bot takes 3-6 seconds. Mysql seems to use most of the RAM, followed by Apache.

A bigger server is overdue, but I wondered if I should even plan to include the hit tracking on the same server. Sounds like it should work from what you say.

Splum · 03-29-2007, 06:17 PM

Quote:

Originally Posted by http

A bigger server is overdue, but I wondered if I should even plan to include the hit tracking on the same server. Sounds like it should work from what you say.

I will tell you honestly I have several servers and do most of my tracking on one server by itself so I dont know about one server, if you do go with one server you really should give it as much RAM as possible but multiple servers is probably the wisest idea in terms of protecting data, load times, growth.

Also make sure to tweak Mysql to handle heavy loads.
http://www.profitpapers.com/papers/p...l-for-load.php

http · 03-29-2007, 06:20 PM

I will probably get the new server w/ 2 GB Ram and then either add RAm or a 2nd server after I see how it behaves. Thanks everybody

fuzebox · 03-29-2007, 06:29 PM

I've been doing this for years... I actually track every hit including timestamp and referring URL seperately. It's had 30-40k hits through it in a day with no problems at all... It's not really a product I could sell though, I still get most my daily reports through raw mysql queries via ssh.

03-29-2007, 05:38 PM	#1
http Confirmed User Join Date: Oct 2001 Posts: 1,811	Do you count the traffic you send somewhere, and how? One of these days I want to start recording the amount of hits I send to every single sponsor site. Who's doing this and how? I figure writing it to a mysql table uses a lot of CPU / RAM? If I sent 100K hits a day to 200 sites, would I run 100K mysql update queries on a table with the 200 sites? Won't that tear down my server? Is there a more elegant way to do this, i.e. write to a text file first or whatever?

03-29-2007, 06:09 PM	#7
camgirlshide Confirmed User Join Date: Jan 2005 Posts: 1,558	I use google analytics javascript tags __________________ Useful adult webmaster links - Alphabetical list of solo models with webcam Stats on my best converting affiliate programs - camgirlshide webmaster blog complete list of affiliate programs I use.

03-29-2007, 05:55 PM	#3
http Confirmed User Join Date: Oct 2001 Posts: 1,811	So a plain "update site_table WHERE site_name='site-name' ..." etc will do? I haven't done much update queries yet, I am mainly doing selects and thought updates would be much more expensive. Server in question is a Seprom w/ 512MB (and quite busy with just the selects) but getting a Core 2 Duo 2.4 w/ 2GB Ram & Raptor drive soon.

03-29-2007, 06:08 PM	#6
http Confirmed User Join Date: Oct 2001 Posts: 1,811	Thanks nestle & Splum Would 4GB RAM make sense instead of 2GB? I serve everything dynamic w/mod_rewrite etc. and can barely answer the SE robot queries because the server is at it's limit. I am afraid part of the RAM would be idle all the time if I get 4GB. Would a mysql_query_cache of 2GB make sense and work well (and the remaining 2 GB for Apache etc..) for dynamic page serving (select) and hit tracking (update) w/ mysql?

03-29-2007, 06:20 PM	#10
http Confirmed User Join Date: Oct 2001 Posts: 1,811	I will probably get the new server w/ 2 GB Ram and then either add RAm or a 2nd server after I see how it behaves. Thanks everybody

03-29-2007, 06:29 PM	#11
fuzebox making it rain Industry Role: Join Date: Oct 2003 Location: seattle Posts: 22,116	I've been doing this for years... I actually track every hit including timestamp and referring URL seperately. It's had 30-40k hits through it in a day with no problems at all... It's not really a product I could sell though, I still get most my daily reports through raw mysql queries via ssh.