Just wondering ... does anybody know what locking strategy Summitpost is using? From the behavioral characteristics, I believe that the performance problems are caused by locks. One possible cause for having too many locks would be the use of 'pessimistic locking', especially if the database doesn't automatically release locks that are held for too long. If this is the case, changing to 'optimistic locking' could solve a lot of the performance problems.
In short, a process having a lock can cause another process to wait, and so you do not want a process to have any kind of lock on any object in the database while waiting for user input
. On the other hand, a process can have locks during the insert, update or delete, as long as the first lock is acquired only after the submit button is pressed, and all locks are released as soon as possible - which, for most processes, means that no lock is held longer than a fraction of a second.Introduction to Concurrency Control
gives a good explanation of the two locking strategies.
Mind you, it may sound like a simple change, switching from pessimistic to optimistic locking, but it can be a whole lot of work. It's not just a switch you can flip: the programming code has to be changed. But the main performance issue seems to be the process of posting pictures, so changing only part of the program might suffice.
Then again, I might be on the wrong track here, and locking issues might not be the cause of the performance issues after all ...