? ?
brad's life [entries|archive|friends|userinfo]
Brad Fitzpatrick

[ website | bradfitz.com ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

MySQL upgrade [Jun. 25th, 2000|08:37 pm]
Brad Fitzpatrick
[Tags|, ]

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).
LinkReply

Comments:
From: (Anonymous)
2000-06-25 10:48 pm (UTC)

Ouch!

Reading that post is painful for a non-techie. :-)
(Reply) (Thread)
From: demo
2000-06-25 11:29 pm (UTC)

postgres

you might look at postgres as an alterative to mysql and see if it can handle shit better. heh i just browsed the postgres www site and found a link that is reasons not to use MySQL you might check it out for when lj is converted over to php4.

jim
(Reply) (Thread)
From: demo
2000-06-25 11:31 pm (UTC)

Re: postgres

oops damn frames.. here is the REAL LINK

WERD
JIM
(Reply) (Parent) (Thread)
[User Picture]From: bradfitz
2000-06-26 01:03 am (UTC)

Re: postgres

I use Postgres on another project. I'm well aware of how much MySQL sucks. :-) The reason I'm using it is because:

1. It's fast for simple queries, which LiveJournal mainly uses. (mostly because I have to, without killing MySQL ... except the new security features require a LEFT JOIN in all /users/* page views, which sort of sucks ... I need to rework things)

2. It was all I had access to when I first started LiveJournal several years ago, and it's just kinda stuck. (upgrading versions of the same database is hard enough... changing your entire database back-end is a huge project)

I'm just really careful what I do with MySQL, and track new versions in hopes that a lot of its short-comings will be fixed...

I'd go Postgres, but it's just too slow. However, if I had an Oracle or MSSQL license, I might consider a switch. Oracle moreso than MSSQL, because the remote management of 2000 scares me ... VNC is about the only option, and if I'm stuck somewhere on a slow link it's painful.


*shrug*

(Reply) (Parent) (Thread)
From: (Anonymous)
2000-06-26 11:26 pm (UTC)

Re: postgres

actually, you can check out remotelyanywhere (http://www.remotelyanywhere.com/) if you want something powerful and lightweight for dealing with nt remotely
(Reply) (Parent) (Thread)
[User Picture]From: bradfitz
2000-06-26 11:27 pm (UTC)

Re: postgres

Do I have to pay for it?
(Reply) (Parent) (Thread)