||[Jun. 25th, 2000|08:37 pm]
I've analyzed the situation w/ LiveJournal's bottlenecks and I'm pretty confident the fault lies in the database. In particular, MySQL is blocking way too often. The old ISAM table format doesn't support concurrent inserts and selects, so whenever somebody goes to update their journal around the same time people are viewing journals, things slow down, and then all the users hit reload like crazy, and makes the situation ten times worse. I've confirmed that this is the source of the locking by looking at the logs and checking the current queries using mysqladmin. I've done all the little tricks and optimizations I can to my queries, and using temporary tables and whatnot, but MySQL 3.22.x just isn't cutting it. I've been monitoring the progress of the 3.23.x series for awhile, and although it's still officially in alpha, they recognize on their site they're about to go beta then release pretty soon, and it passes all the standard compliance tests, so I think it's time to make the switch and keep LiveJournal alive. Once the new database is installed I'll make backups of the old (current) raw .ISD tables, then do an ALTER TABLE on the tables I'm having problems with, and see how things improve. MySQL is building now... it should be done in a few minutes or so, then I'll be taking LJ down for a few minutes (like anybody will notice, with it locking up so much lately anyway).