delayed PayPal payment and subscription drive

During the subscription drive last week, I used PayPal to

subscribe to photo.net. Apparently, PayPal did not process the

payment for almost a week. I'm curious whether photo.net (and

I) get the benefit of Sandy's generous donation matching offer

even if PayPal's delay causes the subscription to begin after the

drive has concluded.

Hi, Mike. First thanks for participating in the subscription drive.

 

When people pay via PayPal and use a credit card, just about 100% of the time, we get a notification from PayPal within seconds that the transaction is "completed", and the software processes this and gives the icon. To most people, it looks immediate.

 

Sometimes, people use an e-Check, or for some other reason, PayPal notifies us of the transaction, but says that it is "pending". Later on, usually around 3 days, we will get a notification that the transaction is "completed".

 

Last Monday, I added the bonus time, courtesy of Sandy, for all the PayPal transactions that were already completed. But my script didn't apply to the transactions that were still pending on Monday, since those people didn't have any enrolled subscriptions to extend yet. But most of those were indeed "completed" over the subsequent days, and people now have their icons (but not their bonus time).

 

Meanwhile, we had also received about 20 mailed-in checks by Wednesday, and there are probably a few more waiting in the PO Box by now. So, I'm planning to pick up the remaining checks from the PO Box tomorrow, and to do the bonus time for all the "pending-now-completed" PayPal transactions and the mail-in subscriptions in one fell swoop tomorrow.

 

Meanwhile, Sandy has already sent us the matching funds based on the PayPal transactions (both pending and completed), and today I am getting quotes for our shiny new database server, which I hope to be able to order in the next couple of days. I am very much hoping to have it in production before the end of January, earlier if possible. It will be just in the nick of time because the database performance the last week or so has been really bad, and I have had to restart the server several times.

 

(One of the things that is really killing the performance is that many queries that were fine before have not scaled. The worst right now are Unified View and the Community Member contributions, especially for people who have posted a lot. These are really slow, and to make matters worse, when people don't get their Unified View or whatever right away, they click again, which fires off another instance of the same query. I have to figure out what to do about these because I don't think just throwing hardware at it is going to be a long-term solution.)

Thanks for the quick explanation. Looking forward to the

upcoming upgrades in site performance . . .

It seems like it would be a relatively simple matter to cache the unified view, if that's a significant problem. The cache could be invalidated any time somebody creates a new thread.

Brian,

 

I'm assuming that the unified view does a lookup of the entire database table using a where clause to pull in the threads from the forums the user selected for the last X days.

 

There are a couple of ways to increase the performance of such querries. You could partition the table in date ranges so you're esentially doing lookups of one or two smaller subests of the entire table.

 

What might be more efficient is to create a duplicate table that mimics the table(s) used for the forums to create a short term db cache. I don't remember the table structures but you'd just have to add an entry whenever a question is asked or answered. You could set up triggers on the main message table to update the cache table so that there wouldn't need to be any changes made to the tcl code except for the display of the unified view list. Then at the end of each day purge the ones that are older than X days. It probably doesn't even need to be a complete mimic of the table but could just be the author info, date posted, date last response, subject, forum id, etc. Basically just the header information and anything needed for sorting for "New Answers". I'm not an expert in oracle administration but I do believe that you can set specific tables up to be contained entirely in memory if it allows. Even if the new hardware improves performance I would recommend something like this just to save cycles for other features.

 

You may want to even do something else like have the table hold date for the last 60 days but only show the unified view for how ever many days you're showing now. Then in the community member page, instead of showing the last 5 (or how ever many it is now) forum postings for the users show the last 5 postings in the last 60 days. You'll quering data from a much smaller table. What's probably also killing performance now is the count(*) to get the total number of forum postings. That might not be necessary or can be optimized. The count might be better implemented as a field in the user table that is updated with postings and decrememnted with deletions and expirations of threads.

 

Having another look at the query to see where you can optimize it as well as any indexes on the table (too many, not enough, not the right ones) could help without having to do any major coding.

One of the things that makes the Unified View not cachable is that everybody has their own selection of forums. However, it is possible to cache an intermediate result which is all the threads which could appear in somebody's Unified View. This is many fewer than all the threads, and computing it and caching it seems like it should be a significant gain of time.

 

A couple of months back, I experimented with creating a materialized view consisting of this intermediate state and generating the UV from that instead of the bboard table. When I put this idea into production to test it under load, it didn't work very well. In fact, the database fell apart right away, and I took it out. I haven't had a chance to go back and work on it more. In retrospect, I've realized that the database was falling apart regularly around that time, and it could have been just a coincidence. I am thinking of just trying the same thing again.