Hit to database

[tcjay]

Is there anyway to monitor the number of hits to a database? I would like to know how many hits are made so I can determine if where I stand in relation to the maximum number of hits allowed in a particular hour.

[Pig]

Yes, but not directly. I'm going to have to write a script for this, it gets asked so much.

Basically what you have to do is always use a custom function for your queries instead of mysql_query(). It is a good idea to do that anyway because it allows you to more easily debug your code. Do a search for "safe_query" in this forum to find an example.

Anyway, besides run the query and give some debugging information, this function should also write a timestamp to a flat file. To check the query usage, you can call the file and load the contents into the array. Then get the number of elements that are in the hour range. To keep the size of the file (and consequently the array) down to a minimum you can create a file for each hour, and have the script delete any files that are for $var hours ago to keep them from multiplying like rabbits.

I don't think anyone has written something like this, and I doubt I'm going to have time to right away. It would make a good project for someone to learn about working with flat files and arrays.

[Pig]

Oh yeah, and if you wanted to get fancy you could also break down the usage by database user. (of course the fewer things that get itterated over a 200,000+ loop the better).

[B&T]

The problem with that, is most people seem to use premade scripts and have no idea how to make changes. And even if they did know how, to changes every sql statement would be a real chore. The to redo them after every update to the application . . .

I have an error trap routine on my Tips & Script page, but I only use it for the custom built things. I would never try and go into Gallery (for example) and redo all the sql.

Particularly, the folks who are worried about max limits are probably those using a cms or something they did not write.

The idea is a good one. The problem is in the implementation. You could write a great script that no one could really use.

[Pig]

I disagree. One batch find/replace, and an addition to the functions file would solve everything. You could be done in 5 minutes.

Anyway, I'm writing it right now. It'll be simpler than I suggested though. No need to keep each time stamp. I'm just incrementing a value in the flat files.

[B&T]

If the sql statement replacement will be that much of a sure thing no brainer, then you could have a scriipt to an auto-install. Read every file in the directory and all subdirectories and replace them all with a string replace. Then the only trick would be to figure out where to put the common function that you know would be available to all the sql. I suppose you could do that auto also by having the install script also look for all scripts that may have a db config and add to that script.

Would be a good test to see if a dumb install would work for phpbb, nule and gallery. I remain skeptical, but would be thrilled if you get it to work.

[Pig]

Well I got it up and running for phpbb VERY easily. Just had to alter one file. :)

Take a look if you want. I can't believe how many queries phpbb uses! OMG. Now I can see how busy forums can use so many queries. I'd be curious to see how many vBulletin uses.

phpbb: http://webhead.cc/phpbb/
couning: http://webhead.cc/phpbb/query_count/view.php

Later tonight I will work on that install idea, B&T. That is a very good thought. I was going to just have a section on how to modify the pages, but it should be a cinch to build an installer. :)

[Pig]

Still got a couple of tweaks to make and decide how I want the pruner to be run. I might even put the output of view.php into XML if I get frisky.

Another cool feature, is that you can easily incorporate this script into other queries and check your current totals. For instance, if you wanted to disable a portion of your site to conserve queries when you get close to the cut off. All you do is:
$current_total = file_get_contents($_SERVER['DOCUMENT_ROOT'] . "path/to/folder/" . date("YmdH") . ".txt";

[B&T]

I would expect a massive amount of queries from phpbb, or any of the generic scripts. When you write for max functionality - even when people don't use it all - you know what you get.

Sounds like you are well down the road.

[Pig]

Started a new thread.
http://forum.powweb.com/showthread.php?t=38205