Kiefer Thomas wrote:Page load pretty good, Matt. Seems to work fine.
OK good. I think so too.
Kiefer Thomas wrote:Is there a difference in the 'coding end of things' between uploading content vs. clicking previously uploaded content?
If I recall correctly, no, the code is essentially the same, but it may be worth looking at again. You're noticing longer times to edit existing content than simply creating new content? If it's repeatable, then it would be worth investigating.
The root of the problem is that in either case (modifying content or creating new content), the `objects` table in the database has to be written to. The `objects` table and the `users` table are the only two tables left in the database that use MyISAM storage engine. All of the rest of the tables in the database have been moved to the InnoDB storage engine. The key difference between the two storage engines that is causing the delays is that the MyISAM storage engine locks at a table level when doing writes, while the InnoDB locks at a row level.
That means that anytime someone is writing to either MyISAM table (objects or users), they must wait for all of the reads to complete before the table can be locked and the write is completed. And every time someone loads a page on SP, they're reading from the MyISAM `objects` and `users` tables. So writes are delayed until all pending reads are completed, which during high traffic times could be quite a wait.
The solution to the problem is to move the `objects` and `users` tables to a storage engine that allows simultaneous reads and writes (the InnoDB engine in MySQL). The problem with that is that the InnoDB engine doesn't support fulltext search, so if those tables were switched to using the InnoDB storage engine, end users couldn't search for users or objects by name which would be unacceptable. So to fix this problem gets very involved and complicated. There are ways around having tables with fulltext indices while maintaining a way to search the entries of the table, but it requires a significant change to site structure (database and code). I want to do it sometime in the near future, but I don't currently have the time to invest in such a large project. I'm hoping that I will sometime this winter though.