Ramblings

As we all know (if you’ve ever used it or heard me complain), the ecommerce process (recording the payment, etc) has been running pretty slow lately. My boss and I recently had a discussion where we agreed we wanted to spend some time fixing this. I put some better logging in place to see where the bottlenecks might be and based on information gathered I have been able to make some upgrades and modifications to enhance the overall performance of the system.

It turned out the the process of sending out emails was really the killer initially. They were taking (cumulatively as there are an average of about 3 that go out per transaction) around one minute on average, and up to 2 or 3 worst case. I have redone the way those emails are being sent and have trimmed that down to an average of under 2 seconds (for all of them). That alone made a huge difference, but it isn’t all
that has changed.

There is a query that I run to determine the journal number (basically a batch number) of the transaction that was just posted. It turns out that it was taking anywhere from 3 – 10 seconds to run. Upon further investigation, the query wasn’t making use of any of the indexes on the table (and it’s a large one, so many, many records). I made a small modification there that reduced its run time to under a second.

Right now I have good data on about 32 transactions that have occurred since I made the bulk of the changes on Tuesday and Wednesday. Here’s a breakdown of the numbers and how they fall into a Service Level Agreement that we are working up:

• Trad Student Account Payments: 7 @ 18, 34, 8, 8, 5, 13, 10 seconds
• Non Trad Student Account Payments: 12 @ 6, 9, 7, 5, 4, 3, 4, 6, 9, 9, 12, 11, 5 seconds
• Non Trad Web Reg Payments: 13 @ 7, 7, 5, 8, 8, 7, 7, 8, 9, 16, 6, 7, 8 seconds

The Service Level Agreement (SLA) is currently defined as the following:

• 50% of the transactions should occur within 5 seconds
• 90% of the transactions should occur within 10 seconds
• 98% of the transactions should occur within 20 seconds

When you look at the numbers I provided previously, we are currently short of that SLA:

• 5s target: Need 16, only have 7 for a real percentage of 21.88%
• 10s target: Need 29, only have 26 for a real percentage of 81.25%
• 20s target: Need 31, have 31, but only have a 96.88% (rounding)

Only the 20 second period is currently within limits. We aren’t far off on the others, but we aren’t done with performance enhancements either. When you look at the averages, things look a little better:

• Max: 34 seconds (fluke, very high system load at the time)
• Min: 3 seconds
• Average: 8.75 seconds
• Median: 7.50 seconds

These numbers are with the posting process (what actually applies the payment to the account) still being a part of the total time. We’ve discussed having that process run sometime after a payment has been made, but within a minute or two of the actual payment. If we go that route, we should be able to subtract at least 80% of the time that we are currently seeing (as far as the student is concerned).