Question on processing delayed proxy packets

[Patric]

Greetings all,

Finally getting my system running nice and smoothly :)

I have a scenario I would like some opinions on, something to think about...

Lets say I have server A and server B getting requests from multiple 
sources. They proxy these requests to each other as well. Consider the 
following scenario:

server A gets a start record at 08h00, and proxies it to server B 
immediately, so server A and server B each have an entry with start time 
08h00.

An hour later server A gets an interim update acct_input_octets = 5. The 
proxied packet is delayed due to a network issue.

Another hour later server _B_ gets an interim update acct_input_octets = 
7. It proxies the request and server A is updated immediately, so now 
server A and server B have an entry with start time 08h00 and 
acct_input_octets = 7.

Great, all is right at this point. Then:

The delayed interim update (which has acct_input_octets = 5) from server 
A finally gets through to server B, and server B processes the packet 
using my "accounting_update_query" query, which is formatted as follows:

         accounting_update_query = "UPDATE ${acct_table_new} \
            SET \
            framed_ip_address = '%{Framed-IP-Address}', \
            acct_session_time = '%{Acct-Session-Time}', \
            x_ascend_xmit_rate = '%{X-Ascend-Xmit-Rate}', \
            x_ascend_data_rate = '%{X-Ascend-Data-Rate}', \
            acct_input_octets = '%{Acct-Input-Octets}', \
            acct_output_octets = '%{Acct-Output-Octets}', \
            acct_input_gigawords = '%{Acct-Input-Gigawords}', \
            acct_output_gigawords = '%{Acct-Output-Gigawords}' \
            WHERE \
            acct_session_id = '%{Acct-Session-Id}' \
            AND \
            user_name = '%{SQL-User-Name}' \
            AND \
            nas_ip_address = '%{NAS-IP-Address}'"

As you can see, the above query will set acct_input_octets = 5 on server 
B, so now server A has acct_input_octets = 7 and server B has 
acct_input_octets = 5.

Thats the problem.

The solution I am toying with is the following:

If a db entry exists, and the acct_input_octets in the db entry is more 
than the current packet we are processing, then the packet data is older 
than the db data in the record, so we want to ignore the packet and keep 
the db data. (Obviously we will need to apply the check to 
acct_output_octets and the gigaword fields as well...)

So the very first problem we see is that checking the record before 
processing the new update is going to slow down the entire process. The 
best way I can think to handle this is to check the acct_delay_time 
field, and if it is a very small number we assume the record is fresh. 
If the delay time is more than say 30 minutes, we first do the lookup.

This means that *most* requests wont need to do a lookup first, and only 
the heavily delayed ones are then checked.

Im not even sure if it is possible to do this in the current setup, or 
if its possible to do it with a more complex SQL statement, but I would 
appreciate any comments on the idea and any experience others have had 
with this.

Many thanks,
Patric