On Wed, Jan 15, 2003 at 11:24:05AM -0800, Jeff Kilbride wrote: > > > > By "updated" you mean inserts? Or are you doing a table scan for each > > update (since they're not indexed)? > > No, they're actual updates (UPDATE table SET field=value WHERE > primary_key=value2). The field being updated is not an index, so I'm > assuming the index files aren't changing -- which, in general, > should be faster than a case where an index is changed. (right?) > However, I'm using the primary key in the WHERE clause to find the > correct record to update -- so it's not a table scan for each > record.
Ah, good. I mis-understood what you were saying about indexes. Cool. > > Yeah, 5 seconds is the ext3 default. You can tune it. I recently saw > > someone suggest this: > > > > # set disk flush to 30,000 clicks or 5 minutes > > echo "30 64 64 256 30000 3000 60 0 0" > /proc/sys/vm/bdflush > > > > But have not tried it myself. > > What's a "click"? I've seen some other suggestions for bdflush, also > -- but I've seen other articles that say the defaults are pretty > good and playing with these numbers could cause more harm than > good... I haven't come across anything definitive that deals with > tuning ext3. I'm tempted to try mounting the DB drive as ext2, to > see what difference it makes. Yeah, I'd suggest diabling the journal and see what happens. I belive you can use "tunefs" to do that. I'm a ReiserFS person myself, so this is mostly second-hand info. That's why I don't know what "clicks" are in that comment either. > Apache/PHP is also running on this box and accessing the database, > but the load is 0.00 until I run the update script -- then the load > jumps to anywhere between 2 and 5. If I switch modes with mytop, I > see something like this: > > 233 > 245 > 218 > 158 > 2 > 120 > 250 > 235 > 195 > 4 > etc... > > Which makes me think the slowdown has something to do with the > journal writes. I've seen numbers as high as 2000 in mytop > consistently over 3 or 4 seconds, and more than once while the > script runs, but I don't know why I'm getting these huge bursts of > speed intermittently. This isn't a huge problem, I'm just puzzled > that I can get such high numbers when my average seems 10 or 20 > times less. Is this normal? Hmm. Yeah I'd expect to see something a bit more even than that. > Is there any way to optimize large numbers of UPDATES with MySQL, > like you can INSERTS? I don't think so. A bulk-update syntax would be interesting... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 31 days, processed 1,043,772,320 queries (381/sec. avg) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
