----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Jeff Kilbride" <[EMAIL PROTECTED]> Cc: "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:41 AM Subject: Re: Optimizing Ext3 for MySQL
> On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote: > > Are there any general guidelines for optimizing ext3 for MySQL? I have a > > perl script that runs 200K + updates into my database once a day and I see > > pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program. > > I've seen in excess of 2000 qps and then seen that number drop to 40 qps. > > The average seems to be about 200 qps, which seems kinda slow given my > > hardware: > > > > Dual P3 1.3MHz > > 1GB RAM > > Dual SCSI drives (160 MB/s) > > RedHat 7.3 > > MySQL 3.23.53a w/MyISAM tables > > This particular table being updated has about 5 million rows. The fields > > being updated are not indexed. > > 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. > > Also, the qps numbers seem to slow down every 5 seconds or so, which I think > > matches the default write timing for the ext3 journal. Can anybody share > > their experience with optimizing ext3 -- i.e. which mode is best (writeback, > > ordered, journal) and any parameters that can be tuned? Any insight is > > appreciated. > > 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. 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? Is there any way to optimize large numbers of UPDATES with MySQL, like you can INSERTS? Thanks, --jeff > 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,040,147,810 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
