DB improvment & suggestion

[mcargile]

These are a things my colleague and I discussed with Matt recently at the Asterisk Meet Up in St. Pete. I figured I should bring them up here for general discussion and the like.

One thing I noticed while looking through the call_log.agi was that the perl scripts do not allow MySQL the chance to cache the compiled queries. For example changing things like this in call_log.agi:

Code: Select all

$stmtA = "SELECT UNIX_TIMESTAMP(parked_time),UNIX_TIMESTAMP(grab_time) FROM park_log where uniqueid='$unique_id' and server_ip='$VARserver_ip' LIMIT 1;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",$dbhA->errstr;
$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;


to:

Code: Select all

$stmtA = "SELECT UNIX_TIMESTAMP(parked_time),UNIX_TIMESTAMP(grab_time) FROM park_log where uniqueid='?' and server_ip='?' LIMIT 1;";
$sthA = $dbhA->prepare_cached($stmtA) or die "preparing: ",$dbhA->errstr;
$sthA->execute($unique_id,$VARserver_ip) or die "executing: $stmtA with uniqueid=$unique_id and server_ip=$VARserver_ip", $dbhA->errstr;


will allow MySQL to cache this query so it will run faster with less load the next time it is run, which with call_log.agi is before and after every call! Having the variable inside the SQL statement makes MySQL think that the statement is different every time it is called. This means that it will not used the cached version. By taking the variables out of the SQL statement and placing ? in their place during the prepare_cached call MySQL will be able to find a cached version. When the execute statement is called MySQL will just replace the ? with the appropriate variables in that cached version.

And as a suggestion, since at least MySQL 3 there has been the ability to add comments to the various table fields. It would be great if these comments were added so that people do not need to pour through the code to see what field does what. For instance this would make writing custom reports much easier.

We plan on writing a reporting script that would parse the database for the names of the fields and then grep the source code for the where those fields were used. It would also do this for the names of the tables. This would generate a nice quick reference on table/field usage. Coupling this with the MySQL comments would produce a very usable data dictionary for the DB.

[mflorell]

Thanks for the great suggestions.

Would you have time to do some tests on some scripts like the FastAGIlog script(we don't use call_log.agi anymore) to see if there are no issues with accuracy and if performance increases doing the caching as you suggest?

Do you have any reference material on MySQL caching and how it works (web links)?

As for comments, just show me how you would like them done and I will try to set aside some time to comment the whole database.

[mcargile]

Sure we can make the changes to FastAGIlog and check for performance and accuracy changes, though I do not know how soon we can get on that with our current work load.

References:
http://dev.mysql.com/tech-resources/art ... ments.html
http://www.perl.com/pub/a/1999/10/DBI.html

I did hear word that there were some bugs with cached statements in MySQL 5.1 but they have been recently fixed (March 15th 2007). Also I do not use PHP much and am having a hell of a time finding how to do this in PHP.

As for the comments in the DB when I get a moment I will fire up our test system and try and figure out how to do this. It is pretty easy to do in phpMyAdmin apparently so I will just just do it there and get the statements to do it.

[mcargile]

Adding a comment to a field in a table:

Code: Select all

ALTER TABLE `call_log` CHANGE `uniqueid` `uniqueid` DOUBLE( 18, 7 ) NOT NULL COMMENT 'Some Nifty Comment'


Replace "Some Nifty Comment" with what ever you want for a comment just be careful putting quotations in there.

Things we would like to see in the comments would be:
1. What is it
2. Sample values and its meaning (ie "2007-04-10|1234 = date|agent_id"
3. How does it get in there (ie "VDADtransfer updates this when call is transfered")
4. What uses it (ie "VDADtransfer checks this when call is transfered")

I realize that some of these fields could have 5 pages written about them, try and be short and sweet.

[mflorell]

Looking into column comments some more there doesn't seem to be an easy way to display them in MySQL 4.0(the lowest compatible version for use with VICIDIAL). Also, there seem to be many bugs associated with using column comments up through the 5.0 tree.

There is also a character limit of 255 for all column comments.

I think it might be better to just put comments into another kind of document than try to get this working with all of the little differences in different MySQL versions.

How about a simple DHTML page with the "create table" on one side and a description frame on the other side that you could click through? This would be a lot easier to read and search through and would probably take the same amount of time to implement.

[mcargile]

sounds good by me